On Wed, Aug 27, 2003 at 09:02:25PM +0530, Shridhar Daithankar wrote:
IIRC in a kernel release note recently, it was commented that IO scheduler is
still being worked on and does not perform as much for random seeks, which
exaclty what database needs.
Yeah, I've read that as well. It would be
Hello,
We're running a set of Half-Life based game servers that lookup user
privileges from a central PostgreSQL 7.3.4 database server (I recently
ported the MySQL code in Adminmod to PostgreSQL to be able to do this).
The data needed by the game servers are combined from several different
I'm running into some performance problems trying to execute simple
queries.
postgresql version 7.3.3
.conf params changed from defaults.
shared_buffers = 64000
sort_mem = 64000
fsync = false
effective_cache_size = 40
ex. query: select * from x where id in (select id from y);
There's an
You probably, more than anything, should look at some kind of
superfast, external storage array
Yeah, I think that's going to be a given. Low end EMC FibreChannel
boxes can do around 20,000 IOs/sec, which is probably close to good
enough.
You mentioned using multiple RAID controllers as a
Currently there's only a few users in the database for testing purposes,
and most of the time the user lookup's take 2-3 ms (I have syslog'ing of
queries and duration turned on), but several times per hour the duration
for one of the queries is 2-3 seconds (1000 times larger), while the
On 27 Aug 2003, matt wrote:
I'm wondering if the good people out there could perhaps give me some
pointers on suitable hardware to solve an upcoming performance issue.
I've never really dealt with these kinds of loads before, so any
experience you guys have would be invaluable. Apologies in
Don't know how cheap they are.
I have an app that does large batch updates. I found that if I dropped
the indexes, did the updates and recreated the indexes, it was faster
than doing the updates while the indexes were intact.
Yeah, unfortunately it's not batch work, but real time financial
I'm running into some performance problems trying to execute simple queries.
postgresql version 7.3.3
.conf params changed from defaults.
shared_buffers = 64000
sort_mem = 64000
fsync = false
effective_cache_size = 40
ex. query: select * from x where id in (select id from y);
There's an
Martha Stewart called it a Good Thing [EMAIL PROTECTED] (matt)wrote:
I'm also looking at renting equipment, or even trying out IBM/HP's
'on-demand' offerings.
You're assuming that this is likely to lead to REAL savings, and that
seems unlikely.
During the recent power outage in the NorthEast,
Are you sure? Have you tested the overall application to see if possibly
you gain more on insert performance than you lose on select performanc?
Unfortunately dropping any of the indexes results in much worse select
performance that is not remotely clawed back by the improvement in
insert
http://mail.sth.sze.hu/~hsz/sql/
New, upgraded test results. As we see, the developers works hard, and with
good results. Million thanks and congratulations.
Sorry *BSD-lovers, if you send a new hard drive, our tester can do bsd
tests also. Life is hard.
And last, but not least, thanks for the
On Tue, 26 Aug 2003, Neil Conway wrote:
Would it be possible to get a profile (e.g. gprof output) for a postgres
backend executing the query on the Sun machine?
Heh. Never thought of doing a profile!
I attached the entire gprof output, but here's the top few functions.
I did the test, 1
On Wed, Aug 27, 2003 at 02:35:13AM +0100, matt wrote:
I need to increase the overall performance by a factor of 10, while at
the same time the DB size increases by a factor of 50. e.g. 3000
inserts/updates or 25,000 selects per second, over a 25GB database with
most used tables of 5,000,000
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED]
(scott.marlowe) belched out... :-):
whether you like it or not, you're gonna need heavy iron if you need
to do this all in one hour once a week.
The other thing worth considering is trying to see if there is a way
of partitioning the
Christopher Browne wrote:
Martha Stewart called it a Good Thing [EMAIL PROTECTED] (matt)wrote:
I'm also looking at renting equipment, or even trying out IBM/HP's
'on-demand' offerings.
You're assuming that this is likely to lead to REAL savings, and that
seems unlikely.
During the recent power
On Tue, 2003-08-26 at 20:35, matt wrote:
I'm wondering if the good people out there could perhaps give me some
pointers on suitable hardware to solve an upcoming performance issue.
I've never really dealt with these kinds of loads before, so any
experience you guys have would be invaluable.
Hey all.
I said I was going to do it, and I finally did it.
As with all performance tests/benchmarks, there are probably dozens or
more reasons why these results aren't as accurate or wonderful as they
should be. Take them for what they are and hopefully everyone can
learn a few things from
matt wrote:
I'm wondering if the good people out there could perhaps give me some
pointers on suitable hardware to solve an upcoming performance issue.
I've never really dealt with these kinds of loads before, so any
experience you guys have would be invaluable. Apologies in advance for
the
On Tue, 26 Aug 2003, JM wrote:
need input on parameter values on confs...
our database is getting 1000 transactions/sec on peak periods..
sitting on RH 7.3
2.4.7-10smp
RAM: 1028400
SWAP: 2040244
1: Upgrade your kernel. 2.4.7 on RH3 was updated to 2.4.18-24 in March,
and the 2.4.18
Jeff [EMAIL PROTECTED] writes:
I'll do a profile for hte p2 and send post that in an hour or two
Please redo the linux profile after recompiling postmaster.c with
-DLINUX_PROFILE added (I use make PROFILE='-pg -DLINUX_PROFILE'
when building for profile on Linux).
On Wed, 2003-08-27 at 21:26, Bill Moran wrote:
Christopher Browne wrote:
Martha Stewart called it a Good Thing [EMAIL PROTECTED] (matt)wrote:
[snip]
With FreeBSD, you have jails, which allow multiple users to share
hardware without having to worry about user A looking at user B's
stuff.
Bill,
Very interesting results. I'd like to command you on your honesty.
Having started out with the intentions of proving that FreeBSD is faster
than Linux only to find that the opposite is true must not have been
rewarding for you. However, these unexpected results serve only to
reinforce the
Nicely done!
Thanks,
Balazs
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tomka
Gergely
Sent: Wednesday, August 27, 2003 5:40 AM
To: [EMAIL PROTECTED]
Subject: Re: [PERFORM] Tests
http://mail.sth.sze.hu/~hsz/sql/
New, upgraded test results. As we
On Wed, 27 Aug 2003, Bruno Wolff III wrote:
Did you check the error status for the records that weren't entered?
My first guess is that you have some bad data you are trying to insert.
Of course, I checked the error status for every insert, there is
no error. It seems like in my
2003-08-27 ragyog napjn matt ezt zente:
Yeah, I can imagine getting 5% extra from a slim kernel and
super-optimised PG.
Hm, about 20%, but only for the correctness - 20% not help you also :(
The FS is ext3, metadata journaling (the default), mounted noatime.
Worst fs under linux :) Try xfs.
Someone who has my:
[EMAIL PROTECTED]
email address has an infected computer, infected with the SoBig.F virus.
I'm getting 200+ infected emails a day from that person(s).
Go to this site and do a free online virus scan. It's safe, and done by
one of the two top virus scanning companies in
On 28 Aug 2003 at 1:07, Anders K. Pedersen wrote:
Hello,
We're running a set of Half-Life based game servers that lookup user
privileges from a central PostgreSQL 7.3.4 database server (I recently
ported the MySQL code in Adminmod to PostgreSQL to be able to do this).
The data needed
On 27 Aug 2003 at 15:50, Tarhon-Onu Victor wrote:
Hi,
I have a (big) problem with postgresql when making lots of
inserts per second. I have a tool that is generating an output of ~2500
lines per seconds. I write a script in PERL that opens a pipe to that
tool, reads every
2003-08-27 ragyog napjn Christopher Browne ezt zente:
After a long battle with technology,[EMAIL PROTECTED] (Tomka Gergely), an earthling,
wrote:
2003-08-27 ragyog napjn Castle, Lindsay ezt zente:
Perhaps some may say Linux isn't the best option for an 8 CPU
server but this is what I
Of course, I checked the error status for every insert, there is
no error. It seems like in my case the postgres server cannot handle so
much inserts per second some of the lines are not being parsed and data
inserted into the database.
That sounds extremely unlikely. Postgres is not one to
Are you *sure* about that 3K updates/inserts per second xlates
to 10,800,000 per hour. That, my friend, is a WHOLE HECK OF A LOT!
Yup, I know!
During the 1 hour surge, will SELECTs at 10 minutes after the
hour depend on INSERTs at 5 minutes after the hour?
Yes, they do. It's a
We have a somewhat similar situation - we're running a fairly constant, but
low priority, background load of about 70 selects and 40 inserts per second
(batched into fairly large transactions), and on top of that we're trying to
run time-sensitive queries for a web site (well two). I should
On 28 Aug 2003 at 10:02, Russell Garrett wrote:
The web site queries will jump up one or two orders of magnitude (I have
seen a normally 100ms query take in excess of 30 seconds) in duration at
seemingly random points. It's not always when the transactions are
committing, and it doesn't seem
On Tue, 2003-08-26 at 20:47, Bill Moran wrote:
Hey all.
I said I was going to do it, and I finally did it.
As with all performance tests/benchmarks, there are probably dozens or
more reasons why these results aren't as accurate or wonderful as they
should be. Take them for what they are
On Tue, 2003-08-26 at 20:47, Bill Moran wrote:
Hey all.
I said I was going to do it, and I finally did it.
As with all performance tests/benchmarks, there are probably dozens or
more reasons why these results aren't as accurate or wonderful as they
should be. Take them for what they are
The web site queries will jump up one or two orders of magnitude (I
have seen a normally 100ms query take in excess of 30 seconds) in
duration at seemingly random points. It's not always when the
transactions are committing, and it doesn't seem to be during
checkpointing either. The same
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Balazs Wellisch)
wrote:
Very interesting results. I'd like to command you on your honesty.
Having started out with the intentions of proving that FreeBSD is faster
than Linux only to find that the opposite is true must not have
On Tue, 26 Aug 2003, Bill Moran wrote:
Intelligent feedback is welcome.
That's some good work there, Lou. You'll make sgt for that someday.
But I think the next step, before trying out other filesystems and options
would be concurrency. Run a bunch of these beasts together and see what
Couple of questions:
What was the postgresql.conf configuration used? Default?
How many threads of the script ran? Looks like a single user only.
I assume there was nothing else running at the time (cron, sendmail,
etc. were all off?)
Do you know whether the machines were disk or I/O bound?
2003-08-28 ragyog napjn Christopher Browne ezt zente:
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Balazs Wellisch)
wrote:
Very interesting results. I'd like to command you on your honesty.
Having started out with the intentions of proving that FreeBSD is faster
than
2003-08-28 ragyog napjn Ludek Finstrle ezt zente:
Intelligent feedback is welcome.
http://www.potentialtech.com/wmoran/postgresql.php
Good work. But I can't find information about xfs. Do you plan to add
this one FS in test?
http://mail.sth.sze.hu/~hsz/sql/
--
Tomka Gergely
S most -
On Tue, 2003-08-26 at 23:59, Ron Johnson wrote:
What a fun exercises. Ok, lets see:
Postgres 7.3.4
RH AS 2.1
12GB RAM
motherboard with 64 bit 66MHz PCI slots
4 - Xenon 3.0GHz (1MB cache) CPUs
8 - 36GB 15K RPM as RAID10 on a 64 bit 66MHz U320 controller
having 512MB cache (for
On 28 Aug 2003 at 11:05, Chris Bowlby wrote:
On Tue, 2003-08-26 at 23:59, Ron Johnson wrote:
What a fun exercises. Ok, lets see:
Postgres 7.3.4
RH AS 2.1
12GB RAM
motherboard with 64 bit 66MHz PCI slots
4 - Xenon 3.0GHz (1MB cache) CPUs
8 - 36GB 15K RPM as RAID10 on a 64 bit
Stephan Szabo wrote:
On Wed, 27 Aug 2003, Michael Guerin wrote:
I'm running into some performance problems trying to execute simple
queries.
postgresql version 7.3.3
.conf params changed from defaults.
shared_buffers = 64000
sort_mem = 64000
fsync = false
effective_cache_size = 40
ex.
sm On 27 Aug 2003, matt wrote:
My app is likely to come under some serious load in the next 6 months,
but the increase will be broadly predictable, so there is time to throw
hardware at the problem.
Currently I have a ~1GB DB, with the largest (and most commonly accessed
and updated) two
On 28 Aug 2003 at 10:38, Michael Guerin wrote:
IN(subquery) is known to run poorly in 7.3.x and earlier. 7.4 is
generally much better (for reasonably sized subqueries) but in earlier
versions you'll probably want to convert into an EXISTS or join form.
Something else seems to be going on,
I need to increase the overall performance by a factor of 10, while at
the same time the DB size increases by a factor of 50. e.g. 3000
inserts/updates or 25,000 selects per second, over a 25GB database with
most used tables of 5,000,000 and 1,000,000 rows.
Ok.. I would be surprised if you
On Thu, 28 Aug 2003, Michael Guerin wrote:
Stephan Szabo wrote:
On Wed, 27 Aug 2003, Michael Guerin wrote:
I'm running into some performance problems trying to execute simple
queries.
postgresql version 7.3.3
.conf params changed from defaults.
shared_buffers = 64000
sort_mem =
On 27 Aug 2003, matt wrote:
You probably, more than anything, should look at some kind of
superfast, external storage array
Yeah, I think that's going to be a given. Low end EMC FibreChannel
boxes can do around 20,000 IOs/sec, which is probably close to good
enough.
You mentioned
Just how big do you expect your DB to grow? For a 1GB disk-space
database, I'd probably just splurge for an SSD hooked up either via
SCSI or FibreChannel. Heck, up to about 5Gb or so it is not that
expensive (about $25k) and adding another 5Gb should set you back
probably another $20k. I
Ok.. I would be surprised if you needed much more actual CPU power. I
suspect they're mostly idle waiting on data -- especially with a Quad
Xeon (shared memory bus is it not?).
In reality the CPUs get pegged: about 65% PG and 35% system. But I agree that memory
throughput and latency is an
I have a table that has about 20 foreign key constraints on it. I think
this is a bit excessive and am considering removing them ( they are all
related to the same table and I don't think there is much chance of any
integrity violations ). Would this improve performance or not?
thanks
Stephan Szabo wrote:
On Thu, 28 Aug 2003, Michael Guerin wrote:
Stephan Szabo wrote:
On Wed, 27 Aug 2003, Michael Guerin wrote:
I'm running into some performance problems trying to execute simple
queries.
postgresql version 7.3.3
.conf params changed from defaults.
Shridhar Daithankar wrote:
On 28 Aug 2003 at 1:07, Anders K. Pedersen wrote:
We're running a set of Half-Life based game servers that lookup user
privileges from a central PostgreSQL 7.3.4 database server (I recently
ported the MySQL code in Adminmod to PostgreSQL to be able to do this).
The
http://www.potentialtech.com/wmoran/postgresql.php
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
Adding my voice to the many, thanks for sharing your results Bill. Very
instructive.
--
Best,
Al Hulaton| Sr. Account Engineer | Command Prompt, Inc.
503.222.2783 |
Stephan Szabo wrote:
On Thu, 28 Aug 2003, teknokrat wrote:
I have a table that has about 20 foreign key constraints on it. I think
this is a bit excessive and am considering removing them ( they are all
related to the same table and I don't think there is much chance of any
integrity violations
What it still leaves quite open is just what happens when the OS has
more than one disk drive or CPU to play with. It's not clear what
happens in such cases, whether FreeBSD would catch up, or be left
further in the dust. The traditional propaganda has been that
there are all sorts of
Tom Lane wrote:
Anders K. Pedersen [EMAIL PROTECTED] writes:
Currently there's only a few users in the database for testing purposes,
and most of the time the user lookup's take 2-3 ms (I have syslog'ing of
queries and duration turned on), but several times per hour the duration
for one of the
On Thu, 2003-08-28 at 12:37, Matt Clark wrote:
Ok.. I would be surprised if you needed much more actual CPU power. I
suspect they're mostly idle waiting on data -- especially with a Quad
Xeon (shared memory bus is it not?).
In reality the CPUs get pegged: about 65% PG and 35% system. But
With regards to other jobs on the server, there is a MySQL server on it
as well, which from time to time has some multi-second queries generated
from a webserver also on this host, but the MySQL is running with nice
10 (PostgreSQL isn't nice'd).
Do those MySQL queries hit disk hard?
I've
On Thu, 28 Aug 2003, Michael Guerin wrote:
Stephan Szabo wrote:
On Thu, 28 Aug 2003, Michael Guerin wrote:
Stephan Szabo wrote:
On Wed, 27 Aug 2003, Michael Guerin wrote:
I'm running into some performance problems trying to execute simple
queries.
postgresql
On Thu, 28 Aug 2003, teknokrat wrote:
Stephan Szabo wrote:
On Thu, 28 Aug 2003, teknokrat wrote:
I have a table that has about 20 foreign key constraints on it. I think
this is a bit excessive and am considering removing them ( they are all
related to the same table and I don't think
Rod Taylor wrote:
With regards to other jobs on the server, there is a MySQL server on it
as well, which from time to time has some multi-second queries generated
from a webserver also on this host, but the MySQL is running with nice
10 (PostgreSQL isn't nice'd).
Do those MySQL queries hit disk
I need to step in and do 2 things:
First, apologize for posting inaccurate test results.
Second, verify that Sean is absolutely correct. FreeBSD 4.8 was accessing
the drives in PIO mode, which is significantly lousier than DMA, which
RedHat was able to use. As a result, the tests are
I need to step in and do 2 things:
Thanks for posting that. Let me know if you have any questions while
doing your testing. I've found that using 16K blocks on FreeBSD
results in about an 8% speedup in writes to the database, fwiw.
I'm likely going to make this the default for PostgreSQL on
I just ran a handful of tests on a 14-disk array on a SCSI hardware
RAID card.
From some quickie benchmarks using the bonnie++ benchmark, it appears
that the RAID5 across all 14 disks is a bit faster than RAID50 and
noticeably faster than RAID10...
Sample numbers for a 10Gb file (speed in
SC == Sean Chittenden [EMAIL PROTECTED] writes:
I need to step in and do 2 things:
SC Thanks for posting that. Let me know if you have any questions while
SC doing your testing. I've found that using 16K blocks on FreeBSD
SC results in about an 8% speedup in writes to the database, fwiw.
SC == Sean Chittenden [EMAIL PROTECTED] writes:
I need to step in and do 2 things:
SC Thanks for posting that. Let me know if you have any questions while
SC doing your testing. I've found that using 16K blocks on FreeBSD
SC results in about an 8% speedup in writes to the database, fwiw.
On Thu, 28 Aug 2003, Sean Chittenden wrote:
What it still leaves quite open is just what happens when the OS has
more than one disk drive or CPU to play with. It's not clear what
happens in such cases, whether FreeBSD would catch up, or be left
further in the dust. The traditional
Stephan Szabo wrote:
On Thu, 28 Aug 2003, Michael Guerin wrote:
Stephan Szabo wrote:
On Thu, 28 Aug 2003, Michael Guerin wrote:
Stephan Szabo wrote:
On Wed, 27 Aug 2003, Michael Guerin wrote:
I'm running into some performance problems trying to
I need to step in and do 2 things:
SC Thanks for posting that. Let me know if you have any questions while
SC doing your testing. I've found that using 16K blocks on FreeBSD
SC results in about an 8% speedup in writes to the database, fwiw.
ok.. ignore my prior request about how to set
Hi,
I have a (big) problem with postgresql when making lots of
inserts per second. I have a tool that is generating an output of ~2500
lines per seconds. I write a script in PERL that opens a pipe to that
tool, reads every line and inserts data.
I tryed both commited
On Wed, Aug 27, 2003 at 15:50:32 +0300,
Tarhon-Onu Victor [EMAIL PROTECTED] wrote:
The problems is that only ~15% of the lines are inserted into
the database. The same script modified to insert the same data in a
similar table created in a MySQL database inserts 100%.
Did you check
The problems is that only ~15% of the lines are inserted into
the database. The same script modified to insert the same data in a
similar table created in a MySQL database inserts 100%.
Did you check the error status for the records that weren't entered?
My first guess is that you have
On Wednesday 27 August 2003 13:50, Tarhon-Onu Victor wrote:
shared_buffers = 520
max_locks_per_transaction = 128
wal_buffers = 8
max_fsm_relations = 3
max_fsm_pages = 482000
sort_mem = 131072
vacuum_mem = 131072
effective_cache_size = 1
random_page_cost = 2
Slightly off-topic,
75 matches
Mail list logo
