0

I have a really small script that takes some parameters from the request headers and sends a single INSERT query to a db hosted on localhost.

The DB has one table and I do not run any kind of reporting or anything, so basically 100% of the queries to this server are INSERT queries.

The script is being heavily accessed so I get a lot of these queries running concurrently.

I'm looking for ways to tweak the config file to support my specific needs.

EDIT

I'm currently seeing these variables on phpMyAdmin->status that seems high:

Slow_queries 22
Handler_read_rnd 1,496 k
Handler_read_rnd_next 111M
Created_tmp_disk_tables 495
Sort_merge_passes 77
Opened_tables 332
Table_locks_waited 154

The reason I'm asking is because I'm going to get a lot more concurrent connections soon and I want to make the tweaks before so the server can handle it.

Radu Murzea
  • 10,724
  • 10
  • 47
  • 69
user838437
  • 1,451
  • 7
  • 23
  • 31

1 Answers1

0

From what you've told us, the only recommandation I have for you is to convert your table to use InnoDB engine, if it currently uses another engine. The reason for this is that, in InnoDB, INSERTs are very fast (the rows are stored in the order of the primary key, so they're basically just appended to the end of the table).

Radu Murzea
  • 10,724
  • 10
  • 47
  • 69
  • OK.. I'm currently using MyISAM - how much of a difference should I expect after changing this to InnoDB? – user838437 Apr 24 '12 at 12:39
  • @Sobolan that cant be right. MyISAM and InnoDB are very different internally. So if I have a email address as a primary key in InnoDB it is going to append it to the end? Not a chance. InnoDB stores its information in a btree. Thus it will place the email address to where it fits in the order of the primary key. MyIsam works differently see http://stackoverflow.com/questions/2362667/how-b-tree-indexing-works-in-mysql – Namphibian Apr 24 '12 at 12:50
  • Also read this http://rorguide.blogspot.com/2011/06/difference-between-innodb-and-myisam.html InnoDB is great for ACID complaint tables but in your case MyISAM probably makes more sense. Essentially in a MyISAM table records are always appended at the end. A MyISAM is a heap with the primary key being a index on top of the heap. This makes it fast for logging data. However the two are extremely different. In a InnoDB data is stored in the order of the primary key. In a MyISAM it is appended at the end with a index on top pointing to the record position in the MyISAM file. – Namphibian Apr 24 '12 at 12:55
  • @Namphibian I assumed he had a primary key that is an auto-incrementing ID. Maybe that's not the case, but if it is, then the `INSERT`s will indeed just append to the end of the table. And InnoDB doesn't store its information in BTrees. Some types of indexes are represented as BTrees, true, but it's a completely different thing. – Radu Murzea Apr 24 '12 at 16:57
  • @SoboLABN. I agree with your statement dont get me wrong InnoDB might be fasters. It depends. However when you create a primary key on a InnoDB it is stored as a b-tree on disk. The MySQL documentation states: All data in InnoDB is stored in database pages that make up a B-tree index (the clustered index organized according to the primary key columns). Here is the link http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html – Namphibian Apr 25 '12 at 06:36