Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

1 Answer

up vote -2 down vote

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.

share|improve this answer
Thank for answer, But How to know the number of io (read and write) that can effect each query and decrease performance or how number of read and write MEMORY table can effect performance – Domezchoc Mar 25 at 11:05
How big can your table grow? Calculate for the inserts only. How many inserts will it take in a day? And how big is your RAM? – Mannoj Mar 25 at 11:43
OK thank, I use Amazon RDS Master don't convert MEMORY to INNODB on salve. How to set up? – Domezchoc Mar 25 at 11:59
1  
Using MEMORY on master and InnoDB on slave is not sound advice for recovery purposes, because the first access of a memory table on the master, after restart, will trigger removal the contents of the table on the slave so that it's empty just like the one on the master... so this won't work. dev.mysql.com/doc/refman/5.5/en/…... and in RDS, you can't do this anyway. – Michael - sqlbot Mar 25 at 21:55
Thanks for the down vote. Have you tried the method? – Mannoj Mar 26 at 5:15
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.