With JDBC using a PreparedStatement and #setInputStream I have not found "max_allowed_packet > N" to be required. With a max_allowed_packet of say 16M, I still have been able to insert LOB data as large as 300MB.
R. -----Original Message----- From: Rick James [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 3:16 PM To: 'Jeremy Cole'; Robert DiFalco Cc: 'Sergei Golubchik'; [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Streaming LOB Data Related inequalities: Given a blob of N bytes: max_allowed_packet > N innodb_log_file_size > 10 * N (if InnoDB) And maybe issues with bulk_insert_buffer_size innodb_log_buffer_size > -----Original Message----- > From: Jeremy Cole [mailto:[EMAIL PROTECTED] > Sent: Monday, May 08, 2006 2:55 PM > To: Robert DiFalco > Cc: Sergei Golubchik; [EMAIL PROTECTED]; mysql@lists.mysql.com > Subject: Re: Streaming LOB Data > > Hi Robert, > > > Anyone know for sure if the memory needed to insert a LOB is a > > percentage of the system's available memory or if it is > allocated from > > the innodb_buffer_pool_size? IOW, how should my > configuration settings > > be modified to allow the insertion of larger blobs? :) > > The majority of the memory needed for that operation will come from > the system's available memory. How much memory it will consume will > depend somewhat on how the query is sent over. > > You should count on at least 2x the size of the blob being needed on > the server for a query of the form: > > INSERT INTO tbl (id, image) VALUES (<id>, <blob data>); > > The original query will be stored in its original form, and the binary > data will be stored in its parsed and unescaped form. > > The storage engine may still make yet another copy of it, but I'm not > sure that InnoDB does. I suspect it does not. > > One thing you can do to save some of the memory is to run it as: > > INSERT INTO tbl (id, image) VALUES (<id>, LOAD_FILE(<filename>)); > > This of course would require that you have the file available on the > MySQL server to load in. LOAD_FILE() will return the contents of the > file. > > Regards, > > Jeremy > > -- > Jeremy Cole > MySQL Geek, Yahoo! Inc. > Desk: 408 349 5104 > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
