what is a mysql 'packet', anyway

Question

Mysql 5.1: if we want to use very large blobs, apparently we need very large packets, configured identically on both ends. How large can we go before something explodes?

See this.

Answer 1

what is a mysql 'packet', anyway

That page you linked to describes exactly what a packet is. It is:

• A communication packet is a single SQL statement sent to the MySQL server
• A single row that is sent to the client
• A binary log event sent from a master replication server to a slave.

So, if your packet size is set to 16Mb, then you won't be able to return a row larger than 16Mb in size. If you have the potential to be storing a 500Mb BLOB then you will need to increase your packet size to > 500Mb to accomodate this.

How large can we go before something explodes?

MySQL cannot return a single row larger than 1Gb, so I guess your maximum blob size is 1Gb - size of other fields in row

Answer 2

First thing you need to understand is what is the definition of a MySQL Packet which I addressed in SuperUser.

I have had cases at work where I set max_allowed_packet to 512M without incident over these years. I wrote something about this in the DBA StackExchange.

You will need to make it this option large enough to accommodate at least 10 BLOBs. If you have BLOB that big, you should store the URL of the BLOB and keep the BLOB in an external file.

CAVEAT

PostgreSQL does that automatically in TOAST files. (TOAST : The Outside Attribute Storage Technique for column data whose size exceeds 1GB).