The parameter sort_buffer_size is one the MySQL parameters that is far from obvious to adjust. It is a per session buffer that is allocated every time it is needed. The problem with the sort buffer comes from the way Linux allocates memory. Monty Taylor (here) have described the underlying issue in detail, but basically above [...]
Percona Launches New Support Option for MySQL
October 5, 2010 By 9 Comments
We’ve just announced a new support offering for MySQL. There’s a press release here, and product information page here. But what does this new service really mean for you, in practical terms? This is actually important — it will open up a range of new choices for you. I’ll explain two major points that matter [...]
Scaling: Consider both Size and Load
July 13, 2010 By 5 Comments
So lets imagine you have the server handling 100.000 user accounts. You can see the CPU,IO and Network usage is below 10% of capacity – does it mean you can count on server being able to handle 1.000.000 of accounts ? Not really, and there are few reasons why, I’ll name most important of them: [...]
InnoDB, InnoDB-plugin vs XtraDB on fast storage
January 13, 2010 By 13 Comments
To continue fun with FusionIO cards, I wanted to check how MySQL / InnoDB performs here. For benchmark I took MySQL 5.1.42 with built-in InnoDB, InnoDB-plugin 1.0.6, and XtraDB 1.0.6-9 ( InnoDB with Percona patches). As benchmark engine I used tpcc-mysql with 1000 warehouses ( which gives around 90GB of data + indexes) on my [...]
Star Schema Bechmark: InfoBright, InfiniDB and LucidDB
January 7, 2010 By 48 Comments
In my previous rounds with DataWarehouse oriented engines I used single table without joins, and with small (as for DW) datasize (see http://www.mysqlperformanceblog.com/2009/10/02/analyzing-air-traffic-performance-with-infobright-and-monetdb/, http://www.mysqlperformanceblog.com/2009/10/26/air-traffic-queries-in-luciddb/, http://www.mysqlperformanceblog.com/2009/11/02/air-traffic-queries-in-infinidb-early-alpha/). Addressing these issues, I took Star Schema Benchmark, which is TPC-H modification, and tried run queries against InfoBright, InfiniDB, LucidDB and MonetDB. I did not get results for MonetDB, will [...]
How many partitions can you have ?
December 5, 2009 By 19 Comments
I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most [...]
table_cache negative scalability
November 16, 2009 By 23 Comments
Couple of months ago there was a post by FreshBooks on getting great performance improvements by lowering table_cache variable. So I decided to investigate what is really happening here. The “common sense” approach to tuning caches is to get them as large as you can if you have enough resources (such as memory). With MySQL [...]
Tokyo Tyrant -The Extras Part III : Write Bottleneck
November 12, 2009 By 3 Comments
This is part 3 of my Tyrant extra’s, part 1 focused on durability, part 2 focused on the perceived performance wall. #3. Tokyo Cabinet Can have only a single writer thread, bottlenecking performance When writing an application using Tokyo Cabinet only one connection can be opened as a “writerâ€Â while the rest are readers. Tyrant [...]
MySQL-Memcached or NOSQL Tokyo Tyrant – part 2
October 16, 2009 By 8 Comments
Part 1 of our series set-up our “test” application and looked at boosting performance of the application by buffer MySQL with memcached. Our test application is simple and requires only 3 basic operations per transaction 2 reads and 1 write. Using memcached combined with MySQL we ended up nearly getting a 10X performance boost from [...]
SystemTap – DTrace for Linux ?
September 14, 2009 By 17 Comments
Since DTrace was released for Solaris I am missing it on Linux systems… It can’t be included in Linux by the same reason why ZFS can’t be – it’s licensing issue. Both ZFS and DTrace are under CDDL, which is incompatible with GPL. So you can see DTrace and ZFS on Solaris, FreeBSD, MacOS, but [...]
