Re: CPU 0.1% IOWAIT 99% for decisonnal queries
От | Simon Riggs |
---|---|
Тема | Re: CPU 0.1% IOWAIT 99% for decisonnal queries |
Дата | |
Msg-id | 1111529891.11750.577.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | CPU 0.1% IOWAIT 99% for decisonnal queries ("Patrick Vedrines" <patrick.vedrines@adpcl.com>) |
Список | pgsql-performance |
On Tue, 2005-03-22 at 19:08 +0100, Patrick Vedrines wrote: > I have 2 databases (A and B) with exactly the same schemas: > -one main table called "aggregate" having no indexes and supporting > only SELECT statements (loaded one time a month with a new bundle of > datas). Row size # 200 bytes (50 columns of type char(x) or integer) > -and several small 'reference' tables not shown by the following > example for clarity reasons. > -Database A : aggregate contains 2,300,000 records ( 500 Mb) > -Database B : aggregate contains 9,000,000 records ( 2 Gb) > (For example : shared_buffers = 190000 , sort_mem = 4096 , > effective_cache_size = 37000 and kernel/shmmax=1200000000 ) > Do I have to upgrade the RAM to 6Gb or/and buy faster HD (of what > type?) ? Setting shared_buffers that high will do you no good at all, as Richard suggests. You've got 1.5Gb of shared_buffers and > 2Gb data. In 8.0, the scan will hardly use the cache at all, nor will it ever, since the data is bigger than the cache. Notably, the scan of B should NOT spoil the cache for A... Priming the cache is quite hard...but not impossible. What will kill you on a shared_buffers that big is the bgwriter, which you should turn off by setting bgwriter_maxpages = 0 > PS (maybe of interest for some users like me) : > I created a partition on a new similar disk but on the last cylinders > (near the periphery) and copied the database B into it: the response > time is 25% faster (i.e. 15mn instead of 21mn). But 15 mn is still too > long for my customers (5 mn would be nice). Sounds like your disks/layout/something is pretty sick. You don't mention I/O bandwidth, controller or RAID, so you should look more into those topics. On the other hand...just go for more RAM, as you suggest...but you should create a RAMdisk, rather than use too large shared_buffers....that way your data is always in RAM, rather than maybe in RAM. Best Regards, Simon Riggs
В списке pgsql-performance по дате отправления: