I'm running into performance problems with our MySQL server. Knowledge here is limited. Hopefully some of you can give any suggestions or directions where to look to be able to get it under control.
At our school we are using databases in a WAMP configuration. For many years we didn't have performance issues when multiple students are executing SELECT-queries on the same database on the server.
I have classes of students who are practicing SQL and running SELECT queries against a specific database using phpMyAdmin. Normally this works fine and has good performance. They all login with the same user account, given them read-only access to the same database.
Several weeks ago I installed a new database with a new single and shared user account that gives them read-only access. Soon (eg 10 min) after everybody logged in and started to execure SELECT-queries the server became terribly slow. A restart fixed it and it worked fine after.
Yesterday I gave all 45 students another database with a new single and shared user-account and the performance issue showed up again. We restarted the server a couple of times, but the performance slowed down within minutes after the restart.
The MySQL server shows CPU 100%. I am talking about 45 students using phpMyAdmin at the same time, all logging in with the same user-account. The server has a WAMP configuration, with MySQL 5.7, Apache 2.4.27, PHP 5.6.31 and phpMyAdmin 4.7.4.
Does anybody recognize this and possibly have any clues what to do to prevent it?
Thanks in advance.
I have been looking settings that influence this behaviour. Are there any settings that we could check, e.g. for concurrent users or connections? Could a wrong charset and collation cause this? How can we trace the situation to get to the cause? I find on the internet about the slow query log which we turned on. And we increased the CPU from 2 to 4. Where else should we look to find the rootcause?