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
Best Answer
You may switch the table from innodb to memory, but ensure the size of the table isn't that huge so that it doesn't eat up entire of its memory. Yes, you may safe guard or recover the table during system crash. Have master/slave replication setup and once the table is created on master with memory as engine type and change the table engine type to innodb (on slave only). So that even at the instance of master crash/restart you will get the data from slave.