Preparing Optimizing MySQL Configuration talk for Percona Live in Washington,DC I noticed how many Variables did MySQL get over years – it is pushing 400 these days even if we do not count variables/options which do not have matching SHOW VARIABLES value, and settings done by changing tables rather than command line options (like much [...]
Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
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 [...]
InnoDB memory allocation, ulimit, and OpenSUSE
August 23, 2010 By 7 Comments
I recently encountered an interesting case. A customer reported that mysqld crashed on start on OpenSUSE 11.2 kernel 2.6.31.12-0.2-desktop x86_64 Â with 96 GB RAM when the innodb_buffer_pool_size was set to anything more than 62 GB. I decided to try it with 76 GB. The error message was an assert due to a failed malloc() [...]
FlashCache: more benchmarks
May 18, 2010 By 15 Comments
Previously I covered simple case with FlashCache, when data fits into cache partitions, now I am trying to test when data is bigger than cache. But before test setup let me address some concern (which I also had). Intel X25-M has a write cache which is not battery backuped, so there is suspect you may [...]
How much memory Innodb Dictionary can take ?
May 6, 2010 By 3 Comments
The amount of memory Innodb will require for its data dictionary depends on amount of tables you have as well as number of fields and indexes. Innodb allocates this memory once table is accessed and keeps until server is shut down. In XtraDB we have an option to restrict that limit. So how much memory [...]
Beware: ext3 and sync-binlog do not play well together
January 21, 2009 By 19 Comments
One of our customers reported strange problem with MySQL having extremely poor performance when sync-binlog=1 is enabled, even though the system with RAID and BBU were expected to have much better performance. The problem could be repeated with SysBench as follows:
1 | ./sysbench --num-threads=2 --test=oltp --oltp-test-mode=complex --oltp-table-size=100000 --oltp-distinct-ranges=0 --oltp-order-ranges=0 --oltp-sum-ranges=0 --oltp-simple-ranges=0 --oltp-point-selects=0 --oltp-range-size=0 --mysql-table-engine=innodb --mysql-user=root --max-requests=0 --max-time=60 --mysql-db=test run |
More patches
November 11, 2008 By 2 Comments
After some pause we are going to announce bunch of patches we made and ported for last period. Ported patches (ported from Google V2 patch): – innodb_fsync_source.patch – Show information about callers of fsync, more info – innodb_io_tune.patch – Port of two patches InnodbIOTune and InnodbAsync, more info – innodb_extra_status.patch – Show more information about [...]
MySQL, AIX5L and malloc()
November 3, 2008 By 11 Comments
Some time ago I get brand new IBM POWER6 server as the replacement for “old” P5 used to host Oracle database. Because we planed to use advanced virtualization with VIOS + LPAR/DLPAR I conceived the idea to use one spare partition for MySQL tests. Because I had no past experience with it and there is [...]
Wanted: Better memory profiling for MySQL
May 18, 2008 By 4 Comments
Quite frequently I would log in to customers system and find MySQL using too much memory. I would look at memory consumed by Innodb (it is often higher than innodb_buffer_pool_size) substract memory used by other global buffers such as query_cache_size and key_buffer and will in many cases see some mysterous memory which I can’t really [...]
MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
Few days ago I wrote about testing writing to many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables so I’ve got the script and ran tests for MyISAM and Innodb tables on ext3 filesystem. Here is what I found:
