1

due to internal reasons (framework structure) I save many images in a table with a mediumBLOB.

Considering the query to retrive these images are sent with a pretty low rate is there a way to tell mysql to keep off this table from memory? I don't want to have a table of 2GBs in memory used only once in a while.

Are there any way to optimize this?

(Note: if this helps I can move this table in a new database containing only this table)

Thanks

Improve this question

2 Answers 2

Reset to default
3

MySQL won't generate in-memory table for BLOB types, as the storage engine doesn't support it.

http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html

"Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

  • Presence of a BLOB or TEXT column in the table"

Which means you should put the BLOB into a different table, and leave other useful data in a BLOBless table so that table will be optimized.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

What? O_O My main table has a TEXT column (for articles) are you saying that my main table requires disk access to be parsed?? O_O
@yes123: Exactly. Another way to overcome this is to have a big RAM disk and MySQL "tricked" to use that.
No matter if you have big RAM, the problem is that the memory consumption cannot be estimated. One solution would be to use the memory up until XX Gbytes, then drop the in memory solution and turn to disk. Can you imagine the unpredictable and horrible user experience, when at point the TABLE goes too big, and the database engine reacts so that it pushes back the data handling to a 100x times slower medium? (^_^)
then I wonder why my mysql process is so big then.. all my biggest table should be only on disk because of TEXT-BLOB =/ Maybe mysql saves the primary index in the table?
MySQL pushes all indexes into memory, if it fits into the allowed memory (config file: key_buffer_size=XXXM where XXX is the amount in Megabytes)
0

Moving that sort of table into a separate database sounds like a perfectly valid approach to me. At work we have one database server for our operational content (orders, catalogue etc) and one for logs (web logs and copies of emails) and media (images, videos and other binaries). Even running separate instances on the same machine can be worthwhile since, as you mentioned, it partitions the buffer cache (or whatever your storage engine's equivalent is).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.