i'm using Solaris8
i tried changing only postgresql parameters
and time has increased of 10mn
i keep in mind your idea, we will soon upgraded to solaris 10
regards
Will
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Envoyé : mardi 14 février 2006 22:47
À
Pradeep Parmar wrote:
Hi,
I'm using Postgres 7.4. I have a web application built with php4 using
postgres7.4
I was going through /var/log/messages of my linux box ( SLES 9). I
encountered the following messages quite a few times.
postgres[20199]: [4-1] ERROR: could not send data to client:
Hi, Ferreira,
FERREIRA, William (VALTECH) wrote:
i load data from files using copy method.
Files contain between 2 and 7 millions of rows, spread on 5 tables.
For loading all the data, it takes 40mn, and the same processing takes 17mn
with Oracle.
I think that this time can be improved
What's your postgresql.conf parameter for the equivalent ones that I
suggested?
I believe your wal_buffers and checkpoint_segments could be bigger. If
that's the case then yep you are fine.
As for the background writer I am seeing mixed results yet so not sure
about that.
But thanks for the
with PostgreSQL 8.1.3, here are my parameters (it's the default configuration)
wal_sync_method = fsync
wal_buffers = 8
checkpoint_segments = 3
bgwriter_lru_percent = 1.0
bgwriter_lru_maxpages = 5
bgwriter_all_percent = 0.333
bgwriter_all_maxpages = 5
and you think times can be improved again ?
Actually fsync is not the default on solaris (verify using show all;)
(If you look closely in postgresql.conf it is commented out and
mentioned as default but show all tells a different story)
In all my cases I saw the default as
wal_sync_method | open_datasync
Also I had
FERREIRA, William (VALTECH) [EMAIL PROTECTED] writes:
with PostgreSQL 8.1.3, here are my parameters (it's the default configuration)
wal_sync_method = fsync
wal_buffers = 8
checkpoint_segments = 3
bgwriter_lru_percent = 1.0
bgwriter_lru_maxpages = 5
bgwriter_all_percent = 0.333
Hi!
I have a question about the query optimizer and the function scan. See
the next case:
CREATE TABLE a (id SERIAL PRIMARY KEY, userid INT4, col TEXT);
CREATE TABLE b (id SERIAL PRIMARY KEY, userid INT4, a_id INT4 REFERENCES
a (id), col TEXT);
CREATE INDEX idx_a_uid ON a(userid);
CREATE
Good morning,
I've increased sort_mem until 2Go !!
and the error out of memory appears again.
Here the request I try to pass with her explain plan,
Nested Loop (cost=2451676.23..2454714.73 rows=1001 width=34)
- Subquery Scan day (cost=2451676.23..2451688.73 rows=1000 width=16)
Good morning,
I try to understand how optimizer uses HashAggregate instead of GroupAggregate
and I want to know what is exactly this two functionnality (benefits
/inconvenients)
In my case, I've this explain plan.
---
Nested Loop (cost=2451676.23..2454714.73 rows=1001
We are a small company looking to put together the most cost effective
solution for our production database environment. Currently in
production Postgres 8.1 is running on this machine:
Dell 2850
2 x 3.0 Ghz Xeon 800Mhz FSB 2MB Cache
4 GB DDR2 400 Mhz
2 x 73 GB 10K SCSI RAID 1 (for xlog and OS)
You're right, release is 7.4.7.
there's twenty millions records query
On Tue, 2006-02-14 at 11:36, Tom Lane wrote:
[EMAIL PROTECTED] writes:
Yes, I've launched ANALYZE command before sending request.
I precise that's postgres version is 7.3.4
Can't possibly be 7.3.4, that version
On Wed, 2006-02-15 at 09:55, [EMAIL PROTECTED] wrote:
Good morning,
I've increased sort_mem until 2Go !!
and the error out of memory appears again.
Here the request I try to pass with her explain plan,
Nested Loop (cost=2451676.23..2454714.73 rows=1001 width=34)
- Subquery
Here the result with hashAgg to false :
Nested Loop (cost=2487858.08..2490896.58 rows=1001 width=34) (actual
time=1028044.781..1030251.260 rows=1000 loops=1)
- Subquery Scan day (cost=2487858.08..2487870.58 rows=1000 width=16)
(actual time=1027996.748..1028000.969 rows=1000 loops=1)
Jeremy Haile wrote:
We are a small company looking to put together the most cost effective
solution for our production database environment. Currently in
production Postgres 8.1 is running on this machine:
Dell 2850
2 x 3.0 Ghz Xeon 800Mhz FSB 2MB Cache
4 GB DDR2 400 Mhz
2 x 73 GB 10K SCSI
Machine 1: $2000
Machine 2: $2000
Machine 3: $2000
Knowing how to rig them together and maintain them in a fully fault-
tolerant way: priceless.
(Sorry for the off-topic post, I couldn't resist).
-- Mark Lewis
On Wed, 2006-02-15 at 09:19 -0800, Craig A. James wrote:
Jeremy Haile wrote:
We
On Wed, 2006-02-15 at 11:18, [EMAIL PROTECTED] wrote:
Here the result with hashAgg to false :
Nested Loop (cost=2487858.08..2490896.58 rows=1001 width=34) (actual
time=1028044.781..1030251.260 rows=1000 loops=1)
- Subquery Scan day (cost=2487858.08..2487870.58 rows=1000 width=16)
Jeremy Haile wrote:
We are a small company looking to put together the most cost effective
solution for our production database environment. Currently in
production Postgres 8.1 is running on this machine:
Dell 2850
2 x 3.0 Ghz Xeon 800Mhz FSB 2MB Cache
4 GB DDR2 400 Mhz
2 x 73 GB 10K SCSI
Joshua D. Drake [EMAIL PROTECTED] writes:
Jeremy Haile wrote:
We are a small company looking to put together the most cost effective
solution for our production database environment. Currently in
production Postgres 8.1 is running on this machine:
Dell 2850
2 x 3.0 Ghz Xeon 800Mhz
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Joshua D. Drake)
belched out:
Jeremy Haile wrote:
We are a small company looking to put together the most cost effective
solution for our production database environment. Currently in
production Postgres 8.1 is running on this
On Wed, 2006-02-15 at 12:44, Christopher Browne wrote:
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Joshua D.
Drake) belched out:
You should probably review the archives for PostgreSQL user
experience with Dell's before you purchase one.
Hear, hear! We found Dell
At 11:21 AM 2/15/2006, Jeremy Haile wrote:
We are a small company looking to put together the most cost effective
solution for our production database environment. Currently in
production Postgres 8.1 is running on this machine:
Dell 2850
2 x 3.0 Ghz Xeon 800Mhz FSB 2MB Cache
4 GB DDR2 400 Mhz
Tom Lane wrote:
Interesting. I tried your test script and got fairly close times
for all the cases on two different machines:
old HPUX machine: shortest 5800 msec, longest 7960 msec
new Fedora 4 machine: shortest 461 msec, longest 608 msec
(the HPUX machine was doing other stuff
Tom Lane wrote:
shared_buffers is unlikely to impact index build time noticeably in
recent PG releases. maintenance_work_mem would affect it a lot, though.
What setting were you using for that?
Also, i tried upping maintenance_work_mem to 65536 and it didn't make
much difference (maybe 10%
On Wed, 2006-02-15 at 20:00 +, Gary Doades wrote:
I have put together a test case
Please enable trace_sort=on and then repeat tests and post the
accompanying log file.
I think this is simply the sort taking longer depending upon the data
distribution, but I'd like to know for sure.
I wrote:
Interesting. I tried your test script and got fairly close times
for all the cases on two different machines:
old HPUX machine: shortest 5800 msec, longest 7960 msec
new Fedora 4 machine: shortest 461 msec, longest 608 msec
So what this looks like to me is a corner case
I've been vacuuming between each test run.
Not vacuuming results in times all the way up to 121 minutes. For a direct
comparison with Access, the vacuuming time with Postgres should really be
included as this is not required with Access.
By removing all of the indexes I have been able to get
Tom Lane wrote:
I tried forcing PG to use src/port/qsort.c on the Fedora machine,
and lo and behold:
new Fedora 4 machine: shortest 434 msec, longest 8530 msec
So it sure looks like this script does expose a problem on BSD-derived
qsorts. Curiously, the case that's much the worst for
Jay Greenfield [EMAIL PROTECTED] writes:
I did a run with checkpoint_segments @ 30 (from 3 in 4.35 min run) and
posted a time of 6.78 minutes. Any idea why this would increase the time?
The first time through might take longer while the machine creates empty
xlog segment files (though I'd not
Simon Riggs [EMAIL PROTECTED] writes:
Please enable trace_sort=on and then repeat tests and post the
accompanying log file.
I did this on my Fedora machine with port/qsort.c, and got the results
attached. Curiously, this run has the spikes in completely different
places than the prior one did.
Jeremy Haile wrote:
Thanks for everyone's feedback. I will definitely take the hardware
comments into consideration when purchasing future hardware. I am
located in Atlanta, GA. If Dell has such a bad reputation with this
list, does anyone have good vendor recommendations?
I can
Gary Doades [EMAIL PROTECTED] writes:
Interestingly, if I don't delete the table after a run, but just drop
and re-create the index repeatedly it stays a pretty consistent time,
either repeatedly good or repeatedly bad!
This is consistent with the theory of a data-dependent performance
Tom Lane wrote:
So it sure looks like this script does expose a problem on BSD-derived
qsorts. Curiously, the case that's much the worst for me is the third
in the script, while the shortest time is the first case, which was slow
for Gary. So I'd venture that the *BSD code has been tweaked
Gary Doades [EMAIL PROTECTED] writes:
If I run the script again, it is not always the first case that is slow,
it varies from run to run, which is why I repeated it quite a few times
for the test.
For some reason I hadn't immediately twigged to the fact that your test
script is just N
Gary Doades [EMAIL PROTECTED] writes:
Is this likely to hit me in a random fashion during normal operation,
joins, sorts, order by for example?
Yup, anytime you're passing data with that kind of distribution
through a sort.
So the options are:
1) Fix the included qsort.c code and use that
This behavior is consistent with the pivot choosing algorithm
assuming certain distribution(s) for the data. For instance,
median-of-three partitioning is known to be pessimal when the data is
geometrically or hyper-geometrically distributed. Also, care must be
taken that sometimes is not
I wrote:
Gary Doades [EMAIL PROTECTED] writes:
Ouch! That confirms my problem. I generated the random test case because
it was easier than including the dump of my tables, but you can
appreciate that tables 20 times the size are basically crippled when it
comes to creating an index on
Ron [EMAIL PROTECTED] writes:
How are we choosing our pivots?
See qsort.c: it looks like median of nine equally spaced inputs (ie,
the 1/8th points of the initial input array, plus the end points),
implemented as two rounds of median-of-three choices. With half of the
data inputs zero, it's not
On Wed, 2006-02-15 at 20:00 +, Gary Doades wrote:
I have put together a test case that demonstrates the problem (see
below). I create a simple table, as close in structure to one of my
problem tables and populate an integer column with 100,000 zeros follow
by 100,000 random integers
Ouch! That confirms my problem. I generated the random test case because
it was easier than including the dump of my tables, but you can
appreciate that tables 20 times the size are basically crippled when it
comes to creating an index on them.
I have to say that I restored a few gigabyte
On Wed, 2006-02-15 at 19:59 -0500, Tom Lane wrote:
I get
amazingly stable runtimes now --- I didn't have the patience to run 100
trials, but in 30 trials I have slowest 11538 msec and fastest 11144 msec.
So this code path is definitely not very sensitive to this data
distribution.
The
On Wed, 2006-02-15 at 18:28 -0500, Tom Lane wrote:
It seems clear that our qsort.c is doing a pretty awful job of picking
qsort pivots, while glibc is mostly managing not to make that mistake.
I haven't looked at the glibc code yet to see what they are doing
differently.
glibc qsort is
At 08:21 PM 2/15/2006, Tom Lane wrote:
Ron [EMAIL PROTECTED] writes:
How are we choosing our pivots?
See qsort.c: it looks like median of nine equally spaced inputs (ie,
the 1/8th points of the initial input array, plus the end points),
implemented as two rounds of median-of-three choices.
43 matches
Mail list logo
