Re: CLEANERS

hanna_shaw@my-deja.com wrote in message <915m0p$m3j$1@nnrp1.deja.com>... >Andrew, will 'pfread' add much workload to disks? Is it usually used >for setting up a production box at the beginning? I'm thinking that >maybe it's not suitable for a running production box. It's absolutely NOT suitable for a running production box!-) Sure it will add some short-term workload to the disk, but the real problem is, the results will be totally meaningless if the I/O system is busy with other tasks. It should only be used on a quiet machine to assess the I/O performance, prior to making chunk and table allocations. It will give you a measure which will not change over time, and therefore it will never need to be measured again over the life of the same pieces of hardware. If you add a new disk then perhaps it may need to be measured individually, if it's a different model or brand to the others, but that is easily achieved before you actually start allocating the disk to chunks, as long as you can find a fairly quite time for the CPU and memory - after all, if the pfread is competing with many processes for a small amount of memory or CPU, then it will be spoil the results. For a machine that's already in production, you will need to silence it for a few minutes, and you will get results which you can then use to think about the allocations and making changes to those allocations. If you have a heavily used 24 hour machine then indeed you will have some trouble getting a quiet time to perform the test, but consider this: pfread doesn't need much memory, or place any demands on the CPU since it's basically I/O bound (as they say in the classics). Therefore if you have a development or other machine WITH THE SAME DISKS AND CONTROLLERS but not necessarily with the same amount of memory or CPU speed, then you will be able to perform the measures on that other machine for reliable results. In fact, you could almost put a sticky label on individual disks after testing on any machine, as long as the I/O controllers are not messing with the results. As I've mentioned in a previous e-mail, I haven't found much difference in the performance curve of several different pieces of hardware, at least when it comes to the best number of threads (although transfer rates will increase with faster and more expensive hardware), so for me it's almost becoming boring and pointless to test new machines. However, when you are proving the case to a particular person, unfortunately most people won't believe it unless they see it with their own eyes, and besides, maybe one day if I stop testing I'll miss some advance in disk technology which allows them to support many more threads. I could imagine that ever-increasing cache sizes could help the burst rate of disks, but for sustained long-term writes, even caches will have to play catchup after a few or several seconds. But this theory does suggest that it may be worth modifying the test to measure the short-term write burst rates for disks. Hmmm - starting to drift off the practical stuff into speculation here. Once you know how many threads the disks can handle, you then need to move on to putting individual tables into specific chunks so as to properly spread the read and write activity around. That's where the real fun begins... Since the sysmasters database supplies various useful measures of table and chunk activity, it's not hard at all to measure the success of your work, or to pick tables which can be profitably shifted around to spread the load. Try sorting the selects (on sysmaster) by the read and write measures (descending) and then allocate the tables round-robin to the chunks. It's a good first attack, and then you can fine tune later. Pay attention to the differences between read and write activity because some tables will be unevenly balanced on those measures. Eventually you will reach a point where more improvements can only come from fragmenting tables to spread out their activity, and hopefully to reduce considerably the number of rows searched by most working programs most of the time. This implies that fragmentation by expression is the best way to go, or hybrid if you have a large working table with a lot of historical data "getting in the way" of current activity. We've got a few of those in our production systems, so studying them and spreading the manure around is my next task. The ability to exploit fragmentation is one of the main reasons this more complex approach can ultimately be better than throwing a huge bunch of hardware striping at the problem. Sure, hardware striping will somewhat spread the load around automatically, but then you can forget about the ability to exploit fragments and PDQ. Don't forget the rowid issue if you switch on fragmentation for a particular table. When you get a chance, can you give the model numbers of your machine, disk and I/O controllers and pass back the results you get? I'm sure it would be interesting to all.

-----Original Message----- From: William Rice [mailto:ricew@operamail.com] http://www.informix.com/informix/services/ilink/pubs/technote/tn00v10i2/chap 3.htm I think we can assume with only 50 mb of dbspaces that there is only one spindle. These are admittedly limited numbers, but do show some of what I was defending. Will P.S. I did find your approach quite interesting, and learned quite a bit from the discussion, I just still feel that sometimes the only option is to have lots of LRU writes. ------------------------------------------------------------ I'm afraid I can't get into that link, wot with not having an account. I suppose that costs, doesn't it? I'm not arguing that LRU writes are the work of the devil, but I am saying that optimising checkpoints is not only a useful end in itself, but can reduce the reliance on LRU writes as well. And more to the point, WHATEVER disk activity is going on, there are very clear guidelines to the amount of pressure disks can handle, and techniques for measuring that. I only heard of them in the performance tuning course. As a corollary, it implies that throwing more disk spindles at a busy system should improve the performance of your LRU and checkpoint writes, with the usual set of provisos thrown in.

In the year of Our Lord Fri, 15 Dec 2000 12:46:02 +1100, "Andrew Hamm" <ahamm@sanderson.net.au> broke a vow of silence to utter: >-----Original Message----- >From: William Rice [mailto:ricew@operamail.com] > >http://www.informix.com/informix/services/ilink/pubs/technote/tn00v10i2/chap >3.htm > >I think we can assume with only 50 mb of dbspaces that there is only one >spindle. These are admittedly limited numbers, but do show some of what I >was >defending. > >Will >P.S. I did find your approach quite interesting, and learned quite a bit >from >the discussion, I just still feel that sometimes the only option is to have >lots of LRU writes. >------------------------------------------------------------ > >I'm afraid I can't get into that link, wot with not having an account. I >suppose that costs, doesn't it? You have to register, but I think it's free. >I'm not arguing that LRU writes are the work of the devil, but I am saying >that optimising checkpoints is not only a useful end in itself, but can >reduce the reliance on LRU writes as well. And more to the point, WHATEVER >disk activity is going on, there are very clear guidelines to the amount of >pressure disks can handle, and techniques for measuring that. I only heard >of them in the performance tuning course. > >As a corollary, it implies that throwing more disk spindles at a busy system >should improve the performance of your LRU and checkpoint writes, with the >usual set of provisos thrown in. Yup.

Obnoxio The Clown wrote in message <3a39c99d.48784248@News.CIS.DFN.DE>... > >>As a corollary, it implies that throwing more disk spindles at a busy system >>should improve the performance of your LRU and checkpoint writes, with the >>usual set of provisos thrown in. > >Yup. Phew!! Think I got away lightly with that no-brainer... I blame Beer O'Clock (more specifically, anticipation of that time on a Friday)