[PERFORM] slow pg_connect()
Hi,
I'm uning postgres 8.1 at P4 2.8GHz with 2GB RAM.
(web server + database on the same server)
Please, how long takes your connectiong to postgres?
$starttimer=time()+microtime();
$dbconn = pg_connect("host=localhost port=5432 dbname=xxx user=xxx
password=xxx")
or die("Couldn't Connect".pg_last_error());
$stoptimer = time()+microtime();
echo "Generated in ".round($stoptimer-$starttimer,4)." s";
It takes more then 0.05s :(
Only this function reduce server speed max to 20request per second.
Than you for any Help!
Best regards.
-
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] slow full table update
Hi, I have table with cca 60.000 rows and when I run query as: Update table SET column=0; after 10 minutes i must stop query, but it still running :( I've Postgres 8.1 with all default settings in postgres.conf Where is the problem? Thak you for any tips. best regards. Marek Fiala -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow full table update
Hi, thank you for your reply. Here is some aditional information: the problem is on every tables with small and large rows too. autovacuum is running. relpagesreltuples 6213 54743 tables are almost write-only Munin Graphs shows that problems is with I/O bottleneck. I found out that Update 100 rows takes 0.3s but update 1000 rows takes 50s Is this better information? Thanks for any help. best regards Marek Fiala __ > Od: [EMAIL PROTECTED] > Komu: [EMAIL PROTECTED] > CC: pgsql-performance@postgresql.org > Datum: 10.11.2008 17:42 > Předmět: Re: [PERFORM] slow full table update > >Sorry, but you have to provide much more information about the table. The >information you've provided is really not sufficient - the rows might be >large or small. I guess it's the second option, with a lots of dead rows. > >Try this: > >ANALYZE table; >SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'; > >Anyway, is the autovacuum running? What are the parameters? Try to execute > >VACUUM table; > >and then run the two commands above. That might 'clean' the table and >improve the update performance. Don't forget each such UPDATE will >actually create a copy of all the modified rows (that's how PostgreSQL >works), so if you don't run VACUUM periodically or autovacuum demon, then >the table will bloat (occupy much more disk space than it should). > >If it does not help, try do determine if the UPDATE is CPU or disk bound. >I'd guess there are problems with I/O bottleneck (due to the bloating). > >regards >Tomas > >> Hi, >> >> I have table with cca 60.000 rows and >> when I run query as: >> Update table SET column=0; >> after 10 minutes i must stop query, but it still running :( >> >> I've Postgres 8.1 with all default settings in postgres.conf >> >> Where is the problem? >> >> Thak you for any tips. >> >> best regards. >> Marek Fiala >> >> >> >> >> >> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > > >-- >Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow full table update
Hi, I've changed settings, but with no effect on speed. I try explain query with this result for 10.000 rows > update songs set views = 0 where sid > 2 and sid < 3 Bitmap Heap Scan on songs (cost=151.59..6814.29 rows=8931 width=526) (actual time=4.848..167.855 rows=8945 loops=1) Recheck Cond: ((sid > 2) AND (sid < 3)) -> Bitmap Index Scan on pk_songs2 (cost=0.00..151.59 rows=8931 width=0) (actual time=4.071..4.071 rows=9579 loops=1) Index Cond: ((sid > 2) AND (sid < 3)) Is there a way to run this query on sigle throughpass with no Recheck Cond? Thank you. best regards Marek Fiala __ > Od: [EMAIL PROTECTED] > Komu: pgsql-performance@postgresql.org > Datum: 12.11.2008 17:48 > Předmět: Re: [PERFORM] slow full table update > >Hi, > >so the table occupies about 50 MB, i.e. each row has about 1 kB, right? >Updating 1000 rows should means about 1MB of data to be updated. > >There might be a problem with execution plan of the updates - I guess the >100 rows update uses index scan and the 1000 rows update might use seq >scan. > >Anyway the table is not too big, so I wouldn't expect such I/O bottleneck >on a properly tuned system. Have you checked the postgresql.conf settings? >What are the values for > >1) shared_buffers - 8kB pages used as a buffer (try to increase this a >little, for example to 1000, i.e. 8MB, or even more) > >2) checkpoint_segments - number of 16MB checkpoint segments, aka >transaction logs, this usually improves the write / update performance a >lot, so try to increase the default value (3) to at least 8 > >3) wal_buffers - 8kB pages used to store WAL (minimal effect usually, but >try to increase it to 16 - 64, just to be sure) > >There is a nicely annotated config, with recommendations on how to set the >values based on usage etc. See this: > >http://www.powerpostgresql.com/Downloads/annotated_conf_80.html >http://www.powerpostgresql.com/PerfList > >regards >Tomas > >> Hi, >> >> thank you for your reply. >> >> Here is some aditional information: >> >> the problem is on every tables with small and large rows too. >> autovacuum is running. >> >> relpages reltuples >> 6213 54743 >> >> tables are almost write-only >> Munin Graphs shows that problems is with I/O bottleneck. >> >> I found out that >> Update 100 rows takes 0.3s >> but update 1000 rows takes 50s >> >> Is this better information? >> >> Thanks for any help. >> >> best regards >> Marek Fiala >> __ >>> Od: [EMAIL PROTECTED] >>> Komu: [EMAIL PROTECTED] >> > CC: pgsql-performance@postgresql.org >>> Datum: 10.11.2008 17:42 >>> PĹ�edmÄ�t: Re: [PERFORM] slow full table update >>> >>>Sorry, but you have to provide much more information about the table. The >>>information you've provided is really not sufficient - the rows might be >>>large or small. I guess it's the second option, with a lots of dead rows. >>> >>>Try this: >>> >>>ANALYZE table; >>>SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'; >>> >>>Anyway, is the autovacuum running? What are the parameters? Try to >>> execute >>> >>>VACUUM table; >>> >>>and then run the two commands above. That might 'clean' the table and >>>improve the update performance. Don't forget each such UPDATE will >>>actually create a copy of all the modified rows (that's how PostgreSQL >>>works), so if you don't run VACUUM periodically or autovacuum demon, then >>>the table will bloat (occupy much more disk space than it should). >>> >>>If it does not help, try do determine if the UPDATE is CPU or disk bound. >>>I'd guess there are problems with I/O bottleneck (due to the bloating). >>> >>>regards >>>Tomas >>> Hi, I have table with cca 60.000 rows and when I run query as: Update table SET column=0; after 10 minutes i must stop query, but it still running :( I've Postgres 8.1 with all default settings in postgres.conf Where is the problem? Thak you for any tips. best regards. Marek Fiala -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance >>> >>> >>> >>>-- >>>Sent via pgsql-performance mailing list >>> (pgsql-performance@postgresql.org) >>>To make changes to your subscription: >>>http://www.postgresql.org/mailpref/pgsql-performance >>> >> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > > >-- >Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-perfo
Re: [PERFORM] slow full table update
hi, select count(*) from songs; count --- 54909 (1 row) Time: 58.182 ms update songs set views = 0; UPDATE 54909 Time: 101907.837 ms time is actually less than 10 minutes, but it is still very long :( vacuum said> VACUUM VERBOSE songs; INFO: vacuuming "public.songs" INFO: index "pk_songs2" now contains 54909 row versions in 595 pages DETAIL: 193 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.06 sec. INFO: index "fk_albums_aid_index" now contains 54909 row versions in 1330 pages DETAIL: 193 index row versions were removed. 812 index pages have been deleted, 812 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.04 sec. INFO: index "fk_artists_artid_index" now contains 54910 row versions in 628 pages DETAIL: 193 index row versions were removed. 114 index pages have been deleted, 114 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.10 sec. INFO: index "fk_users_uid_karaoke_index" now contains 54910 row versions in 2352 pages DETAIL: 193 index row versions were removed. 2004 index pages have been deleted, 2004 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.95 sec. INFO: index "datum_tag_indx" now contains 54910 row versions in 2083 pages DETAIL: 193 index row versions were removed. 1728 index pages have been deleted, 1728 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.47 sec. INFO: index "datum_video_indx" now contains 54910 row versions in 1261 pages DETAIL: 193 index row versions were removed. 826 index pages have been deleted, 826 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.06 sec. INFO: "songs": removed 193 row versions in 164 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: "songs": found 193 removable, 54909 nonremovable row versions in 6213 pages DETAIL: 0 dead row versions cannot be removed yet. There were 132969 unused item pointers. 0 pages are entirely empty. CPU 0.07s/0.04u sec elapsed 1.74 sec. INFO: vacuuming "pg_toast.pg_toast_28178" INFO: index "pg_toast_28178_index" now contains 2700 row versions in 13 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_28178": found 0 removable, 2700 nonremovable row versions in 645 pages DETAIL: 0 dead row versions cannot be removed yet. There were 88 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 1750.460 ms best regards Marek Fiala __ > Od: [EMAIL PROTECTED] > Komu: [EMAIL PROTECTED] > CC: pgsql-performance@postgresql.org > Datum: 12.11.2008 21:55 > Předmět: Re: [PERFORM] slow full table update > >On Mon, Nov 10, 2008 at 9:30 AM, <[EMAIL PROTECTED]> wrote: >> Hi, >> >> I have table with cca 60.000 rows and >> when I run query as: >> Update table SET column=0; >> after 10 minutes i must stop query, but it still running :( > >What does > >vacuum verbose table; > >say? I'm wondering if it's gotten overly bloated. > >How long does > >select count(*) from table; > >take to run (use timing to time it) > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
