Avatar of Jim Youmans
Jim Youmans
Flag for United States of America

asked on 

MySQL 5.6 server super slow queries

I am working on a Centos 7 server running MySQL 5.6.  The server has 32 GB memory and 8 CPUs. It is using INNODB with each table as a file turned on.

The task is pretty straight forward, there are 3 tables (A, B, C) and A is the main table.  B and C are secondary.  I need to update the main table with data from the other two and add any records from B or C that are not in A.

The problem is that the tables are huge.  B has 345 million rows and C has 100 million.  Running even simple queries on B and C either takes an hour or more or MySQL disconnect my session. Here is an example.  There is a column name TimeSlot which is datetime and indexed.  When I run
Select MIN(TimeSlot) from B;

Open in new window

It won't return a value.  I let it run for over an hour before I stop it and before that it had ran for 20 minutes or more 2 or 3 times and then disconnected me.
One odd thing is that when I run top while running the queries, I don't see much thrashing.  CPU is at 2% and memory at 23%.  When I cancel the query, those numbers barely change.
I know you don't have enough information for detailed suggestions but I was hoping that someone could look at the my.cnf file and see where I might be able to optimize it, or any other suggestions.

tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 1024
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 1G
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 21G
innodb-write-io-threads        = 16
innodb-log-buffer-size         = 256M
#innodb_doublewrite            = 0

max_allowed_packet             = 1024M
binlog_format = STATEMENT
expire_logs_days = 0
host_cache_size = 342
#log-error                      = /var/log/mysql_error.log
pid-file                       = /var/run/mysqld/mysqld.pid

Open in new window

Any suggestions would be greatly appreciated.  Thank you!

MySQL ServerDatabasesSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon