Running into performance problems - CPU 100%
Posted by: bart brink
Date: February 01, 2024 10:10AM

Hello, 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?

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?

Thanks in advance.

Options: ReplyQuote

Written By
Running into performance problems - CPU 100%
February 01, 2024 10:10AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.