Website statistics system on php/mysql

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I am in process of optimizing (or at least trying to) a php/mysql system serving as a web stats for a number of users. It is actually a counter system that logs IP addresses, Referrers, Pages visited etc. for users.

Currently it is under FreeBSD, php 4.3.10 and Mysql 4.1

What it basically does is inserts values in tables and updates if duplicates exist.

At the beginning we had no problems with preformance, but since number of users started to grow, server is experiencing problems.

Load averages reaches 80-90 at peak hours,

Here are some statistics
Queries per second
108.35
Query type
change db 194,683.42 per hour 52.03 %
insert 87,656.15 per hour 23.43 %
select 59,077.12 per hour 15.79 %
update 16,373.88 per hour 4.38 %

Tables type Myisam

Tables have one uniqe index bound to 2 fields.

Server is Celeron 1.8, IDE Disk, 512 Mb Ram

What would be recommendations to improve performance with the same hardware.

What about using LOAD DATA INFILE instead of multiple INSERTS ?
How to perform ON DUPLICATE query on using LOAD DATA…

Thanks a lot

EMin

2

You should clarify what you’re trying to optimize.

See what kind of queries or operations are limiting performance and think from where.

good MySQL config is important but it is secondary to queries and schema.