This is the second part in a two-part series comparing Virident’s vCache to FlashCache. The first part was focused on usability and feature comparison; in this post, we’ll look at some sysbench test results. Disclosure: The research and testing conducted for this post were sponsored by Virident. First, some background information. All tests were conducted [...]
Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 29 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 [...]
More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
Since my first post on MySQL transaction descriptors optimization introduced in Percona Server 5.5.30-30.2 and a followup by Dimitri Kravchuk, we have received a large number of questions on why the benchmark results in both posts look rather different. We were curious as well, so we tried to answer that question by retrying benchmarks on [...]
How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from [...]
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 [...]
Adaptive flushing in MySQL 5.6
September 4, 2012 By 8 Comments
As you may know, flushing in MySQL is an area of my interest, I wrote about it several times, i.e. http://www.mysqlperformanceblog.com/2011/09/18/disaster-mysql-5-5-flushing/ http://www.mysqlperformanceblog.com/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/ http://www.mysqlperformanceblog.com/2011/01/03/mysql-5-5-8-in-search-of-stability/ In MySQL 5.6 there was implemented a new flushing logic, so I decided to check what do we have now.
Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
In the first part of this article I have showed how I align IO, now I want to share results of the benchmark that I have been running to see how much benefit can we get from a proper IO alignment on a 4-disk RAID1+0 with 64k stripe element. I haven’t been running any benchmarks [...]
A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
A few day ago, a customer came to Percona needing to recover data. Basically, while doing a transfer from one SAN to another, something went wrong and they lost the ibdata1 file, where all the table meta-data is stored. Fortunately, they were running with innodb_file_per_table so the data itself was available. What they could provide [...]
Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in [...]
Shard-Query EC2 images available
May 11, 2011 By 2 Comments
Infobright and InnoDB AMI images are now available There are now demonstration AMI images for Shard-Query. Each image comes pre-loaded with the data used in the previous Shard-Query blog post. The data in the each image is split into 20 “shards”. This blog post will refer to an EC2 instances as a node from here [...]
