You searched for why is myisam good - MySQL Performance Blog

MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency

May 22, 2013 By Justin Swanhart Leave a Comment

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 [...]

Filed Under: Benchmarks, MySQL Tagged With: InnoDB, Justin Swanhart, Low Concurrency, MyISAM, MySQL, SSB, star schema, Star Schema Benchmark

Benchmarking Percona Server TokuDB vs InnoDB

May 7, 2013 By Vadim Tkachenko 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 [...]

Filed Under: Benchmarks, MySQL, Percona Server Tagged With: Benchmarking, InnoDB, Percona Server, TokuDB

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By Ernie Souhrada 7 Comments

This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: Ernie Souhrada, FTS, FullText Search, InnoDB, MyISAM, MySQL 5.6

InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By Ernie Souhrada 1 Comment

I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: Ernie Souhrada, FTS, FullText Search, InnoDB, MyISAM, MySQL 5.6

MySQL 5.5 and MySQL 5.6 default variable values differences

February 21, 2013 By Peter Zaitsev 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:

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)

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 [...]

Filed Under: Insight for DBAs, MySQL Tagged With: default variable values, MySQL 5.5, MySQL 5.6, Peter Zaitsev

Percona Live MySQL Conference and Expo 2013 – News from the Committee – Tutorial Selection Complete

December 3, 2012 By Jay Janssen 1 Comment

As Percona Live London is raging in the UK, I thought it fitting to remind everyone about the next big Percona Live: MySQL Conference and Expo 2013 in Santa Clara, Californa on April 22-25, 2013. You can register NOW for this conference, and the Super Saving Registration deadline ends on December 28th, so be sure to [...]

Filed Under: Events and Announcements, MySQL

Modeling InnoDB Scalability on Multi-Core Servers

January 26, 2011 By Baron Schwartz 11 Comments

Mat Keep’s blog post on InnoDB-vs-MyISAM benchmarks that Oracle recently published prompted me to do some mathematical modeling of InnoDB’s scalability as the number of cores in the server increases. Vadim runs lots of benchmarks that measure what happens under increasing concurrency while holding the hardware constant, but not as many with varying numbers of [...]

Filed Under: Hardware and Storage, MySQL Tagged With: Mat Keep, Scalability, sysbench

Why you should ignore MySQL’s key cache hit ratio

February 28, 2010 By Baron Schwartz 28 Comments

I have not caused a fist fight in a while, so it’s time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL’s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and [...]

Filed Under: Insight for DBAs, MySQL Tagged With: key_buffer, key_buffer_size, MyISAM, MyISAM Key Cache

How innodb_open_files affects performance

November 18, 2009 By Peter Zaitsev 4 Comments

Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I decided to look at yet another similar variable – innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode. Unlike MyISAM Innodb does not have to keep open file descriptor [...]

Filed Under: Insight for DBAs, MySQL Tagged With: InnoDB

How (not) to find unused indexes

October 16, 2009 By Morgan Tocker 2 Comments

I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.Â This method is flawed – here’s the first reason why:

CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`status` enum('archived','active') DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;
mysql> SELECT count(*), status FROM sales GROUP by status;
+----------+---------+
| count(*) | statusÂ  |
+----------+---------+
|Â Â Â  65536 | archived |
|Â Â Â Â Â  154 | activeÂ  |
+----------+---------+
2 rows in set (0.17 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | keyÂ Â Â  | key_len | refÂ Â  | rows | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | refÂ  | statusÂ Â Â Â Â Â Â  | status | 2Â Â Â Â Â Â  | const |Â  196 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | keyÂ  | key_len | refÂ  | rowsÂ  | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | ALLÂ  | statusÂ Â Â Â Â Â Â  | NULL | NULLÂ Â Â  | NULL | 65690 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

CREATE TABLE `sales` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`customer_id` int(11) DEFAULT NULL,

`status` enum('archived','active') DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `status` (`status`)

) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;

mysql> SELECT count(*), status FROM sales GROUP by status;

+----------+---------+

| count(*) | statusÂ |

+----------+---------+

|Â Â Â 65536 | archived |

|Â Â Â Â Â 154 | activeÂ |

+----------+---------+

2 rows in set (0.17 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)

The cardinality of status index is woeful, but provided that the application [...]

Filed Under: Insight for DBAs, MySQL Tagged With: explain, indexing, MySQL, Tips

MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency

Benchmarking Percona Server TokuDB vs InnoDB

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

InnoDB Full-text Search in MySQL 5.6 (part 1)

MySQL 5.5 and MySQL 5.6 default variable values differences

Percona Live MySQL Conference and Expo 2013 – News from the Committee – Tutorial Selection Complete

Modeling InnoDB Scalability on Multi-Core Servers

Why you should ignore MySQL’s key cache hit ratio

How innodb_open_files affects performance

How (not) to find unused indexes

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA