index query on table with 14mm+ (and growing) records takes more than 2 seconds

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

Looking for help.

We have a table of “child records”. Very simple structure (see below):

CREATE TABLE list_word_map (
id int(20) unsigned NOT NULL auto_increment,
listId int(11) unsigned NOT NULL default ‘0’,
wordId int(20) unsigned NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY listid (listId)
) ENGINE=MyISAM AUTO_INCREMENT=28599140 DEFAULT CHARSET=latin1

This table currently has about 14,000,000+ records (and growing). On average, each parent “list” has about 20 children records in this table.

99% of the queries into this table, look like:

SELECT wordId FROM list_word_map WHERE listid = 12345

These queries on average take 2 to 3 seconds, which considering the size of the table, maybe an acceptable number, but we’re doing a lot of lookups into this table (probably triple the lookups as compared to new records or updates).

Is there a good way (other than sticking the table in RAM, which I will consider if necessary) to speed up these lookup queries? Maybe changing table type to InnoDB (but I’m not sure).

Also, it seems that the same query into the table should take a lot less time due to caching, however, I noticed that running subsequent queries (the same query) takes the same amount of time (i.e. very little to no caching seems to be happening.

Any suggestions? Will be happy to provide additional information if requested.

Thanks

2

Does the key fit in key_cache?

3

Where / how do I detect / measure that?

4

Find the size of all frequently used indices and check the ini setting for key_buffer_size.

5

If you don’t mind, how do I find the size of all frequently used indices?

Also, key_buffer would be a setting in my mysql configuration file?

thanks

6

Yup, ini setting.

Take the sum of the *.MYI files of your frequently queried tables. Or just the sum of all MYI files, if memory allows.

7

Have you ever checked whether you have locking issues? Check the process list for threads in locked mode.

8

So I added up all of my .MYI (thanks for the recommendation) and turns out that they are around 621M.

I’ve set my key_buffer size to 2000M (just to give it some room), but maybe 1000M is sufficient?

What do you think?

Thanks

9
[B]gmouse wrote on Thu, 20 August 2009 12:44[/B]
Have you ever checked whether you have locking issues? Check the process list for threads in locked mode.
10

It is hard to imagine that with queries taking 2-3 seconds you do not have any locking issues.

You could try [URL]http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html[/URL] for checking how those 2-3 seconds are split among several tasks.

11

Yes, what is your server actually doing?

If I run a test on my laptop with 15m+ records with your table design and the average 20 rows per listid.
I can do the first query in 0.27 seconds (before the index is load into key_cache) and consecutive queries in the <0.1 seconds time span.
And that is with just 8MB key_cache.

So I think you need to start by looking at what the server is actually doing instead of looking at what MySQL is doing.
Is it swapping?
Do you have high IO wait?
etc.

Good Luck! )