You searched for mysql innodb count num rows - Page 2 of 8

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

Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:

---TRANSACTION 12303, ACTIVE 26 sec
mysql tables in use 2, locked 0
MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data
select count(*) from sbtest,sbtest x
Trx read view will not see trx with id >= 12304, sees < 12301

---TRANSACTION 12303, ACTIVE 26 sec

mysql tables in use 2, locked 0

MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data

select count(*) from sbtest,sbtest x

Trx read view will not see trx with id >= 12304, sees < 12301

As you can [...]

MySQL 5.5 and MySQL 5.6 default variable values differences

Implications of Metadata Locking Changes in MySQL 5.5

Knowing what pt-online-schema-change will do

Edge-case behavior of INSERT…ODKU

Replaying database load with Percona Playback

How to find MySQL queries worth optimizing ?

Recovery deleted ibdata1

Innodb Table Locks

Impact of memory allocators on MySQL performance

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA