DBSPACETEMP, sorting, temporary tables...

Posted in 2014

Summary

The poster asked whether Informix can do sorting and temporary tables in memory rather than on disk-based temp dbspaces. Replies recommended putting temp dbspace chunks on a RAM disk (tmpfs). Art Kagel gave the recipe: create the temp dbspace with onspaces -t on the RAM disk, use onmode -c block, cp -p the (compressible, mostly empty) chunk file to permanent disk, then onmode -c unblock; at each boot recreate the RAM disk and copy the chunk file back before starting the engine, so fast recovery finds a clean space. The poster tried it and reported roughly double the speed.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Storage & Space Management

Kern Doe — Thu, 08 May 2014 23:17:04 — source: IIUG Forums & Mailing Lists

Hello gurus, Temp dbspaces are for sorting and for temporary tables. These dbspaces are actually disk spaces. We don't have a way to sort or to create temporary tables in memory, do we? Memories are getting less and less expensive, wouldn't it be much faster to use memory instead? My understanding is MySQL uses memory for these types of purposes -- just wonder. Thanks in advance for any input. Kern -- Let's go Green This email contains 100% recycled electrons.

↪ replying to Kern Doe

Jonathan Smaby — Fri, 09 May 2014 00:12:19 — source: IIUG Forums & Mailing Lists

I currently use a RamDisk (tempfs) in my /etc/fstab file SuSE SLES Linux. All of my temp dbspace is there in memory. I have a boot script that copies a compressed file into this RamDisk for the dbspace file. It works extraordinarily fast. Hope that helps. V/r, Jonathan B. Smaby Pomona College (909) 621-8506 -- "If people stop bringing you problems, you've stopped leading." - LTG Jeffrey W. Talley, US Army Reserve -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Kern Doe Sent: Thursday, May 08, 2014 4:17 PM To: ids@iiug.org Subject: DBSPACETEMP, sorting, temporary tables... [32968] Hello gurus, Temp dbspaces are for sorting and for temporary tables. These dbspaces are actually disk spaces. We don't have a way to sort or to create temporary tables in memory, do we? Memories are getting less and less expensive, wouldn't it be much faster to use memory instead? My understanding is MySQL uses memory for these types of purposes -- just wonder. Thanks in advance for any input. Kern -- Let's go Green This email contains 100% recycled electrons. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Jonathan Smaby

Art Kagel — Fri, 09 May 2014 00:31:07 — source: IIUG Forums & Mailing Lists

Kern, I'm with Jonathan on this. You can build your temp dbspaces on RAM disks, you just have to take a copy of each of the empty chunk(s) to a physical drive (you can compress it, since it's completely zeros except for the page headers and trailers it compresses fast and tight) so you can restore them after a restart or crash. Informix assumes that temp spaces are empty at startup and it will clean them up if it actually finds and partition pages in the dbspace's reserved pages at startup, but since you will be restoring a copy of the original empty chunk files before startup, fast recovery will find nothing to do and be happy. This is a supported optimization, it's just not documented. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 8, 2014 at 8:12 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > I currently use a RamDisk (tempfs) in my /etc/fstab file SuSE SLES Linux. > All > of my temp dbspace is there in memory. I have a boot script that copies a > compressed file into this RamDisk for the dbspace file. > > It works extraordinarily fast. > > Hope that helps. > > V/r, > > Jonathan B. Smaby > Pomona College > (909) 621-8506 > -- > "If people stop bringing you problems, you've stopped leading." - LTG > Jeffrey > W. Talley, US Army Reserve > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Kern Doe > Sent: Thursday, May 08, 2014 4:17 PM > To: ids@iiug.org > Subject: DBSPACETEMP, sorting, temporary tables... [32968] > > Hello gurus, > Temp dbspaces are for sorting and for temporary tables. These dbspaces are > actually disk spaces. We don't have a way to sort or to create temporary > tables in memory, do we? Memories are getting less and less expensive, > wouldn't it be much faster to use memory instead? My understanding is MySQL > uses memory for these types of purposes -- just wonder. > Thanks in advance for any input. > Kern -- > > Let's go Green > This email contains 100% recycled electrons. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133b04a538c7504f8ecb6d5

↪ replying to Kern Doe

Kern Doe — Fri, 09 May 2014 14:28:32 — source: IIUG Forums & Mailing Lists

This is brilliant!! why haven't I thought of it? Thank you Paul, Jonathan! Let's go Green This email contains 100% recycled electrons. ________________________________ From: Paul Watson <paul@oninit.com> To: "kern_doe@yahoo.com" <kern_doe@yahoo.com> Sent: Thursday, May 8, 2014 9:17 PM Subject: Re: DBSPACETEMP, sorting, temporary tables... [32968] Kern If you have enough memory then you should sort in memory. A neat trick is to have temp dbspaces in a RAM disk Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 > On May 8, 2014, at 18:17, "Kern Doe" <kern_doe@yahoo.com> wrote: > > Hello gurus, > Temp dbspaces are for sorting and for temporary tables. These dbspaces are > actually disk spaces. We don't have a way to sort or to create temporary > tables in memory, do we? Memories are getting less and less expensive, > wouldn't it be much faster to use memory instead? My understanding is MySQL > uses memory for these types of purposes -- just wonder. > Thanks in advance for any input. > Kern -- > > Let's go Green > This email contains 100% recycled electrons. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Art Kagel

LARRY SORENSEN — Fri, 09 May 2014 16:10:19 — source: IIUG Forums & Mailing Lists

You say this is not documented. Has anyone documented the step-by-step process of setting this up and maintaining it, including startup scripts, etc? Larry > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: DBSPACETEMP, sorting, temporary tables... [32970] > Date: Thu, 8 May 2014 20:31:07 -0400 > > Kern, I'm with Jonathan on this. You can build your temp dbspaces on RAM > disks, you just have to take a copy of each of the empty chunk(s) to a > physical drive (you can compress it, since it's completely zeros except for > the page headers and trailers it compresses fast and tight) so you can > restore them after a restart or crash. Informix assumes that temp spaces > are empty at startup and it will clean them up if it actually finds and > partition pages in the dbspace's reserved pages at startup, but since you > will be restoring a copy of the original empty chunk files before startup, > fast recovery will find nothing to do and be happy. This is a supported > optimization, it's just not documented. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Thu, May 8, 2014 at 8:12 PM, Jonathan Smaby > <Jonathan.Smaby@pomona.edu>wrote: > > > I currently use a RamDisk (tempfs) in my /etc/fstab file SuSE SLES Linux. > > All > > of my temp dbspace is there in memory. I have a boot script that copies a > > compressed file into this RamDisk for the dbspace file. > > > > It works extraordinarily fast. > > > > Hope that helps. > > > > V/r, > > > > Jonathan B. Smaby > > Pomona College > > (909) 621-8506 > > -- > > "If people stop bringing you problems, you've stopped leading." - LTG > > Jeffrey > > W. Talley, US Army Reserve > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Kern Doe > > Sent: Thursday, May 08, 2014 4:17 PM > > To: ids@iiug.org > > Subject: DBSPACETEMP, sorting, temporary tables... [32968] > > > > Hello gurus, > > Temp dbspaces are for sorting and for temporary tables. These dbspaces are > > actually disk spaces. We don't have a way to sort or to create temporary > > tables in memory, do we? Memories are getting less and less expensive, > > wouldn't it be much faster to use memory instead? My understanding is MySQL > > uses memory for these types of purposes -- just wonder. > > Thanks in advance for any input. > > Kern -- > > > > Let's go Green > > This email contains 100% recycled electrons. > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1133b04a538c7504f8ecb6d5 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to LARRY SORENSEN

Art Kagel — Fri, 09 May 2014 16:29:54 — source: IIUG Forums & Mailing Lists

Certainly I have in posts before. But, here it is again: 1. onspaces -t to add a temp dbspace(s) linked to a chunk file(s) in a RAM disk 2. onmode -c block to flush everything to disk and freeze writes to the temp chunk 3. cp -p to copy the chunk file(s) from the RAM disk to a permanent disk 4. onmode -c unblock to release the block 5. Done Before each restart, so in your startup script or rc script just: cp-p the permanent copy of the chunk file(s) back to the RAM disk. Obviously during restart you have to make sure that the RAM disks are created before engine startup. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 9, 2014 at 12:10 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > You say this is not documented. Has anyone documented the step-by-step > process > of setting this up and maintaining it, including startup scripts, etc? > > Larry > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: DBSPACETEMP, sorting, temporary tables... [32970] > > Date: Thu, 8 May 2014 20:31:07 -0400 > > > > Kern, I'm with Jonathan on this. You can build your temp dbspaces on RAM > > disks, you just have to take a copy of each of the empty chunk(s) to a > > physical drive (you can compress it, since it's completely zeros except > for > > the page headers and trailers it compresses fast and tight) so you can > > restore them after a restart or crash. Informix assumes that temp spaces > > are empty at startup and it will clean them up if it actually finds and > > partition pages in the dbspace's reserved pages at startup, but since you > > will be restoring a copy of the original empty chunk files before > startup, > > fast recovery will find nothing to do and be happy. This is a supported > > optimization, it's just not documented. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Thu, May 8, 2014 at 8:12 PM, Jonathan Smaby > > <Jonathan.Smaby@pomona.edu>wrote: > > > > > I currently use a RamDisk (tempfs) in my /etc/fstab file SuSE SLES > Linux. > > > All > > > of my temp dbspace is there in memory. I have a boot script that > copies a > > > compressed file into this RamDisk for the dbspace file. > > > > > > It works extraordinarily fast. > > > > > > Hope that helps. > > > > > > V/r, > > > > > > Jonathan B. Smaby > > > Pomona College > > > (909) 621-8506 > > > -- > > > "If people stop bringing you problems, you've stopped leading." - LTG > > > Jeffrey > > > W. Talley, US Army Reserve > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Kern Doe > > > Sent: Thursday, May 08, 2014 4:17 PM > > > To: ids@iiug.org > > > Subject: DBSPACETEMP, sorting, temporary tables... [32968] > > > > > > Hello gurus, > > > Temp dbspaces are for sorting and for temporary tables. These dbspaces > are > > > actually disk spaces. We don't have a way to sort or to create > temporary > > > tables in memory, do we? Memories are getting less and less expensive, > > > wouldn't it be much faster to use memory instead? My understanding is > MySQL > > > uses memory for these types of purposes -- just wonder. > > > Thanks in advance for any input. > > > Kern -- > > > > > > Let's go Green > > > This email contains 100% recycled electrons. > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1133b04a538c7504f8ecb6d5 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113499080adfcf04f8fa1bd5

↪ replying to Art Kagel

Kern Doe — Mon, 12 May 2014 19:18:45 — source: IIUG Forums & Mailing Lists

Thank you Art and everyone. In my simple experiment with ramdisk, it was twice as fast (WOW). In addition, I followed your recommendations in handling temp dbspaces in case of a system reboot, it worked nice too. {informix@centos_acer}-/tmp $ time ./test1 Database selected. 544456 row(s) retrieved into temp table. Table dropped. Database closed. real 0m14.39s user 0m0.01s sys 0m0.01s --------------------------------------------------------- {informix@centos_acer}-/tmp $ time ./test1 Database selected. 544456 row(s) retrieved into temp table. Table dropped. Database closed. real 0m7.23s user 0m0.01s sys 0m0.00s Let's go Green This email contains 100% recycled electrons. ________________________________ From: Art Kagel <art.kagel@gmail.com> To: ids@iiug.org Sent: Friday, May 9, 2014 12:29 PM Subject: Re: DBSPACETEMP, sorting, temporary tables... [32982] Certainly I have in posts before. But, here it is again: 1. onspaces -t to add a temp dbspace(s) linked to a chunk file(s) in a RAM disk 2. onmode -c block to flush everything to disk and freeze writes to the temp chunk 3. cp -p to copy the chunk file(s) from the RAM disk to a permanent disk 4. onmode -c unblock to release the block 5. Done Before each restart, so in your startup script or rc script just: cp-p the permanent copy of the chunk file(s) back to the RAM disk. Obviously during restart you have to make sure that the RAM disks are created before engine startup. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 9, 2014 at 12:10 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > You say this is not documented. Has anyone documented the step-by-step > process > of setting this up and maintaining it, including startup scripts, etc? > > Larry > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: DBSPACETEMP, sorting, temporary tables... [32970] > > Date: Thu, 8 May 2014 20:31:07 -0400 > > > > Kern, I'm with Jonathan on this. You can build your temp dbspaces on RAM > > disks, you just have to take a copy of each of the empty chunk(s) to a > > physical drive (you can compress it, since it's completely zeros except > for > > the page headers and trailers it compresses fast and tight) so you can > > restore them after a restart or crash. Informix assumes that temp spaces > > are empty at startup and it will clean them up if it actually finds and > > partition pages in the dbspace's reserved pages at startup, but since you > > will be restoring a copy of the original empty chunk files before > startup, > > fast recovery will find nothing to do and be happy. This is a supported > > optimization, it's just not documented. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Thu, May 8, 2014 at 8:12 PM, Jonathan Smaby > > <Jonathan.Smaby@pomona.edu>wrote: > > > > > I currently use a RamDisk (tempfs) in my /etc/fstab file SuSE SLES > Linux. > > > All > > > of my temp dbspace is there in memory. I have a boot script that > copies a > > > compressed file into this RamDisk for the dbspace file. > > > > > > It works extraordinarily fast. > > > > > > Hope that helps. > > > > > > V/r, > > > > > > Jonathan B. Smaby > > > Pomona College > > > (909) 621-8506 > > > -- > > > "If people stop bringing you problems, you've stopped leading." - LTG > > > Jeffrey > > > W. Talley, US Army Reserve > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Kern Doe > > > Sent: Thursday, May 08, 2014 4:17 PM > > > To: ids@iiug.org > > > Subject: DBSPACETEMP, sorting, temporary tables... [32968] > > > > > > Hello gurus, > > > Temp dbspaces are for sorting and for temporary tables. These dbspaces > are > > > actually disk spaces. We don't have a way to sort or to create > temporary > > > tables in memory, do we? Memories are getting less and less expensive, > > > wouldn't it be much faster to use memory instead? My understanding is > MySQL > > > uses memory for these types of purposes -- just wonder. > > > Thanks in advance for any input. > > > Kern -- > > > > > > Let's go Green > > > This email contains 100% recycled electrons. > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1133b04a538c7504f8ecb6d5 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113499080adfcf04f8fa1bd5 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Related threads

Different instances with markedly different times

Glossary

dbspace