Optimising index builds
Neil Truby asked how to speed up building an index on a 2.9-billion-row, 64-fragment table (11.70 on RHEL 6.2), already using PDQPRIORITY 100, PSORT_NPROCS, a RAM disk for sort temp and a huge buffer pool; ~45 of the 60 minutes was read time. Art Kagel pointed out sort memory comes from DS_TOTAL_MEMORY, not SHMVIRTSIZE; raising it to 300GB made the sort fully in-memory but the build still took an hour, suggesting read throughput was the limit. Suggestions followed on light scans (onstat -g scn showed none, and the LIGHT_SCANS variable is ignored in 11.70), a checklist of onstat/iostat diagnostics, and the possibility that post-build automatic statistics collection dominates the time (try USTLOW_SAMPLE). No confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IBM Informix Dynamic Server Version 11.70.FC5XA7 on RHEL 6.2 I'm building an (unfragmented) index on a table, table spread over 64 fragments, with 2.9 billion rows. 8k page size for tables and index. It takes about an hour, 45 minutes of which is spent in reading. I've tried everything I can to optimise this including: - PDQPRIORITY=100 - PSORT_NPROCS=64 # Server has 80 cores - PSORT_DBTEMP is sorting on a 70GB Ramdisk (which is big enough to avoid part-builds) - I have the 8k BUFFERPOOL set to 51,000,000 (ie 408GB), which is the most I can allocate (server has 512GB memory), lru_max at 99.5, lru_min=50, checkpoint interval 7200s. - My data and index is on Solid State Devices. - BATCHEDREAD_TABLE 1 Still this build seems slow, especially the read, seem sluggish given the tin's grunt. Any other suggestions gratefully received. Thx N
How are the tables fragmented? How much memory can PDQ 100% allocate? Sent from my iPad > On Jan 18, 2014, at 2:50 PM, "NEIL TRUBY" <neil.truby@ardenta.com> wrote: > > IBM Informix Dynamic Server Version 11.70.FC5XA7 on RHEL 6.2 > > I'm building an (unfragmented) index on a table, table spread over 64 > fragments, with 2.9 billion rows. 8k page size for tables and index. > > It takes about an hour, 45 minutes of which is spent in reading. I've tried > everything I can to optimise this including: > > - PDQPRIORITY=100 > - PSORT_NPROCS=64 # Server has 80 cores > - PSORT_DBTEMP is sorting on a 70GB Ramdisk (which is big enough to avoid > part-builds) > - I have the 8k BUFFERPOOL set to 51,000,000 (ie 408GB), which is the most I > can allocate (server has 512GB memory), lru_max at 99.5, lru_min=50, > checkpoint interval 7200s. > - My data and index is on Solid State Devices. > - BATCHEDREAD_TABLE 1 > > Still this build seems slow, especially the read, seem sluggish given the > tin's grunt. > > Any other suggestions gratefully received. > > Thx > N > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
>> How are the tables fragmented? How much memory can PDQ 100% allocate? 1. Round-robin 2. I assume it is constrained by SHMVIRTSIZE, which is 24000000? Thx Neil
Is DS_TOTAL_MEMORY set high enough to permit significant in-memory sorting? Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Jan 18, 2014 at 3:49 PM, NEIL TRUBY <neil.truby@ardenta.com> wrote: > IBM Informix Dynamic Server Version 11.70.FC5XA7 on RHEL 6.2 > > I'm building an (unfragmented) index on a table, table spread over 64 > fragments, with 2.9 billion rows. 8k page size for tables and index. > > It takes about an hour, 45 minutes of which is spent in reading. I've tried > everything I can to optimise this including: > > - PDQPRIORITY=100 > - PSORT_NPROCS=64 # Server has 80 cores > - PSORT_DBTEMP is sorting on a 70GB Ramdisk (which is big enough to avoid > part-builds) > - I have the 8k BUFFERPOOL set to 51,000,000 (ie 408GB), which is the most > I > can allocate (server has 512GB memory), lru_max at 99.5, lru_min=50, > checkpoint interval 7200s. > - My data and index is on Solid State Devices. > - BATCHEDREAD_TABLE 1 > > Still this build seems slow, especially the read, seem sluggish given the > tin's grunt. > > Any other suggestions gratefully received. > > Thx > N > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3be5ee0114204f04a3777
No, sort memory for PDQPRIORITY queries is governed by DS_TOTAL_MEMORY,
DS_MAX_QUERIES, and PDQPRIORITY. PDQPRIORITY == 100 will allocate all of
DS_TOTAL_MEMORY to the sorts.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Sat, Jan 18, 2014 at 4:11 PM, NEIL TRUBY <neil.truby@ardenta.com> wrote:
> >> How are the tables fragmented? How much memory can PDQ 100% allocate?
>
> 1. Round-robin
> 2. I assume it is constrained by SHMVIRTSIZE, which is 24000000?
>
> Thx
> Neil
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c36c8a20f06504f04a3e0e
>> Is DS_TOTAL_MEMORY set high enough to permit significant in-memory sorting? I changed DS_TOTAL_MEMORY to 300GB and reduced BUFFERPOOL to 160GB. Now nothing gets written out to the DBTEMP_SORT file system and all is sorted in memory. It still takes an hour! I guess then I am constrained by the speed I can read the data in. But I'm surprised that it's not faster. Thinking about it the data is not on Solid-State as I said previously, but still on pretty fast SAS drives in a fast and modern storage array.
> On 19 January 2014 at 11:10 NEIL TRUBY <neil.truby@ardenta.com> wrote:
>
> >> Is DS_TOTAL_MEMORY set high enough to permit significant in-memory
sorting?
>
> I changed DS_TOTAL_MEMORY to 300GB and reduced BUFFERPOOL to 160GB. Now
> nothing gets written out to the DBTEMP_SORT file system and all is sorted in
> memory.
>
> It still takes an hour!
>
> I guess then I am constrained by the speed I can read the data in. But I'm
> surprised that it's not faster. Thinking about it the data is not on
> Solid-State as I said previously, but still on pretty fast SAS drives in a
> fast and modern storage array.
>
export LIGHT_SCANS=FORCE
May sure light scans can be used,for 11.70 as per
http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.perf.d
oc%2Fids_prf_237.htm
"The query meets one of the following locking conditions:
* The isolation level is Dirty Read (or the database has no transaction
logging).
* The table has at least a shared lock on the entire table and the isolation
level is notCursor Stability.
Note: A sequential scan in Repeatable Read isolation automatically acquires a
share lock on the table."
Run onstat -g scn and see what type of scan is being used.
Run onstat -z then after 30 seconds onstat -g ioa and send the output.
Run onstat -g mgm and make sure PDQ memory is being used and no gating is
occuring.
Run onstat -g ppf twice and diff the output, see what and how many parttitions
are being accessed.
Run onstat -g pqs twice and diff the output, what query operators are being
used?
Run WSTATS 1 and QTSTATS 1 in onconfig and run onstat -g wst/ onstat -g qst
Run onstat -g ses to get the thread ids and run onstat -g tpf twice for the
thread ids and diff the output.
Run iostat -x 4 and check /var/log/messages.
If still nothing obvious send us the output - for those entries above which
required diffs just send the diffs done several times 30 seconds apart.
Regards,
David.
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The index isn't using light scans, according to onstat -g scn, for whatever
reason.
The environment variable isn't (according to TFM) active in 11.70: the engine
decides itself.
cheers
Neil
Is most of the index build time spent automatically collecting statistics
after building the index? This would look like a lot of data page reads, a
lot of index page writes and then a lot of index page reads.
I had a similar situation when doing some testing against version 11.10 when
this new functionality was added. On the old version the index build would
complete in 15 minutes, but on the new version it would complete in 35
minutes due to 20 minutes spent collecting statistics after the index build.
I wish there was a way to disable this feature and let me update statistics
on my own terms.
You could try setting USTLOW_SAMPLE to 1 and doing all of the other things
that makes update statistics run faster if this is why your index build is
slower than normal.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NEIL
TRUBY
Sent: Sunday, January 19, 2014 1:15 PM
To: ids@iiug.org
Subject: Re: Optimising index builds [32267]
The index isn't using light scans, according to onstat -g scn, for whatever
reason.
The environment variable isn't (according to TFM) active in 11.70: the
engine decides itself.
cheers
Neil
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g