2

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
  • Maybe add information about the servers? – Anthony Genovese May 22 '17 at 16:10
  • First server is 64 Go on ram , 4 cores Xeon E3 3.8Ghz ubuntu server 14.04. The second is 16 Go on ram 4 cores Xeon E3 3.2Ghz ubuntu server 16.04 – SWahi May 22 '17 at 16:22
  • 2
    Was the table ENGINE=InnoDB in both servers? What version was the old server? – Rick James May 22 '17 at 21:51
  • Yes innodb for all of them. the version is mysql 5.7 for both but ubuntu server is 14.04 for the first and 16.04 for second – SWahi May 22 '17 at 21:56
  • 2
    Can you add EXPLAIN results for your query? Also, my suspicion is that you have a table referenced in the query that holds large number of rows but the tablespace is fragmented due to DELETEs and INSERTs over a period of time. So when you exported the tables to take it across to the other server (assuming mysqldump) then the table was created on the second sever and did not suffer from fragmentation + table stats were also recalculated and so a better query plan. – thatsaru May 25 '17 at 13:55
-1
  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.

  • 1
    InnoDB counts the entries in the primary key. It doesn't matter if statistics are wrong, it has not another way to perform this operation. – Federico Razzoli Mar 4 '18 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.