Hints to speed-up select into statement

[Former Member]

sybase version: Adaptive Server Enterprise/15.0.3/EBF 17686 ESD#1.1 RELSE/P/Sun_svr4/OS 5.8/ase1503/2681/64-bit/FBO/Thu Aug 20 14:20:57 2009

Server page size is 4K

--

Hi all,

I'm looking at any hints that could speed up some 'select into' statements.

While upgrading our software, the datamodel is changed through 'select into' statements. Basically what we're doing is:

• select ... into NEW_TABLE_A lock datarows from TABLE_A

for some big tables the operation is long because of the size of the table. For example, I have a table with about 17 millions rows and the select into lasted 40 minutes. Looking at some metrics gathered through ASEMON I had the following:

• Phys reads: 339 973
• Logic reads: 22 466 937
• Rows affected: 17 244 487

The showplan looks not too bad for me (no defered updates, using large IO)

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is CREATE TABLE.

    STEP 2
        The type of query is INSERT.

2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)
       |
       |   |INSERT Operator (VA = 1)
       |   |  The update mode is direct.
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  DB..TABLE_A
       |   |   |  Table Scan.
       |   |   |  Forward Scan.
       |   |   |  Positioning at start of table.
       |   |   |  Using I/O Size 32 Kbytes for data pages.
       |   |   |  With MRU Buffer Replacement Strategy for data pages.
       |   |
       |   |  TO TABLE
       |   |  NEW_TABLE_A
       |   |  Using I/O Size 32 Kbytes for data pages.

My assumption is that there's nothing I could do at Sybase level to speed-up the execution time.

Do you agree or anybody has any hints?

Thanks

Simon

[Former Member]

HI Mark,

Thanks for your email.

I've been checking sp_sysmon output and no IO delays were raised but on the other hand I have 7% of LargeIO denied because of Pool < Prefetch Size. I'll increase my 32K buffer pool

As for the Sybase devices, I'm gonna ask the system team but my assumption is sybase devices are on a SAN

Best,

Simon

[Former Member]

What page size are you using ?

[Former Member · Accepted solution]

You're writing 17,000,000 rows in 40 mins,

which equates to 425,000 rows per minute.

What hardware are you using ? This isn't bad performance on 6 year old kit 😉 Is it new ?

Are you using SANs ? (if you're not then skip this most of this)

You've got quite a bit of physical IO at 339,973 pages. SAN speeds, in my experience vary from 0.5ms to 10ms for a single page - depending on how much your company overloads the SAN, what tier your data is on and what time of day it is.

At 4ms/read which isn't too bad for a SAN is a large company which doesn't care about overloading its disk, means the reads will take 1356s - so 24mins. If you're doing it at a busy time of the day then I've seen 8ms for a page read which is 48mins.

Try clearing the source table from the cache and run a select count(*) from table with statistics io, time on. What performance are you getting ?

In my experience SANs are always overloaded and no one cares - the only solution I've found is to buy a machine with huge amounts of RAM or buy your own SAN.

Also if you're using multi-tiered SAN, I'd recommend repeatedly running selects on your prime tables to get the SAN to move the data up the tiers - run this every day.

If you're not using SAN then also run the select(*) so we can see the performance.

When you're writing to SANs you write to the SAN cache synchronously so its very quick *much quicker than direct disk (the write to the disk is async -so we don't care).

If you've got direct disks, then we need to look at how they're set up.

[Former Member · Accepted solution]

Hi Mike,

Thank you for your feedback.

I did the test by

1. try to clear the data cache, to do so I did several 'select count(*) from xxx' on other big tables to make sure TABLE_A was not in the cache. So far only 452Kb was reported in the cache (select CachedKB from monCachedObject where ObjectName='TABLE_A'

2.set statistics timo, io on

3. execute select count(*) from TABLE_A

1> select count(*) from TABLE_A

2> go

Parse and Compile Time 0.

Adaptive Server cpu time: 0 ms.

Parse and Compile Time 0.

Adaptive Server cpu time: 0 ms.

-----------

    17244487

Table: TABLE_A scan count 1, logical reads: (regular=37521 apf=0 total=37521), physical reads: (regular=8 apf=4714 total=4722), apf IOs used=4714

Total writes for this command: 0

Execution Time 37.

Adaptive Server cpu time: 3700 ms.  Adaptive Server elapsed time: 122120 ms.

(1 row affected)

Unless mistaken, the time to execute 1 IO is: (122120-3700)/4722 = 25ms

so 25ms for 1 IO seems very slow for me. Am I right?

Thanks

Simon

[Former Member · Accepted solution]

> Unless mistaken, the time to execute 1 IO is: (122120-3700)/4722 = 25ms

> so 25ms for 1 IO seems very slow for me. Am I right?

Looks correct to me- its seems very slow.

I've got a SAN here doing 12ms and I consider that to be slow. 25ms is awful.

I consider reading a page from a SAN in 2ms to be good. I have servers which will sometimes do 0.5ms which is fast.

Either

- your devices aren't set up correctly ?

- your SAN is slow or overloaded ? (What SAN are you using ? EMC? HDS ? etc and what model ?)

- your Sybase server is busy ? (Were you the only user on the Sybase server ?)

Not sure I can help much with these but here's so thoughts.

First - check your devices are set up correctly.

If you've got SAN (which it looks like you have) then contact the SAN team. Ask them what *YOUR* throughput is.

In my experience, you'll get ignored and brushed off. They'll talk about "IOPS" - ignore this - its useful for SAN performance but has no relationship to your individual database performance. Ask them repeatedly the worse case time for a page read. Get the SA's to help with showing the IO waits. SAN teams will usually only talk to SA's.

Ask them what page size the SAN uses. (We've gained a lot of performance by increasing the Sybase page size to match the page size of the SAN. Our SAN is 16k pages, so 16k Sybase pages is good - generally not much to be gained to be larger than the SAN page size. It took a while to rebuild but performance is better!)

What size are you using ?

Askt hem if the SAN does dynamic tiering ? Heavily used pages are held "higher" up the SAN.

When we installed a new server on the SAN - performance was initially very poor as we were automatically allocated disks lower down the SAN. As we used the server, the SAN automagically moves the heavily used pages to higher up the SAN. Now when I install a new server, I ran "unbindcache, select count(*) from ...." repeatedly for about 24 hours - this will cause the SAN to move those tables to better disks (obviously it means other teams move down the SAN. In fact, you can probably see the dynamic-tiering by repeatedly running the select count(*), clearing sybase cache and checking the times. In my experience the 3rd run is twice the speed of the first run.

[Former Member · Accepted solution]

Thanks Mike,

Maybe your explanation about the SAN was right because now whenever I issue my statement, the operation is taking about 5 minutes.

I did several tests by restarting the dataserver to clean the cache and re-execute the SQL statement and now it's quite fast.

As long as I don't know what's behind my FileSystem and that I can't have the control over it. I'll have to leave with it

Anyway, thanks all for your help, it was very helpfull and usefull to read.

[Former Member · Accepted solution]

Glad I could help.

There are things you can do to improve the SAN though.

1) During quiet time - run "select * from tables" - to keep tables high up the SAN tiers

2) Rebuild your server with page sizes that match the SAN page size - EMC and HDS will be 16k or 32k pages (Clariions were 8k I think). SANs read this much page off disk but if you have ask for a smaller one - it will then issue multiple writes to send them to the db server. The Sybase default of 2k pages is ludicrously small - should be increased to 8k (like MS-SQL) or 16k.

The main thing you can do (and I wouldn't bother wasting time again on other options) - is just buy a box with more RAM. Not sure what machine you've got, but servers with 512Gb RAM are pretty cheap - much cheaper than software licenses and 10 times more effective than any other thing I've tried. And you can order one with less CPU's and pay less fees as well.

[crisnormand]

Hello Simon,

As this is not about an ASE for Business Suite environment, I'm moving your question to space .

Thank you!

Regards,

Cris