Bob Dusek redu...@gmail.com writes:
So, pgBouncer is pretty good. It doesn't appear to be as good as
limiting TCON and using pconnect, but since we can't limit TCON in a
production environment, we may not have a choice.
You can still use pconnect() with pgbouncer, in transaction mode, if
your
Hello together,
I need to increase the write performance when inserting
bytea of 8MB. I am using 8.2.4 on windows with libpq.
The test setting is simple:
I write 100x times a byte array (bytea) of 8 MB random data
into a table having a binary column (and oids and 3 other
int columns, oids are
On 1/13/2010 11:36 PM, Craig Ringer wrote:
Robert Haas wrote:
I'm kind of surprised that there are disk I/O subsystems that are so
bad that a single thread doing non-stop I/O can take down the whole
server. Is that normal?
No.
Does it happen on non-Windows operating
systems?
Yes. My
fka...@googlemail.com wrote:
Hello together,
I need to increase the write performance when inserting
bytea of 8MB. I am using 8.2.4 on windows with libpq.
This takes about 50s, so, 800MB/50s = 16MB/s.
However the harddisk (sata) could write 43 MB/s in the worst
case! Why is write
Hi,
=== Problem ===
i have a db-table data_measurand with about 6000 (60 Millions)
rows and the following query takes about 20-30 seconds (with psql):
mydb=# select count(*) from data_measurand;
count
--
60846187
(1 row)
=== Question ===
- What can i do to improve the
On Thu, 14 Jan 2010, fka...@googlemail.com wrote:
This takes about 50s, so, 800MB/50s = 16MB/s.
However the harddisk (sata) could write 43 MB/s in the worst
case! Why is write performance limited to 16 MB/s?
Several reasons:
The data needs to be written first to the WAL, in order to provide
* fka...@googlemail.com fka...@googlemail.com [100114 09:29]:
This takes about 50s, so, 800MB/50s = 16MB/s.
However the harddisk (sata) could write 43 MB/s in the worst
case! Why is write performance limited to 16 MB/s?
I altered the binary column to STORAGE EXTERNAL.
Some experiments
On Thu, 14 Jan 2010, tom wrote:
i have a db-table data_measurand with about 6000 (60 Millions)
rows and the following query takes about 20-30 seconds (with psql):
mydb=# select count(*) from data_measurand;
count
--
60846187
(1 row)
Sounds pretty reasonable to me. Looking at your
Do you have any further idea why 16MB/s seems to be the limit here?
BYTEA deserialization is very slow, and this could be a factor here.
Have you checked that you are in fact I/O bound?
You can speed things up by sending the data in binary, by passing
approriate parameters to PQexecParams().
In response to tom :
Hi,
=== Problem ===
i have a db-table data_measurand with about 6000 (60 Millions)
rows and the following query takes about 20-30 seconds (with psql):
mydb=# select count(*) from data_measurand;
count
--
60846187
(1 row)
=== Question ===
Matthew Wakeling matt...@flymine.org wrote:
This is an FAQ.
I just added it to the wiki FAQ page:
http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
So, pgBouncer is pretty good. It doesn't appear to be as good as
limiting TCON and using pconnect, but since we can't limit TCON in a
production environment, we may not have a choice.
Actually, you can : use lighttpd and php/fastcgi.
Lighttpd handles the network stuff, and
Kevin Grittner wrote:
Matthew Wakeling matt...@flymine.org wrote:
This is an FAQ.
I just added it to the wiki FAQ page:
http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F
Maybe you could add a short note why an estimation like from the
Ivan Voras ivo...@freebsd.org wrote:
Maybe you could add a short note why an estimation like from the
pg_class table is usually enough.
OK. Will do.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
However the harddisk (sata) could write 43 MB/s in the worst
case! Why is write performance limited to 16 MB/s?
Some more hints what I do:
I use PQexecParams() and the INSERT ... $001 notation to NOT
create a real escapted string from the data additionally but
use a pointer to the 8MB data
high CPU usage It might very well be high IO usage.
Try this :
Copy (using explorer, the shell, whatever) a huge file.
This will create load similar to ALTER TABLE.
Measure throughput, how much is it ?
If your server blows up just like it did on ALTER TABLE,
Hi,
version: 8.4.2
I have a table called values:
test=*# \d values
Table public.values
Column | Type | Modifiers
+-+---
id | integer |
value | real|
Indexes:
idx_id btree (id)
The table contains 10 random rows and is analysed.
And i have 2
Andreas Kretschmer akretsch...@spamfence.net writes:
No question, this is a silly query, but the problem is the 2nd query: it
is obviously not possible for the planner to put the where-condition
into the subquery.
Well, yeah: it might change the results of the window functions.
I see no bug
Tom Lane t...@sss.pgh.pa.us wrote:
Andreas Kretschmer akretsch...@spamfence.net writes:
No question, this is a silly query, but the problem is the 2nd query: it
is obviously not possible for the planner to put the where-condition
into the subquery.
Well, yeah: it might change the
Andreas Kretschmer akretsch...@spamfence.net writes:
Tom Lane t...@sss.pgh.pa.us wrote:
I see no bug here. Your second query asks for a much more complicated
computation, it's not surprising it takes longer.
But sorry, I disagree. It is the same query with the same result. I can't see
how
Kevin Grittner wrote:
Matthew Wakeling matt...@flymine.org wrote:
This is an FAQ.
I just added it to the wiki FAQ page:
http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F
The content was already there, just not linked into the main
Tom Lane t...@sss.pgh.pa.us wrote:
Andreas Kretschmer akretsch...@spamfence.net writes:
Tom Lane t...@sss.pgh.pa.us wrote:
I see no bug here. Your second query asks for a much more complicated
computation, it's not surprising it takes longer.
But sorry, I disagree. It is the same
Andy Colson wrote:
On 1/13/2010 11:36 PM, Craig Ringer wrote:
Yes. My 3ware 8500-8 on a Debian Sarge box was so awful that launching a
terminal would go from a 1/4 second operation to a 5 minute operation
under heavy write load by one writer. I landed up having to modify the
driver to partially
Greg Smith g...@2ndquadrant.com wrote:
The content was already there, just not linked into the main FAQ
yet:
http://wiki.postgresql.org/wiki/Slow_Counting
For a question asked this frequently, it should probably be in the
FAQ. I'll add a link from there to the more thorough write-up.
On 1/14/2010 12:07 PM, Greg Smith wrote:
Andy Colson wrote:
On 1/13/2010 11:36 PM, Craig Ringer wrote:
Yes. My 3ware 8500-8 on a Debian Sarge box was so awful that launching a
terminal would go from a 1/4 second operation to a 5 minute operation
under heavy write load by one writer. I landed
Kevin Grittner wrote:
Greg Smith g...@2ndquadrant.com wrote:
The content was already there, just not linked into the main FAQ
yet:
http://wiki.postgresql.org/wiki/Slow_Counting
For a question asked this frequently, it should probably be in the
FAQ. I'll add a link from there to
Andy Colson wrote:
So if there is very little io, or if there is way way too much, then
the scheduler really doesn't matter. So there is a slim middle ground
where the io is within a small percent of the HD capacity where the
scheduler might make a difference?
That's basically how I see it.
Andreas Kretschmer akretsch...@spamfence.net wrote:
they are being done over all rows. In this particular example you
happen to get the same result, but that's just because avg(foo) over
partition by foo is a dumb example --- it will necessarily just yield
identically foo. In more
Greg Smith g...@2ndquadrant.com wrote:
There's a whole list of FAQs that are documented on the wiki but
not in the main FAQ yet leftover from before the main FAQ was
hosted there. You can see them all at
http://wiki.postgresql.org/wiki/Frequently_Asked_Questions
I just haven't had time
Kevin Grittner wrote:
Greg Smith g...@2ndquadrant.com wrote:
There's a whole list of FAQs that are documented on the wiki but
not in the main FAQ yet leftover from before the main FAQ was
hosted there. You can see them all at
http://wiki.postgresql.org/wiki/Frequently_Asked_Questions
I
My client just informed me that new hardware is available for our DB server.
. Intel Core 2 Quads Quad
. 48 GB RAM
. 4 Disk RAID drive (RAID level TBD)
I have put the ugly details of what we do with our DB below, as well as the
postgres.conf settings. But, to summarize: we have a PostgreSQL
Guys, I want to thank you for all of the advice - my client has just made a
surprise announcement that he would like to set start from scratch with a
new server, so I am afraid that all of this great advice has to be seen in
the context of whatever decision is made on that. I am out there,
On Thu, 14 Jan 2010 14:17:13 -0500, Carlo Stonebanks
stonec.regis...@sympatico.ca wrote:
My client just informed me that new hardware is available for our DB
server.
. Intel Core 2 Quads Quad
. 48 GB RAM
. 4 Disk RAID drive (RAID level TBD)
I have put the ugly details of what we do with
Hi all,
I've just received this new server:
1 x XEON 5520 Quad Core w/ HT
8 GB RAM 1066 MHz
16 x SATA II Seagate Barracuda 7200.12
3ware 9650SE w/ 256MB BBU
It will run an Ubuntu 8.04 LTS Postgres 8.4 dedicated server. Its database
will be getting between 100 and 1000 inserts per second (those
Thank You for your reply.
Ivan Voras:
Are you doing it locally or over a network? If you are accessing the
server over a network then it could be the location of the bottleneck.
All is done locally (for now).
Felix
--
Sent via pgsql-performance mailing list
Thanks a lot for the detailed reply.
Matthew Wakeling:
On Thu, 14 Jan 2010, fka...@googlemail.com wrote:
This takes about 50s, so, 800MB/50s = 16MB/s.
However the harddisk (sata) could write 43 MB/s in the worst
case! Why is write performance limited to 16 MB/s?
Several reasons:
Thanks a lot for your reply.
Hannu Krosing:
4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
try inserting the same data using 4 parallel connections or even 8
parallel ones.
Interesting idea -- I forgot to mention though that 2-3
cores will be occupied soon with other tasks.
Felix
--
Sent
Regarding the hardware the system is running on:
It's an HP Proliant DL-180 G5 server.
Here are the specs... our actual configuration only has one CPU, and 16G of
RAM.
The model of the 2 disks I will post later today, when I get to the server.
I was with many things, sorry.
Regarding the EA-200 card, here are the specs.
It seems it has support for SAS disks, so it is most probably that we are
using the embedded/default controller.
http://h18000.www1.hp.com/products/quickspecs/12460_div/12460_div.html
On Thu, Jan 14, 2010 at 1:03 PM, Fernando Hevia fhe...@ip-tel.com.ar wrote:
Hi all,
I've just received this new server:
1 x XEON 5520 Quad Core w/ HT
8 GB RAM 1066 MHz
16 x SATA II Seagate Barracuda 7200.12
3ware 9650SE w/ 256MB BBU
It will run an Ubuntu 8.04 LTS Postgres 8.4 dedicated
Aidan Van Dyk:
So, your SATA disk can do 43MB/s of sequential writes, but you're example
is doing:
1) Sequential writes to WAL
2) Random writes to your index
3) Sequential writes to table heap
4) Sequential writes to table' toast heap
5) Any other OS-based FS overhead
Ok, I see. Thanks a
Florian Weimer:
Do you have any further idea why 16MB/s seems to be the limit here?
BYTEA deserialization is very slow, and this could be a factor here.
Have you checked that you are in fact I/O bound?
Could you elaborate that a bit? It sounds interesting but I
do not get what you mean by:
Pierre Frédéric Caillaud:
4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
Big CPU and slow disk...
You should add another disk just for the WAL -- disks are pretty cheap
these days.
Writing the WAL on a second disk is the first thing to do on a
configuration like yours,
-Mensaje original-
De: fka...@googlemail.com
Nevertheless: If your explanation covers all what can be said
about it then replacing the hard disk by a faster one should
increase the performance here (I'll try to check that out).
Moving the pg_xlog directory to the OS drive
Carlo Stonebanks wrote:
Guys, I want to thank you for all of the advice - my client has just
made a surprise announcement that he would like to set start from
scratch with a new server, so I am afraid that all of this great advice
has to be seen in the context of whatever decision is made on
On Thu, Jan 14, 2010 at 12:17 PM, Carlo Stonebanks
stonec.regis...@sympatico.ca wrote:
My client just informed me that new hardware is available for our DB server.
. Intel Core 2 Quads Quad
. 48 GB RAM
. 4 Disk RAID drive (RAID level TBD)
I have put the ugly details of what we do with our
My client just informed me that new hardware is available for our DB server.
. Intel Core 2 Quads Quad
. 48 GB RAM
. 4 Disk RAID drive (RAID level TBD)
I have put the ugly details of what we do with our DB below, as well as the
postgres.conf settings. But, to summarize: we have a PostgreSQL
My bad - I thought I had, so it has been re-posted with a (v2) disclaimer in
the title... like THAT will stop the flaming! g
Thanks for your patience!
Craig James craig_ja...@emolecules.com wrote in message
news:4b4f8a49.7010...@emolecules.com...
Carlo Stonebanks wrote:
Guys, I want to thank
I'll bite
1. In general, RAID-10 is the only suitable RAID configuration for a
database. The decision making comes in how many drives, and splitting stuff
up into LUNs (like putting pg_xlog on its own LUN).
2. None of the above - you're asking the wrong question really. PostgreSQL
is open
On Thu, 14 Jan 2010 22:28:07 +0100, fka...@googlemail.com
fka...@googlemail.com wrote:
Pierre Frédéric Caillaud:
4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
Big CPU and slow disk...
You should add another disk just for the WAL -- disks are pretty cheap
these days.
On 15/01/2010 6:35 AM, Dave Crooke wrote:
I'll bite
1. In general, RAID-10 is the only suitable RAID configuration for a
database. The decision making comes in how many drives, and splitting
stuff up into LUNs (like putting pg_xlog on its own LUN).
The biggest problem with Postgres
Carlo Stonebanks wrote:
1) Which RAID level would you recommend
It looks like you stepped over a critical step, which is will the
server have a good performing RAID card?. Your whole upgrade could
underperform if you make a bad mistake on that part. It's really
important to nail that
2010/1/15 Pierre Frédéric Caillaud li...@peufeu.com:
On Thu, 14 Jan 2010 22:28:07 +0100, fka...@googlemail.com
fka...@googlemail.com wrote:
Pierre Frédéric Caillaud:
4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
Big CPU and slow disk...
You should add another disk just for the
Fernando Hevia wrote:
I justified my first choice in that WAL writes are sequentially and OS
pretty much are too, so a RAID 1 probably would hold ground against a
12 disc RAID 10 with random writes.
The problem with this theory is that when PostgreSQL does WAL writes and
asks to sync the
Pretty much everyone thinks their requirements are exceptional. It's
funny how infrequently that's actually true. The techniques that favor
index-use aren't that unique: collect better stats, set basic parameters
correctly, adjust random_page_cost, investigate plans that don't do what
you
55 matches
Mail list logo
