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.
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.
you suck jeffry what took you so long? ;)
(\/)(\/)
hehe.. sorry.. too much *bokplop*
(\/)hehe.. sorry.. too much *bokplop*
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
small bug with update into static feed.. should be fixed tomorow or so.. O_o
let's ask msn and google to remove pouët from their pages!
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.
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.
oops, this needs a lot of cash of course...
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. :)