troubleshooting Question

MySQL 5.6 server super slow queries

Avatar of Jim Youmans
Jim YoumansFlag for United States of America asked on
MySQL ServerDatabasesSQL
8 Comments1 Solution34 ViewsLast Modified:
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.
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# CACHES AND LIMITS #
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 #
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
[mysqld_safe]
#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!






ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson
Database Administrator / Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 8 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 8 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004