Recently, I am experiencing low database server performance and trying to figure out what the cause may be.
Attached, please find database connections. What concerns for the moment is:
the number of connections with “sleep” mode
Connections from unauthenticated user
I wonder if these are normal and what else I should possibly look at.
Thanks in advance,
Mima
Does that mean some programing languages don’t handle system calls very well or something else? I have produced some really wakki structures and found some languages seem to cope better than others
No - just that it amazes me just how many seem to take no effort in designing their DB (of any flavor) and seem to believe in one big table (containing everything from images to goodness knows what) with no index.
and just assume an index solves everything!
So that assumes no one understands the sql join comand, and yes a flat table can be awful, but if images are to be included may be this should be a reference to the image rather than the image itself so the html app can just reference it, I thought that was school boy stuff ?
If they’re all from localhost, it just indicates some web application has invalid credentials. If they’re from another host, you probably shouldn’t have made Mariadb accessible to the internet (the default configuration only listens on localhost and there’s a firewall on a default Virtualmin system that does not open the Mariadb port). But, even if they are from another host, as long as they’re unauthenticated, it means nothing bad happened. They couldn’t login so they couldn’t do anything.
You need to be looking at what’s taking time. You could enable slow query logging and see which specific queries are chewing up resources. Odds are extremely high it’s a web application with one or more poorly constructed queries. You may be able to mitigate it by tuning your Mariadb configuration to suit your application(s).
Also, make sure it’s not some fundamental underlying problem unrelated to the database. Do you have enough memory? If you’re swapping, you don’t have enough memory (having swap usage is not the same as swapping…look at avail Mem and buff/cache in top…if you have a reasonable amount free there, you’re OK, but if it’s low, it’s probably a sign you’re doing too much for the amount of memory you have). Disk speed? Probably not an issue on modern SSDs, but if you’re on a VM that’s on hardware shared by a lot of others, you might be running into limits.
But, keep in mind that tuning is context dependent. The default config is somewhat conservative (assuming a modest system without a lot of RAM), but just cranking up various caches and buffers may not actually help performance. It may just force the system to go into swap more, if you don’t have a lot of available memory, which would hurt performance.
Also, you can usually figure out which app(s) are causing trouble by looking at which ones are running the most and taking up the most CPU. Just look at top, and see which users are the most demanding. The default sort order is CPU usage, so the hardest working will be first, and the user running it is the domain owner, which allows you to narrow down what app(s) may be demanding.
Thanks everyone for your valuable feedback, problem solved!
Optimizing the database improved the system but the main delays were related to php scripts which perform 3x faster now with Opcache.
Thanks again
opcache is enabled by default when using PHP-FPM execution mode in Virtualmin. So, what did you change that improved performance? (Or were you using some other execution mode?)
