Splitting IQ Temp dbspace into multiple temp dbspaces

Are the tempspaces going to different storage? (Physical disks/LUNS, different SAN, different HBA cards, etc?) If not I think you won't see any performance difference at all unless you have very specific deep technical reasons to think IQ will do better - and in which case please share as I'm super curious. :)

100Gb is nothing to IQ and so there's no architectural reason I'm aware of to want to split that.

If on the other hand you can gain access to faster storage by doing this, by all means, but then I'd ask why not just migrate the single 100Gb dbspace to there?

I don't know of any way to split an IQ dbspace. This is kinda like splitting an ASE device - you will have to backup the database, drop the database, drop the device, recreate new devices, recreate the database on the new devices in a way that is still compatible with the backup, and then load the backup. I'm not even sure that will work as IQ isn't as flexible about remapping dbspaces as ASE is with devices - you can change the name & path of where it expects a dbspace to be, but I don't think you can trick it into loading what it thinks should be on one dbspace to four.

I'd love to be wrong about that though! :) Can anyone else confirm/refute?

If you don't even have that option you're looking at a full physical rebuild - object level migration from the old to the new. Ick. Doable but slow and needs a lot of disk space in the meantime.

Hello Joe - thanks for your input.

As a general recommendation when sizing storage, Sybase recommends that you create main/temp stores as follows:

3 + (no. of cores / 10) so for a 4 core system that would equate to 4.

The P&T guide says that you should create multiple dbspaces to cause randomness which should help improve throughput and performance provided you have the DISK_STRIPING option enabled also...

From Sybase Central, you can add temp stores quite easily and then drop out the single 100GB dbspace!

I'd be interested to hear what others have to say about this method though?

Thanks!!

Re. striping - yes, striping helps any database particularly ones which do large sequential I/Os. I don't think it matters where the striping occurs though. If I have IQ dbspaces on a beautiful RAID10 SAN with multiple I/O paths and 32 spindles, I will already be getting the best possible striping and don't need to do anything at the IQ level. I don't think IQ has ASE's issue of spinlock contention per device so there's nothing stopping us in this scenario from having one big MAIN and one big TEMP and leaving it at that.

At the other extreme if I have standalone JBOD (Just a Bunch Of Disks) then IQ striping is essential to spread I/O and scale to the best throughput possible on that storage.

So if you have any kind of SAN and/or RAID going on already, unless it's pure mirroring (RAID 1), I suggest strongly that you benchmark and compare before (1 x 100Gb Temp) and after (4 x 25Gb Temp with IQ striping) for loads and for your worst queries. Run each benchmark after a freshly booted IQ for repeatability. Probably also run two read benchmarks - single-user and multi-user. I am very very interested to see if anything changes! My money says (at these sizes - 100Gb and 4 cores) it won't. (I agree it could be a factor if we were talking 10Tb and 64 cores.)

Re. adding and then dropping, does that work for removing the first dbspace, the one defined at CREATE DATABASE time? I didn't think so, but wow, it'd be great if it were as simple as that! I'm crossing my fingers for you!

We are using IBM XIV as our back-end storage for Sybase IQ so i'm not sure what RAID level that is....
I don't know how to determine how performant the storage is for IQ...any ideas would be welcome there :-)

As for the temp dbspaces, this is what I did: -

1. Log onto the Query server using Sybase Central
2. Click on the Dbspaces pane.
3. Right-click in the window - New -> dbspace
4. Add my 4 x (25GB) temp dbspaces.
5. Right-click on the original temp store - > Properties
6. Click on the usage tab
7. From the drop down menu, select 'relocate' (this will stop new queries from using it so can be dropped later)
8. Apply changes to dbspace.

All you can do is check IQ's performance (reads and writes, single user and multi user) before and after your proposed change.

I note from your changes you still haven't actually dropped the original Temp dbspace, that's the bit I'm wondering about.

Yes you're right, you can only delete the temp dbspace once it is no longer in use by any queries or objects...hence marking the dbspace as relocate would help acheive this???

I have a vague memory that the first Main and Temp dbspaces - the ones appearing in the simplest form of the CREATE DATABASE statement - could never be deleted even if you added others.

I would love to be proved wrong on this!!

It would be a very good idea to know if this measure made any difference to performance before committing the end user to the cost and hassle. :)

we have been carrying out this measure (splitting temp store into multiple dbspaces) throughout our multiplex systems and it has seemed to have helped with some issues we were facing...such as Loads hanging after appearing to be complete -- this is probably phase 2 where the cache data in the temp store is loading the intermediate data into HG Indexes..we have found that this technique works well for us but I wish I could prove this definitively..

Interested in anything you're allowed to share with us. :)

How can I check if a given temp dbspace is being used? i.e. it has any objects assigned to it?

Doesn't Sybase Central (don't have one in front of me right now) just show you that?

Joe - can I pick your brain on the other open question I have?

I'll close this question and assign you the points - thanks!

Eh, points only when they're earned, and it's not clear to me I've answered the question in a way that will be helpful to you or to others searching on this later...

Looking at Sybase IQ 12.7 > Performance and Tuning Guide > Managing System Resources > Balancing I/O > Internal striping, if you add tempdb space, the new space will automatically be used as new blocks are written.

But echoing the other comments in this thread, it's not clear if this will buy you any performance improvement.   IQ has lots of threads for doing I/O and most modern RAID disk systems already do the striping for you under the covers.

Re: Loads hanging after appearing to be complete -- this is probably in phase 2 where the cache data in the temp store is loading the intermediate data into HG Indexes

We're seeing this delay too.   Specifically, when we add a few million rows to a humongous table (72 billion rows, nearly 1tbyte) with a single HG index, step 2 of out insert operation (a type of "load" in IQ land), takes forever.   You can turn on an obscure tracing option to see that the HG index being "loaded" in step 2 is what's taking all the time.  Run "set temporary option Core_Options1 = 33554432" in your login session before running the big load.   Based on what disks were active for us during  step 2 of the HG index "load", it looks like the bottleneck was random reading from the main dbspace, not temp space usage (although temp space was being used).

Ben

Thanks for the answer