[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Splitting IQ Temp dbspace into multiple temp dbspaces

Posted on 2011-03-22
17
Medium Priority
?
1,253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Q2 2017 - Latest Malware & Internet Attacks

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out our latest Quarterly Internet Security Report!

 

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
 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

WooCommerce is becoming the most powerful e-commerce plugin for Wordpress. And why not. The platform comprises of numerous core plugins that may come in handy, powerful options to make your website development task much easier.
Here in this article, you will get a step by step guidance on how to restore an Exchange database to a recovery database. Get a brief on Recovery Database and how it can be used to restore Exchange database in this section!
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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