Update statistics and cpu
I have a 11.9.2 database (size about 200 GB) running on HP-UX 11.0 with 4 cpu's. Update statistics for this database lasts about 16 hours.
Looking at some monitoring I notice that while update statistics runs 1 of the 4 cpu's is 100% busy, the other 3 are doing absolutely nothing. The Sybase server is configured for parallel querying and this works, too.
My question now is, how comes update statistics doesn't use the other cpu's? Is that not possible or is there another configuration setting, Sybase or Unix, necessary?
Looking at some monitoring I notice that while update statistics runs 1 of the 4 cpu's is 100% busy, the other 3 are doing absolutely nothing. The Sybase server is configured for parallel querying and this works, too.
My question now is, how comes update statistics doesn't use the other cpu's? Is that not possible or is there another configuration setting, Sybase or Unix, necessary?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can carry out the same divide & conquer approach even within a table. You could run multiple update statistics jobs, one per index, on the main table concurrently - effectively giving you parallelism on the main table. You might then consider running 3 streams on the big table, and one stream for everything else, assuming the big table has at least three indexes.
Failing that, you *can* run the "with consumers" clause (again, specifying three consumers) if you specify a column list that is equal to the indexed column(s).
You shouldn't have problems with the log - the vast majority of what update statistics does is read-only, it only writes when it is updating the relatively few rows in systabstats and sysstatistics.
As for tempdb, this depends on what you are running. If you are asking for update stats on unindexed columns, or update all statistics, there could be some worktable use, otherwise it reads from the indexes.
We should mention for completeness that upgrading to 12.5.0.3 or higher would give you the greatest speedups here. 8->
Good luck!
Failing that, you *can* run the "with consumers" clause (again, specifying three consumers) if you specify a column list that is equal to the indexed column(s).
You shouldn't have problems with the log - the vast majority of what update statistics does is read-only, it only writes when it is updating the relatively few rows in systabstats and sysstatistics.
As for tempdb, this depends on what you are running. If you are asking for update stats on unindexed columns, or update all statistics, there could be some worktable use, otherwise it reads from the indexes.
We should mention for completeness that upgrading to 12.5.0.3 or higher would give you the greatest speedups here. 8->
Good luck!
ASKER
Thanks again, I'll try it this weekend and evaluate what happened.
I know, Sybase 12.5, we were going to upgrade a year ago but you know it goes. Too many projects in too little time, and the first project to be dropped is upgrading Sybase. Hopefully next year.
I know, Sybase 12.5, we were going to upgrade a year ago but you know it goes. Too many projects in too little time, and the first project to be dropped is upgrading Sybase. Hopefully next year.
ASKER
Ok, it worked.
The first batch now took 14 and a half hours. During 3 hours another batch ran simulatenously. Last week the total was 16 and a half hours, so it's 2 hours in the pocket. I will try and devide the batches even more so I can win more time, but I'll figure that out myself.
Thanks.
The first batch now took 14 and a half hours. During 3 hours another batch ran simulatenously. Last week the total was 16 and a half hours, so it's 2 hours in the pocket. I will try and devide the batches even more so I can win more time, but I'll figure that out myself.
Thanks.
You're welcome, glad we could improve on the timings for you.
ASKER
I have indeed configured the ASE to use all 4 cpu's (max online engines = 4).
Still one question, should I run 2 simultaneous jobs on different tables, can a problem occur with the logsegment or tempdb? I would say not, but is that true?