Re: Streaming LOB Data
* Robert DiFalco <[EMAIL PROTECTED]> [06/04/30 03:09]: > Thanks Sergei, it's nice to know for sure. Do you know if there is any > documentation on how memory is used to store LOB data? For example, is > it a percentage of the total buffer pool size or is it allocated from > available memory un-allocated to the buffer pool size? There is no streaming interface between the SQL layer and the storage engines layer. An engine operates with at least one column, not with a piece of a column (one exception is that we can use row IDs when walking through records in a join, in this case the LOB is not loaded). So, at first the LOB is assembled on the SQL layer (e.g. if you supply a LOB object in pieces via mysql_stmt_send_long_data), and then submitted to the storage layer. This means than when inserting a LOB the server creates at least two copies of it in memory - on the SQL layer and on the storage layer. If instead of mysql_stmt_send_long_data you use the text protocol and simply encode the LOB in the query text with mysql_real_escape_string, you get one more copy - in the text of the query itself, which is also kept in memory. -- -- Konstantin Osipov Software Developer, Moscow, Russia -- MySQL AB, www.mysql.com The best DATABASE COMPANY in the GALAXY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Streaming LOB Data
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 (, ); > > 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 (, LOAD_FILE()); > > 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]
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 (, ); > > 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 (, LOAD_FILE()); > > 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 (, ); 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 (, LOAD_FILE()); 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Streaming LOB Data
Hi! On Apr 29, Robert DiFalco wrote: > Thanks Sergei, it's nice to know for sure. Do you know if there is any > documentation on how memory is used to store LOB data? For example, is > it a percentage of the total buffer pool size or is it allocated from > available memory un-allocated to the buffer pool size? No, I don't. Sorry :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Streaming LOB Data
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? :) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:49 AM To: Sergei Golubchik Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Streaming LOB Data Thanks Sergei, it's nice to know for sure. Do you know if there is any documentation on how memory is used to store LOB data? For example, is it a percentage of the total buffer pool size or is it allocated from available memory un-allocated to the buffer pool size? -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:34 AM To: Robert DiFalco Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Streaming LOB Data Hi! On Apr 28, Robert DiFalco wrote: > It appears (for InnoDB at least) that while INSERTing a LOB that all > LOB data must be loaded into memory before it is written to disk. Or > is it just the size of the combined log files? Looking at Task > Manager, it looks like it DOES try to load the whole thing into memory > before streaming it to disk; which just seems wacky. > > The error I get is this: > > "Out of memory (Needed xxx ..." > > If the server crashes I get this: > > InnoDB: which exceeds the log group capacity 18870682. > InnoDB: If you are using big BLOB or TEXT rows, you must set the > InnoDB: combined size of log files at least 10 times bigger than the > InnoDB: largest such row. > 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of > InnoDB: memory with malloc! Total allocated memory > InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 > InnoDB: Check if you should increase the swap file or > InnoDB: ulimits of your operating system. > InnoDB: On FreeBSD check you have compiled the OS with > InnoDB: a big enough maximum process size. > InnoDB: Note that in most 32-bit computers the process > InnoDB: memory space is limited to 2 GB or 4 GB. > > How can I make the size of LOBs I insert NOT be memory constrained? No way. Unfortunately, it's the way MySQL works at the moment. Which does not mean it's not going to be changed in the future. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql 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]
RE: Streaming LOB Data
Thanks Sergei, it's nice to know for sure. Do you know if there is any documentation on how memory is used to store LOB data? For example, is it a percentage of the total buffer pool size or is it allocated from available memory un-allocated to the buffer pool size? -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:34 AM To: Robert DiFalco Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Streaming LOB Data Hi! On Apr 28, Robert DiFalco wrote: > It appears (for InnoDB at least) that while INSERTing a LOB that all > LOB data must be loaded into memory before it is written to disk. Or > is it just the size of the combined log files? Looking at Task > Manager, it looks like it DOES try to load the whole thing into memory > before streaming it to disk; which just seems wacky. > > The error I get is this: > > "Out of memory (Needed xxx ..." > > If the server crashes I get this: > > InnoDB: which exceeds the log group capacity 18870682. > InnoDB: If you are using big BLOB or TEXT rows, you must set the > InnoDB: combined size of log files at least 10 times bigger than the > InnoDB: largest such row. > 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of > InnoDB: memory with malloc! Total allocated memory > InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 > InnoDB: Check if you should increase the swap file or > InnoDB: ulimits of your operating system. > InnoDB: On FreeBSD check you have compiled the OS with > InnoDB: a big enough maximum process size. > InnoDB: Note that in most 32-bit computers the process > InnoDB: memory space is limited to 2 GB or 4 GB. > > How can I make the size of LOBs I insert NOT be memory constrained? No way. Unfortunately, it's the way MySQL works at the moment. Which does not mean it's not going to be changed in the future. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Streaming LOB Data
Hi! On Apr 28, Robert DiFalco wrote: > It appears (for InnoDB at least) that while INSERTing a LOB that all LOB > data must be loaded into memory before it is written to disk. Or is it > just the size of the combined log files? Looking at Task Manager, it > looks like it DOES try to load the whole thing into memory before > streaming it to disk; which just seems wacky. > > The error I get is this: > > "Out of memory (Needed xxx ..." > > If the server crashes I get this: > > InnoDB: which exceeds the log group capacity 18870682. > InnoDB: If you are using big BLOB or TEXT rows, you must set the > InnoDB: combined size of log files at least 10 times bigger than the > InnoDB: largest such row. > 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of > InnoDB: memory with malloc! Total allocated memory > InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 > InnoDB: Check if you should increase the swap file or > InnoDB: ulimits of your operating system. > InnoDB: On FreeBSD check you have compiled the OS with > InnoDB: a big enough maximum process size. > InnoDB: Note that in most 32-bit computers the process > InnoDB: memory space is limited to 2 GB or 4 GB. > > How can I make the size of LOBs I insert NOT be memory constrained? No way. Unfortunately, it's the way MySQL works at the moment. Which does not mean it's not going to be changed in the future. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
