This post was initially going to be two sets of polls: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before you read on, ponder those questions and come up with your own answers… and see if you’re right or can prove me wrong! [...]
MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
A new Percona Toolkit series has been released: Percona Toolkit 2.2 for MySQL 5.6. Several months in the making and many new features, changes, and improvements make this a great new series. It replaces the 2.1 series for which we plan to do only one more bug fix release (as 2.1.10) then retire. 2.2 is [...]
Testing the Virident FlashMAX II
March 21, 2013 By 1 Comment
Approximately 11 months ago, Vadim reported some test results from the Virident FlashMax 1400M, an MLC PCIe SSD device. Since that time, Virident has released the FlashMAX II, which promises both increased capacity and increased performance over the previous model. In this post, we present some benchmark results comparing this new model to its predecessor, [...]
MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
As part of Percona Remote DBA for MySQL service we recognize that reliable backups are one of the most important things we can bring to the table. In my experience handling emergencies, the single worst thing that can happen is finding out you don’t have backups available when some sort of data loss or catastrophic [...]
MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
So far most of the benchmarks posted about MySQL 5.6 use the sysbench OLTP workload. I wanted to test a set of queries which, unlike sysbench, utilize joins. I also wanted an easily reproducible set of data which is more rich than the simple sysbench table. The Star Schema Benchmark (SSB) seems ideal for this. [...]
MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
As the part of analyzing surprising MySQL 5.5 vs MySQL 5.6 performance results I’ve been looking at changes to default variable values. To do that I’ve loaded the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56 on var55.variable_name=var56.variable_name where var55.variable_value!=var56.variable_value; +---------------------------------------------------+------------------------------------------+------------------------------------------+ | variable_name | value55 | var56 | +---------------------------------------------------+------------------------------------------+------------------------------------------+ | PERFORMANCE_SCHEMA | OFF | ON | | PID_FILE | /mnt/data/sandboxes/msb_5_5_30/data/mysq | /mnt/data/sandboxes/msb_5_6_10/data/mysq | | CHARACTER_SETS_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. | | PERFORMANCE_SCHEMA_MAX_COND_INSTANCES | 1000 | 836 | | PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES | 1000000 | 3282 | | OLD_PASSWORDS | OFF | 0 | | INNODB_STATS_ON_METADATA | ON | OFF | | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE | 10 | 5 | | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE | 10000 | 100 | | PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES | 1000000 | 1724 | | PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES | 100000 | 2223 | | INNODB_LOG_FILE_SIZE | 5242880 | 50331648 | | BASEDIR | /mnt/nfs/dist/5.5.30 | /mnt/nfs/dist/5.6.10 | | BACK_LOG | 50 | 80 | | OPEN_FILES_LIMIT | 1024 | 5000 | | INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS | 10 | 100 | | SORT_BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER_SIZE | 131072 | 262144 | | TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp | | TABLE_OPEN_CACHE | 400 | 2000 | | INNODB_VERSION | 5.5.30 | 1.2.10 | | INNODB_BUFFER_POOL_INSTANCES | 1 | 8 | | QUERY_CACHE_SIZE | 0 | 1048576 | | SLOW_QUERY_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 | | TABLE_DEFINITION_CACHE | 400 | 1400 | | PORT | 5530 | 5610 | | QUERY_CACHE_TYPE | ON | OFF | | REPORT_PORT | 5530 | 5610 | | PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES | 10000 | 1556 | | SQL_MODE | | NO_ENGINE_SUBSTITUTION | | INNODB_OLD_BLOCKS_TIME | 0 | 1000 | | LOG_ERROR | /mnt/data/sandboxes/msb_5_5_30/data/msan | /mnt/data/sandboxes/msb_5_6_10/data/msan | | VERSION_COMPILE_OS | linux2.6 | linux-glibc2.5 | | THREAD_CACHE_SIZE | 0 | 9 | | PLUGIN_DIR | /mnt/nfs/dist/5.5.30/lib/plugin/ | /mnt/nfs/dist/5.6.10/lib/plugin/ | | SYNC_RELAY_LOG | 0 | 10000 | | GENERAL_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 | | PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES | 50000 | 445 | | SYNC_RELAY_LOG_INFO | 0 | 10000 | | SLAVE_LOAD_TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp | | SECURE_AUTH | OFF | ON | | VERSION | 5.5.30 | 5.6.10 | | INNODB_CONCURRENCY_TICKETS | 500 | 5000 | | INNODB_PURGE_THREADS | 0 | 1 | | INNODB_OPEN_FILES | 300 | 2000 | | INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend | ibdata1:12M:autoextend | | INNODB_PURGE_BATCH_SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_TABLE | OFF | ON | | SYNC_MASTER_INFO | 0 | 10000 | | DATADIR | /mnt/data/sandboxes/msb_5_5_30/data/ | /mnt/data/sandboxes/msb_5_6_10/data/ | | OPTIMIZER_SWITCH | index_merge=on,index_merge_union=on,inde | index_merge=on,index_merge_union=on,inde | +---------------------------------------------------+------------------------------------------+------------------------------------------+ 56 rows in set (0.05 sec) |
Lets go over to see what are the most important changes [...]
Can’t Create Thread: Errno 11 (A Tale of Two Ulimits)
February 4, 2013 By 10 Comments
Recently some of my fellow Perconians and I have noticed a bit of an uptick in customer cases featuring the following error message:
1 2 | SQLSTATE[HY000] [1135] Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug. |
The canonical solution to this issue, if you do a bit of Googling, is to increase the number of processes / threads available to the MySQL user, typically by adding a [...]
Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not [...]
Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
I prefer to run MySQL with innodb_flush_method=O_DIRECT in most cases – it makes sure there is no overhead of double buffering and I can save the limited amount of file system cache I would normally have on database server for those things which need to be cached — system files, binary log, FRM files, MySQL [...]
Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
I had a customer recently who needed to reduce their database size on disk quickly without a lot of messy schema redesign and application recoding. They didn’t want to drop any actual data, and their index usage was fairly high, so we decided to look for unused indexes that could be removed. Collecting data It’s [...]
