On 9/13/2021 at 7:57 PM, El Patron said:look at your mysql configuration via phpmyadmin, there it will tell you how optimal your mysql config is compared to your runtime stats
it's always been about getting data from disk to cpu...ever since compters were invented.
for last 3 years we have moved all clients to nvme/ssd...which are now available for vps....
you can spend a lot of time/money trying to debug/tune the application (prestashop) when it's almost always time to get data from disk..
so I recommend, get vps with nvme/ssd drive ) with plesk (never cpanel), move to new hosting (ngnix/fpm)...run loader.io or other to volume simulator...look at your mysql stats, tune myslq...
zoom zoom
Thanks for your reply El Patron.
I would not worry about my server specs. However, I am wondering why you are saying to run it on plesk (never cpanel). I am currently running on cpanel and switching and migrating everything would be quite a big effort.
So I am focusing on MySQL.
As I said on my first comment, based on my profiler results, number of queries and total querying time are very high.
- I have already done some cleaning of useless tables (session data, old carts etc), no significant imporovement
- A couple of addons that I am using are probably poorly designed and they killing MySQL with running hunders of identical queries for each page request, but even if I disable every single addon, there is no significant improvement.
So I had a look at the stats on phpmyadmin. You suggested "there it will tell you how optimal your mysql config is compared to your runtime stats" but I could not find something like this. So I reset Mysql, let it run for a day and then I checked under "Status". The stats look VERY strange:
- According to Google Analytics on this day I had about 500 sessions with 1300 pageviews in total. This was expected.
- According to phpmyadmin, my SQL Server sends 6 GB of data and gets 4500 connections and 1.4 Million Queries EVERY HOUR!!
- If you do the math, it's about 100MB, 80 connections, and 25K queries per pageview.
This makes no sense, the amount of data is huge! My homepage e.g. is not more than 7MB. I think this is what is killing the DB and TTFB. Any ideas?