SQL handle overflow

Community Forums/Common Room/SQL handle overflow

skidracer(Posted May) [#1]
One of the reasons original search was suspended was because I thought it may be responsible for the dreaded sql no handle site outage we suffer here occasionally.

But alas, it happened again this morning so it wasn't search, or not on this site anyways.

Maybe we just tick over some limit. The site got backed up to a secure location instead of reading the forums, so yeh. productivity gain there....


GfK(Posted May) [#2]
For me this site's been slow for the last few days, even now. Seems to not load whole forum threads most of the time and I have to hit refresh two or three times before it loads the entire page.

Is it related? Anyone else getting this?


Derron(Posted May) [#3]
Site loads "complete" here without issues.

Only starting page might be a bit slowe for you as it includes external media (and their connections might be problematic to your destination).


@ SQL
you might monitor MySQL stats. They will show you open connections and the likes. According to the server setup connections might not be dropped fast enough stacking up to such "error outs".
More likely it is a script not doing what it should - or a script called externally (by bots, bad users, external services checking for updates...).

If you have access to the apache logs, you might also have a look in analyzing them with awstats (could be done locally then) and then check how visitors / page impressions increased (or not) before the outage.


Another potential source:
in your scripts you open up the DB connection ... then do something (eg. fetching information) and finally you close the DB connection.
BUT if your script "excepts out" then the connection will stay open (at least for a while).

With "Mysql workbench" or the mysql-command-line you could query this:
show variables like "max_connections";

it will show the value of "max_connections" which might be "100". This means the most concurrent value of connections to mysql is "100". There is also a "user limit".

But there is one interesting question left: this error should only come "from time to time" (in the moment of too many people trying to access something which connects to MySQL).
But for blitzmax.com (and your other domains) this error will stay for hours - I assume until you restart the server/services.
If that really needs a restart, then something is keeping the connection alive (or the server is thinking it needs to be kept alive). It is like a funnel which is "full". Normally the funnel should get empty after a certain time but in this case either there is still water coming down filling the water (incoming page requests or so) or the funnel is blocked somehow (server keeps connections alive).


Maybe this is of help too:
https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html



Further thinking: I assume the server still uses PHP 5 and no PDO but raw "mysql_connect".
If you have access to monkey-x-page-sources (and blitzmax.com-sources) then have a look if some scripts contain
"mysql_pconnect" rather than "mysql_connect". The P stands for "persistent", which means "keep connection for user open even if the script finished"


Also check the disk space of the mysql server ... if it is running out of space then the error will happen too.


While looking for options like .htaccess "php_value mysql.connect_timeout 10" (to lower timeout values - to free connections earlier) I found users using "mysqltuner" which reports potential improvements to mysql settings and/or the tables.


While you removed "search" from blitzmax.com I am not sure whether "monkey-x" is running on the same host. As "monkey-x.com" was "down" during the same time, I assume it is. This means disabling the search here does not bring efforts if the search was enabled on the other websites using the same MySQL installation.



Ok, conclusion:
- find out if there were higher page impressions before the outage started
- find out if there were certain pages (not-that-common ones!) called right before
- check if "mysql_pconnect" is used - and replace that with "mysql_connect" then (I assume you are not really needing persistent connections)
- if the server was not running on its limits then configure mysql to allow more simultaneous connections (especially if you are always running it near the current limit)
- decrease timeout values so obsolete connections get closed earlier
optional:
- if mysqltuner (or raw commands given) state huge "slow queries" counts, then there might be time to optimize them
- "optimize tables" if you have big tables with many adjustments/updates done - so accesses will be faster



bye
Ron


Brucey(Posted May) [#4]
I'm all for the "not properly closing connections you've opened" line of inquiries.
Perhaps there's some code that gets "stuck" for some reason, and hence never closes its resources.

Good luck :-)