MySQL Forums
Forum List  »  InnoDB

Select count very slow
Posted by: soufiane wahi
Date: May 22, 2017 09:32AM

Hi, 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

Options: ReplyQuote


Subject
Views
Written By
Posted
Select count very slow
6282
May 22, 2017 09:32AM
1927
May 22, 2017 11:24AM
1411
May 22, 2017 11:38AM
1180
May 23, 2017 10:43AM
1187
May 23, 2017 11:10AM
1171
May 23, 2017 02:24PM
1243
May 23, 2017 04:05PM
1375
May 23, 2017 08:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.