MySQL Optmization

New solution needed - critical

[FredrikMH]

I have file named tracker.php which take user_id parameter like tracker.php?u=#*$!. The user_id is stored in MYISAM table together with the ip-number of the request. These two fields have a unique-indexed so I don't get duplicate ips for each user (I calculate unique visitors based on the ip). After that the tracker gets the users current ranking from memcached and displays an image with the number on it.

It used not to be a problem but now i have about 180 requests/second and a few times/hour a script will start calculating ips, which will lock the tables for writing which is not good. I get a queue with 5000 connections very fast and my orginal site get slow or reach "too many connections".

So how can fix this? What I understand is that MYISAM is the fastest engine for writes but not if we are mixing write/reads. So my idea was to get a seperated server, which will slightly lower the the load on the main server. This server is simply a mysql-server tracking the visitors and if it get under heavy load or a big queue comes from nowhere, it will not affect the orginal site.

But it would be great to optimize the tables on this server anyway. So how should I build my tables with ip and user_id? Could MYISAM Merge Tables be the solution or should I go with an InnoDB?

And If I understand right a normal MYISAM table can't effective use multiple processors/cores for writes? Since the server is a standalone mysql-server which are only doing this I should take advantage of all the hardware I can?

Please help me here :)

[physics]

180 requests/second and a few times/hour a script will start calculating ips, which will lock the tables for writing which is not good.

Is it set up to happen a few times an hour or is this a 'random' phenomenon? If it is planned maybe doing this more or often will help.

Maybe you could have two tables, table_a and table_b. For one period of time (say a minute or whatever works for you) you write to table_b and read from table_a. Then for the next period of time you write to table_a and read from table_b. Repeat. Not sure if this works for you though since I don't know what your site 'does' ;)

[FredrikMH]

Currently the table looks like this

Table `visitors`, Engine=MYISAM, Unique Index over both columns
site_id ¦ int(11)
ip ¦ varchar(15)

as I said the tracker inserts the ip into this table. The table is reset every week, which means at the end of the week it has over 15 million rows.

The calculation script is a cronjob going throguh 3000 sites per run and I recalculate everything from the beginning (basically I count how many rows exist for a specific site_id). I could add a time column and just add the new ips, but I'm not sure if it will speed anything up.

So I'm thinking of storing the ips per day instead of week now which will give me 7 tables and change the carchar to 4 tiny int. This should decrease the size of the table whitout loosing performance.

site_id ¦ int(11)
ip_1 ¦ tinyint(4)
ip_2 ¦ tinyint(4)
ip_3 ¦ tinyint(4)
ip_4 ¦ tinyint(4)

I will also be able to merge all the tables together into a new one.

The writing problems I can use something like you said. I store a variable in memcached telling that "update in progress" and tracker will then temporarily store the ips in a temp talbe which is an exact copy of the one above. When the update is done all ips will be copied from the temporary table into the ordinary. At least I will not start a queue due to a locked table.