You searched for myisam select slow - MySQL Performance Blog

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

Full Text Search Webinar Questions Followup

August 24, 2012 By Bill Karwin 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 [...]

Filed Under: Events and Announcements, Insight for Developers, MySQL

MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By Peter Zaitsev 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 [...]

Filed Under: Insight for DBAs, MySQL

Ultimate MySQL variable and status reference list

January 8, 2011 By Baron Schwartz 4 Comments

I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on variables, or look for content in the Percona documentation or forums. So I present to you what is now my newest Firefox toolbar bookmark: an option and [...]

Filed Under: Insight for DBAs, MySQL

Impact of the sort buffer size in MySQL

October 25, 2010 By Yves Trudeau 8 Comments

The parameter sort_buffer_size is one the MySQL parameters that is far from obvious to adjust. It is a per session buffer that is allocated every time it is needed. The problem with the sort buffer comes from the way Linux allocates memory. Monty Taylor (here) have described the underlying issue in detail, but basically above [...]

Filed Under: Benchmarks, MySQL Tagged With: Performance, Tuning

More on table_cache

November 26, 2009 By Peter Zaitsev 7 Comments

In my previous post I looked into how large table_cache actually can decrease performance. The “miss” path is getting more expensive very quickly as table cache growths so if you’re going to have high miss ratio anyway you’re better off with small table cache. What I have not checked though is how does table_cache (or [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: MyISAM

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

table_cache negative scalability

November 16, 2009 By Peter Zaitsev 23 Comments

Couple of months ago there was a post by FreshBooks on getting great performance improvements by lowering table_cache variable. So I decided to investigate what is really happening here. The “common sense” approach to tuning caches is to get them as large as you can if you have enough resources (such as memory). With MySQL [...]

Filed Under: Benchmarks Tagged With: MyISAM

Air traffic queries in MyISAM and Tokutek (TokuDB)

November 5, 2009 By Vadim Tkachenko 26 Comments

This is next post in series Analyzing air traffic performance with InfoBright and MonetDB Air traffic queries in LucidDB Air traffic queries in InfiniDB: early alpha Let me explain the reason of choosing these engines. After initial three posts I am often asked “What is baseline ? Can we compare results with standard MySQL engines [...]

Filed Under: Benchmarks, MySQL Tagged With: Data Warehousing, OLAP

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 5.5 and MySQL 5.6 default variable values differences

Full Text Search Webinar Questions Followup

MySQL Indexing Best Practices: Webinar Questions Followup

Ultimate MySQL variable and status reference list

Impact of the sort buffer size in MySQL

More on table_cache

How innodb_open_files affects performance

table_cache negative scalability

Air traffic queries in MyISAM and Tokutek (TokuDB)

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