My web application is real time site that need a speed of access on every query (SELECT, UPDATE, INSERT) that must less than 200ms
Because one of my php script task is query (SELECT, UPDATE, INSERT) the work on every secound and then loop every secound alway across web server running.
Well, On average 98% each query time is less than 0.001-0.005 secound on each query stagement. the problem is Sometime around 3 time per hour, the query (SELECT, UPDATE, INSERT) can take up to 1s and may be 5s, so they has waiting and effect the next query loop that damage for whole business application.
i so find the solution such as row locking, turning mysql it not ensure speed of access less than 200ms, sometime it query 5s ooh!! but the solution that i found to reducing the problem are move that from INNODB to MEMORY but sometime still a problem and the important thing are the need the recovery when system has crash.
Example >> in some UPDATE problem that table size < 1M with 30 record and update on primary key it not sence
the question are it has many thing effect each other. HOW to slove this problem. How to start to find problem.
Do you have any idea or algorithm or if u need more some information i will give you.
thank you so sorry about my english. i am thai