| Any ideas for performance optimization? [message #2028] |
Mon, 08 October 2007 09:45  |
 esudnik
Messages: 9
Registered: March 2007 |
Junior Member |
|
|
Following situation:
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.
Do you have any other (better) ideas?
|
|
|
| Re: Any ideas for performance optimization? [message #2030 is a reply to message #2028 ] |
Mon, 08 October 2007 11:28  |
|
| esudnik wrote on Mon, 08 October 2007 09:45 |
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.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
| Re: Any ideas for performance optimization? [message #2032 is a reply to message #2028 ] |
Mon, 08 October 2007 16:36 |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
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.
[Updated on: Mon, 08 October 2007 16:39]
|
|
|
| Re: Any ideas for performance optimization? [message #2038 is a reply to message #2032 ] |
Tue, 09 October 2007 03:41 |
esudnik
Messages: 9
Registered: March 2007 |
Junior Member |
|
|
|
Thank you! INSERT DELAYED is a good idea!
|
|
|
Members
Search
Help
Register
Login
Home
