MySQL 5.7 performance issue with 300 GB Innodb We have a database running on MysSQL 5.7. The database size is around 300 GB, running on a dedicated Linux server (RHEL 6) with 144 GB RAM, 16 CPUs and 15 GB swap size.

The server is busy throughout the day with a minimum of 50 connections. Most of the queries have indexes and are optimized once weekly. But we are still facing a performance issue.

So requesting a review of the my.cnf configuration and please suggest any changes.

my.cnf

[mysqld]
basedir=/usr
datadir=/sql/mysql/data57
socket=/sql/mysql/data/mysql.sock
skip-external-locking
key_buffer_size = 4000M
max_allowed_packet = 5120M
table_open_cache = 4000
sort_buffer_size = 128M
read_buffer_size =8M
join_buffer_size=128M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 100
query_cache_size=0
query_cache_limit=4M
query_cache_type="ON"
query_cache_min_res_unit=20K
query_prealloc_size=40K
query_alloc_block_size=40K
max_connections=300
sql_mode = ""
interactive_timeout = 28800
wait_timeout = 7200
connect_timeout = 60
default_password_lifetime=0
old_passwords=2
lower_case_table_names=1
tmpdir=/tmpfs
tmp_table_size=20G
max_heap_table_size=170M
innodb_buffer_pool_size=100G
innodb_buffer_pool_instances=16
innodb_buffer_pool_chunk_size=6562M
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_log_file_size=2G
innodb_lock_wait_timeout=180
net_buffer_length=8K
transaction-isolation=READ-COMMITTED
innodb_lock_wait_timeout=180
federated


log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/sql/mysql/data/mysql.sock

[mysql.server]
user=mysql
basedir=/usr
log-bin=mysql-bin
binlog_format=mixed
server-id=1

[mysqldump]
quick
max_allowed_packet=1G

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size=1024M
sort_buffer_size=256K
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout


Query

 SELECT year_no              AS "Year",
       month_no             AS "Month",
       area                 AS "Area",
       hq                   AS "Headquarters",
       region               AS "Region",
       depot                AS "Depot Name",
       aw                   AS "Dist Name",
       ss_channel           AS "Channel",
       sales.classification AS "Classification",
       sales.beat           AS "Beat",
       sales.salesman       AS "Salesman",
       sales.cust           AS "Retailer UID",
       Sum(qty)             AS "Qty",
       Sum(amt)             AS "Amount"
FROM   sales
       JOIN date_tbl
         ON dt = sales.dt
       JOIN or_areas
         ON area = sales.area
       JOIN hq
         ON hq = sales.hq
       JOIN or_regions
         ON region = sales.region
       JOIN depot
         ON depot = sales.depot
       JOIN or_aws
         ON aw = sales.aw
       JOIN prods
         ON sku = sales.sku
       JOIN prod_grp
         ON product_group = sales.product_group
       JOIN brand
         ON brand = sales.brand
       JOIN dsm
         ON ( salesman = sales.salesman )
       JOIN cust
         ON retailer = sales.retailer
WHERE  ss_inv_status IN ( 1, 3, -3 )
       AND ss_cust_name IS NOT NULL
       AND cal_date BETWEEN ? AND ?
       AND sales.zone IN ( ? )
       AND sales.region IN ( ? )
       AND sales.depot IN ( ? )
       AND sales.zone IN ( ? )
       AND sales.distributor_division = ?
       AND sales.brand IN ( ? )
GROUP  BY year_no,
          month_no,
          aw,
          sales.cust,
          prod,
          cal_date;  


The ? are place holders and the values are entered as inputs.

Explain



More information


The performance problem is very big queries, which take 30 mins to execute.
These are report queries which are generated on daily basis.
It is not a DW system.
I think you have found an example of "over-normalization" and why it is bad to "normalize continuous values" such as dates.

I recommend you abandon date_tbl and pull a date into sales.  Also get rid of separate columns for year_no and month_no; instead compute them as needed.

Once you have done that, some of these may be useful:

INDEX(cal_date)
INDEX(brand, cal_date)
INDEX(zone, cal_date)


It depends on what ? are likely to be.  If, say, brand is frequently a single item, then (brand, cal_date) is likely to be the most useful.

Have you measured any improvement after OPTIMIZE TABLE?  I doubt it.