At the beginning I would like to explain that I'm not a DB expert. My hosting provider is struggling with DB optimization for my website. Unfortunately seems that they are out of ideas.
I would like to ask for some help.
Main issue seems to be that MySQL server is massively creating temporary tables on disk, which is very slow.
DB size is 3,6 GB
Biggest table has 1,7 GB (~1,085,507 records), second one 1,4 GB (~1,185,706 records), all others are smaller (next one is 143,8 MB, then 92,7 MB, all other smaller than 50 MB).
I have no control over DB structure/tables/columns layout, because it is Invision Power Board (IPB) system, latest version.
All tables are in InnoDB, utf8mb4_unicode_ci.
Serwer is LiteSpeed, Percona Server (5.6.28-76.1-log - Percona Server (GPL), Release 76.1, Revision 5759e76), libmysql - 5.1.49
I've got 4 GB RAM available on webserver, currently arount 60 percent is in use.
Values marked as alert:
- Aborted clients - 200
- Aborted connects - 143
- Created tmp disk tables - 114 997
- Handler read rnd - 21 287 655
- Handler read rnd next - 112 934 517 814
- Innodb buffer pool pages dirty - 74
- Innodb buffer pool reads - 6 025 850
- Innodb row lock waits - 108
- Opened tables - 745
- Qcache free blocks - 2719
- Qcache lowmem prunes - 969 904
- Select full join - 41
- Slow queries - 528
- Sort merge passes - 4 757
- Table locks waited - 74 365
I have all current settings from phpmyadmin, but there are a lot of them. I've uploaded all here: https://www79.zippyshare.com/v/3g75k0gP/file.html
Some important ones:
- innodb buffer pool size - 1 GB (increased by hosting provider)
- innodb sort buffer size - 1,048,576
- key buffer size - 64 MB
- max heap table size - 128 MB (increased by hosting provider)
- net buffer length - 16,384
- query cache limit - 1 MB
- query cache size - 32 MB
- read buffer size - 128 KB
- read rnd buffer size - 256 KB
- sort buffer size - 256 KB
- thread cache size - 50
- tmp table size - 128 MB (increased by hosting provider)
I'm able to run any diagnostic SQL query, just please ask what You need to check to suggest some changes in configuration, which I will be able to push further to my hosting provider...
Thanks for any help, any suggestion would be very appreciated.
Best Regards, DJ