Re: [GENERAL] Vacuum problem
I confirmed that there is no bloating unfortunately. Did some experiment and it seems that connection open are always slow in case of vacuuming.. - Experiment done are as follows:-- Do frequent vacuuming.- Execute xx connections every min and close after one sec.- when vacuum is running connection open takes time. I can almost 100% reduce it when my database size is 30 MB only with 1.5 bloating. > Date: Tue, 14 May 2013 10:54:04 -0600 > Subject: Re: [GENERAL] Vacuum problem > From: scott.marl...@gmail.com > To: msq...@live.com > CC: pgsql-general@postgresql.org > > Not saying you HAVE bloating there, saying you MIGHT. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problem
Not saying you HAVE bloating there, saying you MIGHT. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problem
I am doing regular insertion/deletion on the same tables .. and running vacuum in an hour... I suspect there is bloating in my tables.. but how does bloating in pgcatalog is happening is not clear...> Normally vacuum full is NOT required on a regular basis. However, if > you did something like creation 100M tables and then dropped them, or > did it one at a time real fast, you could outrun your autovacuum > daemons and get bloat in the pg catalog tables. > > Just offering a possibility for why a connection might be taking a > long time. There's plenty of other possibilities I'm sure.
Re: [GENERAL] Vacuum problem
On Tue, May 14, 2013 at 7:27 AM, S H wrote: >> I wonder if you've got a bloated pg catalog then. Certainly sounds >> like it's a possibility. >> So other than vacuuming when you recreate this, is the server working >> hard? What is vacuum vacuuming when this happens (pg_stat_activity >> should show that) > > Does vacuum full is required to avoid bloating, i am running vacuum analyze > regularly but not vacuum full. Normally vacuum full is NOT required on a regular basis. However, if you did something like creation 100M tables and then dropped them, or did it one at a time real fast, you could outrun your autovacuum daemons and get bloat in the pg catalog tables. Just offering a possibility for why a connection might be taking a long time. There's plenty of other possibilities I'm sure. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problem
> I wonder if you've got a bloated pg catalog then. Certainly sounds > like it's a possibility. > So other than vacuuming when you recreate this, is the server working > hard? What is vacuum vacuuming when this happens (pg_stat_activity > should show that) Does vacuum full is required to avoid bloating, i am running vacuum analyze regularly but not vacuum full. Could it be cause of bloating ?
Re: [GENERAL] Vacuum problem
On 5/13/2013 7:10 PM, S H wrote: My disk is utilized by many other components, thus do we have minimum recommendation my postgres to have sufficient speed. Current perfomance of my disk is around 1-5MB/sec. Is it sufficient? how are you measuring this? thats painfully slow by today's standards, even my desktop SATA drives can sustain well over 100MB/second on sequential read or write what counts in a database server like postgres is NOT the sequential transfer speed, instead its the random IO operations/second. I'm benchmarking a 8 disk RAID10 right now and seeing around 2000-4000 write/sec and as high as 1000-2400 read/sec during this TPC-B style transaction benchmark.iostat -xm during this operation looks like... avg-cpu: %user %nice %system %iowait %steal %idle 3.340.001.93 39.430.00 55.31 Device: rrqm/s wrqm/s r/s w/srMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.10 153.50 4901.30 1.89 40.69 17.25 211.50 42.57 0.20 100.00 avg-cpu: %user %nice %system %iowait %steal %idle 10.950.003.98 36.350.00 48.72 Device: rrqm/s wrqm/s r/s w/srMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.10 583.40 2955.40 7.10 28.75 20.75 215.77 63.01 0.28 99.97 avg-cpu: %user %nice %system %iowait %steal %idle 44.360.00 14.28 24.610.00 16.75 Device: rrqm/s wrqm/s r/s w/srMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 1.10 2377.30 1062.9029.23 29.97 35.2429.538.59 0.29 99.95 avg-cpu: %user %nice %system %iowait %steal %idle 48.390.00 16.45 21.180.00 13.98 Device: rrqm/s wrqm/s r/s w/srMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.00 2564.30 1182.4031.15 32.42 34.7528.817.69 0.27 99.97 avg-cpu: %user %nice %system %iowait %steal %idle 44.590.00 15.02 25.380.00 15.02 Device: rrqm/s wrqm/s r/s w/srMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.10 2346.10 1097.5028.66 29.28 34.4629.588.60 0.29 99.98 (these are 10 second averages) in general, the solution to more storage performance for a database server is to use more disks in a raid10 configuration, I have some raids that are a many as 20 disks, dedicated to database use (everything else on the server uses other storage). These are all 15000rpm SAS2 server drives, on a raid controller with 1GB flash-backed write-back cache. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problem
My disk is utilized by many other components, thus do we have minimum recommendation my postgres to have sufficient speed. Current perfomance of my disk is around 1-5MB/sec. Is it sufficient? Is it slow and can be cause of slow vacuuming..
Re: [GENERAL] Vacuum problem
On Mon, May 13, 2013 at 8:37 AM, S H wrote: >> What is the db server doing when this happens? What does top, vmstat, >> iostat etc have to say about it? > > It is high end server with 96 GB of RAM , 16 core server, but there are many > other application running, This db is used for monitoring the performance of > this server and inserting/updating data every one in 10-20 tables. > > I am able to reproduce this issue, in case i run vacuuming of my database > and in parallel try to open 30 connections, sometime db client takes time to > open connection Is there any workaround or there is some known issue > already existing. If there is any known issue it will be easy to persuade my > customers to upgrade.. I wonder if you've got a bloated pg catalog then. Certainly sounds like it's a possibility. So other than vacuuming when you recreate this, is the server working hard? What is vacuum vacuuming when this happens (pg_stat_activity should show that). >> Running 8.1 means you're asking about a system no one else on this >> list is likely to still be using much. The hackers aren't gonna be >> interested in fixing it either, since it's out of support. > > I am migrating to new version, for new customers , but for old existing > customer, it would require significant time. Is there any workaround for the > same. Like improving some DB parameters. Probably not. If the tables that make up the pg catalogs are bloated, you might have to go take some outage time to run a vacuum full / reindex on them. Some of them, I believe, require single user mode to do this, but not sure, and definitely not sure if it's na 8.1 thing or not. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problem
> vacuum or vacuum full?
vacuum analyze.
> What is the db server doing when this happens? What does top, vmstat,
> iostat etc have to say about it?
It is high end server with 96 GB of RAM , 16 core server, but there are many
other application running, This db is used for monitoring the performance of
this server and inserting/updating data every one in 10-20 tables.
I am able to reproduce this issue, in case i run vacuuming of my database and
in parallel try to open 30 connections, sometime db client takes time to open
connection Is there any workaround or there is some known issue already
existing. If there is any known issue it will be easy to persuade my customers
to upgrade..
Stack trace of client is as follows:-
sendto(3, "p\0\0\0(md5de8bdf202e563b11a4384ba5"..., 41, 0, NULL, 0) = 41
<0.12>rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 <0.05>poll([{fd=3,
events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}]) <35.027745>
Running 8.1 means you're asking about a system no one else on this
> list is likely to still be using much. The hackers aren't gonna be
> interested in fixing it either, since it's out of support.
>
I am migrating to new version, for new customers , but for old existing
customer, it would require significant time. Is there any workaround for the
same. Like improving some DB parameters.
> Date: Mon, 13 May 2013 08:25:30 -0600
> Subject: Re: [GENERAL] Vacuum problem
> From: scott.marl...@gmail.com
> To: msq...@live.com
> CC: pgsql-general@postgresql.org
>
> On Mon, May 13, 2013 at 8:05 AM, S H wrote:
> > Hi,
> >
> > I have my database in which i am executing vacuuming running manually in one
> > hour.
>
> vacuum or vacuum full?
>
> > In my production database sometime when vacuuming is running it is taking
> > long time in opening connection.
>
> What is the db server doing when this happens? What does top, vmstat,
> iostat etc have to say about it?
>
> > My current version is version 8.1. Is there any known issue about open
> > connection problem with vacuuming.
>
> 8.1 is out of support and has been for some time.
>
> > I found something but i am not sure if it is applicable to V8.1 too?
>
> Tom wrote:
> > broken if we start the scan at a random place within the catalog, so
> > that
> > allowing syncscan is actually a big deoptimization if pg_attribute is
> > large
> SNIP
> > Back-patch to 8.3, where syncscan was introduced.
>
> It was added in 8.3 so no, it is likely not your issue.
>
> Running 8.1 means you're asking about a system no one else on this
> list is likely to still be using much. The hackers aren't gonna be
> interested in fixing it either, since it's out of support.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problem
On Mon, May 13, 2013 at 8:05 AM, S H wrote: > Hi, > > I have my database in which i am executing vacuuming running manually in one > hour. vacuum or vacuum full? > In my production database sometime when vacuuming is running it is taking > long time in opening connection. What is the db server doing when this happens? What does top, vmstat, iostat etc have to say about it? > My current version is version 8.1. Is there any known issue about open > connection problem with vacuuming. 8.1 is out of support and has been for some time. > I found something but i am not sure if it is applicable to V8.1 too? Tom wrote: > broken if we start the scan at a random place within the catalog, so > that > allowing syncscan is actually a big deoptimization if pg_attribute is > large SNIP > Back-patch to 8.3, where syncscan was introduced. It was added in 8.3 so no, it is likely not your issue. Running 8.1 means you're asking about a system no one else on this list is likely to still be using much. The hackers aren't gonna be interested in fixing it either, since it's out of support. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problem
Kari Lavikka <[EMAIL PROTECTED]> writes: > However, database wide vacuum prevents user_online vacuum from deleting > old row versions and the table gets incredibly bloated. Update to 8.2. There was a fix put in for this specific issue. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vacuum problem?
On Tue, Dec 07, 2004 at 06:35:39PM -0700, Ed L. wrote: > On Tuesday December 7 2004 6:27, Ed L. wrote: > > On Tuesday December 7 2004 5:58, marcelo Cortez wrote: > La traducción de Babelfish de mi mensaje anterior era hilarante, e indica > que mi español es lejos peor que pensamiento de I. Tends to happen :-) Babelfish translation is usually bad with good spanish, and is really funny with not-so-good spanish. If you want to practice spanish I invite you to join the pgsql-es-ayuda mailing list. And of course, I invite Marcelo to join that list too, because we can probably find out what's really happen if he is able to actually describe his problem. For Marcelo's sake, the URL of that list is http://archives.postgresql.org/pgsql-es-ayuda -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Las mujeres son como hondas: mientras más resistencia tienen, más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] vacuum problem?
On Tuesday December 7 2004 6:27, Ed L. wrote: > On Tuesday December 7 2004 5:58, marcelo Cortez wrote: > > > > /psql someDatabase > > > > vaccum full verbose; > > > > but nothing informs. > > > > in which cases vacuum do not inform anything? > > > > postgresql 7.4 on red hat 9.0 > > > > any clue be appreciate. > > > > best regards > > > > > > Is your server logging to a file? What does it show > > > when you issue your > > > command? > > > > nothing only the prompt :( > > Mi espanol es muy mal, pero yo creo que usted necessita aprender > como encender escribiendo registro para su servidor. Es differente > que símbolo del sistema. Cuando su tiene un registro, puede mirar > a eso para mas pistas. Es possible que esto recurso va a ayudar: > > http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CON >FIG-LOGGING > > Espero eso ayuda usted. La traducción de Babelfish de mi mensaje anterior era hilarante, e indica que mi español es lejos peor que pensamiento de I. Esto es quizá mejor: Le pienso necesidad de aprender cómo forzar su servidor escribir a un fichero de diario. Cuando usted tiene eso, usted puede poder ver algunas pistas en ese archivo. Vea el URL arriba para los directorios de postgresql.conf que usted puede fijar para comenzar a registrar a un archivo. Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] vacuum problem?
On Tuesday December 7 2004 5:58, marcelo Cortez wrote: > > > > > /psql someDatabase > > > vaccum full verbose; > > > but nothing informs. > > > in which cases vacuum do not inform anything? > > > postgresql 7.4 on red hat 9.0 > > > any clue be appreciate. > > > best regards > > > > Is your server logging to a file? What does it show > > when you issue your > > command? > > nothing only the prompt :( Mi espanol es muy mal, pero yo creo que usted necessita aprender como encender escribiendo registro para su servidor. Es differente que símbolo del sistema. Cuando su tiene un registro, puede mirar a eso para mas pistas. Es possible que esto recurso va a ayudar: http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-LOGGING Espero eso ayuda usted. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Vacuum problem on large table
mordicus <[EMAIL PROTECTED]> writes: > vacuum verbose analyse record_blob; > NOTICE: --Relation record_blob-- > NOTICE: Pages 62437: Changed 0, reaped 0, Empty 0, New 0; Tup 1005540: Vac > 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 80, MaxLen 2032; Re-using: > Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 4.57s/0.37u sec. > NOTICE: --Relation pg_toast_44260091-- > NOTICE: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 2: Vac 0, > Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 63, MaxLen 2034; Re-using: > Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. > NOTICE: Index pg_toast_44260091_idx: Pages 2; Tuples 2. CPU 0.00s/0.00u > sec. > NOTICE: Analyzing... > ERROR: Memory exhausted in AllocSetAlloc(875769886) This looks like corrupt data --- specifically, a varlena value with a ridiculous length word --- in your table. Hard to say more with so little information. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
