• Fur Affinity Forums are governed by Fur Affinity's Rules and Policies. Links and additional information can be accessed in the Site Information Forum.

FA Current Status : was The SQL Holocaust [UPDATED]

Dragoneer

Site Developer
Staff member
Site Director
Administrator
Crypto is currently working on a fix to re-write how the FA code handles queries and talks to the server. Currently, our code became more inefficient than it should have been. I am not entirely sure what happened (I am not a coder) but Crypto is trying to fix it ASAP.

I should have more of an update on the situation tonight.

SQL errors have been fixed. Thank Crypto and Jheryn. Currently, they are working on performance revisions to further enhance the site.

My apologies for the latest problems. We are currently re-organizing the site and its priorities, as well as adding new admins to the site. I will make introductions soon.

NOTE: THE SYSTEM IS CURRENTLY DOWN FOR UPGRADES 3/6/06
 

Keffria

New Member
Ah... so I guess that would explain why it doesn't even load. If I try to use my bookmark I end up staying at the same site I was at previously x_X;
 

uncia2000

Member
Re: The SQL Holocaust

Dragoneer said:
Crypto is currently working on a fix to re-write how the FA code handles queries and talks to the server. Currently, our code became more inefficient than it should have been.
Earlier this week d'you mean? Aside from the general/ongoing issues, that is...

Dragoneer said:
I am not entirely sure what happened (I am not a coder) but Crypto is trying to fix it ASAP.

I should have more of an update on the situation tonight.
Thanks for the update.
Likewise thanks & good luck to Crypto (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...).
 

yak

Site Developer
Staff member
Administrator
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..
 

Catamount

New Member
yes, I hope we are talking about bad coding here and not so much bad table design. :? Thxs for the update anyways Dragoneer.
 

uncia2000

Member
(oops, didn't post this earlier today before heading out... :oops:)
yak said:
...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?...
Yes, I was perplexed by that, too... ( => http://www.furpawz.net/fa/forums/viewtopic.php?p=8830&highlight=#8830 )

I doubt that's the only issue, but it's the most visible one.
Still leaves me trying to guess whether that was a recently (badly) "tweaked" DB2 call or not, because things were not running this poorly last weekend.

Thanks, yak.

yak said:
but you admin guys probably know this better then me, so i'll just press the 'submit' button now...
There's that ol' admin vs. tech split again, btw.
I'd guess Cypto and Gushi have DBA knowledge, but wouldn't presume that all admins are automatically tech. gurus.
 

yak

Site Developer
Staff member
Administrator
uncia2000 said:
yak said:
but you admin guys probably know this better then me, so i'll just press the 'submit' button now...
There's that ol' admin vs. tech split again, btw.
well, actually not. it is more like 'my english is so crappy that i sometimes misuse words, especially when trying to adress a number of different people with one word' case. :x
and if it wouldn't cause any problems, is it posible to tell just what was fixed and in what way :?: problem->solution situations like this pose a great value to the developers like myself.. thanks in advance.
and in the mean time it runs like never ran before. well done!
 

tundra_arctic_wolf

aka The Furry Punisher
I'm having the same problem that you are, NeoWarriorCat. I get just a blank page. I hope FA comes back soon.

*Tundra patiently waits for FA to come back online*

cheers,
Tundra Arctic Wolf
 

Mitch_DLG

Member
I am still getting either "cannot locate server" "Document contains no data" or SQL errors constantly, whith no actual access to the site all day.
 

yak

Site Developer
Staff member
Administrator
symptoms are the same... guess some table got a deadlock again.
this is a stupid suggestion, but while in the process of correcting mistakes, could, say, 'cron' the mysql server to be restarted each 6 hours or so? like i said, stupid...
 

Kougar

Member
Re: The SQL Holocaust [UPDATED]

Dragoneer said:
SQL errors have been fixed. Thank Crypto and Jheryn. Currently, they are working on performance revisions to further enhance the site.

Not quite so fast, there! :3

Long afer peak usage here, and not only am I getting them but it's a solid string of them. Hopefully is only due to work being done and such on something, but incase not I'll post this.

Thanks for all of the effort so far, it's appreciated! :)

Edit: It seems I was getting almost all of them from trying to +fav and +watch, while general image viewing/browsing was still working during the same time...
 

Pico

Member
I can get pages to load now (barely), but I get lovely load times such as this:

This page was generated in 105.71117 seconds. Queries used: 2. Server load: 0.03%
 

timoran

Banned
Banned
Pico said:
I can get pages to load now (barely), but I get lovely load times such as this:

This page was generated in 105.71117 seconds. Queries used: 2. Server load: 0.03%

I've got you beat on that one...

http://furaffinity.net/view/69566/
 

verix

some dragon
Pico said:
I can get pages to load now (barely), but I get lovely load times such as this:

This page was generated in 105.71117 seconds. Queries used: 2. Server load: 0.03%
You can stop trolling now.
 

uncia2000

Member
verix said:
Pico said:
I can get pages to load now (barely), but I get lovely load times such as this:
This page was generated in 105.71117 seconds. Queries used: 2. Server load: 0.03%
You can stop trolling now.
Eh... Pico's definitely a FA supporter and OK by me.
We all get frustrated from time-to-time and there's plenty reason for people to feel that, at present. (Frustrated for self and for the rest of the community...).
Hopefully everyone will have taken that "in context" and seems so, given there aren't a dozen follow-up gripes.

Thanks y'all for patience above-and-beyond the norm: hope that will be repaid in due course.

=
Nice "Bug Hunter" label, btw, verix. Good luck, friend! :)
 

verix

some dragon
uncia2000 said:
Eh... Pico's definitely a FA supporter and OK by me.
We all get frustrated from time-to-time and there's plenty reason for people to feel that, at present. (Frustrated for self and for the rest of the community...).
Hopefully everyone will have taken that "in context" and seems so, given there aren't a dozen follow-up gripes.

Thanks y'all for patience above-and-beyond the norm: hope that will be repaid in due course.

=
Nice "Bug Hunter" label, btw, verix. Good luck, friend! :)
uh I was kidding and I am pretty sure Pico would pick up on my sarcastic response :shock: You could always call it cheerleading, I guess.

By the way, I stopped being a bug-hunter a few months ago. For some reason they just never got rid of this title. I'd divulge the details as to why, but I'd probably anger people by insighting some sort of drama or DISSENT AMONG THE USERS!! or something like that.
 

dave hyena

A wonderous moorhen
verix said:
I'd divulge the details as to why, but I'd probably anger people by insighting some sort of drama or DISSENT AMONG THE USERS!! or something like that.

You already posted "an open letter" about it on the FA LJ last year and garnered 29 comments worth of attention.

See:

http://community.livejournal.com/furaffinity/63019.html
 

verix

some dragon
Dave Hyena said:
verix said:
I'd divulge the details as to why, but I'd probably anger people by insighting some sort of drama or DISSENT AMONG THE USERS!! or something like that.

You already posted "an open letter" about it on the FA LJ last year and garnered 29 comments worth of attention.

See:
thanks that is kind of what I was trying not to post here
 

dave hyena

A wonderous moorhen
verix said:
Dave Hyena said:
verix said:
I'd divulge the details as to why, but I'd probably anger people by insighting some sort of drama or DISSENT AMONG THE USERS!! or something like that.

You already posted "an open letter" about it on the FA LJ last year and garnered 29 comments worth of attention.

See:

http://community.livejournal.com/furaffinity/63019.html
thanks that is kind of what I was trying not to post here

Preyfar himself admitted that there had been a breakdown in communication so I hardly think you're going to cause any drama. :)
 

Zephyer

Gogglejackal
I'm getting the same problem as Keffria. Now, instead of SQL errors, I get a loading bar for about five minutes, then stay on the same page.
 
Top