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(at)argia-engineering(dot)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_VALUE ilike '' 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_attribute as reqin3 where reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as req0 join mpng2_ei_attribute on 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(at)gnireenigne-aigra(dot)rf'.split('@')])"