Take the 2-minute tour ×
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.

The Setup here is: OS: Ubuntu 12.04 (1 Core and 1.75 GB of RAMO) MySQL Version: 5.5

There are about 8 to 9 tables in the database. On initially starting mysql the queries are fast at around 0.05 - 0.10 seconds, however after sometime the INSERTS and DELETES slow down to around 1 - 2 seconds, however the SELECTS are still around 0.05 - 0.10 seconds.

Then after rebooting the system the situation reverts back to how it was in the beginning of the previous paragraph. Everything is fast, but then INSERTS and DELETES slow down tremendously after a while.

Note: I'm just doing simple inserts and deletes on very few records, and the database starts with empty tables in the beginning.

Any insight?

Edit: Create Table of 1 of the Tables

| FeedUps | 
CREATE TABLE `FeedUps` (
  `post_id` int(10) unsigned DEFAULT NULL,
  `liker` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `post_id` (`post_id`,`liker`),
  KEY `liker` (`liker`),
  CONSTRAINT `FeedUps_ibfk_1` FOREIGN KEY (`post_id`) 
      REFERENCES `Feed` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FeedUps_ibfk_2` FOREIGN KEY (`liker`) 
      REFERENCES `Users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
share|improve this question
    
Can you add the structure of 1 or 2 tables (the output of SHOW CREATE TABLE table_name;) that exhibit this behaviour? –  ypercube Mar 27 '13 at 23:32
    
@ypercube added it. note that the table size doesn't matter, like when I restart the OS, the table contents are the same i.e same number of rows, and its fast on execution then, however after some inserts the performance degrades –  raj_n Mar 28 '13 at 0:48
    
@ypercube I have a feeling that it has to do with some sort of buffer cache. On restarting the OS that gets cleared and the DB executes fast. –  raj_n Mar 28 '13 at 0:50
    
@ypercube however I don't know what exactly to change out there. –  raj_n Mar 28 '13 at 0:50
    
You are low on resources as you mentioned 1 core and 1.7 gb ram which are near to nothing. Share your my.cnf file so that we can look into this, there might be parameters not defined correctly, what else is running on the box. How busy is your server. Masood –  Masood Alam Mar 31 '13 at 17:49

1 Answer 1

up vote 0 down vote accepted

I found the problem, in the my.cnf file I had turned on TABLE_CACHE.
The way I resolved it without having to restart it was by using the command FLUSH TABLES. Shortly after that the inserts/updates/deletes resumed working at normal speeds.

share|improve this answer

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.