php/MySQl driven site - opmtimization tips needed

[emin]

Hello All,

I have one site on my box - 1 single site on 1 server
it deals with MySQL it is a statistics server (counter) like spylog or the counter
that adds rows to a table: each counter's visitors IP.
Stats are refreshed every midnight and table is being emptied.
By the end of date number of IP addresses (rows) is close to 40 000 -50 000.

The problem is server's load average:
when the table is empty load is 0.2 -0.5
When it is 20 000 and more it goes over 2-4
By the end of the day load average reaches 10

table type= myIsam
table has one index field

What would be your recommendation to optimze it

For example, I have been said that if queries are collected in an sql file and executed once in 10 minutes it would be faster that executing it query by query, but i doubt it will help - i guess the problem is not number of running queries, but huge number of rows in the table

thanks

[mtimdog]

Well, what does your table structure look like (with indexes), what does your insert/select queries look like?

[emin]

Basically, the structure is like this:
id-------- ip----------- number

Queryes are like this, first query checks if an ip exists in the list, if it doesnot it makes an INSERT if does exist it makes an UPDATE - number=number+1

[mtimdog]

First, I would suggest having an index on both id and on ip. Also, what do your queries look like??

There are ways to optimize mysql itself (of which I myself am not too familiar with)---a good place to start might be maximum number of connections.

Also, you could use an optimization tool for your php code like zend accelerator or zend encoder.