Code:
WWWWWWWWW_WW_KWWWKWWWWWWKWWWWWWW_WW_WWWWWWW_WWWWWWWWW_WWWWWWWWWW
WWWWW_WWWWWWWWW__W_WW_WWWWWWW_W_W_W_WWWWW_WWWWWWW____W_.W.WWW__W
W._WWWW...W.....................................................
................................................................
ok, FA is down. well, not really, but it is not serving anyone but my keep-alive connection. been doing this all day, and something like this for couple of days now. WSOD are everywhere.
but what is a WSOD? to me it looks like a timed out(max_execution_time=30s) PHP script with error_reporting set to E_ALL, so users do not see this 'timed out' error message.
timeouts from where? apache is fine, has a lot of free server-slots and running servers. PHP never times out, only in a case of severe data processing, which FA clearly does not have. so this leaves us with MySQL.
and this recent error.
Code:
Database error in www.furaffinity.net: Invalid SQL: DELETE FROM df_useronline WHERE online<'1141262669'
mysql error: Lock wait timeout exceeded; try restarting transaction
that is odd, i thought you comented out the code responsible for 'online users' SQL queries?...
and OMG - is that the DELETE query? sorry dudes, but i have all the reasons to suspect that this is a very badly written piece of code.
just imagine 200 concurrent SELECT+DELETE queries on a single table? obviuosly SELECTs have to wait for DELETEs to complete, and DELETEs have to wait for SELECTs to complete - they are mutually exclusive in this case, since you SELECT and the DELETE on all the rows, hence all of them geting locked. as the result all these queries from all the concurrently running copies of the MySQL server are literally put in one huge queue.
so instead of running at the same time, each copy of MySQL, and thus each copy of PHP, and thus each copy of Apache, and thus each user are waining their turn to SELECT and DELETE data from that table. it is only obviuos that one day the server would not be capable to empty the queue during peak times in a reasonable amount of time, and thus the queries would eventually time out, resulting in that confusing 'table lock' error - because the entire table is literally getting locked by each SELECT and DELETE.
to even worsten the case, these timeouts sometimes cause deadlocks, meaning that the table remains locked untill MySQL is restarted.. and this is kind of what i see now, thou there are only ~100 request/sec.
but really, i thought you were SELECT'ing every time, but DELETE'ing once in, say, 5 or 10 minutes or so (well, at least once a second for the sake of sanity, but not 200 times with each user request
). running both queries every time creates a huge ass bottleneck. and this bug is so damn hard to trace....
so as i see it now, this was the major reason of slowdowns of the site. but then again, i haven't (yet?
) seen other SQL code or/and PHP code to be able to judge that.
then again i could be wrong, but i really hope that helps, because i grew to be addicted somehow to the site and to some people around here...
PS:
and you can also consider this handy little thing
http://sqlrelay.sourceforge.net/ - for database connection pooling, proxying and load balancing they say. all you will need is pooling. haven't used it yet (i'll try eventually), but heart from some people that it helps a lot.
PPS:
and i suggest that you try to optimize apache for FA's way of it's usage (my regards to you if you did it already, i really cannot tell it without seeing httpd.conf). even in the worst case you will probably manage to cut a megabyte or so off each of the running server processes. but you admin guys probably know this better then me, so i'll just press the 'submit' button now...
EDIT
serious DB2 access path tuning and messing around in the guts of the database design/setup to resolve conflicts? Sounds like an interesting Friday evening
you bet. it is like a good detective story. intrigueing..
