Re: [PERFORM] Moving pg_xlog
My database has two scsi disks my current configuration has pg_xlog on disk1 and data on disk2 the machine is used for database only now did some logging and came to a conclusion that my disk2(data disk) is getting used around 3 times more than disk1(pg_xlog) so wht is recommended... move some of the data to disk1 so that both disks are equally used... by creating tablespaces or let my configuration be whts its currently... iowait is one of the bottlenecks in my application performance. thx Himanshu__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [PERFORM] How to avoid database bloat
> > Our database increases in size 2.5 times during the day. > > What to do to avoid this? Autovacuum running with quite > > aggressive settings, FSM settings are high enough. > > First thing I'd suggest is to get a more detailed idea of exactly > what is bloating --- which tables/indexes are the problem? I think the most problematic table is this one. After vacuum full/reindex it was 20MB in size now (after 6 hours) it is already 70MB and counting. vacuum verbose output below. msg_id is integer, next_retry - timestamp, recipient - varchar(20). max_fsm_pages = 20. Another table has foregn key which referenced msg_id in this one. Thanks, Mindaugas $ vacuumdb -v -z -U postgres -t queue database INFO: vacuuming "queue" INFO: index "queue_msg_id_pk" now contains 110531 row versions in 5304 pages DETAIL: 31454 index row versions were removed. 95 index pages have been deleted, 63 are currently reusable. CPU 0.03s/0.07u sec elapsed 2.50 sec. INFO: index "queue_next_retry" now contains 110743 row versions in 3551 pages DETAIL: 31454 index row versions were removed. 1163 index pages have been deleted, 560 are currently reusable. CPU 0.04s/0.06u sec elapsed 4.93 sec. INFO: index "queue_recipient_idx" now contains 111596 row versions in 1802 pages DETAIL: 31454 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.05u sec elapsed 0.16 sec. INFO: "queue": removed 31454 row versions in 1832 pages DETAIL: CPU 0.00s/0.01u sec elapsed 0.27 sec. INFO: "queue": found 31454 removable, 110096 nonremovable row versions in 9133 pages DETAIL: 119 dead row versions cannot be removed yet. There were 258407 unused item pointers. 0 pages are entirely empty. CPU 0.12s/0.25u sec elapsed 8.20 sec. INFO: analyzing "queue" INFO: "queue": scanned 3000 of 9133 pages, containing 34585 live rows and 1808 dead rows; 3000 rows in sample, 105288 estimated total rows VACUUM ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] SURVEY: who is running postgresql on 8 or more CPUs?
Hi, I just got one reply for this survey. Is almost nobody using postgresql on 8+ machines? Regards, Dirk Dirk Lutzebäck wrote: 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): Architecture (Intel/Sparc/AMD/IBM): Processors (Type/Number/GHz): RAM: Operating System: PostgreSQL Version: Database size (GB): Disk system: Type of application: Your email contact: Willing to answer questions in this group: 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 -- Dirk Lutzebäck <[EMAIL PROTECTED]> Tel +49.30.5362.1635 Fax .1638 CTO AEC/communications GmbH, Berlin, Germany, http://www.aeccom.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Adaptec/LSI/?? RAID
On Wed, 2005-06-01 at 20:42 -0700, Stacy White wrote: > We're in the process of buying another Opteron server to run Postgres, and > based on the suggestions in this list I've asked our IT director to get an > LSI MegaRaid controller rather than one of the Adaptecs. > > But when we tried to place our order, our vendor (Penguin Computing) advised > us: > > "we find LSI does not work well with 4GB of RAM. Our engineering find that > LSI card could cause system crashes. One of our customer ... has found that > Adaptec cards works well on PostGres SQL -- they're using it as a preforce > server with xfs and post-gress." > > Any comments? Suggestions for other RAID controllers? Hi, We're using the Megaraid (Intel branded model) on a dual Opteron system with 8G RAM very happily. The motherboard is a RioWorks one, the OS is Debian "Sarge" AMD64 with kernel 2.6.11.8 and PostgreSQL 7.4.7. Cheers, Andrew. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 - signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Adaptec/LSI/?? RAID
On Wed, 01 Jun 2005 22:00:09 -0700 William Yu <[EMAIL PROTECTED]> wrote: > I've previously stayed away from Adaptec because we used to run Solaris > x86 and the driver was somewhat buggy. For Linux and FreeBSD, I'd be > less worried as open source development of drivers usually lead to > better testing & bug-fixing. Adaptec is in the doghouse in some corners of the community because they have behaved badly about releasing documentation on some of their current RAID controllers to *BSD developers. FreeBSD has a not-quite-free driver for those latest Adaptecs. OpenBSD wants nothing to do with them. richard -- Richard Welty [EMAIL PROTECTED] Averill Park Networking Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security "Well, if you're not going to expect unexpected flames, what's the point of going anywhere?" -- Truckle the Uncivil ---(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
Re: [PERFORM] SURVEY: who is running postgresql on 8 or more CPUs?
On 6/2/05, Dirk Lutzebäck <[EMAIL PROTECTED]> wrote: > I just got one reply for this survey. Is almost nobody using postgresql > on 8+ machines? My guess is when someone is using PostgreSQL on 8+ machine, she's in highly competitive (or sensitive) market and either cannot give company's work details to everyone or simply doesn't want to. Probably if you asked 'I am thinking about buying 8-way Opteron box, does PostgreSQL have problems with such hardware' you would get a response. But surveys are awfully close to statistics and many people simply doesn't like them. (They say that 46.7% of statisticts are just made up ;-)). Regards, Dawid ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] SURVEY: who is running postgresql on 8 or more CPUs?
Hi Dawid, postgresql is open source and we also want it to be used in high performance areas. What's wrong with people telling on which machines they use it? I don't care about business details but techinal details would be quite interesting. In the end it is interesting to know how you need to tune postgresql on high end machines and how well they perform on the different highend platforms. This is meant to be more a field study and not a benchmark. We know that Opteron performs well but what are people actually using in high performance areas? Does postgresql run on an E1? Who did it? Regards, Dirk Dawid Kuroczko wrote: On 6/2/05, Dirk Lutzebäck <[EMAIL PROTECTED]> wrote: I just got one reply for this survey. Is almost nobody using postgresql on 8+ machines? My guess is when someone is using PostgreSQL on 8+ machine, she's in highly competitive (or sensitive) market and either cannot give company's work details to everyone or simply doesn't want to. Probably if you asked 'I am thinking about buying 8-way Opteron box, does PostgreSQL have problems with such hardware' you would get a response. But surveys are awfully close to statistics and many people simply doesn't like them. (They say that 46.7% of statisticts are just made up ;-)). Regards, Dawid -- Dirk Lutzebäck <[EMAIL PROTECTED]> Tel +49.30.5362.1635 Fax .1638 CTO AEC/communications GmbH, Berlin, Germany, http://www.aeccom.com ---(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
Re: [PERFORM] postgresql-8.0.1 performance tuning
Hi, hmmm i don't understand which are the best values for shmmax and shmall. I've googled around but every site says something different. I've 2GB of RAM now and set it to: kernel.shmmax=715827882 kernel.shmall=2097152 Is that value ok for 2GB of RAM? I've set the shared_buffers in my postgresql.conf to 87381 (87381*8*1024 = ~715827882). Can I use www.powerpostgresql.com as reference to set this parameters? Or which site can i use? Best regards, Martin Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson: > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > >>fsync = true > > false > > Just setting fsync=false without considering the implications is a _bad_ > idea... > > /* Steinar */ Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson: > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > >>fsync = true > > false > > Just setting fsync=false without considering the implications is a _bad_ > idea... > > /* Steinar */ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] postgresql-8.0.1 performance tuning
Ups, i'm sorry. i've set the following values: postgresql.conf: shared_buffers = 7 effective_cache_size= 1744762 work_mem= 32768 maintenance_work_mem= 262144 max_fsm_pages = 20 sysctl.conf: vm.swappiness=10 kernel.shmmax=715827882 kernel.shmall=2097152 Are the values ok for a 2 GB machine? I'm testing these settings with contrib/pgbench. With this configuration i become up to 200tps including connection establishing. Is that value ok for this hardware?: 1xP4 3Ghz (hyperthreading enabled) 2GB 266 Mhz RAM CL2.5 pg_xlog is on sda (raid1 with two 10k discs) and the database on sdb(raid10 with four 10k discs). My Linux distribution is Suse Linux 9.3 with postgresql 8.0.1. best regards, Martin Am Donnerstag, den 02.06.2005, 14:50 +0200 schrieb Martin Fandel: > Hi, > > hmmm i don't understand which are the best values for shmmax and shmall. > I've googled around but every site says something different. > > I've 2GB of RAM now and set it to: > > kernel.shmmax=715827882 > kernel.shmall=2097152 > > Is that value ok for 2GB of RAM? > > I've set the shared_buffers in my postgresql.conf to 87381 > (87381*8*1024 = ~715827882). > > Can I use www.powerpostgresql.com as reference to set this > parameters? Or which site can i use? > > Best regards, > Martin > > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson: > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > > >>fsync = true > > > false > > > > Just setting fsync=false without considering the implications is a > _bad_ > > idea... > > > > /* Steinar */ > > > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson: > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > > >>fsync = true > > > false > > > > Just setting fsync=false without considering the implications is a _bad_ > > idea... > > > > /* Steinar */ > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] postgresql-8.0.1 performance tuning
I've forgotten the settings for the pgbench-tests. I use 150 clients with 5 transactions each. Am Donnerstag, den 02.06.2005, 15:10 +0200 schrieb Martin Fandel: > Ups, > i'm sorry. i've set the following values: > > postgresql.conf: > shared_buffers = 7 > effective_cache_size= 1744762 > work_mem= 32768 > maintenance_work_mem= 262144 > max_fsm_pages = 20 > > sysctl.conf: > vm.swappiness=10 > kernel.shmmax=715827882 > kernel.shmall=2097152 > > Are the values ok for a 2 GB machine? I'm testing these settings > with contrib/pgbench. With this configuration i become up to 200tps > including connection establishing. Is that value ok for this hardware?: > > 1xP4 3Ghz (hyperthreading enabled) > 2GB 266 Mhz RAM CL2.5 > > pg_xlog is on sda (raid1 with two 10k discs) and the database on > sdb(raid10 with four 10k discs). > > My Linux distribution is Suse Linux 9.3 with postgresql 8.0.1. > > best regards, > Martin > > Am Donnerstag, den 02.06.2005, 14:50 +0200 schrieb Martin Fandel: > > Hi, > > > > hmmm i don't understand which are the best values for shmmax and shmall. > > I've googled around but every site says something different. > > > > I've 2GB of RAM now and set it to: > > > > kernel.shmmax=715827882 > > kernel.shmall=2097152 > > > > Is that value ok for 2GB of RAM? > > > > I've set the shared_buffers in my postgresql.conf to 87381 > > (87381*8*1024 = ~715827882). > > > > Can I use www.powerpostgresql.com as reference to set this > > parameters? Or which site can i use? > > > > Best regards, > > Martin > > > > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson: > > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > > > >>fsync = true > > > > false > > > > > > Just setting fsync=false without considering the implications is a > > _bad_ > > > idea... > > > > > > /* Steinar */ > > > > > > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson: > > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > > > >>fsync = true > > > > false > > > > > > Just setting fsync=false without considering the implications is a _bad_ > > > idea... > > > > > > /* Steinar */ > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How to avoid database bloat
"Mindaugas Riauba" <[EMAIL PROTECTED]> writes: >> First thing I'd suggest is to get a more detailed idea of exactly >> what is bloating --- which tables/indexes are the problem? > I think the most problematic table is this one. After vacuum full/reindex > it was 20MB in size now (after 6 hours) it is already 70MB and counting. AFAICT the vacuum is doing what it is supposed to, and the problem has to be just that it's not being done often enough. Which suggests either an autovacuum bug or your autovacuum settings aren't aggressive enough. Which PG version is this exactly? Some of the earlier autovacuum releases do have known bugs, so it'd be worth your while to update if you're not on the latest point release of your series. I don't know much about autovacuum settings, but if you'll show what you're using someone can probably comment on them. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] How to avoid database bloat
> >> First thing I'd suggest is to get a more detailed idea of exactly > >> what is bloating --- which tables/indexes are the problem? > > > I think the most problematic table is this one. After vacuum full/reindex > > it was 20MB in size now (after 6 hours) it is already 70MB and counting. > > AFAICT the vacuum is doing what it is supposed to, and the problem has > to be just that it's not being done often enough. Which suggests either > an autovacuum bug or your autovacuum settings aren't aggressive enough. -D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10 That is autovacuum settings. Should be aggressive enough I think? > Which PG version is this exactly? Some of the earlier autovacuum > releases do have known bugs, so it'd be worth your while to update > if you're not on the latest point release of your series. 8.0.3 > I don't know much about autovacuum settings, but if you'll show what > you're using someone can probably comment on them. And what in vacuum verbose output suggests that vacuum is not done often enough? Current output (table is 100MB already) is below. Thanks, Mindaugas $ vacuumdb -v -z -U postgres -t queue database INFO: vacuuming "queue" INFO: index "queue_msg_id_pk" now contains 302993 row versions in 18129 pages DETAIL: 102763 index row versions were removed. 1 index pages have been deleted, 1 are currently reusable. CPU 0.87s/0.46u sec elapsed 76.40 sec. INFO: index "queue_next_retry" now contains 310080 row versions in 9092 pages DETAIL: 102763 index row versions were removed. 675 index pages have been deleted, 658 are currently reusable. CPU 0.38s/0.31u sec elapsed 79.47 sec. INFO: index "queue_recipient_idx" now contains 323740 row versions in 2900 pages DETAIL: 102763 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.27u sec elapsed 9.06 sec. INFO: "queue": removed 102763 row versions in 9623 pages DETAIL: CPU 0.16s/0.39u sec elapsed 29.26 sec. INFO: "queue": found 102763 removable, 292342 nonremovable row versions in 12452 pages DETAIL: 14 dead row versions cannot be removed yet. There were 183945 unused item pointers. 0 pages are entirely empty. CPU 1.56s/1.51u sec elapsed 194.39 sec. INFO: analyzing "queue" INFO: "queue": scanned 3000 of 12452 pages, containing 72850 live rows and 7537 dead rows; 3000 rows in sample, 302376 estimated total rows VACUUM ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Moving pg_xlog
Himanshu Baweja <[EMAIL PROTECTED]> writes: > My database has two scsi disks > my current configuration has pg_xlog on disk1 and data on disk2 > the machine is used for database only > now did some logging and came to a conclusion that my disk2(data disk) is > getting used around 3 times more than disk1(pg_xlog) > so wht is recommended... move some of the data to disk1 so that both disks > are equally used... by creating tablespaces or let my configuration be whts > its currently... iowait is one of the bottlenecks in my application > performance. It seems highly unlikely that putting more stuff on the xlog disk will improve performance --- at least not if your bottleneck is update speed. If it's a read-mostly workload then optimizing xlog writes may not be the most important thing to you. In that case you might want to ignore xlog and try something along the lines of tables on one disk, indexes on the other. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Moving pg_xlog
Tom Lane <[EMAIL PROTECTED]> wrote: >It seems highly unlikely that putting more stuff on the xlog disk will>improve performance --- at least not if your bottleneck is update speed. Tom you are right.. i did some testing... 1) default config--- xlog on disk1 and data on disk2=> 27 mins and 22 secs 2) xlog and some tables on disk1 and rest of tables on disk2=> 28 mins and 38 secs but the most startling of the results is 3) xlog on disk1 and half the tables on partition 1 of disk2 and other half on partition 2 of disk2 24 mins and 14 secs ?? shouldnt moving data to diff partitions degrade performance instead of enhancing it also in configuration 1, my heap_blks_hit/heap_blks_fetched was good enough but in configuration 3, its was really low.. something of the order of 1/15... still the performance improved any ideas. does moving across partitions help... Regards Himanshu __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [PERFORM] Specific query performance problem help requested - postgresql 7.4
How is it that the index scan has such poor performance? Shouldn't index
lookups be quicker?
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 26, 2005 1:32 PM
To: Brad Might
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Specific query performance problem help requested
- postgresql 7.4
"Brad Might" <[EMAIL PROTECTED]> writes:
> Can someone help me break this down and figure out why the one query
> takes so much longer than the other?
It looks to me like there's a correlation between filename and bucket,
such that the indexscan in filename order takes much longer to run
across the first 25 rows with bucket = 3 than it does to run across the
first 25 with bucket = 7 or bucket = 8. It's not just a matter of there
being fewer rows with bucket = 3 ... the cost differential is much
larger than is explained by the count ratios. The bucket = 3 rows have
to be lurking further to the back of the filename order than the others.
> Here's the bucket distribution..i have clustered the index on the
> bucket value.
If you have an index on bucket, it's not doing you any good here anyway,
since you wrote the constraint as a crosstype operator ("3" is int4 not
int8). It might help to explicitly cast the constant to int8.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[PERFORM] Query plan for very large number of joins
Hi, I am using PostgreSQL (7.4) with a schema that was generated automatically (using hibernate). The schema consists of about 650 relations. One particular query (also generated automatically) consists of left joining approximately 350 tables. At this stage, most tables are empty and those with values have less than 50 entries. The query takes about 90 seconds to execute (on a P4, 2.6Ghz). All of the relations have a primary key which is indexed and all of the joins are on foreign keys which are explicitly declared. I've checked the obvious tunables (effective_cache_size, shared_memory and sort_buffer) but changing these has had no effect. The system has a total of 750MB RAM, I've varied the shared memory up to 256MB and the sort buffer up to 128MB without affecting the performance. Running the query as a JDBC prepared statement indicates that the query optimiser is spending a negligable amount of time on the task (~ 36 ms) compared to the executor (~ 90 seconds). The output of EXPLAIN indicates (AFAICT) that all of the joins are of type "Nested Loop Left Join" and all of the scans are of type "Seq Scan". I have refrained from posting the query and the query plan since these are 80K and 100K apiece but if anyone wants to see them I can certainly forward them on. My (uninformed) suspicion is that the optimiser has failed over to the default plan on the basis of the number of tables in the join. My question is, is there anyone out there using PostgreSQL with this size of schema? Is there anything that can be done to bring about the order of magnitude increase in speed that I need? Thanks for your help, -phil I'm using Vodafone Mail - to get your free mobile email account go to http://www.vodafone.ie Use of Vodafone Mail is subject to Terms and Conditions http://www.vodafone.ie/terms/website ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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
Re: [PERFORM] How to avoid database bloat
Mindaugas Riauba wrote: AFAICT the vacuum is doing what it is supposed to, and the problem has to be just that it's not being done often enough. Which suggests either an autovacuum bug or your autovacuum settings aren't aggressive enough. -D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10 That is autovacuum settings. Should be aggressive enough I think? Might e aggressive enough, but might not. I have seen some people run -V 0.1. Also you probably don't need -A that low. This could an issue where analyze results in an inaccurate reltuples value which is preventing autovacuum from doing it's job. Could you please run it with -d 2 and show us the relevant log output. Which PG version is this exactly? Some of the earlier autovacuum releases do have known bugs, so it'd be worth your while to update if you're not on the latest point release of your series. 8.0.3 That should be fine. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Query plan for very large number of joins
[EMAIL PROTECTED] wrote: Hi, I am using PostgreSQL (7.4) with a schema that was generated automatically (using hibernate). The schema consists of about 650 relations. One particular query (also generated automatically) consists of left joining approximately 350 tables. May I be the first to offer an "ouch"! At this stage, most tables are empty and those with values have less than 50 entries. The query takes about 90 seconds to execute (on a P4, 2.6Ghz). All of the relations have a primary key which is indexed and all of the joins are on foreign keys which are explicitly declared. I've checked the obvious tunables (effective_cache_size, shared_memory and sort_buffer) but changing these has had no effect. The system has a total of 750MB RAM, I've varied the shared memory up to 256MB and the sort buffer up to 128MB without affecting the performance. The sort-mem is the only thing I can see helping with a single query. Running the query as a JDBC prepared statement indicates that the query optimiser is spending a negligable amount of time on the task (~ 36 ms) compared to the executor (~ 90 seconds). The output of EXPLAIN indicates (AFAICT) that all of the joins are of type "Nested Loop Left Join" and all of the scans are of type "Seq Scan". I have refrained from posting the query and the query plan since these are 80K and 100K apiece but if anyone wants to see them I can certainly forward them on. Well, if most tables are small then a seq-scan makes sense. Does it look like it's estimating the number of rows badly anywhere? I'm not sure the list will accept attachments that large - is it possible to upload them somewhere accessible? My (uninformed) suspicion is that the optimiser has failed over to the default plan on the basis of the number of tables in the join. My question is, is there anyone out there using PostgreSQL with this size of schema? Is there anything that can be done to bring about the order of magnitude increase in speed that I need? Well - the genetic planner must surely be kicking in here (see the run-time configuration chapter of the manuals, query-planning, geqo_threshold). However, I'm not sure how much leeway there is in planning a largely left-joined query. It could be there's some overhead in the executor that's only noticable with hundreds of tables involved, you're running at about 0.25 secs per join. I take it you have no control over the schema or query, so there's not much fiddling you can do. You've tried sort_mem, so there are only two things I can think of: 1. Try the various enable_xxx config settings and see if disabling seq-scan or the relevant join-type does anything (I'm not sure it will) 2. Try against 8.0 - there may be some improvement there. Other people on this list have experience on larger systems than me, so they may be able to help more. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] How to avoid database bloat
"Mindaugas Riauba" <[EMAIL PROTECTED]> writes: > And what in vacuum verbose output suggests that vacuum is not done > often enough? Current output (table is 100MB already) is below. The output shows vacuum cleaning up about a third of the table. Usually people like to keep the overhead down to 10% or so ... 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
Re: [PERFORM] Query plan for very large number of joins
Richard Huxton writes: > [EMAIL PROTECTED] wrote: >> I am using PostgreSQL (7.4) with a schema that was generated >> automatically (using hibernate). The schema consists of about 650 >> relations. One particular query (also generated automatically) >> consists of left joining approximately 350 tables. > May I be the first to offer an "ouch"! Seconded. > However, I'm not sure how much leeway there is in > planning a largely left-joined query. Not much. The best hope for a better result is to order the LEFT JOIN clauses in a way that will produce a good plan. One thought is that I am not sure I believe the conclusion that planning is taking only 36 ms; even realizing that the exclusive use of left joins eliminates options for join order, there are still quite a lot of plans to consider. You should try both EXPLAIN and EXPLAIN ANALYZE from psql and see how long each takes. It'd also be interesting to keep an eye on how large the backend process grows while doing this --- maybe it's being driven into swap. Also: I'm not sure there *is* such a thing as a good plan for a 350-way join. It may be time to reconsider your data representation. If Hibernate really forces this on you, it may be time to reconsider your choice of tool. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Adaptec/LSI/?? RAID
I've got a bunch of mission-critical Postgres servers on Opterons, all with no less than 4GB RAM, running Linux + XFS, and most with LSI MegaRAID cards. We've never had a single system crash or failure on our postgres servers, and some of them are well-used and with uptimes in excess of a year. It may be anecdotal, but LSI MegaRAID cards generally seem to work pretty well with Linux. The only problem I've ever seen was a BIOS problem between the LSI and the motherboard, which was solved by flashing the BIOS on the motherboard with the latest version (it was grossly out of date anyway). J. Andrew Rogers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Adaptec/LSI/?? RAID
It may be anecdotal, but LSI MegaRAID cards generally seem to work pretty well with Linux. The only problem I've ever seen was a BIOS problem between the LSI and the motherboard, which was solved by flashing the BIOS on the motherboard with the latest version (it was grossly out of date anyway). At Command Prompt we have also had some great success with the LSI cards. The only thing we didn't like is the obscure way you have to configure RAID 10. Sincerely, Joshua D. Drake J. Andrew Rogers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(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
Re: [PERFORM] Adaptec/LSI/?? RAID
On Jun 1, 2005, at 11:42 PM, Stacy White wrote: "we find LSI does not work well with 4GB of RAM. Our engineering find that LSI card could cause system crashes. One of our customer ... has found that Adaptec cards works well on PostGres SQL -- they're using it as a preforce server with xfs and post-gress." Any comments? Suggestions for other RAID controllers? I have twin dual opteron, 4GB RAM, LSI MegaRAID-2X cards with 8 disks ([EMAIL PROTECTED] system+pg_xlog, [EMAIL PROTECTED] data) running FreeBSD 5.4-RELEASE. Works just perfectly fine under some very heavy insert/update/delete load. Database + indexes hovers at about 50Gb. I don't use the adaptec controllers because they don't support FreeBSD well (and vice versa) and the management tools are not there for FreeBSD in a supported fashion like they are for LSI. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] Query plan for very large number of joins
I am using PostgreSQL (7.4) with a schema that was generated automatically (using hibernate). The schema consists of about 650 relations. One particular query (also generated automatically) consists of left joining approximately 350 tables. At this Just out of curiosity, what application is this ? And what are the reasons for so many tables ...and especially such a query ? Not criticizing, but curious. ---(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
Re: [PERFORM] Adaptec/LSI/?? RAID (what about JBOD?)
I have a similar question about what to choose (either LSI or Adaptec U320), but plan to use them just for JBOD drivers. I expect to be using either net or freebsd. The system CPU will be Opteron. My impression is that both the ahd and mpt drivers (for U320 Adaptec and LSI, respectively) are quite stable, but not from personal experience. Like I said, I don't plan to have the cards doing RAID in hardware. Should I be pretty safe with either choice of HBA then? Thanks (and sorry for the semi-hijack). Quoting Vivek Khera <[EMAIL PROTECTED]>: > > On Jun 1, 2005, at 11:42 PM, Stacy White wrote: > > > "we find LSI does not work well with 4GB of RAM. Our engineering > > find that > > LSI card could cause system crashes. One of our customer ... has > > found that > > Adaptec cards works well on PostGres SQL -- they're using it as a > > preforce > > server with xfs and post-gress." > > > > Any comments? Suggestions for other RAID controllers? > > > > I have twin dual opteron, 4GB RAM, LSI MegaRAID-2X cards with 8 disks > ([EMAIL PROTECTED] system+pg_xlog, [EMAIL PROTECTED] data) running FreeBSD > 5.4-RELEASE. > > Works just perfectly fine under some very heavy insert/update/delete > load. Database + indexes hovers at about 50Gb. > > I don't use the adaptec controllers because they don't support > FreeBSD well (and vice versa) and the management tools are not there > for FreeBSD in a supported fashion like they are for LSI. > > > Vivek Khera, Ph.D. > +1-301-869-4449 x806 > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Query plan for very large number of joins
Tom Lane schrieb: Richard Huxton writes: [EMAIL PROTECTED] wrote: I am using PostgreSQL (7.4) with a schema that was generated automatically (using hibernate). The schema consists of about 650 relations. One particular query (also generated automatically) consists of left joining approximately 350 tables. May I be the first to offer an "ouch"! Seconded. However, I'm not sure how much leeway there is in planning a largely left-joined query. Not much. The best hope for a better result is to order the LEFT JOIN clauses in a way that will produce a good plan. If this is the best way, you should consider to use an sql query and not the hibernate ql language in this case. This is possible with Hibernate! I suppose you could also consider a view in Postgre and let Hibernate read from this view. This is also possible. One thought is that I am not sure I believe the conclusion that planning is taking only 36 ms; even realizing that the exclusive use of left joins eliminates options for join order, there are still quite a lot of plans to consider. You should try both EXPLAIN and EXPLAIN ANALYZE from psql and see how long each takes. It'd also be interesting to keep an eye on how large the backend process grows while doing this --- maybe it's being driven into swap. Also: I'm not sure there *is* such a thing as a good plan for a 350-way join. It may be time to reconsider your data representation. If Hibernate really forces this on you, it may be time to reconsider your choice of tool. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Kind Regards / Viele Grüße Sebastian Hennebrueder - http://www.laliluna.de/tutorials.html Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more. ---(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
Re: [PERFORM] Adaptec/LSI/?? RAID (what about JBOD?)
On Thu, 2005-06-02 at 14:02 -0700, [EMAIL PROTECTED] wrote: > I have a similar question about what to choose (either LSI or Adaptec U320), > but > plan to use them just for JBOD drivers. I expect to be using either net or > freebsd. The system CPU will be Opteron. My impression is that both the ahd > and mpt drivers (for U320 Adaptec and LSI, respectively) are quite stable, but > not from personal experience. Like I said, I don't plan to have the cards > doing > RAID in hardware. Should I be pretty safe with either choice of HBA then? On the machine I mentioned earlier in this thread we use the Megaraid for JBOD, but the card setup to use the disks that way was somewhat confusing, requiring us to configure logical drives that in fact matched the physical ones. The card still wanted to write that information onto the disks, reducing the total disk space available by some amount, but also meaning that we were unable to migrate our system from a previous non-RAID card cleanly. Regards, Andrew. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Whereof one cannot speak, thereon one must remain silent. -- Wittgenstein - signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Adaptec/LSI/?? RAID (what about JBOD?)
Thanks, Andrew. I expect to choose between HBAs with no RAID functionality or with the option to completely bypass RAID functionality--meaning that I'll hopefully avoid the situation that you've described. I'm mostly curious as to whether the driver problems described for U320 Adaptec RAID controllers also apply to the regular SCSI drivers. Thanks. Quoting Andrew McMillan <[EMAIL PROTECTED]>: > On Thu, 2005-06-02 at 14:02 -0700, [EMAIL PROTECTED] wrote: > > I have a similar question about what to choose (either LSI or Adaptec > U320), but > > plan to use them just for JBOD drivers. I expect to be using either net > or > > freebsd. The system CPU will be Opteron. My impression is that both the > ahd > > and mpt drivers (for U320 Adaptec and LSI, respectively) are quite stable, > but > > not from personal experience. Like I said, I don't plan to have the cards > doing > > RAID in hardware. Should I be pretty safe with either choice of HBA > then? > > On the machine I mentioned earlier in this thread we use the Megaraid > for JBOD, but the card setup to use the disks that way was somewhat > confusing, requiring us to configure logical drives that in fact matched > the physical ones. The card still wanted to write that information onto > the disks, reducing the total disk space available by some amount, but > also meaning that we were unable to migrate our system from a previous > non-RAID card cleanly. > > Regards, > Andrew. > > - > Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St > DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 > Whereof one cannot speak, thereon one must remain silent. -- Wittgenstein > - > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance tuning)
On 6/1/05, Mark Kirkwood <[EMAIL PROTECTED]> wrote: > Cosimo Streppone wrote: > > # Config > >> /etc/sysctl.conf: > >> kernel.shmall = 786432000 > >> kernel.shmmax = 786432000 > > > > I think you have a problem here. > > kernel.shmmax should *not* be set to an amount of RAM, but > > to maximum number of shared memory pages, which on a typical linux system > > is 4kb. Google around: > > > This is somewhat confusing : > > kernel.shmmax is in bytes (max single segment size) > kernel.shmall is in (4k) pages (max system wide allocated segment pages) Can someone resummarize the situation with these linux parameters for the dummies? I thought I had my calculations all sorted out but now I've confused myself again. The documentation at http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html puts the same figure into both values but the posts here seem to suggest that is wrong? Or is it different on a 2.4 kernel and the documentation needs updating? In my specific case I have about 800meg of memory on a linux 2.4 kernel box. Based on the powerpostgresql.com Performance Checklist [1] and Annotated Postgresql.conf [2] I understand that: -I should have less than 1/3 of my total memory as shared_buffers -For my server 15000 is a fairly reasonable starting point for shared_buffers which is ~120MB -I have 100 max_connections. So I was going to set SHMMAX to 134217728 (ie 128 Meg) What should SHMALL be? The current system values are [EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmmax 33554432 [EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmall 2097152 ie SHMALL seems to be 1/16 of SHMMAX Paul [1] http://www.powerpostgresql.com/PerfList/ [2] http://www.powerpostgresql.com/Downloads/annotated_conf_80.html ---(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
Re: SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance
Paul McGarry wrote: Based on the powerpostgresql.com Performance Checklist [1] and Annotated Postgresql.conf [2] I understand that: -I should have less than 1/3 of my total memory as shared_buffers -For my server 15000 is a fairly reasonable starting point for shared_buffers which is ~120MB -I have 100 max_connections. So I was going to set SHMMAX to 134217728 (ie 128 Meg) What should SHMALL be? The current system values are [EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmmax 33554432 [EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmall 2097152 ie SHMALL seems to be 1/16 of SHMMAX No - shmall is in 4k pages _ so this amounts to 8G! This is fine - unless you wish to decrease it in order to prevent too many shared memory applications running. BTW - the docs have been amended for 8.1 to suggest shmmax=134217728 and shmall=2097152 (was going to point you at them - but I cannot find them on the Postgresql site anymore...). There seems to be some longstanding confusion in the Linux community about the units for shmall (some incorrect documentation from Oracle on the issue does not help I am sure) - to the point where I downloaded kernel source to check (reproducing here): linux-2.6.11.1/include/linux/shm.h:13-> #define SHMMAX 0x200 /* max shared seg size (bytes) */ #define SHMMIN 1 /* min shared seg size (bytes) */ #define SHMMNI 4096 /* max num of segs system wide */ #define SHMALL (SHMMAX/PAGE_SIZE*(SHMMNI/16)) /* max shm system wide (pages) */ #define SHMSEG SHMMNI Hope that helps Best wishes Mark ---(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
[PERFORM] Query limitations (size, number of UNIONs ...)
Hello, I've split my data in daily tables to keep them in an acceptable size. Now I have quite complex queries which may be very long if I need to query a large number of daily tables. I've just made a first test wich resulted in a query being 15KB big annd containing 63 UNION. The Query plan in PGAdmin is about 100KB big with 800 lines :-) The performance is not such bad, but I'm wondering if there are some POSTGRES limitations I should take care of with this strategy. Thanks, Marc -- Geschenkt: 3 Monate GMX ProMail gratis + 3 Ausgaben stern gratis ++ Jetzt anmelden & testen ++ http://www.gmx.net/de/go/promail ++ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
