| Are rows or fields stored in the INNOdb Buffer? [message #1910] |
Wed, 19 September 2007 19:15  |
 SabreCEO
Messages: 4
Registered: September 2007 |
Junior Member |
|
|
Here is my problem, I have a table for products which is about 1,000,000 rows, and 5GB. Some queries (especially with ORDER by) take a very long time, and then go quickly when called again for a while. This happens even with query cache off. My best guess so far as to why this is, is that the first time I run a query the data is not in the INNOdb buffer, but subsequent queries run faster because then the data is in the buffer.
Currently my buffer is set to 2GB. I thought that if my queries never use a field, such as description, then that field would not be with the row in the buffer, like in the query cache. However, if all of the data of the row is put in the buffer, regardless of what fields you use, that would explain my problem.
Can anyone confirm if all the data for a row goes in the innodb buffer, or just the fields you are using?
If it is all the data, then I imagine I could speed things up by putting the description field in a separate table, because this field can be 1KBytes - 4KBytes in size. It's only used when someone on my site clicks on a product. In the results list it's never used. So assuming that the innodb buffer pool puts all the data in the buffer, if I remove all the descriptions the table should be significantly smaller and fit in the buffer, and queries would go faster...
Thank You for your help I've been trying to figure this out for days!
-Mark
|
|
|
|
| Re: Are rows or fields stored in the INNOdb Buffer? [message #1922 is a reply to message #1910 ] |
Thu, 20 September 2007 19:46  |
|
AFAIK, InnoDB buffer pool "caches" entire innodb pages (16kb blocks of records/indexes) and it does not work with records/fields on this level. So, if you read one field from one row in some page, it would read an entire page from the disk and then would store it in buffer pool.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
| Re: Are rows or fields stored in the INNOdb Buffer? [message #1923 is a reply to message #1922 ] |
Thu, 20 September 2007 21:07 |
SabreCEO
Messages: 4
Registered: September 2007 |
Junior Member |
|
|
| scoundrel wrote on Thu, 20 September 2007 19:46 |
AFAIK, InnoDB buffer pool "caches" entire innodb pages (16kb blocks of records/indexes) and it does not work with records/fields on this level. So, if you read one field from one row in some page, it would read an entire page from the disk and then would store it in buffer pool.
|
That's what I though so I made a new table, with itemid as a primary key and description. And I'm storing the descriptions compressed (takes about 1/4 the size). That seems to be working fine but it doesn't seem to have really sped up my products table yet. However, I think that's because I didn't drop the description field, I just set them all to blank. My partition where mysql's data is, is only 12 GB, and 11 GB are used. So I can't optimize the table (or alter it) (the diskspace would fill up and crash!). So it's probably fragmented a lot now. The only think I can think of is to export all the innodb tables, shutdown mysql remove the innodb data file, start back up and import the data again. I've done this once before and it wasn't fun and took me from midnight to 8am. I'd rather not do that, but if I have to I will. Any other suggestions?
Thanks,
Mark
|
|
|
| Re: Are rows or fields stored in the INNOdb Buffer? [message #1924 is a reply to message #1923 ] |
Thu, 20 September 2007 22:53 |
|
In such cases (not so big datadir) I prefer to use innodb file per table so I can optimize tables once in a while and get my space back.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
Members
Search
Help
Register
Login
Home
