You can use the test with InnoDB by giving the --create-options=engine=innodb
option in the
command line. Even with InnoDB, in some specific tests PG looks very bad
compared to InnoDB.
--- Tom Lane [EMAIL PROTECTED] wrote:
yoav x [EMAIL PROTECTED] writes:
Are there any tuning parameters
Hi, Yoav X,
yoav x wrote:
You can use the test with InnoDB by giving the --create-options=engine=innodb
option in the
command line. Even with InnoDB, in some specific tests PG looks very bad
compared to InnoDB.
As far as I've seen, they include the CREATE TABLE command in their
benchmarks.
Have you tuned postgresql ?
You still haven't told us what the machine is, or the tuning
parameters. If you follow Merlin's links you will find his properly
tuned postgres out performs mysql in every case.
--dc--
On 14-Sep-06, at 2:55 AM, yoav x wrote:
You can use the test with InnoDB by
On 9/14/06, Jérôme BENOIS [EMAIL PROTECTED] wrote:
I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
completely full, by moment load average 40
All queries analyzed by EXPLAIN, all indexes are used .. IO is good ...
What is the bottleneck? Are you CPU bound? Do you
Hi Guillaume,
Le jeudi 14 septembre 2006 à 15:46 +0200, Guillaume Smet a écrit :
On 9/14/06, Jérôme BENOIS [EMAIL PROTECTED] wrote:
I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
completely full, by moment load average 40
All queries analyzed by EXPLAIN, all
Hi All,
I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
completely full, by moment load average 40
All queries analyzed by EXPLAIN, all indexes are used .. IO is good ...
My configuration is correct ?
- default configuration and se + somes updates :
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS [EMAIL PROTECTED] writes:
I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
completely full, by moment load average 40
Did you remember to ANALYZE the whole database after reloading it?
pg_dump/reload won't by itself regenerate statistics.
Hi Tom,
Le jeudi 14 septembre 2006 à 10:13 -0400, Tom Lane a écrit :
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS [EMAIL PROTECTED] writes:
I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
completely full, by moment load average 40
Did you remember to ANALYZE the whole database
On Thu, 2006-09-14 at 09:00, Jérôme BENOIS wrote:
Hi Guillaume,
Le jeudi 14 septembre 2006 à 15:46 +0200, Guillaume Smet a écrit :
On 9/14/06, Jérôme BENOIS [EMAIL PROTECTED] wrote:
I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
completely full, by moment load
Wow, that correlation value is *way* away from order.If they werereally in exact order by dsiacctno then I'd expect to see
1.0 inthat column.Can you take another look at the tables and confirmthe ordering?Does the correlation change if you do an ANALYZE on thetables?(Some small change is to be
On Thu, 2006-09-14 at 09:17, Jérôme BENOIS wrote:
Hi Tom,
Le jeudi 14 septembre 2006 à 10:13 -0400, Tom Lane a écrit :
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS [EMAIL PROTECTED] writes:
I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
completely full, by moment load
On 9/14/06, Jérôme BENOIS [EMAIL PROTECTED] wrote:
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
15667 postgres 25 0 536m 222m 532m R 98.8 11.0 1:39.29 postmaster
19533 postgres 25 0 535m 169m 532m R 92.9 8.3 0:38.68 postmaster
16278 postgres 25 0 537m
Hello,
Le jeudi 14 septembre 2006 à 09:21 -0500, Scott Marlowe a écrit :
On Thu, 2006-09-14 at 09:17, Jérôme BENOIS wrote:
Hi Tom,
Le jeudi 14 septembre 2006 à 10:13 -0400, Tom Lane a écrit :
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS [EMAIL PROTECTED] writes:
I migrated Postgres
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Jérôme BENOIS
explain analyze select distinct
INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from (((
select distinct ei_id as
Hi Dave,
Le jeudi 14 septembre 2006 à 10:02 -0500, Dave Dutcher a écrit :
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Jérôme BENOIS
explain analyze select distinct
INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
My setup:
Freebsd 6.1
Postgresql 8.1.4
Memory: 8GB
SATA Disks
Raid 1 10 spindles (2 as hot spares)
500GB disks (16MB buffer), 7200 rpm
Raid 10
Raid 2 4 spindles
150GB 10K rpm disks
Raid 10
shared_buffers = 1
temp_buffers = 1500
work_mem = 32768# 32MB
maintenance_work_mem
On Thu, 2006-09-14 at 10:02, Dave Dutcher wrote:
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Jérôme BENOIS
explain analyze select distinct
INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
On 14-Sep-06, at 11:23 AM, Francisco Reyes wrote:
My setup:
Freebsd 6.1
Postgresql 8.1.4
Memory: 8GB
SATA Disks
Raid 1 10 spindles (2 as hot spares)
500GB disks (16MB buffer), 7200 rpm
Raid 10
Raid 2 4 spindles
150GB 10K rpm disks
Raid 10
shared_buffers = 1
shared buffers should be
Dave Cramer writes:
What is effective_cache set to ?
Default of 1000. Was just reading about this parameter.
Will try increasing it to 8192 (8192 * 8K = 64MB)
why not just let autovac do it's thing ?
Have been playing with decresing the autovac values. With 100GB+ tables even
1% in
Dave Cramer writes:
What is effective_cache set to ?
Increasing this seems to have helped significantly a web app. Load times
seem magnitudes faster.
Increased it to effective_cache_size = 12288 # 96MB
What is a reasonable number?
I estimate I have at least 1 to 2 GB free of memory.
I'm experiment with RAID, looking for an inexpensive way to boost performance.
I bought 4 Seagate 7200.9 120 GB SATA drives and two SIIG dual-port SATA cards.
(NB: I don't plan to run RAID 0 in production, probably RAID 10, so no need to
comment on the failure rate of RAID 0.)
I used this
Francisco
On 14-Sep-06, at 1:36 PM, Francisco Reyes wrote:
Dave Cramer writes:
What is effective_cache set to ?
Increasing this seems to have helped significantly a web app. Load
times seem magnitudes faster.
Increased it to effective_cache_size = 12288 # 96MB
What is a reasonable
Craig A. James wrote:
I'm experiment with RAID, looking for an inexpensive way to boost
performance. I bought 4 Seagate 7200.9 120 GB SATA drives and two SIIG
dual-port SATA cards. (NB: I don't plan to run RAID 0 in production,
probably RAID 10, so no need to comment on the failure rate of
On Thursday 14 September 2006 11:05, Craig A. James
[EMAIL PROTECTED] wrote:
I'm experiment with RAID, looking for an inexpensive way to boost
performance. I bought 4 Seagate 7200.9 120 GB SATA drives and two SIIG
dual-port SATA cards. (NB: I don't plan to run RAID 0 in production,
probably
[ Hint: If you want someone to help you with your query, take some time
yourself to make the query easy to read. ]
---
Pi?eiro wrote:
Hi,
a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
7.4.7).
Dave Cramer writes:
What is a reasonable number?
I estimate I have at least 1 to 2 GB free of memory.
You are using 6G of memory for something else ?
Right now adding up from ps the memory I have about 2GB.
Have an occassional program which uses up to 2GB.
Then I want to give some breathing
On Thu, Sep 14, 2006 at 04:30:46PM -0400, Francisco Reyes wrote:
Right now adding up from ps the memory I have about 2GB.
That's not how you find out how much memory you have. Try free or
somesuch.
Mike Stone
---(end of broadcast)---
TIP 5:
Hi Scott,
Le jeudi 14 septembre 2006 à 10:56 -0500, Scott Marlowe a écrit :
On Thu, 2006-09-14 at 10:02, Dave Dutcher wrote:
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Jérôme BENOIS
explain analyze select distinct
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS [EMAIL PROTECTED] writes:
Le jeudi 14 septembre 2006 =C3=A0 10:56 -0500, Scott Marlowe a =C3=A9crit :
I'm gonna make a SWAG here and guess that maybe your 7.4 db was initdb'd
with a locale of C and the new one is initdb'd with a real locale, like
en_US. Can
Jérôme,
Perhaps it's a stupid question but are your queries slower than
before? You didn't tell it.
IMHO, it's not a problem to have a high load if you have a lot of
users and your queries are fast (and with 8.1, they should be far
faster than before).
To take a real example, we had a problem
Alan Hodgson wrote:
On Thursday 14 September 2006 11:05, Craig A. James
[EMAIL PROTECTED] wrote:
I'm experiment with RAID, looking for an inexpensive way to boost
performance. I bought 4 Seagate 7200.9 120 GB SATA drives and two SIIG
dual-port SATA cards. (NB: I don't plan to run RAID 0 in
On Thu, 2006-09-14 at 11:23 -0400, Francisco Reyes wrote:
My setup:
Freebsd 6.1
Postgresql 8.1.4
Memory: 8GB
SATA Disks
Raid 1 10 spindles (2 as hot spares)
500GB disks (16MB buffer), 7200 rpm
Raid 10
Raid 2 4 spindles
150GB 10K rpm disks
Raid 10
shared_buffers = 1
Why so
Hi Evgeny,
Le jeudi 14 septembre 2006 à 20:47 +0400, Evgeny Gridasov a écrit :
Jérôme,
How many concurrent connections do you have?
I have between 300 and 400 concurrent connections.
Because You've got only 2GB of ram this is important! Postgres process
takes some bytes in memory =) .. I
Hi Guillaume,
Le jeudi 14 septembre 2006 à 23:22 +0200, Guillaume Smet a écrit :
Jérôme,
Perhaps it's a stupid question but are your queries slower than
before? You didn't tell it.
No, it's not stupid question !
Yes queries speed but when the load average exceeds 40 all queries are slower
Francisco
On 14-Sep-06, at 4:30 PM, Francisco Reyes wrote:
Dave Cramer writes:
What is a reasonable number?
I estimate I have at least 1 to 2 GB free of memory.
You are using 6G of memory for something else ?
Right now adding up from ps the memory I have about 2GB.
Have an occassional
On Thu, 2006-09-14 at 16:35, Craig A. James wrote:
Alan Hodgson wrote:
On Thursday 14 September 2006 11:05, Craig A. James
[EMAIL PROTECTED] wrote:
I'm experiment with RAID, looking for an inexpensive way to boost
performance. I bought 4 Seagate 7200.9 120 GB SATA drives and two SIIG
On 9/14/06, Jérôme BENOIS [EMAIL PROTECTED] wrote:
Yes i have a lot of users ;-)
So your work_mem is probably far too high (that's what I told you in
my first message) and you probably swap when you have too many users.
Remember that work_mem can be used several times per query (and it's
Hyper threading. It's usually not recommended to enable it on
PostgreSQL servers. On most servers, you can disable it directly in
the BIOS.
Maybe for specific usage scenarios, but that's generally not been my experience
with relatively recent versions of PG. We ran some tests with pgbench, and
Jeff Davis writes:
shared_buffers = 1
Why so low?
My initial research was not thorough enough with regards to how to compute
how many to use.
You have a lot of memory, and shared_buffers are an
important performance setting. I have a machine with 4GB of RAM, and I
found my best
Dave Cramer writes:
personally, I'd set this to about 6G. This doesn't actually consume
memory it is just a setting to tell postgresql how much memory is
being used for cache and kernel buffers
Gotcha. Will increase further.
regarding shared buffers I'd make this much bigger, like 2GB
Michael Stone writes:
On Thu, Sep 14, 2006 at 04:30:46PM -0400, Francisco Reyes wrote:
Right now adding up from ps the memory I have about 2GB.
That's not how you find out how much memory you have. Try free or
somesuch.
Wasn't trying to get an accurate value, just a ballpark figure.
When
On 14-Sep-06, at 7:50 PM, Francisco Reyes wrote:
Dave Cramer writes:
personally, I'd set this to about 6G. This doesn't actually
consume memory it is just a setting to tell postgresql how much
memory is being used for cache and kernel buffers
Gotcha. Will increase further.
regarding
On Thu, Sep 14, 2006 at 08:04:39PM -0400, Francisco Reyes wrote:
Wasn't trying to get an accurate value, just a ballpark figure.
Won't even be a ballpark.
When you say free are you refering to the free value from top? or some
program called free?
Depends on your OS.
Mike Stone
Tom Lane wrote:
It'd be interesting to see what mysql's performance looks like on this
test using innodb tables, which should be compared against fsync = true
... but I don't know how to change it to get all the tables to be
innodb.)
Just a point (I've taught some MySQL courses before,
On Fri, Sep 15, 2006 at 02:11:23AM +0200, Grega Bremec wrote:
Just a point (I've taught some MySQL courses before, sorry 'bout that;
if you're not, I am, sort of :)) - the crash-proof version of
transactional tables in MySQL was supposed to be the Berkeley ones, but
(oh, the irony) they're
On Thu, 2006-09-14 at 19:30 -0400, Francisco Reyes wrote:
Will have to talk to the developers. In particular for every insert there
are updates. I know they have at least one table that gets udpated to have
summarized totals.
If the table being updated is small, you have no problems at
On Thu, 2006-09-14 at 20:04 -0400, Francisco Reyes wrote:
Michael Stone writes:
On Thu, Sep 14, 2006 at 04:30:46PM -0400, Francisco Reyes wrote:
Right now adding up from ps the memory I have about 2GB.
That's not how you find out how much memory you have. Try free or
somesuch.
On Thu, 2006-09-14 at 20:07 -0400, Dave Cramer wrote:
On 14-Sep-06, at 7:50 PM, Francisco Reyes wrote:
Dave Cramer writes:
personally, I'd set this to about 6G. This doesn't actually
consume memory it is just a setting to tell postgresql how much
memory is being used for cache
On Thu, Sep 14, 2006 at 05:52:02PM -0700, Jeff Davis wrote:
Any long-running system will have very little free memory. Free memory
is wasted memory, so the OS finds some use for it.
The important part of the output of free in this context isn't how
much is free, it's how much is cache vs how
On Thu, 2006-09-14 at 19:50 -0400, Francisco Reyes wrote:
regarding shared buffers I'd make this much bigger, like 2GB or more
Will do 2GB on the weekend. From what I read this requires shared memory so
have to restart my machine (FreeBSD).
You should be able to do:
# sysctl -w
On Thu, 2006-09-14 at 21:04 -0400, Michael Stone wrote:
On Thu, Sep 14, 2006 at 05:52:02PM -0700, Jeff Davis wrote:
Any long-running system will have very little free memory. Free memory
is wasted memory, so the OS finds some use for it.
The important part of the output of free in this
Josh,
On 9/14/06 11:49 AM, Joshua D. Drake [EMAIL PROTECTED] wrote:
I am assuming linux here, Linux software raid 0 is known not to be super
duper.
I've obtained 1,950 MB/s using Linux software RAID on SATA drives.
- Luke
---(end of
Luke Lonergan wrote:
Josh,
On 9/14/06 11:49 AM, Joshua D. Drake [EMAIL PROTECTED] wrote:
I am assuming linux here, Linux software raid 0 is known not to be super
duper.
I've obtained 1,950 MB/s using Linux software RAID on SATA drives.
With what? :)
- Luke
--
=== The
Luke Lonergan wrote:
Josh,
On 9/14/06 8:47 PM, Joshua D. Drake [EMAIL PROTECTED] wrote:
I've obtained 1,950 MB/s using Linux software RAID on SATA drives.
With what? :)
Sun X4500 (aka Thumper) running stock RedHat 4.3 (actually CentOS 4.3) with
XFS and the linux md driver without lvm.
54 matches
Mail list logo
