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

[iberkner]

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

[gmouse]

Does the key fit in key_cache?

[iberkner]

Where / how do I detect / measure that?

[gmouse]

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

[iberkner]

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

[gmouse]

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.

[gmouse]

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

[iberkner]

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

[vgatto]

If 99% of your queries only need to get wordId, then you should consider changing your index on listId to (listId, wordId). This is called a covering index, and it allows MySQL to answer that query using only the index, removing the need to lookup data rows.


Regarding key cache stuff, you should be checking the following to determine if your key buffer is adequately sized.

SHOW STATUS LIKE 'Key_reads';
SHOW STATUS LIKE 'Key_read_requests';

Key_reads is the number of time MySQL used disk instead of the key buffer, and Key_read_requests is the total number of times a key was needed.

Also, since MyISAM only stores indexes in memory, not data, you want to make sure to leave some memory for the OS filesystem cache, which is responsible for caching the data.

[iberkner]

Thank you for your ongoing suggestions, excellent!

I have added the wordId to the index.

MYI file size is at 677M, for now, I will keep key buffer at 2000M, unless you think its just to much.

Per your suggestions, I ran the show status queries, bleow are the results, but I can't tell if they are good or bad?

Key_read_requests 178949107
Key_reads 287589

Thanks!

[vgatto]

It basically means for every 600 or so key lookups, one of them will need to read from the disk. That's very good, it usually indicates that your key buffer is large enough.

You can also do

SHOW STATUS LIKE 'Key_blocks_unused';

which will give an idea of how much of the key cache isn't being used at all. I'm not sure what the key cache block size is, but I'd guess it's probably 16K like InnoDB. If the number of unused blocks is fairly large, you can probably reduce the key buffer size. Having a little room for growth is always a good idea, though.

[gmouse]

gmouse wrote on Thu, 20 August 2009 12:44

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

[iberkner]

When you mention looking at the process list, I'm constantly looking at it, but am not seeing anything "lock up". Is there a particular way I should be looking at it?

[vgatto]

You can check out this status values:

SHOW STATUS LIKE 'Table_locks_immediate';
SHOW STATUS LIKE 'Table_locks_waited';

That should give you some idea of how often a query must wait to obtain a table lock. If Table_locks_waited is very low compared to Table_locks_immediate, chances are you're not having locking problems. I haven't tried to use MyISAM in a high read-write concurrency setup, so I have no idea how close these two numbers can get before it signals a real problem.

[iberkner]

Looks like we are ok...

Table_locks_immediate 38358888
Table_locks_waited 118385

?

Thanks