Re: [PERFORM] SURVEY: who is running postgresql on 8 or more CPUs?
On Tue, 31 May 2005, Dirk Lutzebäck wrote: > Date: Tue, 31 May 2005 15:16:37 +0200 > From: Dirk Lutzebäck <[EMAIL PROTECTED]> > To: pgsql-performance@postgresql.org > Subject: [PERFORM] SURVEY: who is running postgresql on 8 or more CPUs? > > Hi, > > I would like to start a little survey who is running postgresql on an > 8way or more machine (Intel, Sparc, AMD no matter). Purpose: find out > how postgresql runs in high performance areas. > > Please fillout: > > Machine (Vendor, Product): TX200 Fujitsu siemens > Architecture (Intel/Sparc/AMD/IBM): Intel > Processors (Type/Number/GHz): bi-Xeon 2.8G > RAM: 3g > Operating System: Unixware 714 > PostgreSQL Version: 8.0.3 > Database size (GB): 6G > Disk system: 6xU320 36G SCSI (software raid) > Type of application: from accounting to game > Your email contact: ohp@pyrenet.fr > Willing to answer questions in this group: yes > Comments: > > > Please answer here or to me. I compile the results and feed them back here. > > Regards, > > Dirk > > > ---(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 > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(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
[PERFORM] index as large as table
Hi, While testing 8.1dev I came to this: CREATE TABLE t ( a int, b int PRIMARY KEY (a,b)); In that case, the index is as big as the table. My question is is it worthwhile to have such index peformance wise. I understand I'd loose uniqness buthas such an index any chance to be used against seq scan. Is there any chance we have a "btree table" in the future for that case? Regards, -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 1: 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
Re: [PERFORM] URGENT: Out of disk space pg_xlog
On Fri, 22 Dec 2006, Tom Lane wrote: > Date: Fri, 22 Dec 2006 13:14:18 -0500 > From: Tom Lane <[EMAIL PROTECTED]> > To: Kevin Grittner <[EMAIL PROTECTED]> > Cc: Jeremy Haile <[EMAIL PROTECTED]>, pgsql-performance@postgresql.org > Subject: Re: [PERFORM] URGENT: Out of disk space pg_xlog > > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > As I understand it, the log space accumulates for the oldest transaction > > which is still running, and all transactions which started after it. > > No, pg_xlog can be truncated as soon as a checkpoint occurs. Even for currently running transactions ? My understanding was that checkpoint was syncing data files for commited transactions. What happens to pg_xlogs when a transaction updates M of rows/tables and runs for hours? [snip] > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] URGENT: Out of disk space pg_xlog
Thanks for your reply. I learnt something again! On Fri, 22 Dec 2006, Alvaro Herrera wrote: > Date: Fri, 22 Dec 2006 17:25:43 -0300 > From: Alvaro Herrera <[EMAIL PROTECTED]> > To: ohp@pyrenet.fr > Cc: Tom Lane <[EMAIL PROTECTED]>, > Kevin Grittner <[EMAIL PROTECTED]>, > Jeremy Haile <[EMAIL PROTECTED]>, pgsql-performance@postgresql.org > Subject: Re: [PERFORM] URGENT: Out of disk space pg_xlog > > ohp@pyrenet.fr wrote: > > On Fri, 22 Dec 2006, Tom Lane wrote: > > > > > Date: Fri, 22 Dec 2006 13:14:18 -0500 > > > From: Tom Lane <[EMAIL PROTECTED]> > > > To: Kevin Grittner <[EMAIL PROTECTED]> > > > Cc: Jeremy Haile <[EMAIL PROTECTED]>, pgsql-performance@postgresql.org > > > Subject: Re: [PERFORM] URGENT: Out of disk space pg_xlog > > > > > > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > > > As I understand it, the log space accumulates for the oldest transaction > > > > which is still running, and all transactions which started after it. > > > > > > No, pg_xlog can be truncated as soon as a checkpoint occurs. > > > > Even for currently running transactions ? > > > > My understanding was that checkpoint was syncing data files for commited > > transactions. > > No, it syncs data files for all transactions, even those currently > running. > > > What happens to pg_xlogs when a transaction updates M of rows/tables and > > runs for hours? > > They get recycled as the update goes. > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Wierd context-switching issue on Xeon
Hi Tom, You still have an account on my Unixware Bi-Xeon hyperthreded machine. Feel free to use it for your tests. On Mon, 19 Apr 2004, Tom Lane wrote: > Date: Mon, 19 Apr 2004 20:53:09 -0400 > From: Tom Lane <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Cc: Joe Conway <[EMAIL PROTECTED]>, scott.marlowe <[EMAIL PROTECTED]>, > Bruce Momjian <[EMAIL PROTECTED]>, [EMAIL PROTECTED], > [EMAIL PROTECTED], Neil Conway <[EMAIL PROTECTED]> > Subject: Re: [PERFORM] Wierd context-switching issue on Xeon > > I wrote: > > Here is a test case. > > Hmmm ... I've been able to reproduce the CS storm on a dual Athlon, > which seems to pretty much let the Xeon per se off the hook. Anybody > got a multiple Opteron to try? Totally non-Intel CPUs? > > It would be interesting to see results with non-Linux kernels, too. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Wierd context-switching issue on Xeon
How long is this test supposed to run? I've launched just 1 for testing, the plan seems horrible; the test is cpu bound and hasn't finished yet after 17:02 min of CPU time, dual XEON 2.6G Unixware 713 The machine is a Fujitsu-Siemens TX 200 server On Mon, 19 Apr 2004, Tom Lane wrote: > Date: Mon, 19 Apr 2004 20:01:56 -0400 > From: Tom Lane <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Cc: Joe Conway <[EMAIL PROTECTED]>, scott.marlowe <[EMAIL PROTECTED]>, > Bruce Momjian <[EMAIL PROTECTED]>, [EMAIL PROTECTED], > [EMAIL PROTECTED], Neil Conway <[EMAIL PROTECTED]> > Subject: Re: [PERFORM] Wierd context-switching issue on Xeon > > Here is a test case. To set up, run the "test_setup.sql" script once; > then launch two copies of the "test_run.sql" script. (For those of > you with more than two CPUs, see whether you need one per CPU to make > trouble, or whether two test_runs are enough.) Check that you get a > nestloops-with-index-scans plan shown by the EXPLAIN in test_run. > > In isolation, test_run.sql should do essentially no syscalls at all once > it's past the initial ramp-up. On a machine that's functioning per > expectations, multiple copies of test_run show a relatively low rate of > semop() calls --- a few per second, at most --- and maybe a delaying > select() here and there. > > What I actually see on Josh's client's machine is a context swap storm: > "vmstat 1" shows CS rates around 170K/sec. strace'ing the backends > shows a corresponding rate of semop() syscalls, with a few delaying > select()s sprinkled in. top(1) shows system CPU percent of 25-30 > and idle CPU percent of 16-20. > > I haven't bothered to check how long the test_run query takes, but if it > ends while you're still examining the behavior, just start it again. > > Note the test case assumes you've got shared_buffers set to at least > 1000; with smaller values, you may get some I/O syscalls, which will > probably skew the results. > > regards, tom lane > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Wierd context-switching issue on Xeon
Hi I'd LOVE to contribute on this but I don't have vmstat and I'm not running linux. How can I help? Regards On Wed, 28 Apr 2004, Robert Creager wrote: > Date: Wed, 28 Apr 2004 18:57:53 -0600 > From: Robert Creager <[EMAIL PROTECTED]> > To: Josh Berkus <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED], Dirk_Lutzebäck <[EMAIL PROTECTED]>, [EMAIL PROTECTED], > Tom Lane <[EMAIL PROTECTED]>, Joe Conway <[EMAIL PROTECTED]>, > scott.marlowe <[EMAIL PROTECTED]>, > Bruce Momjian <[EMAIL PROTECTED]>, [EMAIL PROTECTED], > Neil Conway <[EMAIL PROTECTED]> > Subject: Re: [PERFORM] Wierd context-switching issue on Xeon > > When grilled further on (Wed, 21 Apr 2004 10:29:43 -0700), > Josh Berkus <[EMAIL PROTECTED]> confessed: > > > Dave, > > > > > After some testing if you use the current head code for s_lock.c which > > > has some mods in it to alleviate this situation, and change > > > SPINS_PER_DELAY to 10 you can drastically reduce the cs with tom's test. > > > I am seeing a slight degradation in throughput using pgbench -c 10 -t > > > 1000 but it might be liveable, considering the alternative is unbearable > > > in some situations. > > > > > > Can anyone else replicate my results? > > > > Can you produce a patch against 7.4.1? I'd like to test your fix against a > > real-world database. > > I would like to see the same, as I have a system that exhibits the same behavior > on a production db that's running 7.4.1. > > Cheers, > Rob > > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Slow in morning hours
Have you tried VACUUM ANALYZE at least one a day? Regards On Fri, 20 Feb 2004 [EMAIL PROTECTED] wrote: > Date: Fri, 20 Feb 2004 14:46:15 +0530 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: [PERFORM] Slow in morning hours > > Hi All, > > I am using Linux 7.2 and postgresql 7.2. > > Our Office hours are over at 6pm but we use to keep our server > running 24 hours a day. On the second day morning, Our PGSQL > Server becomes very slow. > > After continuous usage of one hour, It gradually starts responding > faster ! This has become every day routine ! > > do u have any idea related to this Is there any other reason that I > need to check up? > > Please any any idea to get relief daily morning problem !! > > Thanxs, > Vishal > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] 7 hrs for a pg_restore?
On Wed, 20 Feb 2008, Pavan Deolasee wrote: > Date: Wed, 20 Feb 2008 14:31:09 +0530 > From: Pavan Deolasee <[EMAIL PROTECTED]> > To: Jeff Davis <[EMAIL PROTECTED]> > Cc: Douglas J Hunley <[EMAIL PROTECTED]>, > pgsql-performance@postgresql.org > Subject: Re: [PERFORM] 7 hrs for a pg_restore? > > On Feb 19, 2008 11:53 PM, Jeff Davis <[EMAIL PROTECTED]> wrote: > > > > > > Keep in mind, if you have several GB worth of indexes, they take up > > basically no space in the logical dump (just the "CREATE INDEX" command, > > and that's it). But they can take a lot of processor time to build up > > again, especially with localized text. > > > > > > I think it would be interesting if we can build these indexes in parallel. > Each index build requires a seq scan on the table. If the table does > not fit in shared buffers, each index build would most likely result > in lots of IO. > > One option would be to add this facility to the backend so that multiple > indexes can be built with a single seq scan of the table. In theory, it > should be possible, but might be tricky given the way index build works > (it calls respective ambuild method to build the index which internally > does the seq scan). > > Other option is to make pg_restore multi-threaded/processed. The > synchronized_scans facility would then synchronize the multiple heap > scans. ISTM that if we can make pg_restore mult-processed, then > we can possibly add more parallelism to the restore process. > > My two cents. > > Thanks, > Pavan > > That'd be great! Maybe an option to pg_restore to spawn AT MOST n processes (1 per CPU) my .02 Euro -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
