4

I have a very simple table

CREATE TABLE IF NOT EXISTS `largecache` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tooltip` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `attributes` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tooltip` (`tooltip`)
) ENGINE=InnoDB

With about 8 million entries that is used almost entirely for reads. I'm trying to make sure I'm accessing the data as quickly as possible. I'm currently using InnoDB, is it worth it to switch to MyISAM? My only concern is performance on reads. Also any other recommends I could use to speed up since the DB reads are pretty much my only bottleneck in my application.

I'm using MySQL client version: 5.1.47

Thanks.

[edit: More details] I'm using php, and I'm querying based on exact matches of the tooltip field. It's being hosted on a shared VPS with 1 gig of ram, any tweaks to mysql that I can make I'll try, but I know mysql tweaking is a pretty complex issue.

Example query:

select * from largecache where `tooltip` = "Cm8IuIyq9QMSBwgEFS0iGWAd30SUNR2EHJ0nHYYCY-odxZ6okR0pEnPgHWzQbvIiCwgBFXZCAwAYCCAiMAk4_gNAAEgPUBBg_gNqJQoMCAAQvKHZ5oCAgIA6EhUIrcC1xggSBwgEFUn1i18wCTgAQAEY2ojJgQxQAlgA"

I do have APC installed, but not memcached

Srikar Appalaraju
  • 71,928
  • 54
  • 216
  • 264
Rob
  • 2,332
  • 7
  • 31
  • 35
  • Without details more efficient answer would be "migrate to SSD and install 64Gb of memory for buffers" – zerkms Sep 25 '12 at 03:01
  • Still nothing useful in the details. What kind of queries do you perform? Is it possible to cache the results in memcached? Of all 8Gb of data - what's distribution for what you select often and rare? – zerkms Sep 25 '12 at 03:06
  • I tried to add a few more details, if there's anything specific that would be useful let me now. – Rob Sep 25 '12 at 03:06
  • best way to speed up reads is to have enough ram in the computer that indexes can be fully cached in ram. Anytime your db has to hit disk to read an index, performance will tank – Marc B Sep 25 '12 at 03:07

3 Answers3

7

It would be good to have the exact query you do. But based on what you have posted here goes-

Are you planning to do any full text search (read myISAM)? Since innodb does row level locking as opposed to myISAM which does table level locking by the looks of it innodb suits your requirements. Try the following using innodb engine-

  1. Put an index on tooltip field.
  2. Try to disable transaction management from innodb config. Although this is more for writes than reads.
  3. Try to increase the cache size for innodb. All these DB engines are suckers for memory, its as simple as throwing more memory at them & suddenly they behave well.
  4. Finally, I cannot end this post without mentioning memcached. This is a distributed cache management module & it awesomely integrates with mysql. This can significantly boost your read operations.

Provided links wherever appropriate.

Check this for completeness sake - Why use InnoDB over MySIAM

Community
  • 1
  • 1
Srikar Appalaraju
  • 71,928
  • 54
  • 216
  • 264
  • I added an example query. Thanks for info. – Rob Sep 25 '12 at 03:14
  • got it, since you are using `tooltip` to find your row, index the hell out of this column and put cache in place at multiple levels, cache at innodb level, cache at memcached level. – Srikar Appalaraju Sep 25 '12 at 03:16
  • Sorry this might sound like a silly question, but for #1, is an index needed since I have it set as a unique key? I was always under the impression that did both? – Rob Sep 25 '12 at 03:17
  • oh yes, overlooked the `unique` constraint. Just a clarification UNIQUE and PRIMARY KEY(s) are constraints, not indexes. Though most databases implement these constraints by using an index. – Srikar Appalaraju Sep 25 '12 at 03:22
  • Thanks for help and all the links, I have some tweaking to do now :) I'll take a look at memcached too. – Rob Sep 25 '12 at 03:26
1

With the default innodb in 5.1x, all things equal, you might eek ou a little performance boost from myisam if properly tweaked based on only your limited details.

My suggestIon is go to 5.5 and innodb.

In reality, we need to see some of your queries to understand how you are accessing it. Do youhave lots of count(*) 's?

Also, if you're dealing with million of rows 1G of RAM might a little tight--assuming this is not the only table you have. This is probably your biggest issue regardless of type of table type you choose.

Ray
  • 40,256
  • 21
  • 101
  • 138
  • I added an example query, the only thing I do is query based on an exact `tooltip` match. – Rob Sep 25 '12 at 03:15
  • Based on your query example, given enough memory with vanilla mysql 5.1 I'd stick with MyISAM. – Ray Sep 25 '12 at 13:19
0

Seems like it's more on the MyISAM side. In response of "any other recommends I could use to speed up" you can implement full-text search.

kta
  • 19,412
  • 7
  • 65
  • 47