Performance if storing files in DB
A user on IDS 11.50.FC6 (Solaris) stored files (a few KB up to 400–500 MB) in a BYTE column inside a 2K-page dbspace, and large inserts badly hurt OLTP performance. They wanted to switch to BLOB in a smart blobspace but plain INSERT...SELECT / load-unload failed on the BYTE-to-BLOB conversion. Replies suggested using BLOB with the documented load/unload functions, or an in-place ALTER TABLE to change the column type and then put the column into an sbspace; it was also noted that blobspaces don't work with replication, though logged sbspaces (LOGGING=ON) can be replicated. Questions about space/log requirements, application code changes and expected gains went unanswered; no confirmed resolution is recorded, with the poster also considering moving files to the OS filesystem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have a table with a column of datatype "byte". In that column we store data in form of images,spreadsheets, pdf files. Usually the size of these files range from below 1KB to a few KBs. But sometimes size of a file reaches 400 or 500 MB. This table is in a 2K pagesize along with other tables used in application. We observed that when a large file is being inserted into database, we face a huge performance impact on overall transactions on the system. It is an OLTP system basically. We thought to move the data into BLob DBSpace with BLob data type to store hese files. But simple "insert into select..." or unload/load does not do the stuff (conversion issue from Byte to Lob). How can we optimize the DML on such table, and what are major overhead on system performance during large files loading? System Specs: IBM Informix Dynamic Server Version 11.50.FC6 Solaris SPARC-Enterprise-T5220
Hello. You should use BLOB data types. Follow the Information Center docs, to help you on functions you´ll need to load /unload the data: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids _sqr_098.htm?resultof=%22%62%6c%6f%62%22%20 Best regards. Em 27/04/2011 06:27, KAMRAN HAQ escreveu: > We have a table with a column of datatype "byte". In that column we store data > in form of images,spreadsheets, pdf files. > Usually the size of these files range from below 1KB to a few KBs. But > sometimes size of a file reaches 400 or 500 MB. > This table is in a 2K pagesize along with other tables used in application. > We observed that when a large file is being inserted into database, we face a > huge performance impact on overall transactions on the system. > It is an OLTP system basically. We thought to move the data into BLob DBSpace > with BLob data type to store hese files. > But simple "insert into select..." or unload/load does not do the stuff > (conversion issue from Byte to Lob). > How can we optimize the DML on such table, and what are major overhead on > system performance during large files loading? > > System Specs: > IBM Informix Dynamic Server Version 11.50.FC6 > Solaris > SPARC-Enterprise-T5220 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 <http://www.iiug.org/conf/2011/iiug/>;
Hi, you could try to modify the column type to blob (should be possible using alter table) and then modify the storage using alter table put column into sdbspace. Maybe this helps (would have some load on the server, but is in-place). Marcus -----Original Message----- From: Alexandre Marini [mailto:amarini@fazenda.ms.gov.br] Sent: Wednesday, April 27, 2011 1:47 PM To: ids@iiug.org Subject: Re: Performance if storing files in DB [23504] Hello. You should use BLOB data types. Follow the Information Center docs, to help you on functions youll need to load /unload the data: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids _sqr_098.htm?resultof=%22%62%6c%6f%62%22%20 Best regards. Em 27/04/2011 06:27, KAMRAN HAQ escreveu: > We have a table with a column of datatype "byte". In that column we > store data > in form of images,spreadsheets, pdf files. > Usually the size of these files range from below 1KB to a few KBs. But > sometimes size of a file reaches 400 or 500 MB. > This table is in a 2K pagesize along with other tables used in application. > We observed that when a large file is being inserted into database, we > face a > huge performance impact on overall transactions on the system. > It is an OLTP system basically. We thought to move the data into BLob DBSpace > with BLob data type to store hese files. > But simple "insert into select..." or unload/load does not do the > stuff (conversion issue from Byte to Lob). > How can we optimize the DML on such table, and what are major overhead > on system performance during large files loading? > > System Specs: > IBM Informix Dynamic Server Version 11.50.FC6 Solaris > SPARC-Enterprise-T5220 > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informao - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 <http://www.iiug.org/conf/2011/iiug/>; ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Haarmann How much space should be free on current DBSpace for completion of this process or do we need to add more logical logs before modifying data if table size is around 30 GB?
Sorry. One thing more! Do we need to change the application code to manipulate data as underlying datatype is changed. And can we be sure that datatype change would not corrupt the stored files?
On 27/04/2011 11:27, KAMRAN HAQ wrote: > We have a table with a column of datatype "byte". In that column we store data > in form of images,spreadsheets, pdf files. > Usually the size of these files range from below 1KB to a few KBs. But > sometimes size of a file reaches 400 or 500 MB. > This table is in a 2K pagesize along with other tables used in application. > We observed that when a large file is being inserted into database, we face a > huge performance impact on overall transactions on the system. > It is an OLTP system basically. We thought to move the data into BLob DBSpace > with BLob data type to store hese files. > But simple "insert into select..." or unload/load does not do the stuff > (conversion issue from Byte to Lob). > How can we optimize the DML on such table, and what are major overhead on > system performance during large files loading? > > System Specs: > IBM Informix Dynamic Server Version 11.50.FC6 > Solaris > SPARC-Enterprise-T5220 Are you using replication? If you are, then you can't move the data in blobspaces. Why won't an unload / reload fix it?? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.
We are using Mach11 cluster(HDR+RSS). But we think we can create a logged smart blob space with the -Df "LOGGING=ON" option to replicate the blob data. But we are not sure how much performance gain we can get or we start thinking to move these files out from database into OS file-system.