Mysql – Query very slow on innodb

innodbMySQLperformance

I'm using mysql 5.7 on ubuntu server 14.04, and engine innodb.
I have a table that are very huge (about 66 Go), I insert and delete from it without problem, but some query of select are taking lot of time till they are abondoned from java.

When I do select count(*) from TABLE, it take about 10 minutes.

I export all my database and data to another server ubuntu server 16.04 mysql 5.7, and do the same query and it take about 2 minutes.

Why this difference happends on the same table and same data?

Here is my config files :

First server (10 minutes)

[mysqld] 
pid-file    = /var/run/mysqld/mysqld.pid 
socket  = /home/mysql_data/mysqld.sock 
datadir = /home/mysql_data 
log-error   = /var/log/mysql/error.log 
bind-address    = 127.0.0.1 
symbolic-links=0 
open_files_limit = 10000 
query_cache_type=0 
query_cache_size=0 
innodb_buffer_pool_size = 36G 
innodb_log_file_size = 512M 
innodb_flush_log_at_trx_commit = 0 
innodb_flush_method = O_DIRECT 
innodb_buffer_pool_instances = 8 
innodb_thread_concurrency = 8 
innodb_io_capacity = 1000 
innodb_io_capacity_max = 3000 
innodb_stats_on_metadata = 0 
innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+ 
innodb_buffer_pool_load_at_startup = 1 # MySQL 5.6+ 
innodb_buffer_pool_dump_pct = 75 # MySQL 5.7 only 
innodb_checksum_algorithm = crc32 # MySQL 5.6 or newer 
innodb_read_io_threads = 16 
innodb_write_io_threads = 16 
innodb_file_per_table 

Second server (2 minutes)

[mysqld_safe] 
socket  = /var/run/mysqld/mysqld.sock 
nice    = 0 
[mysqld] 
user    = mysql 
pid-file    = /var/run/mysqld/mysqld.pid 
socket  = /home/mysql/mysqld.sock 
port    = 3306 
basedir = /usr 
datadir = /home/mysql 
tmpdir  = /tmp 
lc-messages-dir = /usr/share/mysql 
bind-address    = 127.0.0.1 
skip-external-locking 
key_buffer_size = 16M 
max_allowed_packet  = 16M 
thread_stack    = 192K 
thread_cache_size = 8 
query_cache_limit   = 1M 
query_cache_size = 16M 
myisam-recover-options = BACKUP 
log_error = /var/log/mysql/error.log 
expire_logs_days    = 10 
max_binlog_size = 100M

Best Answer

  1. Let check the the table size after you migration to new db(in new db server). a reason is

    First server (10 minutes)

your table might not up to day of statistic use ANALYZE TABLE for Innodb. you mention you table is 66GB but it might not the real table size it may include the dead-tuple(caused by your update,delete operation).

when you migration it to other place this table or whole database will be ANALYZE to the real time statistic.