Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

MySQL Tables braking!

Quote Reply
MySQL Tables braking!
Hi guys,
My website is really growing fast and now has 30,000 daily visitors and A LOT of searching is going on. We are hosting on a Dual P-III 700Mhz with 1Gb RAM. Apache is set up with a limit of 200 connections and MySQL with a limit of 700 queries. We are running under mod_perl.
Over the last few days, many tables from different databases at our servers have broken (the table says to have -1 records).
When the server´s load average exceeds 12, we turn of mysql.server and then turn it on again, solving the problem.

This is the latest error we´ve had:

DBSQL (8124): Fatal Error: Unable to execute query: INSERT INTO
Hits_Track (LinkID, IP) VALUES (3991, '').
Reason: Got error -1 from table handler at
/usr/local/etc/httpd/sites/monografias.com/cgi-bin/jump.cgi line 82
Can anyone help?

Quote Reply
Re: MySQL Tables braking! In reply to
Are you sure the server's load is 12 ? Or 12% ?

You have plenty of RAM... are you running under NT or Unix (I'm assuming UNIX).

What sort of Disk I/O subsystem are you using? You might be hitting an I/0 bottleneck on disk writes and reads.

Also, let's do a little math.

Mod_perl Apache is about 4 Meg per Unix process. (average). 200 processes running is 800 meg, plus the overhead control. MySQL is a 12-15 Meg process, that grows as it needs to. The more requests it's serving, and more tables it's got open, the more room it's going to require.

So, my point is that even with 1 GB of RAM, you might be running out of RAM, and doing a lot of disk swapping. If that's true, then we sort of go back to the previous I/0 problem.

You might be a candidate for dual-apache (proxy the mod_perl requests to a back end apache that's 4 Meg, but run 100 1 meg "light weight" servers to do the page serving). That could reclaim over half the RAM you are using with "fat" apache processes that are not actually processing CGI requests.

So, this begs the questions: What's your "swapfile" stats (how much are you using, and how much of your RAM is being used??

FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: MySQL Tables braking! In reply to

A couple questions about your configuration:

1. You say you are running under mod_perl, do you have it behind a proxy, or are you just using mod_perl directly? If you say directly, you will get huge performance increases by moving it behind a proxy.

2. Are you running any other scripts that are heavily used not under mod_perl? i.e. banner ads, etc. Running something using CGI under very heavy loads can be deadly.

3. You mention a p3 with 1 gb of ram. Is this just for you (i.e. dedicated) or are there other accounts?

On the face of it, your hardware should definately cover your traffic, but we'd need some more details to be able to help.



Gossamer Threads Inc.
Quote Reply
Re: MySQL Tables braking! In reply to
The server´s load exceeds 12 (not 12%) but that only happens onces or twice a day, when somehow it seems that processes get stuck and it becomes a big snowball that grows by the second. By stopping and then starting the apache server, the load goes down back to 1 or less, so it´s fine.
We´re using Linux.
mod_perl is not running behind a proxy, although we might do this in a while.
The scripts that shouldn´t run on mod_perl have been replaced and are not using mod_perl, so that´s something we´ve fixed.
The server is mainly just for the site. There are a few other webs but they are much smaller ones.
Anyway, we´ll work a bit more with what you commented, it was really helpful.