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
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