开发者

Mysql BLOB performance degradation after hitting "a limit" (MyISAM)

I have a rather small mysql DB in which I store an handful of pretty small images (320x480), it all behaved really well until recently, the table size is about 1.5Gb and there are about 34,000 records. At that point...

...All of a sudden the performance degradated by a factor of 100.

It looks like the DB has hit a rock hard limit and it can't cope with it anymore.

Anyone experienced anything like it?

(Please don't suggest to move the images off the DB on the filesystem, we have dec开发者_Go百科ided to do that with the next version of the software)


Possible reason is that your query is not using indexes. When table was rather small, it was not a problem, but a bigger it gets, the tougher it is for MySQL to get your data.

Check if column property_id is indexed. To speed up your query you can add covering index - (property_id, image)

You can see if your query uses indexes and more useful info by adding EXPLAIN to your query:

EXPLAIN SELECT image FROM mytable WHERE property_id = 30000


I am quite certain that the database has started utilizing the disk instead of memory to do an operation. This operation could very well be to store data to temporary tables. Store indexes on disk in instead etc. Figure out what it is, and you solution will be a lot simpler to find.

The performance degration of 1 to 100 would fit with the speed difference between memory and disk.


Do not create an index on the BLOB field, it is useless (and MySQL won't allow it).

Do you have proper indexes ? ie in this case, (property_id) ?...

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜