Performance and Tuning

[Former Member]

I am currently running sp_iqemptyfile on large database and have run sp_iqsysmon. I have main cache (-iqmc) set at 96,000 or 96 GB on a solaris system with 125 GB of RAM.

I've run iqsysmon several times and I am getting back what looks like a high number for GDirty (Grabbed Dirty) --around 27857. The Performance and Tuning Guide indicates SWEEPER_THREADS_PERCENT or WASH_AREA_BUFFERS_PERCENT may need to be adjusted in such case.

Does anyone have any experience they can share with these two options?

Would I make the WASH_AREA_BUFFERS_PERCENT higher or lower to reduce the number of dirty reads?

Again, from sp_iqsysmon, I am also gettign a high number of IONumWaits (between 17 and 23 %) when the ideal is less than 10%.  Performance and Tuning Guide indicates "Check for disk errors" or I/O retries.  How would I check for I/O retries?

Also I have a high number of FalseMisses which may indicate rollbacks are occuring, according to Performance and Tuning manual.  Any ideas on how to avoid this given the njumber should be 0 and I'm seeing 1.5 million total.

Thanks in advance for any commnets or suggestions.

[harpreet_chaudhary]

Hi Steve,

Based on your RAM information if you have assigned main cache to 96G out of 125G then your temp cache setting might be very low. Assuming you are one IQ 16 then the RAM should be initially distributed equally between iqmc, iqtc and iqlm after keeping some memory aside for OS processes.

Please review the Hardware Sizing Guide -

and Best Practices Guide -

this might help in your performance tuning effort.

Regards

Harpreet

[Former Member]

I didn't think temp had much of a role in sp_iqemtyfile?

[markmumy]

It shouldn't.  All we should be doing is reading a main page into MC then writing that page to a new, compressed location on another main store.  In essence, a memory page copy that should all happen in MC, not in TC.  That's my basic understanding, though.

You can monitor that with output from sp_iqsysmon or any other monitor you use to capture the cache diagnostics.

Which version of IQ is this?  Your observances and will have different outcomes based on it being IQ 15 or IQ 16.

Mark

[Former Member]

Mark,

We're running  version 15.4.  (We do hope to upgrade fairly soon to IQ16).

Thanks.

[markmumy]

OK, good to know.

I would check on the monitor output in the threads sections.  The prefetch and sweeper threads are allocated at startup in their own pools.  I think it was 16.0 where this was changed to a global thread pool rather than a reserved pool.  Anyway, you should be able to increase the prefetch and sweeper threads percentage to something more than the default of 10%.  If threads are being starved, then you should see that the sweeper or prefetch pools have a high amount of activity.

You mention that there are lots of dirty grabs.  That is usually because a page has moved past the wash marker.  This can happen for a highly volatile cache.  More important than this, though, is what you see at the OS.  Are you able to watch IOSTAT?  If so, what is the service time, blocking percentage, use, etc for the devices?  Additionally, look at the read transactions and read throughput as well as the same for writes.  This will tell you what the MB/sec throughput is for that device.  We want to see that the IO rate is more than a few MB/sec.  Also, you want to compute the IO size (throughput / # iops).  The IO size should be something close to about half your page size.  It shouldn't be the IQ block size (1/16th the page) or smaller.  If it is, there's an issue with how the storage was configured at the OS level.

Lastly, are you changing the threads per connection and threads per team settings?  These will control just how parallel you can go.  I take an extreme approach.  I would set them to 500 or so, temporary of course, just to see how things perform.

Mark

[Former Member]

Mark,

I checked the I/O size (with a dtrace script) and got exactly half of the page size (I/O size = 131072, page size = 262144).

Also I have been running with an increase in max_iq_threads_per_connection = 5000, and max_iq_threads_per_team = 2500.  I really didn't notice much of an improvement with these settings from the default values, however.

From iostat and another dtrace .the read rate is way behind the write rate....perhaps up to 10 times slower.

I have not adjusted the sweeper_threads_percent or the wash_area_buffers_percent, yet. I can try the sweeper setting to higher than 10%...you don't seem to concerned about the wash_area option setting but if I were to change that, do I go higher or lower?

Finally, we did have a fairly significant improvement when the system admin told us he adjusted his SAN block size down from 512K to 8K.  Don't know why. But around 4x faster.

Perhaps a different topic: Would our stripesize which is set at 16K have any impact on this? Iwe have striping at O/S level  (RAID 5) should we also stripe at DB level and should those settings match?

I think I read where DB striping should match page size.(default_kb_per_stripe matching IQ page size is good practice).

Thanks.