The database's schema is as follows.

 CREATE  TABLE  `items` (  
 `id` mediumint( 8  )  unsigned NOT  NULL  AUTO_INCREMENT ,
 `name` varchar( 45  )  NOT  NULL ,
 `main_type` tinyint( 4  )  NOT  NULL ,
 `rarity` tinyint( 4  )  NOT  NULL ,
 `stack_size` smallint( 6  )  NOT  NULL ,
 `sub_type` tinyint( 4  )  NOT  NULL ,
 `cost` mediumint( 8  )  unsigned NOT  NULL ,
 `ilvl` smallint( 6  )  unsigned NOT  NULL DEFAULT  '0',
 `flavor_text` varchar( 250  )  NOT  NULL ,
 `rlvl` tinyint( 3  )  unsigned NOT  NULL ,
 `final` tinyint( 4  )  NOT  NULL DEFAULT  '0',
 PRIMARY  KEY (  `id`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = ascii;

Now, doing an insert on this table takes 0.22 seconds. I don't know why it's taking so long to do a single row insert. Reads are really really fast something like 0.005 seconds. With using the example configuration from here dev mysql innodb it averages ~0.002 to ~0.005 seconds. Why it takes more than 100x more time to do a single insert makes no sense to me. My computer is as follows. OS:Debian Sid x86-x64, Mysql 5.1, RAM:4GB ddr2, cpu 2.0Ghz dual core, HDD 7200RPM 32MB cache 640GB.

Why it's taking almost 100x as much time for a SELECT * FROM items; vs INSERT INTO items ...; will never make any sense to me. It's still a small table at only 70 rows, and took that long even when it had 0 rows.

Edit: Also this table has quite a few other tables linked to itself via the id. There's a few of them out there that are linked to it and do an on update=cascade; on delete=cascade;. I believe that that is the biggest issue here. If it is then, i can probably go in and change it and do individual deletes from the various little things when they are removed. The insert speed seems to be ~0.2 seconds whether i'm doing the insert on just items or i'm also doing it on another table that has a foreign key link to the main one.

  • Can you run top on the server and post a snapshot of it? – Tom O'Connor Dec 25 '10 at 22:45
  • during the inserts? or just when it's on its own thing? Plus, the 'server' that we're talking about is my desktop. Since this is just used for 'local testing'. As i work on it. And its performance when i do the inserts is when i'm doing nothing but working with it. – 133794m3r Dec 25 '10 at 22:48
  • Yup, I think you've nailed the problem with the cascade updates and deletes. See if messing with those settings will do it. Incidentally, it's normal for reads to be faster than writes. There's no referential integrity checks to be done, no indexes to update, etc. – user3914 Dec 27 '10 at 18:19
  • even after doing that, apparently the one that had the biggest problem was the one where i update the number of assets. It finds the two rows via the index on it's category. Then does an amount=amount+1. It is taking ~0.3 seconds it seems. And it has zero foreign keys and only two indexes, one on the id and one on the category. The rest are uber-fast now. That's just the slowest one it seems. – 133794m3r Jan 1 '11 at 3:07
  • With your hardware I'd fully expect there to a be a big difference between read and write speed - you have no battery-backed cache, so all writes have to hit the disk, which is always going to be orders of magnitude slower than reading from RAM, which is what the majority of your reads will be doing. When you have foreign keys to update, that just means more disk operations, so no surprise it's slower. Also make sure you have innodb_flush_method=O_DIRECT set in your my.cnf. – Synchro Feb 14 '12 at 8:35

Well, my first guess is that your InnoDB is probably broken. You can check whether there aren't any

  • triggers that would do slow operation on insert
  • processes going on that would lock the table
  • foreign keys/constraints pointing to this table

Best way to completely audit a database against anything that would cause such trouble is to read schemas dump from mysqldump command.

  • the dump says the same thing, should i just well completely destroy MySQL and wipe the install, and then go get the latest mysql from the mysql site? Also i do have quite a few foreign keys in other tables. Should i remove all of their things with itself? Since i always saw foreign keys as the best way to do joins on tables. The items have something like 10 tables that use its id for the various 'little' types that go along with it. Should i change them to not do on update cascade, on delete cascade? Since i always saw that as the best way to do it. if it's the problem then i'll change it up. – 133794m3r Dec 26 '10 at 0:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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