Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that holds large BLOBs (up to 10M), and is updated frequently. The problem is that UPDATE statements can take up to 1 second to execute, and due to app design this blocks UI. I need to speed up those UPDATE/INSERT statements. Is there a way to do it by tuning MySQL server/storage engine/etc?

The table in question is InnoDB, and I have tried tunring compression on, but that didn't seem to make a lot of difference. Client is on the same machine as server so there is no network overhead. Server is MySQL 5.5

share|improve this question
    
Are you using Amazon MySQL RDS ??? –  RolandoMySQLDBA Jul 18 '13 at 17:17
    
No, this is our own XenServer + Nexenta –  rootkit Jul 18 '13 at 18:35

2 Answers 2

This looks like a job for InnoDB Architecture Tuning !!!

InnoDB Architecture

Where can BLOB data become a bottleneck for InnoDB? Three places

Place #1 : InnoDB Log File

The size of the InnoDB Log Files (set by innodb_log_file_size) could be a bottleneck if there are many transactions being written that update BLOB data.

Place #2 : InnoDB Log Buffer

The InnoDB Log Buffer (set by innodb_log_buffer_size) can be a bad bottleneck if it it set smaller that the BLOB itself. The default is 8M. You may need to resize it to accommodate multiple rows that have that BLOBs. Perhaps 128M would be a good place to start. That can also reduce disk I/O when writing to the InnoDB Log Files.

Place #3 : The MySQL Packet

What is a MySQL Packet?

According to the Book

Understanding MySQL Internals

Page 99 Paragraphs 1-3 explains it as follows:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Anytime there is transmission of MySQL Data, the MySQL Packet channels MySQL Data into the OS and network. Like the InnoDB Log Buffer, the MySQL Packet must accommodate multiple BLOBs as well.

EPILOGUE

I have discussed this topics in the past

CAVEAT

Compression may have made things a little worse. Why ?

When compress an InnoDB table, any time data and index pages for that table is read, the page and an uncompressed copy of the page coexist in the InnoDB Buffer Pool. If you want to keep the table compressed, you must expand the InnoDB Buffer Pool (set by innodb_buffer_pool_size). I wrote about this as well as an update to an old answer of mine : innodb_file_format Barracuda

UPDATE 2013-07-19 16:11 EST

Based on your last comment

Initial impressions: while tuning InnoDB buffers helped a bit, it seems like more improvement came from turning off binary logs for this particular table. Still monitoring the performance...

I have a suggestion

You may need to rearchitect your InnoDB files so that all .ibd files are on the data volume, while other MySQL components (ibdata1, ib_logfile0, ib_logfile1, all binary logs) could be mounted on another disk. I wrote about this before in a post about PostgreSQL : Postgres Write Performance on Intel S3700 SSD, The person asking that question said he got a 10% increase in performance.

UPDATE 2013-07-26 15:33 EST

Since I asked

QUESTIONS : 1) How much RAM is on the DB Server? 2) What is the result of SHOW VARIABLES LIKE 'innodb_file_per_table';; 3) What do you get when you runSELECT SUM(data_length+index_length) FROM information_schema.tables WHERE engine='InnoDB';?

and you said

8gb RAM, 4gb available to MySQL. 'innodb_file_per_table', 'ON'. SUM is 3316736000

I recommend the following (if you have not already done so)

Give it a Try !!!

share|improve this answer
    
Thank you very much for the exhaustive answer! My innodb_log_file_size was already increased to 96M, changed that to 128M, increased pool size to 128M as well. max_packet was already at 1G. Tests seem to indicate that updates are indeed much faster, I am seeing ~100ms. I'll put in the changes on production system, and if the results are the same will accept the answer. –  rootkit Jul 18 '13 at 18:55
    
Initial impressions: while tuning InnoDB buffers helped a bit, it seems like more improvement came from turning off binary logs for this particular table. Still monitoring the performance... –  rootkit Jul 19 '13 at 20:01
    
Unfortunately it is still lagging. We use New Relic agent, and it is showing us UPDATE operations that last up to 7 seconds. Oddly enough INSERTs are not a problem... –  rootkit Jul 26 '13 at 18:56
    
QUESTIONS : 1) How much RAM is on the DB Server? 2) What is the result of SHOW VARIABLES LIKE 'innodb_file_per_table';; 3) What do you get when you runSELECT SUM(data_length+index_length) FROM information_schema.tables WHERE engine='InnoDB';? –  RolandoMySQLDBA Jul 26 '13 at 19:15
    
8gb RAM, 4gb available to MySQL. 'innodb_file_per_table', 'ON'. SUM is 3316736000 –  rootkit Jul 26 '13 at 19:23

1) For a table with several columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and referencing it with a join query when needed.

2) since the performance requirements to retrieve and display a BLOB value might be very different from other data types, you could put the BLOB-specific table on a different storage device or even a separate database instance. For example, to retrieve a BLOB might require a large sequential disk read that is better suited to a traditional hard drive than to an SSD device.

You can refer this existing SO link as well http://stackoverflow.com/questions/5318961/optimizing-mysql-files-in-database-blob

share|improve this answer
    
The table in question already contains only the blob column. Besides, its UPDATE/INSERT statement that I'm looking to optimize, not SELECT. Putting blobbed table on separate device wont help as this is virtualized environment. How about a RAM disk? –  rootkit Jul 18 '13 at 14:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.