Thread: Bad performance for a 3000 rows table updated permanently
Hi,
Hardware: Biprocessor PIII 1.3GHz and SCSI RAID
database=> SELECT VERSION();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
I have a table with 3000 rows (this number is almost constant, and
never decrease), where columns are declared as follow:
table "data"
---------------------------------------------------------------------
field1 SERIAL
field2 TEXT (average of 17 char.) (500 distincts values)
field3 TEXT (average of 4 char.) (25 distincts values)
field4 TEXT (average of 5 char.) (5 distincts values)
field5 TEXT (average of 600 char., up to 8K, may be NULL)
field6 TIMESTAMP W/O TZ
field7 TIMESTAMP W/O TZ
field8 TIMESTAMP W/O TZ
field9 TEXT (15 char.)
field10 TIMESTAMP W/O TZ
And some indexes are defined:
idx_1 ON data(field2,field3)
idx_2 ON data(field4)
idx_3 ON data(field7)
idx_4 ON data(field10)
Note: PostgreSQL is the only process actively used, and table "data"
is the only "dynamic" table (only one other table exists and is
used for INSERT a few times per days.) Very few query (SELECT)
are performed for now, until I discover why the performance are
bad.
A stored procedure (PL/pgSQL) is called with an average of 14 times
per seconds and, 99% of the time, this result on one SELECT followed
by an UPDATE on table "data".
The SELECT check if the pair (field2,field3) exists, then UPDATE the
(unique) matching row from procedure parameters.
While using RRD to graph average time taken for 3 consecutives request
SELECT COUNT(*) FROM data;
I obtain the following duration:
- immediatly after a
VACUUM VERBOSE FULL data;
the request take 50ms
- after 6 hours, 1s
- after 8 hours, 2s
- after 14 hours, 4s
- after 18 hours, 20s
while the number of row doesn't grow.
My need are to obtain very quick response to allow 10-20 users to
access simultaneously the database for various read-only queries. And
50ms for a SELECT is really great ! But 20s is not acceptable (in fact
more than 2-3s is already too high..) All of this need to be available
24/7.
In fact, I don't understand why the count "unused" grows permanently
without the place being reused. Here is an example of VACUUM FULL on
the table data (after 18 hours without running any VACUUM):
database=> VACUUM FULL VERBOSE data;
INFO: --Relation public.data--
INFO: Pages 40857: Changed 67, reaped 40723, Empty 0, New 0; Tup 3058:
Vac 522857, Keep/VTL 3/3, UnUsed 323596, MinLen 148, MaxLen 2024;
Re-using: Free/Avail. Space 329059648/329056556; EndEmpty/Avail. Pages
0/40811.
CPU 2.86s/0.08u sec elapsed 8.04 sec.
INFO: Index idx_2: Pages 141310; Tuples 3058: Deleted 522857.
CPU 10.05s/2.95u sec elapsed 152.18 sec.
INFO: Index idx_1: Pages 19395; Tuples 3058: Deleted 522857.
CPU 2.31s/2.70u sec elapsed 96.05 sec.
INFO: Index idx_4: Pages 126919; Tuples 3058: Deleted 522857.
CPU 8.67s/3.21u sec elapsed 50.46 sec.
INFO: Index idx_3: Pages 8127; Tuples 3058: Deleted 522857.
CPU 1.01s/2.86u sec elapsed 32.62 sec.
INFO: Rel data: Pages: 40857 --> 180; Tuple(s) moved: 2948.
CPU 8.84s/5.10u sec elapsed 184.16 sec.
INFO: Index idx_2: Pages 141321; Tuples 3058: Deleted 2948.
CPU 9.78s/0.30u sec elapsed 137.80 sec.
INFO: Index idx_1: Pages 19395; Tuples 3058: Deleted 2948.
CPU 1.91s/0.39u sec elapsed 78.71 sec.
INFO: Index idx_4: Pages 126927; Tuples 3058: Deleted 2948.
CPU 8.41s/0.09u sec elapsed 41.62 sec.
INFO: Index idx_3: Pages 8127; Tuples 3058: Deleted 2948.
CPU 0.81s/0.13u sec elapsed 20.91 sec.
INFO: --Relation pg_toast.pg_toast_301706--
INFO: Pages 76: Changed 38, reaped 75, Empty 0, New 0; Tup 4: Vac 178,
Keep/VTL 0/0, UnUsed 189, MinLen 78, MaxLen 2034; Re-using:
Free/Avail. Space 615164/615164; EndEmpty/Avail. Pages 0/76.
CPU 0.01s/0.00u sec elapsed 0.14 sec.
INFO: Index pg_toast_301706_index: Pages 4; Tuples 4: Deleted 178.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Rel pg_toast_301706: Pages: 76 --> 1; Tuple(s) moved: 4.
CPU 0.01s/0.00u sec elapsed 0.08 sec.
INFO: Index pg_toast_301706_index: Pages 4; Tuples 4: Deleted 4.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 803886.56 ms
database=>
Almost 14 minutes to perform the task !
Running VACUUM data; or VACUUM ANALYSE data; doesn't help for
performance.
I don't know what to do:
- perform VACUUM FULL more frequently (every hour for example) (to
lower the time the table is LOCKed),
- perform VACUUM FULL only every 24 or 48h, or even more, and queue
request in my application until completed,
- optimize field type perharps..,
- or sometimes else !
Any idea ?
--
Frédéric Jolliton
On Sat, 05 Apr 2003 16:39:42 +0200, fred-pg@jolliton.com wrote: >I have a table with 3000 rows (this number is almost constant, and >never decrease) >A stored procedure (PL/pgSQL) is called with an average of 14 times >per seconds and, 99% of the time, this result on one SELECT followed >by an UPDATE on table "data". So there are almost 900 updates per minute. Do a VACUUM FULL once and then a VACUUM every minute. From time to time do ANALYSE or VACUUM ANALYSE. MAX_FSM_RELATIONS should be no problem, but make sure that MAX_FSM_PAGES is not too low. Servus Manfred
>On Sat, 05 Apr 2003 16:39:42 +0200, fred-pg@jolliton.com wrote: >>I have a table with 3000 rows (this number is almost constant, and >>never decrease) > >>A stored procedure (PL/pgSQL) is called with an average of 14 times >>per seconds and, 99% of the time, this result on one SELECT followed >>by an UPDATE on table "data". Manfred Koizar <mkoi-pg@aon.at> writes: > So there are almost 900 updates per minute. > > Do a VACUUM FULL once and then a VACUUM every minute. Well, doing a VACUUM every minute is really fast (<1s), and query test is near 4ms ! I think I misunderstood how VACUUM work. So, this is a great improvement ! > From time to time do ANALYSE or VACUUM ANALYSE. MAX_FSM_RELATIONS > should be no problem, but make sure that MAX_FSM_PAGES is not too > low. I don't know exactly how to pick a good value for MAX_FSM_PAGES. I'm not familiar with pages, and how they are used. So I'm rereading the Administrator Guide. I've noticed the following: SELECT relname,relpages FROM pg_class WHERE relkind = 'r' AND relname = 'data'; give 156 for the main table, doing a VACUUM every minute, then after a VACUUM FULL give 52 (and a initial value of 10 when benching from a clean database.) Thanks for your tips, they helped me a lots. -- Frédéric Jolliton
Hi all. Has somebody a pl/pgsql function for search/replace by hand ? I heard that 7.3 can do this with plain sql, but unfortunately 7.3 is only available in unstable (debian) at the moment. So I have to stick to 7.2 A solution which could be easily replaced by the native search/replace in 7.3 would be nice. A search/replace with regex would be perfect, but simple string replacement should be ok for the moment ;) TIA -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : www.e-trolley.de
Nabil,
> Has somebody a pl/pgsql function for search/replace by hand ?
> I heard that 7.3 can do this with plain sql, but unfortunately 7.3 is
> only available in unstable (debian) at the moment.
> So I have to stick to 7.2
I don't know about this ... search and replace is so easy in PL/perl, why
don't you use that?
CREATE FUNCTION strswap(
TEXT, VARCHAR, VARCHAR )
RETURNS TEXT AS '
my($the_text, $look_up, $replace_with) = @_;
$the_text =~ s:$look_up:$replace_with:eg ;
return $the_text;
' LANGUAGE 'plperl' WITH (ISCACHABLE, ISSTRICT);
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Am Son, 2003-04-06 um 18.58 schrieb Josh Berkus: > I don't know about this ... search and replace is so easy in PL/perl, why > don't you use that? > > CREATE FUNCTION strswap( Great, exactly what i was looking for. Any idea how to use subexpressions ? like: $_="<A href=\"blah\">" s/"[^"]*"/$1/ cu -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : www.e-trolley.de
Nabil, > Great, exactly what i was looking for. > > Any idea how to use subexpressions ? > like: > > $_="<A href=\"blah\">" > s/"[^"]*"/$1/ You could pick up a copy of "Leaning Perl". PL/perl implements your host system's perl library. Keep in mind, though that any single quotes or backslashes in your expression will have to be escaped in your function. -- -Josh Berkus Aglio Database Solutions San Francisco
> Has somebody a pl/pgsql function for search/replace by hand ? > I heard that 7.3 can do this with plain sql, but unfortunately 7.3 is > only available in unstable (debian) at the moment. > So I have to stick to 7.2 > > A solution which could be easily replaced by the native search/replace > in 7.3 would be nice. > > A search/replace with regex would be perfect, but simple string > replacement should be ok for the moment ;) Hope the below link could be of much help. http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=8886 regards, bhuvaneswaran
On Sat, 05 Apr 2003 23:38:25 +0200, <fred-pg@jolliton.com>(Frederic Jolliton) wrote: >I don't know exactly how to pick a good value for MAX_FSM_PAGES. If you have only two active tables and with the size you reported, the defaults should be sufficient. FSM settings have been discussed in more deatil before. Search the archives for details. >SELECT relname,relpages [...] > >give 156 for the main table, doing a VACUUM every minute, Is this number stable or always increasing? If the latter, how fast is it growing? VACUUM (without FULL) never truncates a relation. When your relation size gets out of control, do a VACUUM FULL to restore a sane state. > then after a >VACUUM FULL give 52 (and a initial value of 10 when benching from a >clean database.) This initial value is only an assumption and has nothing to do with the real size. relpages is not accurate at every moment, AFAIK it is updated only by some administrative commands (VACUUM, ANALYSE, CREATE INDEX, etc). Servus Manfred
Re: Bad performance for a 3000 rows table updated
From
fred-pg@jolliton.com (Frederic Jolliton)
Date:
> On Sat, 05 Apr 2003 23:38:25 +0200, <fred-pg@jolliton.com>(Frederic > Jolliton) wrote: >>SELECT relname,relpages [...] >> >>give 156 for the main table, doing a VACUUM every minute, Manfred Koizar <mkoi-pg@aon.at> writes: > Is this number stable or always increasing? If the latter, how fast > is it growing? Just checked, I have actually 236. Runnnig a VACUUM FULL take less than 1 second, and the value decreased to 174. But, waiting ~20 minutes, I now have 220. I think value stop progressing when reaching ~240. If I guess correctly, 236 is the maximum space that the table used. Since the number of row rarely grow, and field length average are almost constant, I expect this maximum value to stay also constant. -- Frédéric Jolliton