Re: [PERFORM] Most transactions per second on largest box?

2004-06-04 Thread Greg Stark
[EMAIL PROTECTED] writes: Currently we are running a dual cpu dell blade server on redhat linux (2.4?) and PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96, raid5 and am using sqlrelay for connection pooling. It works fine under ordinary load but bogs down too much under these

[PERFORM] Planner problem

2004-06-04 Thread Mikael Kjellström
Hi! I am having a bit of problem with the plan that the planner produces. First a little info: OS: Linux Debian Version: PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc GCC) 3.3.3 (Debian 20040401) CPU: AMD Athlon XP 2000+ Memory: 1GB Disk: 4 SCSI-3 UW 1rpm in RAID 1 (mirror) mode

Re: [PERFORM] Planner problem

2004-06-04 Thread Tom Lane
=?ISO-8859-1?Q?Mikael_Kjellstr=F6m?= [EMAIL PROTECTED] writes: I am having a bit of problem with the plan that the planner produces. Actually, your problem is with the row-count estimates. Some of them are pretty wildly off, which inevitably leads to bad plan choices. In particular the price

Re: [PERFORM] filesystem option tuning

2004-06-04 Thread CH
Hi! Does that mean only the xlog, or also the clog? As far as I understand, the clog contains some meta-information on the xlog, so presumably it is flushed to disc synchronously together with the xlog? That would mean that they each need a separate disk to prevent one disk having to seek

Re: [PERFORM] filesystem option tuning

2004-06-04 Thread Tom Lane
CH [EMAIL PROTECTED] writes: So the clog is not written to every time the xlog is written to? No. One clog page holds 32000 transactions' worth of transaction status values, so on average we need only one clog page write per 32000 transactions. On a related issue, what's the connection

[PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Michael Nonemacher
I have two instances of a production application that uses Postgres 7.2, deployed in two different data centers for about the last 6 months. The sizes, schemas, configurations, hardware, and access patterns of the two databases are nearly identical, but one consistently takes at least 5x longer

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Merlin Moncure
Michael wrote: I have two instances of a production application that uses Postgres 7.2, deployed in two different data centers for about the last 6 months. The sizes, schemas, configurations, hardware, and access patterns of the two databases are nearly identical, but one consistently takes at

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Tom Lane
Michael Nonemacher [EMAIL PROTECTED] writes: I have two instances of a production application that uses Postgres 7.2, deployed in two different data centers for about the last 6 months. The sizes, schemas, configurations, hardware, and access patterns of the two databases are nearly

Re: [PERFORM] Pl/Pgsql Functions running simultaneously

2004-06-04 Thread Robert Treat
Uh... I don't think this is necessarily the wrong list, sometimes people don't have much to chime in. You could try reposting to -sql or -general I suppose. As for my take on your questions, I wasn't exactly clear on what the problem is. If its just that things seem slow, make sure you have done

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Michael Nonemacher
Slight update: Thanks for the replies; this is starting to make a little more sense... I've managed to track down the root of the problem to a single query on a single table. I have a query that looks like this: select count(*) from members where group_id = ? and member_id 0; The

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Rod Taylor
On Fri, 2004-06-04 at 18:07, Michael Nonemacher wrote: Slight update: Thanks for the replies; this is starting to make a little more sense... I've managed to track down the root of the problem to a single query on a single table. I have a query that looks like this: select

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Michael Nonemacher
Agreed. We originally created the indexes this way because we sometimes do searches where one of the columns is constrained using =, and the other using a range search, but it's not clear to me how much Postgres understands multi-column indexes. Will I get the gain I'd expect from a (member_id,

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Greg Stark
Michael Nonemacher [EMAIL PROTECTED] writes: Agreed. We originally created the indexes this way because we sometimes do searches where one of the columns is constrained using =, and the other using a range search, but it's not clear to me how much Postgres understands multi-column indexes.

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Tom Lane
Michael Nonemacher [EMAIL PROTECTED] writes: It seems like the statistics are wildly different depending on whether the last operation on the table was a 'vacuum analyze' or an 'analyze'. Vacuum or vacuum-analyze puts the correct number (~500k) in pg_class.reltuples, but analyze puts 7000 in