Re: Mysterious update statistics

Habichtsberg, Reinhard wrote: > Hi Art. > > First thank you for the detailed answer. I read the manuals and the paper of > John Miller now again but some questions remain. > > I use your dostats to generate UPDATE STATISTICS statements putting them in > a file which I run periodically. I never used parallelism because the > servers run a high workload 24x7. > It might be OK to at least use parallel sorting during the update stats runs. I'd set: PDQPRIORITY=34 # or higher allow at least 5 CPU VPS to participate (1/3 * 15) PSORT_NPROCS=15 # OK it's very aggressive to use all 15 CPU VPs for a sort, but you do have billions of rows. Try lower settings first and see how it works out. Adjust as you like - your system your call. > With a new server I try to use your drive_dostats utility. There is only one > database on this server, though tables with more than a billion rows. > > Version of IDS is 9.40 FC9W2 on Solaris 8, 9, and 10. Host has 16 processors > and 32 GB memory, 15 CPU VP's are configured, > IDS uses 6.476.800 Kbytes, SHMTOTAL is 12000000, DS_TOTAL_MEMORY is 300000. > > - I have set DBSPACETEMP in onconfig to four exclusive temp dbspaces. Have I > to set it in the environment again? > No, the ONCONFIG setting acts as a default. > - When running dostats I see the message "Warning! Optimized servers > produce statistics fastest with PSORT_DBTEMP set." Manuals don't recommend > to set PSORT_DBTEMP but DBSPACETEMP what is done in onconfig. Do I miss > something here? > OK, this is my thing. DBSPACETEMP space, though it's at least not logged, still will flush to disk as the LRU queues fill with dirty pages and certainly at checkpoint time. This is often unnecessary IO. If you use PDQPRIORITY, and PSORT_NPROCS with PSORT_DBTEMP set to contain a list of 3 to 6 filesystems each with enough free space to hold the sorted keys of the largest table sorts that cannot fit in memory (DS_TOTAL_MEMORY * PDQPRIORITY/100) will be sent there instead of to the dbspaces listed in DBSPACETEMP. This has several advantages: * Doesn't use the buffer cache so it doesn't affect running queries as much * Takes advantage of the system buffer cache as sort-work memory * The sort-work files rarely live long enough for the OS to decide to flush them to disk so writing and rereading them will be almost as fast as the in-memory sorting > - I use -a -A 20 and -b -B 5 meaning tables are processed if the last run on > the table is older than 20 days _or_ the number of rows has changed more > than 5 percent. Is this true especial for the _or_? > That's fine if you regenerate the scripts every time you want to run them. Using -a and -b with scripts that will be reused over and over doesn't work. Yes, -a and -b are treated as an OR relationship. Tables satisfying either or both criteria will be processed. > - Is DBUPSPACE of any use if PDQPRIORITY ist set? > No. With PDQPRIORITY set the memory granted for in-memory sorting will be DS_TOTAL_MEMORY * (PDQPRIORITY/100). With PDQPRIORITY set as below and DS_TOTAL_MEMORY set to 300000 that will give you ~150MB for in-memory sorting. That's 3 times the maximum DBUPSPACE setting of 50MB. > - For running dostats I call drive_dostats in a little shell script which I > want to run via crontab frequently: > . /home/informix/.informix.rc # Informix environment > PATH=$PATH:/home/informix/tools/utils2_ak; export PATH> DBUPSPACE=50; export DBUPSPACE > PSORT_NPROC=4; export PSORT_NPROC > PDQPRIORITY=50; export PDQPRIORITY > As noted above, I'm a bit more aggressive with the PSORT_ parameters, but that looks workable. Just add PSORT_DBTEMP if you have the filesystems available. (Certainly test it both ways to verify that I'm not nuts here.) > ### drive_dostats nprocs database [tablespec] [-x@excl|-xexctbl] [dostats > options] > > drive_dostats 5 kva -xkva_ergebnisse_alt -a -A 20 -b -B 5 -X -v 2 -E -F -R > 0.1 -r 1.0 -m > OK, so this runs 5 copies of dostats for database 'kva' except for table 'kva_ergebnisse_alt' including system catalog tables. Looks reasonable and the higher resolutions are a good idea for larger tables, so go for it. You can use the timing stats generated by the -E option to compare runs with different options (like PSORT_DBTEMP or not; PSORT_NROCS set ot 5, 10, 15, 30; different PDQPRIORITY; more or less MGM memory (DS_TOTAL_MEMORY) settings. > Did I set the necessary environment variables and is the invocation of > dostats rather reasonable? > > Regards, > Reinhard. > Art S. Kagel Oninit > > >> -----Ursprüngliche Nachricht----- >> Von: informix-list-bounces@iiug.org >> [mailto:informix-list-bounces@iiug.org]Im Auftrag von Art S. Kagel >> (Oninit) >> Gesendet am: Montag, 14. April 2008 16:10 >> An: Habichtsberg, Reinhard >> Cc: Informix-List (E-Mail) >> Betreff: Re: Mysterious update statistics >> >> Habichtsberg, Reinhard wrote: >> >>> Hi all. >>> >>> >> Hi. >> >> First to answer your first direct question. No it doesn't make any >> difference. The UPDATE STATISTICS commands do not operate >> ONLY on the >> changes to the table but on the entire table (or a sample of >> the table >> if MEDIUM level stats are requested). >> >> No, on to something different. There are several ways to >> speed up the >> generation of statistics, depending on what version of the engine you >> are running (it is always a good idea to quote your version >> and platform >> information when you post so those who reply can be >> specific). Have you >> read the Performance Guide and John Miller III's white paper >> on the subject? >> (http://www-128.ibm.com/developerworks/db2/zones/informix/libr >> ary/techarticle/miller/0203miller.html) >> >> Basically you do not run the database wide or even the table wide >> commands but run sets of columns as required. Also using PDQPRIORITY >> maximizing MGM memory available for sorting during the update >> statistics, running mulitple tables in parallel if you have the >> available CPU and memory resources, and minimizing the number >> of tables >> that are updated on any given day. These last two points I >> can help with: >> >> Do you use my dostats utility? If not get it and use it, many shops >> do. Dostats is contained in the package utils2_ak available for >> download from the IIUG Software Repository (www.iiug.org/software) or >> from the Oninit web site (www.oninit.com). The package also contains >> the script drive_dostats. Dostats implements the >> recommendations in the >> performance guide as modified by John's paper and its actual >> operation >> is dependent on the server version that it detects at runtime. >> Drive_dostats is a script that will run a specified number of >> copies of >> dostats against a single database handing each a subset of >> the tables in >> that database. Dostats has options, -a/-A and -b/-B, which >> are used to >> determine exactly which tables are likely to require the >> recalculation >> of their data distributions. On our clients' databases we