Solved

Splitting IQ Temp dbspace into multiple temp dbspaces

Posted on 2011-03-22
17
1,171 Views
Last Modified: 2012-05-11
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!

Thanks!
0
Comment
Question by:SybaseUk
17 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 35197317
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.
0
 

Author Comment

by:SybaseUk
ID: 35197450
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!!
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 35197485
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!
0
 

Author Comment

by:SybaseUk
ID: 35197977
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.



0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 35198039
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.
0
 

Author Comment

by:SybaseUk
ID: 35198107
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???

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 35198166
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. :)
0
 

Author Comment

by:SybaseUk
ID: 35198223
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..
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 35198313
Interested in anything you're allowed to share with us. :)
0
 

Author Comment

by:SybaseUk
ID: 35199659
How can I check if a given temp dbspace is being used? i.e. it has any objects assigned to it?
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 35203284
Doesn't Sybase Central (don't have one in front of me right now) just show you that?
0
 

Author Comment

by:SybaseUk
ID: 35217004
Joe - can I pick your brain on the other open question I have?

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

Expert Comment

by:Joe Woodhouse
ID: 35229568
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...
0
 
LVL 1

Expert Comment

by:BenSlade
ID: 35244188
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
0
 
LVL 2

Accepted Solution

by:
drittenh earned 500 total points
ID: 35514606
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.  

HTH,

- David
0
 

Author Closing Comment

by:SybaseUk
ID: 35792027
Thanks for the answer
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
BDE errors / Sybase DB on Windows 2008 4 854
How do we check sybase license in ASE 1 2,484
Sybase 2 MS SQL migration/conversion 4 131
SQL Query Help! 11 112
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now