Last week at Percona Live, I delivered a six-hour tutorial about Percona XtraDB Cluster (PXC) for MySQL. I actually had more material than I covered (by design), but one thing I regret we didn’t cover was Flow control. So, I thought I’d write a post covering flow control because it is important to understand. What [...]
Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
In our last 5.5 series release of Percona Server, we included a regression in the RPM packaging that prevented the server from restarting following an upgrade — instead, the server would remain stopped after the upgrade was completed regardless of its state before updating. This caused some problems for some users, especially if automatic upgrading was configured [...]
Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 8 Comments
Percona is glad to announce the release of Percona Server for MySQL 5.5.30-30.2 on April 10, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.30, including all the bug fixes in it, Percona Server 5.5.30-30.2 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of [...]
MySQL performance: Impact of memory allocators (Part 2)
March 8, 2013 By 12 Comments
Last time I wrote about memory allocators and how they can affect MySQL performance in general. This time I would like to explore this topic from a bit different angle: What impact does the number of processor cores have on different memory allocators and what difference we will see in MySQL performance in this scenario? [...]
Announcing Percona Server for MySQL version 5.5.29-30.0
February 26, 2013 By Leave a Comment
Percona is glad to announce the release of Percona Server for MySQL version 5.5.29-30.0 on February 26th, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.29, including all the bug fixes in it, Percona Server 5.5.29-30.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details [...]
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 [...]
Is MySQL 5.6 slower than MySQL 5.5?
February 18, 2013 By 23 Comments
There have been a number reports/benchmarks showing MySQL 5.6 to be slower than MySQL 5.5 on variety of workloads. There are many possible reasons and I believe we will learn about many of them in the next few weeks and months as MySQL 5.6 is starting to get production battle-tested and there is inflow of [...]
Write contentions on the query cache
September 5, 2012 By 5 Comments
While doing a performance audit for a customer a few weeks ago, I tried to improve the response time of their top slow query according to pt-query-digest‘s report. This query was run very frequently and had very unstable performance: during the time data was collected, response time varied from 50µs to 1s. When I ran [...]
Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
I presented a webinar this week to give an overview of several Full Text Search solutions and compare their performance. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. During my webinar, a number of attendees asked some good questions. Here are their questions and my [...]
MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with [...]
