I have a user_access table (MyISAM) with user access information (like IP, access date,…). New user accesses will be created when user go to the webpage, …and then each minute one user access.
And now my problem, when many users go to the page at the same time then all inserts are locked and this increases the page execution time.
Is it possible to avid this problem? My idea is:
Create a memory drive and on each paga generation create a file with user_access information. After it create a daemon which will be executed in background. This daemon reads each file, create user_access insert (into database) and then delete already processed files.
[B]esudnik wrote on Mon, 08 October 2007 09:45[/B]
Do you have any other (better) ideas?
Much better idea would be to avoid these table locks. To achieve this goal you’ll need to convert this table to innodb. But be aware of innodb table locks for tables with auto-increment fields. But I don’t see any reasons to have such fields in log tables.
If you use auto-increment fields in your logs, then you can try mysql 5.1.22 (warning: it is RC release) which has some solution for auto-inc table locks problem.
Are you performing deletes/updates on this table in parallel?
If you don’t then just make sure that you run OPTIMIZE TABLE after any deletes or updates.
Because MyISAM tables has an optimization that if the table is optimized it can run inserts without blocking the table. Which can speed up things a lot for log tables.
Second suggestion that you should look at is INSERT DELAYED.
Which performs the exact thing you are suggesting.