Re: High CPU Load

Hello,




Le jeudi 14 septembre 2006 à 09:21 -0500, Scott Marlowe a écrit :
> On Thu, 2006-09-14 at 09:17, Jérôme BENOIS wrote:
> > Hi Tom,
> >
> > Le jeudi 14 septembre 2006 à 10:13 -0400, Tom Lane a écrit :
> > > =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <benois@argia-engineering.fr> writes:
> > > >    I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
> > > > completely full, by moment load average > 40
> > >
> > > Did you remember to ANALYZE the whole database after reloading it?
> > > pg_dump/reload won't by itself regenerate statistics.
> > >
> > >             regards, tom lane
> > I tested, dump + restore + vaccumdb --analyze on all databases but no change ...
>
>
> OK, set your db to log queries that take more than a few seconds to
> run.  Execute those queries by hand with an explain analyze in front and
> post the output here.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

i tested all queries, but she used indexes ... an example :

 explain analyze select distinct
INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from
(((select distinct ei_id as EIID from mpng2_ei_attribute as reqin1 where reqin1.CATEGORYATTR_ID = 0 AND
reqin1.TEXT_VALUEilike '' and ei_id in ( select distinct ei_id as EIID from mpng2_ei_attribute as reqin2 where
reqin2.CATEGORYATTR_ID= 0 AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct ei_id as EIID from
mpng2_ei_attributeas reqin3 where reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as req0 join
mpng2_ei_attributeon req0.eiid = mpng2_ei_attribute.ei_id order by ei_id asc; 
          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=758.53..762.19 rows=122 width=233) (actual
time=0.191..0.191 rows=0 loops=1)
   ->  Sort  (cost=758.53..758.84 rows=122 width=233) (actual
time=0.182..0.182 rows=0 loops=1)
         Sort Key: mpng2_ei_attribute.ei_id,
mpng2_ei_attribute.integer_value, mpng2_ei_attribute.date_value,
mpng2_ei_attribute.value_type, mpng2_ei_attribute.float_value,
mpng2_ei_attribute.id, mpng2_ei_attribute.text_value,
mpng2_ei_attribute.category_id, mpng2_ei_attribute.string_value,
mpng2_ei_attribute.categoryattr_id, mpng2_ei_attribute.name
         ->  Nested Loop  (cost=365.83..754.31 rows=122 width=233)
(actual time=0.126..0.126 rows=0 loops=1)
               ->  Unique  (cost=365.83..374.34 rows=1 width=4) (actual
time=0.116..0.116 rows=0 loops=1)
                     ->  Nested Loop  (cost=365.83..374.34 rows=1
width=4) (actual time=0.108..0.108 rows=0 loops=1)
                           ->  Unique  (cost=350.22..354.69 rows=1
width=4) (actual time=0.097..0.097 rows=0 loops=1)
                                 ->  Nested Loop  (cost=350.22..354.69
rows=1 width=4) (actual time=0.089..0.089 rows=0 loops=1)
                                       ->  Unique  (cost=334.60..335.03
rows=1 width=4) (actual time=0.080..0.080 rows=0 loops=1)
                                             ->  Sort
(cost=334.60..334.82 rows=86 width=4) (actual time=0.072..0.072 rows=0
loops=1)
                                                   Sort Key:
reqin3.ei_id
                                                   ->  Bitmap Heap Scan
on mpng2_ei_attribute reqin3  (cost=2.52..331.84 rows=86 width=4)
(actual time=0.056..0.056 rows=0 loops=1)
                                                         Recheck Cond:
(((name)::text = ''::text) AND ((string_value)::text = ''::text))
                                                         ->  Bitmap
Index Scan on mpng2_ei_attribute_name_svalue  (cost=0.00..2.52 rows=86
width=0) (actual time=0.043..0.043 rows=0 loops=1)
                                                               Index
Cond: (((name)::text = ''::text) AND ((string_value)::text = ''::text))
                                       ->  Bitmap Heap Scan on
mpng2_ei_attribute reqin2  (cost=15.61..19.63 rows=1 width=4) (never
executed)
                                             Recheck Cond:
((reqin2.ei_id = "outer".ei_id) AND (reqin2.categoryattr_id = 0))
                                             Filter: (text_value ~~*
''::text)
                                             ->  BitmapAnd
(cost=15.61..15.61 rows=1 width=0) (never executed)
                                                   ->  Bitmap Index Scan
on mpng2_ei_attribute_ei_id  (cost=0.00..2.43 rows=122 width=0) (never
executed)
                                                         Index Cond:
(reqin2.ei_id = "outer".ei_id)
                                                   ->  Bitmap Index Scan
on mpng2_ei_attribute_categoryattr  (cost=0.00..12.94 rows=1982 width=0)
(never executed)
Index Cond: (categoryattr_id = 0)
                           ->  Bitmap Heap Scan on mpng2_ei_attribute
reqin1  (cost=15.61..19.63 rows=1 width=4) (never executed)
                                 Recheck Cond: ((reqin1.ei_id =
"outer".ei_id) AND (reqin1.categoryattr_id = 0))
                                 Filter: (text_value ~~* ''::text)
                                 ->  BitmapAnd  (cost=15.61..15.61
rows=1 width=0) (never executed)
                                       ->  Bitmap Index Scan on
mpng2_ei_attribute_ei_id  (cost=0.00..2.43 rows=122 width=0) (never
executed)
                                             Index Cond: (reqin1.ei_id =
"outer".ei_id)
                                       ->  Bitmap Index Scan on
mpng2_ei_attribute_categoryattr  (cost=0.00..12.94 rows=1982 width=0)
(never executed)
                                             Index Cond:
(categoryattr_id = 0)
               ->  Index Scan using mpng2_ei_attribute_ei_id on
mpng2_ei_attribute  (cost=0.00..378.43 rows=122 width=233) (never
executed)
                     Index Cond: ("outer".ei_id =
mpng2_ei_attribute.ei_id)

Thanks,

--
Jérôme,

python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'sioneb@gnireenigne-aigra.rf'.split('@')])"