pouët.net

Pouet slow / offline (25 oct 2004)

category: general [glöplog]
 
Sorry people, but msnbot and googlebot decided to "attack" pouet.net... they were so agressively crawling the entire site (every single product page, userpage, etc, etc, etc) that the sql server was hit my too many queries.. and because pouet has some very very _VERY_ heavy queries on most of it's pages, the server couldn't keep up.

Also the bots did not look at the new robots.txt i put up in a hurry.. so now i wrote some lines in the header of all pages that blocks them actively with a nice error message for them :)

We will also try to do some optimization on the entire site and queries to make it a bit lighter.

Sorry for the inconvenience.
added on the 2004-10-25 23:01:39 by Redhound Redhound
you suck jeffry what took you so long? ;)
added on the 2004-10-25 23:55:49 by psenough psenough
BB ImageBB Image
added on the 2004-10-26 01:07:00 by okkie okkie
(\/)BB ImageBB Image(\/)

hehe.. sorry.. too much *bokplop*
added on the 2004-10-26 01:07:57 by okkie okkie
if index.php info looks frozen, thats because it is.

small bug with update into static feed.. should be fixed tomorow or so.. O_o
added on the 2004-10-26 01:32:06 by psenough psenough
let's ask msn and google to remove pouët from their pages!
added on the 2004-10-26 07:34:05 by stil stil
let's ask ps to make sane sql queries!
No, just surf Google chache the next time pouet isn't reachable. ;)
select *;
MySQL tips

Connection timeouts:
if apache uses persistent connections (mysql_pconnect), apache children open connections to the mysql db and leave them open even if unused. Put this:

set-variable = wait_timeout=300

in my.cnf and mysql connections die quicker when idle, leaving more room for others. You can also increase the maximum concurrent mysql connections in my.cnf.

Indexes:
This is the most powerfull, easily implemented yet frequently unused performance feature in MySQL. if a field is used in WHERE, ORDER BY, JOIN <table> ON clauses it is a perfect candidate for an index. Just add it with:
ALTER TABLE <tablename> ADD INDEX <index_name> (<field_name>);

if the field is a VARCHAR or TEXT (strong indication of bad db design), you should specify the size of the index, typically around 8 chars, like this:
ALTER TABLE users ADD INDEX key_username (username(8));

Indexes increase reading performance (sometimes dramaticaly) at the cost of increased disk space usage and slower writing performance. Pouet does mostly reads, like every website.

Query cache:
MySQL 4.0 and greater does query caching so it's prefered. I think pouet uses 5.0 so that's no problem. Query cache though can be tweaked in my.cnf if the machine has lots of memory.

Heavy iron stuff:
You can create replicating MySQL setups _very_ easily. That way, you can spread the load between replicating servers and handle extremely high loads. However this needs alot of cache and is obviously out of the question for pouet.

ps, my time is quite limited, but if you have SQL queries which give you grief, send a copy.
added on the 2004-10-26 17:37:35 by moT moT
oops, this needs a lot of cash of course...
added on the 2004-10-26 17:39:09 by moT moT
we (well, matti) replaced yesterday most of the main page queries with include/cache.inc files which get generated only when new info is added.. (on add.php for example) so, things should be quite faster now.. we'll keep optimizing things though. :)
added on the 2004-10-26 19:41:20 by psenough psenough

login