[PERFORM] Benchmark

Hi guys, i'm planning try to do a comparative between some DBMS and postgresql (informix, oracle, m$ sql server, firebird and even mysql) i'm coordinating with people in the irc spanish postgresql channel. 1) maybe can anyone give me suggestions on this? 2) point me to a good benchmark test or sc

Re: [PERFORM] Solaris 9 tuning

Hi, Paul Josh helped my company with this issue -- PG doesn't use shared memory like Oracle, it depends more on the OS buffers. Making shared mem too large a fraction is disasterous and seriously impact performance. (though I find myself having to justify this to Oracle trained DBA's) :) What I

Re: [PERFORM] Performance Tuning

On Wed, 2005-02-09 at 15:01 -0500, Chris Kratz wrote: > Hello All, > > In contrast to what we hear from most others on this list, we find our > database servers are mostly CPU bound. We are wondering if this is because > we have postgres configured incorrectly in some way, or if we really need

Re: [PERFORM] Performance Tuning

The world rejoiced as [EMAIL PROTECTED] (PFC) wrote: >> As a side note, I learned something very interesting for our >> developers here. >> We had been doing a drop database and then a reload off a db dump >> from our >> live server for test data. This takes 8-15 minutes depending on the >> serv

Re: [PERFORM] Performance Tuning

2. Moving to more materialized views and prepared statements where we can. Definitely worth investigating. I wish I could, but I can't get my customers to even consider slightly out of date stats :( Put a button 'Stats updated every hour', which gives the results in 0.1 seconds, and a but

Re: [PERFORM] How can I make use of both CPUs in a dual processor

You can wait for processes to finish as follows: #launch 3 processes sh -c './build_indexes1.sh' & PID1=$! sh -c './build_indexes2.sh' & PID2=$! sh -c './build_indexes3.sh' & PID3=$! # then wait $PID1 wait $PID2 wait $PID3 #continue My feeling is that doing so should generally reduce the overall

Re: [PERFORM] Performance Tuning

On Wed, 9 Feb 2005 17:30:41 -0500, Chris Kratz <[EMAIL PROTECTED]> wrote: > The solutions appear to primarily be: > 1. Going to faster hardware of which probably Opterons would be about the only > choice. And even that probably won't be a huge difference. I'd beg to differ on that last part. The

Re: [PERFORM] GiST indexes and concurrency (tsearch2)

On Sat, 2005-02-05 at 14:42 -0500, Tom Lane wrote: > Marinos Yannikos <[EMAIL PROTECTED]> writes: > > Some more things I tried: > > You might try the attached patch (which I just applied to HEAD). > It cuts down the number of acquisitions of the BufMgrLock by merging > adjacent bufmgr calls during

Re: [PERFORM] GiST indexes and concurrency (tsearch2)

Tom Lane wrote: I'm not completely convinced that you're seeing the same thing, but if you're seeing a whole lot of semops then it could well be. I'm seeing ~280 semops/second with spinlocks enabled and ~80k semops/second (> 4 mil. for 100 queries) with --disable-spinlocks, which increases total

Re: [PERFORM] Performance Tuning

As a side note, I learned something very interesting for our developers here. We had been doing a drop database and then a reload off a db dump from our live server for test data. This takes 8-15 minutes depending on the server (the one above takes about 8 minutes). I learned through testi

Re: [PERFORM] Performance Tuning

On Wednesday 09 February 2005 05:08 pm, Merlin Moncure wrote: > > Hello All, > > > > In contrast to what we hear from most others on this list, we find our > > database servers are mostly CPU bound. We are wondering if this is > > because > > we have postgres configured incorrectly in some way, or

Re: [PERFORM] Performance Tuning

On Wednesday 09 February 2005 03:27 pm, you wrote: ---snip--- > > We continue to tune our individual queries where we can, but it seems we > > still are waiting on the db a lot in our app. When we run most queries, > > top shows the postmaster running at 90%+ constantly during the duration > > of

Re: [PERFORM] Performance Tuning

On Wednesday 09 February 2005 03:59 pm, Greg Stark wrote: > Chris Kratz <[EMAIL PROTECTED]> writes: > > We continue to tune our individual queries where we can, but it seems we > > still are waiting on the db a lot in our app. When we run most queries, > > top shows the postmaster running at 90%+

Re: [PERFORM] Performance Tuning

> Hello All, > > In contrast to what we hear from most others on this list, we find our > database servers are mostly CPU bound. We are wondering if this is > because > we have postgres configured incorrectly in some way, or if we really need > more powerfull processor(s) to gain more performanc

Re: [PERFORM] Solaris 9 tuning

Yes, I agree it's unnecessary -- but you'll never have to worry about the postmaster not starting due to lack of allocatable memory -- when I was testing setups, I got sick of rebooting everytime I had to make a change to /etc/system, that I threw up my hands and said, "let it take all it wants".

[PERFORM] annotated PostgreSQL.conf now up

Folks, A lot of people have been pestering me for this stuff, so I've finally finished it and put it up. http://www.powerpostgresql.com/ Hopefully this should help people as much as the last one did. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(

Re: [PERFORM] Performance Tuning

On Wednesday 09 February 2005 03:38 pm, John Arbash Meinel wrote: >... > I'm very surprised you are doing RAID 0. You realize that if 1 drive > goes out, your entire array is toast, right? I would recommend doing > either RAID 10 (0+1), or even Raid 5 if you don't do a lot of writes. Yeah, we kn

Re: [PERFORM] Performance Tuning

Chris Kratz <[EMAIL PROTECTED]> writes: > We continue to tune our individual queries where we can, but it seems we > still > are waiting on the db a lot in our app. When we run most queries, top shows > the postmaster running at 90%+ constantly during the duration of the request. > > The d

Re: [PERFORM] Tell postgres which index to use?

John Arbash Meinel <[EMAIL PROTECTED]> writes: > >-> Hash (cost=1418.68..1418.68 rows=3226 width=4) (actual > > time=77.062..77.062 rows=0 loops=1) > > This seems to be at least one of the problems. The planner thinks there > are going to be 3000+ rows, but in reality there are 0. No, that

Re: [PERFORM] Solaris 9 tuning

Hi Tom, I've made changes to postgresql.conf as recommended on Josh's site and this seems to be working well so far. Given your comments on shared memory, it would appear that the following entry in /etc/system is unnecessary: set shmsys:shminfo_shmmax=0x Ironically, we both have this id

Re: [PERFORM] Performance Tuning

Chris Kratz wrote: Hello All, In contrast to what we hear from most others on this list, we find our database servers are mostly CPU bound. We are wondering if this is because we have postgres configured incorrectly in some way, or if we really need more powerfull processor(s) to gain more perform

[PERFORM] Performance Tuning

Hello All, In contrast to what we hear from most others on this list, we find our database servers are mostly CPU bound. We are wondering if this is because we have postgres configured incorrectly in some way, or if we really need more powerfull processor(s) to gain more performance from postg

Re: [PERFORM] Solaris 9 tuning

... Trying again again with right email address -- list server rejected previous :) Hi, Paul Josh helped my company with this issue -- PG doesn't use shared memory like Oracle, it depends more on the OS buffers. Making shared mem too large a fraction is disasterous and seriously impact performan

Re: [PERFORM] GiST indexes and concurrency (tsearch2)

Tom Lane wrote: You might try the attached patch (which I just applied to HEAD). It cuts down the number of acquisitions of the BufMgrLock by merging adjacent bufmgr calls during a GIST index search. [...] Thanks - I applied it successfully against 8.0.0, but it didn't seem to have a noticeable e

Re: [PERFORM] Solaris 9 tuning

Hi Josh, there are 8 internal disks - all are [EMAIL PROTECTED],000 RPM, fibre connected. The O/S is on 2 mirrored disks, the Postgres cluster is on the /data1 filesystem that is striped across the other 6 disks. The shared_buffers value is a semi-educated guess based on having made 4GB shared me

Re: [PERFORM] Tell postgres which index to use?

Silke Trissl wrote: Sorry, is there a way to tell Postgres which index to use when a query is issued in 7.4.2? PostgreSQL adjusts usage through global parameters, statistics, and periodic ANALYZE. Please post an EXPLAIN ANALYZE (not just EXPLAIN) for your query and people on this list can help

Re: [PERFORM] Tell postgres which index to use?

Sorry, is there a way to tell Postgres which index to use when a query is issued in 7.4.2? PostgreSQL adjusts usage through global parameters, statistics, and periodic ANALYZE. Please post an EXPLAIN ANALYZE (not just EXPLAIN) for your query and people on this list can help you with your spec

Re: [PERFORM] Tell postgres which index to use?

Silke, > is there a way to tell Postgres which index to use when a query is > issued in 7.4.2? PostgreSQL adjusts usage through global parameters, statistics, and periodic ANALYZE. Please post an EXPLAIN ANALYZE (not just EXPLAIN) for your query and people on this list can help you with your

[PERFORM] Tell postgres which index to use?

Hi, is there a way to tell Postgres which index to use when a query is issued in 7.4.2? I have a query for which costwise a Hash-Join and no Index-Usage is the best, but timewise using the index and then do a NestedLoop join is much better (3 - 4 times). I have vacuumed before I started the co

Re: [PERFORM] How can I make use of both CPUs in a dual processor

Alex wrote: Thanks John. Well as I mentioned. I have a Dual AMD Opteron 64 2.4ghz, 15k rpm SCSI Disks, 4GB of memory. Disks are pretty fast and memory should be more than enough. Currently we dont have many concurrent connections. Well, you didn't mention Opteron before (it makes a difference a

Re: [PERFORM] How can I make use of both CPUs in a dual processor

> Thanks John. > > Well as I mentioned. I have a Dual AMD Opteron 64 2.4ghz, 15k rpm SCSI > Disks, 4GB of memory. > Disks are pretty fast and memory should be more than enough. Currently > we dont have many concurrent connections. > > I run PG 8.0.1 on Fedora Core 3 > > When I now run the batch

Re: [PERFORM] How can I make use of both CPUs in a dual processor

Thanks John. Well as I mentioned. I have a Dual AMD Opteron 64 2.4ghz, 15k rpm SCSI Disks, 4GB of memory. Disks are pretty fast and memory should be more than enough. Currently we dont have many concurrent connections. I run PG 8.0.1 on Fedora Core 3 When I now run the batch job, one CPU runs i

Re: [PERFORM] How can I make use of both CPUs in a dual processor

Alex wrote: Hi, we just got a new dual processor machine and I wonder if there is a way to utilize both processors. Our DB server is basically fully dedicated to postgres. (its a dual amd with 4gb mem.) I have a batch job that periodically loads about 8 million records into a table. for this I

[PERFORM] How can I make use of both CPUs in a dual processor machine

Hi, we just got a new dual processor machine and I wonder if there is a way to utilize both processors. Our DB server is basically fully dedicated to postgres. (its a dual amd with 4gb mem.) I have a batch job that periodically loads about 8 million records into a table. for this I drop the in

Re: [PERFORM] query produces 1 GB temp file

I'm doing VACUUM ANALYZE once a night. Before the tests I did VACUUM and then ANALYZE. I'd suggest once an hour on any resonably active database... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings