Sybase table -- index creation taking long time. Table size is huge

Using parellel sort to speed the create index

-this is the first that comes to me right now however you should be able to use sp_sysmon to tune index creation like in pseudo code below:

sp_sysmon begin_sample
create index ...
sp_sysmon end_sample

Select allOpen in new window

Of course lot more detail at Sybase docs:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20023_1251/html/optimizer/optimizer331.htm
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20023_1251/html/optimizer/optimizer308.htm

Thanks Lcohan, i will wait for our dear friend "Joe" to throw some more light on this.
Also if we have huge data loads on these tables .. truncating and reloading again ....what should be the best and fast way.
any parameteres to be tuned ?

Parallel sort definitely helps. But you need to set 'number of sort buffers' properly.

Is the table partitioned? If not you can consider partitioning. You can load data in parallel with partitioning and make it faster.

Also 'network packet size' setting can help for a faster load:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20023_1251/html/optimizer/optimizer331.htm

Hi alpmoon, thanks for your valuable guidance.
The table is not partitioned and we cannot partition it due to some desgin issues.

Regarding network packet size , the default is 2048. Should we increase it ? i have seen by keeping it default the performance is better, increasing it takes more time.

number of sort buffers , yes increasing them definitely helps. Any other parameters ?

Many Thanks Joe, no words for your extreme expertise on the subject. I still need to try all these options which you have mentioned.

following are some of the values :

1. My ASE is overall working very fine and have good hardware, memory , data cache,
2. Max memory is 150GB, datacache is 100GB (2k pool 80GB, 16K 20GB), proc cache is 19GB
3. sort buffers is 1000, 10 engines, threaded model

kindly suggest how can i speed up the data load too, running 3-4 tables in parallel.

Some simple questions first:

How many physical CPUs on this host?
Does the host run anything other than this ASE?
How many threads in your syb_default_pool? (sp_helpthread)


Re. ASE configuration:

- definitely increase "number of sort buffers" (sp_configure) to 10000.
- consider increasing (even temporarily, just for CREATE INDEX jobs) 16K pool in default data cache to 30Gb. Sorts benefit from sequential I/O.


Re. data load:

You don't say how you're currently loading. Are you using bcp? Some general bcp tuning tips:

- configure ASE for a large network packet size (say, 64Kb). This means setting "max network packet size" to 65536 and setting "additional network memory" to a formula: ((3 * num_connections * packet size) * 1.03 ), rounded up to nearest multiple of 2Kb. Example: to allow 10 concurrent connections at 64Kb packet size, "additional network memory" must be set to ((3 * 10 * 65536) * 1.03) rounded up, or 2025062.4 rounded up to next multiple of 2Kb, or 2025472.

- specify this packet size on bcp command line: bcp [...] -A65536

- if at all possible, use bcp in native mode rather than character

- set a batch size but now you must check that every batch succeeded: bcp [...] -b50000

- make sure the database you're loading into has its logs bound to a log_only data cache

- disable triggers, constraints, and referential integrity in the tables before loading. Don't forget to re-enable them!!

- per my earlier comment, if you are going to be creating a clustered index, see if you can have the file pre-sorted by the CL index key

- per my earlier comment, you can run concurrent bcp jobs into separate tables at the same time

- per even earlier comment by alpmoon, you can partition a table, split the file, and run multiple bcp jobs into the same table concurrently. More below.


Re. partitions and multiple bcp's:

I'm referring to the simplest/dumbest form of partitions, or "round-robin" partitioning. This doesn't need a special license. You specify a number of partitions on the table, and you can specify a partition number on the bcp command line:

bcp <database>..<table>:1 in <file01> [...] &
bcp <database>..<table>:2 in <file02> [...] &
bcp <database>..<table>:3 in <file03> [...] &
bcp <database>..<table>:4 in <file04> [...] &
wait

Doing it this way requires that your load file be split into multiple files. Take great care that you split only on row boundaries!

You can also do it without splitting by specifiying first and last lines of the file but this is not as fast as you'd think as you still have to scan the file from the start every time:

bcp <database>..<table>:1 in <file> -F1 -L10000000 [...] &
bcp <database>..<table>:1 in <file> -F10000001 -L20000000 [...] &
bcp <database>..<table>:1 in <file> -F20000001 -L30000000 [...] &
bcp <database>..<table>:1 in <file> -F30000001 -L40000000 [...] &
wait

-F and -L specify "first row" and "last row" to use for this bcp. I don't recommend this way though, it's far slower than having multiple files.

At the end of this process you can unpartition the table and all rows end up concatenated in the one partition as though it had never been partitioned. It is well worth combining this with the advice to use sorted data. You can sort the data first and then split it. Unpartitioning should then maintain the order. This can be tricky though as you can't guarantee row order after a split.

I personally think it's usually too much trouble to attempt parallelism per command. It's far easier to run on multiple tables concurrently. So that might look something like this:

bcp_table01_script && create_index_table01_script &
bcp_table02_script && create_index_table02_script &
[...]
wait

That approach ensures each table is loaded separately and concurrently, and the CREATE INDEX for that table will kick off as soon as it has been loaded. You need to write your scripts to test for any issues with the bcp though!!

How many physical CPUs on this host?  -- 32 logical SPU's
Does the host run anything other than this ASE? - 2 ASE servers running .. each configured 10 engines
How many threads in your syb_default_pool? (sp_helpthread) - both ASE's have 10 threads

Name              Type                 CurrentSize TargetSize IdleTimeout Description                                  InstanceName
 ----------------- -------------------- ----------- ---------- ----------- -------------------------------------------- ------------
 syb_blocking_pool Run To Completion              4          4           0 A pool dedicated to executing blocking calls NULL
 syb_default_pool  Engine (Multiplexed)          10         10         100 The default pool to run query sessions       NULL
 syb_system_pool   Run To Completion              3          3           0 The I/O and system task pool                 NULL


To increase the threads do we need to increase the engines too ? Here I can increase engines for my ASE (which has 10 engines and which is the point in question )
How are engines and threads related ?? any suggestions to improve these numbers ?

In threaded mode, an ASE engine is like a potential CPU - not actually a CPU, but the potential for one CPU's worth of power. A thread is what actually runs on the engine and uses CPU. You can't have more threads in a pool than there are engines.

Leaving aside the distinction of physical/logical/virtual CPUs, broadly speaking in threaded mode it's good to keep the total number of engines to be N-2 on N CPUs. Your two ASEs are using only 20 between them when they could be using 30 between them. You could in principle add another 5 engines & threads to each, or add the full 10 to the ASE we're looking at the CREATE INDEX jobs in.

Let's say we did that - increase "max online engines" to 20, and then set 20 threads in syb_default_pool. That in principle means you could probably run up to 20 tasks at the same time... so that could be creating five separate (non-clustered) indexes concurrently on four separate tables, or running one CREATE INDEX with 20 consumers, etc.

Awesome.

You mentioned "You can't have more threads in a pool than there are engines." -- but I think we can have more threads than number of online engines and that's what threaded model says.
So if I have 10 engines and 10 threads then I can configure 40 threads too with engines same. This will benefit in achieving more multi threading for smaller and frequent transactions but yes for Laing operations this may not help.

Please correct me if I am wrong.

Sort-of.

The total number of threads in all your thread pools can be greater than the number of engines, because the threads in syb_blocking_pool aren't really counted (they don't work the same way or as hard). I think syb_system_pool is also double-counted in this way.

You'll find though that you cannot set threads in syb_default_pool, or any user created thread pool, to be higher than your number of engines... and you cannot set your number of engines higher than the number of CPUs visible at the O/S level.

My bad.  You are right.


The total number of ASE engines is the number of threads in syb_default_pool plus the threads in all user defined thread pools; this total must less than or equal to option " max online engines"

It makes sense though - we don't want to define more "CPU's worth of power" than we actually have CPUs available.

Consider also that what looks like a CPU at the O/S level often isn't actually a CPU. Some physical CPUs have threads on cores, where each thread is presented as if it were a CPU. Experience with ASE on early Sun T-series chips was that we absolutely couldn't run "max online engines" up to the on-die thread count. Consider also HyperThreading where one physical CPU looks like two... but it's really just hardware attempting to pipeline instructions and not a true pair of CPUs. Lastly experience on virtual machines and the cloud says that even when we think we have a CPU it can be stolen by the virtualisation layer and redeployed elsewhere.

So all up I do think it makes sense that ASE limits engines to # of CPUs defined at the O/S level, and limits all user-threads to the number of engines.

FYI the reason why syb_blocking_pool and syb_system_pool don't count towards this limit is partly because of their architecture and partly because of their workload. They are defined as "Run To Completion" pools rather than standard multiprocessors, so if there's something they have to do they will run until the task is done - meaning that default and user pools will lose if a thread is competing for execution. Also they tend to not really do much work; mostly they are polling for completion of locks, disk I/Os or network I/Os and waking up other theads when that wait is over.

I should also say that in the threaded kernel there are definitely penalties to over-configuring engines and threads. The manual clearly states that ASE works better with 4 engines at 80% busy than 8 engines at 40% busy, and I totally agree. I've seen ASEs with 80+ engines defined and they were crippled by spinlock contention (imagine 80 cooks in your kitchen at the same time - they will get in each other's way a lot).

Thanks Joe, i believe the above parameters which you mentioned for tuning for creation of indexes are also relevant for alter tables on big tables , my alters are also taking long time 8-10hrs sometimes for size of 50-60GB tables. ??

Most of the time an ALTER TABLE will complete very quickly if it can be done "in-place". If it's taking a long time then it requires a rebuild. When this happens it is effectively doing SELECT INTO new_table, DROP TABLE old_table, and sp_rename new_table, old_table (while ensuring that all other table attributes like indexes, defaults, constraints, triggers, replication settings and audit settings are maintained).

So broadly yes some of the same things benefits ALTER TABLE and CREATE INDEX. "number of sort buffers" probably won't help directly but will help any implicit index rebuilds.