Обсуждение: Re: cpu bound postgresql setup.
PasteBin for the vmstat output http://pastebin.com/mpHCW9gt On Wed, Jun 23, 2010 at 8:22 PM, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote: > Dear List , > > I observe that my postgresql (ver 8.4.2) dedicated server has turned cpu > bound and there is a high load average in the server > 50 usually. > The server has > 2 Quad Core CPUs already and there are 6 or 8 drives in raid 10 , there is > negligable i/o wait. There is 32GB ram and no swapping. > > When i strace processes at random i see lot of lseek (XXX,0,SEEK_END) calls > which i feel were not that frequent before. can any pointers be got > for investigating > the high cpu usage by postgresql processes. > > attached is strace out in strace.txt file (sorry if that was not > allowed, i am not sure) > > vmstat output > > # vmstat 10 > > output. > procs -----------memory---------- ---swap-- -----io---- --system-- > -----cpu----------- > r b swpd free buff cache si so bi bo in > cs us sy id wa st > 13 2 150876 2694612 4804 24915540 1 0 443 203 0 0 > 50 6 39 5 0 > 17 1 150868 3580472 4824 24931312 1 0 1395 803 12951 15403 > 63 11 22 4 0 > 20 5 150868 3369892 4840 24938180 0 0 1948 1827 12691 14542 > 79 13 6 2 0 > 8 0 150868 2771920 4856 24968016 0 0 2680 1254 13890 14329 > 72 11 11 5 0 > 18 2 150864 2454008 4872 24995640 0 0 2530 923 13968 15434 > 63 10 20 7 0 > 45 3 150860 2367760 4888 25011756 0 0 1338 1327 13203 14580 > 71 11 16 3 0 > 5 6 150860 1949212 4904 25033052 0 0 1727 1981 13960 15079 > 73 11 12 5 0 > 27 0 150860 1723104 4920 25049588 0 0 1484 794 13199 13676 > 73 10 13 3 0 > 28 6 150860 1503888 4928 25069724 0 0 1650 981 12625 14867 > 75 9 14 2 0 > 8 3 150860 1807744 4944 25087404 0 0 1521 791 13110 15421 > 69 9 18 4 0 > > Rajesh Kumar Mallah. > Avid/Loyal-PostgreSQL user for (past 10 years) >
Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote: > PasteBin for the vmstat output > http://pastebin.com/mpHCW9gt > > On Wed, Jun 23, 2010 at 8:22 PM, Rajesh Kumar Mallah > <mallah.rajesh@gmail.com> wrote: >> Dear List , >> >> I observe that my postgresql (ver 8.4.2) dedicated server has >> turned cpu bound and there is a high load average in the server > >> 50 usually. >> The server has 2 Quad Core CPUs already and there are 6 or 8 >> drives in raid 10 , there is negligable i/o wait. There is 32GB >> ram and no swapping. >> >> When i strace processes at random i see lot of lseek >> (XXX,0,SEEK_END) calls which i feel were not that frequent >> before. can any pointers be got for investigating the high cpu >> usage by postgresql processes. I'm not clear on what problem you are experiencing. Using a lot of your hardware's capacity isn't a problem in itself -- are you getting poor response time? Poor throughput? Some other problem? Is it continuous, or only when certain queries run? One thing that is apparent is that you might want to use a connection pool, or if you're already using one you might want to configure it to reduce the maximum number of active queries. With eight cores and eight drives, your best throughput is going to be at somewhere around 24 active connections, and you appear to be going to at least twice that. If you can provide a copy of your postgresql.conf settings (without comments) and an EXPLAIN ANALYZE of a slow query, along with the schema information for the tables used by the query, you'll probably get useful advice on how to adjust your configuration, indexing, or query code to improve performance. -Kevin