update statistics at the same time
Question: can UPDATE STATISTICS be run concurrently on different tables to shorten the overall run time? The consensus answer is yes — it's safe and supported. Gains depend on hardware: one poster saw no improvement, while others reported good results, e.g. splitting table lists by dbspace/spindle across 5 parallel scripts, and one site cut total time to about a third using 6 concurrent sessions. Advice included watching I/O bandwidth and tuning PDQPRIORITY and PSORT_NPROCS, with pointers to a performance-tuning white paper and John Miller III's article on update statistics.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi, everybody. can I run update statistics on different tables at the same time, then they will run Parallel, to save time. thanks
Hey Shan, we have tried this out. You can, but in our case we could not save any time. Regards Knuth -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von SHAN SALEON Gesendet: Mittwoch, 30. Januar 2008 07:46 An: ids@iiug.org Betreff: update statistics at the same time [11103] hi, everybody. can I run update statistics on different tables at the same time, then they will run Parallel, to save time. thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
thanks, I try it later.
SHAN SALEON schrieb: > hi, everybody. > can I run update statistics on different tables at the same time, then they > will run > Parallel, to save time. thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > yes you can, and if your I/O bandwith is not a bottleneck, this can improve overall duration. You did not tell us, what you do when running update stats and no info was given about version of IDS and OS and no Hardware info. So I suggest you read thru this white paper: http://www.informix-zone.com/files/whitepapers/perf-tuning-tips.pdf You will want to search for 'update statitics informix miller' thru ibm.com to find John Miller III's great article about tuning update stats and how it all works. Most important is to set PDQPRIORITY and PSORT_NPROCS in a way fitting to you parallelism. dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe
SHAN SALEON wrote: > hi, everybody. > can I run update statistics on different tables at the same time, then they > will run > Parallel, to save time. thanks > Yes. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
I've done it: - I had 5 dbspaces on 5 different spindles - generate lists of tables by dbspace (partition number) - started 5 scripts to process those lists of tables in parallel Worked fine, no problems. Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SHAN SALEON Sent: Wednesday, January 30, 2008 1:46 AM To: ids@iiug.org Subject: update statistics at the same time [11103] hi, everybody. can I run update statistics on different tables at the same time, then they will run Parallel, to save time. thanks ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
We run a parallel update stats every night on 50 or so fragmented tables, some with 20 million rows, with 6 concurrent sessions on an HP-UX box with 2 CPUs. Experimentation showed best response at 6 sessions, taking overall time (for us, at least) down to about 1/3 of running all 6 sequentially. Richard Kofler wrote: > SHAN SALEON schrieb: > >> hi, everybody. >> can I run update statistics on different tables at the same time, then they >> will run >> Parallel, to save time. thanks >> >> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> See you at the IIUG Informix 2008 Conference >> The Power Conference for Informix Professionals >> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas >> http://www.iiug.org/conf >> Registration Now Open!! >> >> > > yes you can, and if your I/O bandwith is not a bottleneck, this > can improve overall duration. > > You did not tell us, what you do when running update stats and no > info was given about version of IDS and OS and no > Hardware info. > > So I suggest you read thru this white paper: > http://www.informix-zone.com/files/whitepapers/perf-tuning-tips.pdf > > You will want to search for 'update statitics informix miller' > thru ibm.com to find John Miller III's great article about tuning > update stats and how it all works. > Most important is to set PDQPRIORITY and PSORT_NPROCS in a way > fitting to you parallelism. > > dic_k > >