This blog post is part two in what is now a continuing series on the Star Schema Benchmark. In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using only the InnoDB storage engine. In my testing I discovered that innodb_old_blocks_time had an effect on performance of the benchmark. There was [...]
Is Synchronous Replication right for your app?
May 14, 2013 By 17 Comments
I talk with lot of people who are really interested in Percona XtraDB Cluster (PXC) and mostly they are interested in PXC as a high-availability solution. But, what they tend not to think too much about is if moving from async to synchronous replication is right for their application or not. Facts about Galera replication [...]
Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
After compiling Percona Server with TokuDB, of course I wanted to compare InnoDB performance vs TokuDB. I have a particular workload I’m interested in testing – it is an insert-intensive workload (which is TokuDB’s strong suit) with some roll-up aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements [...]
Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
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 [...]
Facebook at Percona Live MySQL Conference and Expo and Advanced Registration Ending Soon
March 19, 2013 By Leave a Comment
Facebook is a major user of MySQL and has pushed the performance limits of the technology. Their MySQL experts have deep, hands on knowledge of the technology. I’m pleased to welcome Mark Callaghan, Software Engineer for Database Infrastructure at Facebook, back again this year to the Percona Live MySQL Conference & Expo to share his [...]
MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
While preparing the webinar I will deliver this Friday, I ran into a quite interesting (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a really nice exercise for checking the performance and inner workings of one [...]
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. [...]
Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
I was curious to check how Percona XtraDB Cluster behaves when it comes to MySQL replication latency — or better yet, call it data propagation latency. It was interesting to see whenever I can get stale data reads from other cluster nodes after write performed to some specific node. To test it I wrote quite a [...]
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 [...]
