Recent scalability fixes in InnoDB and also Google's and your SMP fixes almost made InnoDB results acceptable in primary key lookups queries, but secondary indexes were forgotten for some time. Now having Dell PowerEdge R900 on board (16CPU cores, 16GB RAM) I have some time for experiments, and I played with queries

against table

with table size 1mil records fully fitting in memory. I run it with innodb_thread_concurrency=16 to match threads inside InnoDB with count of cores.

Results for InnoDB-plugin-1.0.2 were kind of discouraging, dropping down just after 8 connections, si I decided to test MySQL-5.0.30 with standard InnoDB. It was better, but still far from what we can expect.

After investigation Yasufumi pointed to page_hash mutex, which was abused. This mutex is used as mutex even in places where shared read lock is enough - and we replaced page_hash mutex to page_hash read-write lock.

The new results you can see on graph.

The patch for page_hash will be included in next release of XtraDB, and expect new results.

for reference InnoDB was run with next parameters:

Entry posted by Vadim | No comment

Add to: | | | |

After several important fixes to our patches we made binaries for build12.

Fixes include:

Control of InnoDB insert buffer to address problems Peter mentioned http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/, also check Bug 41811 to see symptoms of problem with Insert buffer.

http://www.percona.com/docs/wiki/patches:innodb_io_patches

* innodb_flush_neighbor_pages (default 1) - When the dirty page are flushed (written to datafile), this parameter determines whether the neighbor pages in the datafile are also flushed at the same time or not. If you use the storage which don't have “head seek delay” (e.g. SSD or enough Write-Buffered), 0 may show better performance. 0:disable, 1:enable

* innodb_ibuf_max_size (default [the half of innodb_buffer_pool_size](bytes)) - This parameter is startup parameter. If the lower value is set than the half of innodb_buffer_pool_size, it is used as maximum size of insert buffer. To restrict to the too small value (e.g. 0) is not recommended for performance. If you don't like the insert buffer growing bigger, you should use the following parameters instead. (* If you use very fast storage, small value (like several MB) may show better performance.)

* innodb_ibuf_accel_rate (default 100(%)) - This parameter is additional tuning the amount of insert buffer processing by background thread. Sometimes, only innodb_io_capacity is insufficient to tune the insert buffer.

* innodb_ibuf_active_contract (default 0) - By default (same to normal InnoDB), the each user threads do nothing about contracting the insert buffer until the insert buffer reaches its maximum size. 1 makes the each user threads positive to contract the insert buffer as possible in asynchronous.

Second important fix introduces variable use_global_long_query_time, which allows all current threads see change of long_query_time. By default value set in SET GLOBAL long_query_time=N command is visible only on new established connection, which is problem if you have pre-established connection pool, say in Java or Ruby on Rails application. With use_global_long_query_time=true even all current threads will respect SET GLOBAL long_query_time=N. The feature made for EngineYard, hosting provider for Ruby on Rails application.

You can download binaries (RPMS x86_64) and sources with patches here
http://www.percona.com/mysql/5.0.75-b12/

Entry posted by Vadim | No comment

Add to: | | | |

How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they're all the same and not calculate the result for each of them?

I had a specific case where I needed to find out for sure, so I made a little benchmark. The query looks something like this:

I wrote a bash script to execute the query 500 times and redirect the results to /dev/null. Results: 31s wall-clock time.

The following is an alternative query that returns the same result:

This query runs 500 iterations in 12s. So it looks like the optimizer was computing the SUM() 18 times in the first query. (A response on the internals mailing list agreed with this finding. Thanks Konstantin.)

Along the same lines, I benchmarked POW() and found that squaring a number is more than twice as fast to do with ordinary multiplication as with POW(). Does this matter? Sometimes... we had a client who was doing vector math in a many-dimensional space, and the POW() seemed to be taking a pretty significant amount of time; changing to ordinary multiplication made the query faster. (It was not the ultimate suggestion though.)

Entry posted by Baron Schwartz | No comment

Add to: | | | |

建设大坝意味着能给陆地带来能源和洁净用水，但是，最贫穷的人们却无法分享到这些成果。科利娜•霍塔和洛丽•波廷格报道。

莱索托王国是一个山地小国，四周被南非环抱。该国水源缺乏，几乎连自身的用水需求都无法满足，旱灾频发，大部分人口都无法获得清洁的用水。但是，这样一个国家十年前竟然成了水出口国。2007，联合国不得不呼吁对莱索托进行紧急救援，包括为居民和牲畜提供水源，以缓解日益加剧的人道主义危机。

莱索托成为水出口国，是在卡齐大坝（它是巨大的莱索托高原调水工程的第一步）建成之后，开始通过数英里长的山间水渠向南非的工业心脏地带送水。这个项目由世界银行发起，其理论基础就是：一个水源丰富的国家可以通过向缺水的邻国出口水来赚钱，用于脱贫项目。工程的实施主要靠世界银行的财政支持，但同时其他机构（包括英国政府的出口信贷担保署，ECGD）也参与了投资，在这个巨大工程的第一期中，共为其筹资35亿美元。

然而，原来向莱索托承诺的发展收益却踪迹渺茫。实际上，工程已经给该国造成了严重的影响。脆弱的高山环境已经退化，本土的鱼类和植物种群走向灭绝。工程导致高原上至少27，000人失去了家园、田地和其他赖以为生的资源。此外，由于河水流量减少，下游还有15万人的饮用水、耕种和捕鱼都受到影响。

本来，水源出口的收入将被放进一项发展基金，让大家“分享财富”。但是，就连基金本身都被卷入了政治和赞助者的烂摊子，不得不在2003年停办。

在工程合同中有一项条款，就是决不能让人们因为工程变得更加贫困。但是，补偿工作非常不到位，人们失去的生计并没有得到恢复。失去家园的人们生活赤贫，安置问题却悬而未决。另外，由于成千上万的外国大坝建设工人带来了艾滋病，当地人极易受到侵害，并且由此产生了越来越多无依无靠的孤儿。

“他们承诺说会改善我们的生活，但正好相反，我们的尊严被践踏了。”当地莱索托水坝幸存者组织（SOLD）的一位成员说。 

和其他大型基础设施项目一样，在这个工程中，贿赂行为十分普遍。有多家著名公司被发现向工程官员行贿以获得有利的合同。然而，和其他贿赂案件不同的是，这个案子竟然出现了以弱胜强的结果——莱索托法院裁决数家国际咨询和工程公司参与贿赂。但是，对于自己的国民涉嫌贿赂，工程的西方赞助者们竟显得深以为然。[2008年11月，英国严重欺诈办公室停止了对一所英国公司在莱索托涉嫌腐败的调查。]

非洲的大坝建设史充满了悲剧。许多大坝都引起了债务、政治不公和普遍的环境退化。同时，它们也剥夺了小型开发项目的资源，而这些项目是更加适宜的。现在，全世界很多国家都在计划修建更多的大型水坝，在背后支持的有西方的赞助人，还有越来越多的中国、巴西和印度投资者。

备受争议的布加哥利大坝位于乌干达境内的尼罗河上，坝址是一个风景秀丽的瀑布。这座正在建设中的大坝投资8亿美元，得到了世界银行的私人企业组织——国际金融公司(IFC)以及欧洲投资银行(EIB)的支持。除了对濒危鱼类以及当地社区的影响，大坝的批判者们还质疑它是否有能力提供大家都买得起的能源。“项目的高成本将使得农村的用电资金更加捉襟见肘。”弗兰克·穆拉姆齐说，他是全国专业环保论者联合会(NAPE)的负责人，该组织设在乌干达首都坎帕拉。“乌干达的电已经是本地区最贵的了，近来电费又涨了一倍还多。供电市场本来就很有限，这下用电的人就更少了。”

研究表明，全球变暖将减少维多利亚湖的流出水量，影响布加哥利大坝的水力发电能力，使这个项目的可行性变得更低。

在莫桑比克，中国和巴西可能联合在赞比西河上修建一座新的大坝。一旦大坝开建，赞比西河的恢复计划就会付诸东流。这项计划力图通过释放现有水坝的蓄水，增加河水的自然流量，来恢复赞比西河三角洲及其丰富的鱼类、野生动物活动区和农田。

但是，所有项目中最耀眼的明珠还是大因加，它吸引了全世界企业的目光。这将是世界上最大的水坝工程，位于刚果民主共和国(DRC)境内。大因加的总造价估计高达800亿美元，在这个非洲最不稳定的地区之一，它将成为腐败滋生的温床。但是，这个造价中并没有包含电力分配网络的预算，而这正是长期饱受能源短缺之苦的该国人民所急需的。大因加项目的主要受益者是采矿、木材和其他工业，贫苦的农民和渔民则会变成受害者，他们依靠河流和肥沃的沉积土壤谋生，而这些都会受到大坝的影响。

要减少贫困，买得起的能源和清洁的水源至关重要。但是，我们丝毫看不出这些大型水坝可以满足上述核心需要。更让人沮丧的是，在现有状况下，在分享全球水坝建设这块蛋糕的竞争中，穷人完全被扔到了一边。

作者简介：科利娜·霍塔，美国非营利组织“环境保卫”经济学家；洛丽·波廷格，国际河流活动团体非洲项目经理。

首页图片henribergieus摄

来源：http://environment.guardian.co.uk/

卫报新闻传媒有限公司2008年版权所有

from:baozi

This article is part of on-going Software For Geeks series. PuTTY is hands-down the best, free, and lightweight SSH client for Windows. I have provided list of 12 powerful PuTTY add-ons with screenshots, that will solve few shortcomings of the original PuTTY. Play around with these add-ons and choose the one that suites your need.

1. PuTTY Connection Manager

PuTTYCM gives a nice feature to arrange several PuTTY sessions in tabs . While starting PuTTYCM for the first time, you should specify the location of the original PuTTY. This requires .NET 2.0 to be installed on the windows system. Following screen-shot displays three putty sessions in tabs within the same window.

Note: If the PuTTY Connection Manager opens the original PuTTY in a separate window, instead of opening as a TAB, please go to Tools -> Options -> Select the check-box “Enable additional timing for PuTTY capture (ms)” -> set the value to 300 ms. This will open the PuTTY window inside the TAB as shown below.

2. PuTTYcyg

Cygwin users will absolutely love PuTTYcyg. This lets you use PuTTY as a local cygwin terminal. If you use cygwin on your windows, I’m sure you’ll hate the default MS-DOS looking cygwin window. Using PuTTYcyg, you can run cygwin inside PuTTY. I love this add-on and use it for my cygwin on Windows.

On PuTTYcyg, click on cygterm radio button in the Connection type. Enter - (hyphen) in the “Command (use - for login shell“, to connect to the cygwin on the windows laptop using PuTTY as shown below.

3. PuTTYtray

Using PuTTYtray, you can minimize the PuTTY window to the system tray on windows. By default, original PuTTY stores the session information in the registry. This is painful, when you want to transfer PuTTY sessions from one laptop to another. In PuTTYtray, there is an additional radio button “Sessions from file” as shown below, that will let you store session information in a file.

4. PuttyTabs

PuttyTabs provides a floating bar, that will display the open PuTTY sessions in TABs. Clicking on one of the tabs will bring the respective PuTTY session to the foreground. While starting PuTTYTabs for the first time, you should specify location of the original PuTTY. It reads the windows registry to get all the available PuTTY sessions. This also requires .NET 2.0 to be installed on the windows system. Following screen-shot displays three putty sessions arranged in tab.

5. Quest PuTTY

Quest Software modified the PuTTY to add Active Directory (GSSAPI Kerberos) single sign-on feature. Quest PuTTY uses Microsoft’s Security Service Provider Interface (SSPI), which is Microsoft’s version of the GSSAPI, with which it is wire compatible. This version of PuTTY adds a new menu-item called GSSAPI, under Connection -> SSH, as shown below.

6. Modified PuTTY

This modified PuTTY stores the PuTTY sessions in folder instead of storing it in the registry. If you already have sessions stored in the registry, it will display those also. The sessions stored in registry will be marked as [registry] as shown below. When you create a session using this PuTTY, this creates a sub-folder called session in the same folder where putty.exe is located, to store all the sessions in the file.

7. PocketPuTTY

PocketPuTTY runs on Windows Mobile 2003/5.0 operating system. After I got my blackberry, I have dumped my Dell Axim that was running on Windows Mobile. So, I have not tried PocketPuTTY myself. If you’ve used PocketPuTTY or other mobile version of PuTTY, please leave your feedback.

8. portaPuTTY

portaPuTTY is a modified version of the PuTTY that stores the session information in a file by default, instead of storing it in the windows registry. The session files are stored under .putty/sessions folder. The .putty folder is created under the same directory where the putty.exe is located.

9. PuTTY Portable

PuTTY Portable is part of PortableApps suite. Use this to launch PuTTY from the USB drive and carry the sessions along with you.

10. PuTTY Launchy Plugin

If you are using Launchy, the open source keystroke launcher for windows, you can use Putty Launchy Plugin, to launch putty sessions from Launchy very easily. i.e you can type “ssh” or “putty” followed by tab or space to list all of your PuTTY sessions. Once you select a particular session, Launchy will automatically launch that particular PuTTY session.

11. PuTTY Session Manager

PuTTY Session Manager will let you organize the PuTTY sessions into folders and assign hotkeys. This requires Microsoft .NET 2.0. Right click on the PSM icon in the system track and select “Session Hotkeys” to assign hot-keys for PuTTY session as shown below.

To create a folder, right click on a particular PuTTY session -> Session Management -> New Folder. To move a existing session to a folder, just drag the session and drop to the corresponding folder.

12. PuTTY Command Sender

PuTTYCS is very helpful little tool that can boost your productivity by eliminating repetitive tasks performed on different servers. Using PuTTYCS, you can send a unix command to multiple PuTTY windows at the same time. You can use this to backup files, view log files, start and stop processes, copying file etc., on multiple servers, just by executing the command once, as shown below.