Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1268
  • Last Modified:

Splitting IQ Temp dbspace into multiple temp dbspaces

I am on IQ 12.7 ESD#7 at the moment and as a strategic change, I am planning to split the current (100GB) Temp dbspace into 4 equal temp dbspaces to improve throughput & performance. This is a Sybase Query node on a 4 core Linux box (part of a Multiplex)...

What are the steps involved to do this?

Appreciate the help!

1 Solution
Joe WoodhousePrincipal ConsultantCommented:
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.
SybaseUkAuthor Commented:
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?

Joe WoodhousePrincipal ConsultantCommented:
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!
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

SybaseUkAuthor Commented:
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.

Joe WoodhousePrincipal ConsultantCommented:
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.
SybaseUkAuthor Commented:
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???

Joe WoodhousePrincipal ConsultantCommented:
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. :)
SybaseUkAuthor Commented:
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..
Joe WoodhousePrincipal ConsultantCommented:
Interested in anything you're allowed to share with us. :)
SybaseUkAuthor Commented:
How can I check if a given temp dbspace is being used? i.e. it has any objects assigned to it?
Joe WoodhousePrincipal ConsultantCommented:
Doesn't Sybase Central (don't have one in front of me right now) just show you that?
SybaseUkAuthor Commented:
Joe - can I pick your brain on the other open question I have?

I'll close this question and assign you the points - thanks!
Joe WoodhousePrincipal ConsultantCommented:
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).

Hi SybaseUk,

On your temp space performance issue:
As for the temp space performance question, note that IQ opens an "I/O Stack" structure for each DbSpace (12.7) and each DbFile (15.x).  These I/O Stack structures are used by the sweeper threads to sweep dirty pages from cache to disk - think of a DbSpace / DbFile as a "path to disk."  The more DbSpaces / DbFiles you have, the more theoretical bandwidth can be achieved between IQ server and SAN - but with a few caveats:  

1.)  IQ striping must be on, and you must have a multi-core machine.

2.)  Not all SANs are created equal if the SAN in question does not have sufficient cache, the striping algorithm of the SAN begins interfering with the striping algorithm of IQ and you can get excessive head movement - aka the windscreen washer effect.  

3.)  Do not exceed more than 3 DbSpaces / DbFiles per core.  Past this point performance is on the decline.  

4.)  Adding too many DbSpaces / DbFiles can easily dry up your spare HBA bandwidth.  

5.)  Watch out for environments where Sybase IQ is "sharing" underlying SAN disks with other applications - many shops apply write throttling at the HBA level to avoid the case where IQ is adversely affecting the performance of other applications, so try to have dedicated storage for IQ wherever possible.

6.)  Sybase IQ has a built in I/O test tool which is undocumented and unsupported, but in a safe environment (like a brand new test server) can be used to profile the performance of your storage array and can be used to spot bottlenecks.  You might want to get a Sybase consultant with specialist knowledge of this tool in for a day to show you how to operate it.  Not all Sybase consultants know how to do this, so make sure you get one of the good ones.  ;-)  

On your loading issue:  The delay Ben is referring to sounds like what we call the "page crawl effect."  In a HG index where a lot of g-arrays have flipped to bitmaps it is necessary to traverse the entire bitmap chain to find the insertion point for every value inserted into the index - and for 72 billion rows that will take a very long time!!  (and will continue to increase over time.)  

Another thing that loads require is "breathing room" - try to have at least 20% free space available in mainstore (this will keep Sybase support off your back as well as prevent many late night calls if you are running 12.7 !!)    If you  only have 10% free space in mainstore you are asking for trouble in more ways than one.  


- David
SybaseUkAuthor Commented:
Thanks for the answer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now