On Tue, Sep 12, 2006 at 03:33:08PM -0400, [EMAIL PROTECTED] wrote:
Hello All
I am getting this message in my log files for my database.
LOG: out of file descriptors: Too many open files; release and retry.
At some point the memomy didn't get released and the postmaster reset itself
On Tue, Sep 12, 2006 at 04:17:34PM -0600, Joshua Marsh wrote:
data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
s.dsiacctno;
I have had extremely bad performance historically with onboard SATA chipsets
on Linux. The one exception has been with the Intel based chipsets (not the
CPU, the I/O chipset).
This board has Intel chipset. I cannot remember the exact type but it
was not in the low end category.
dmesg
On 13-Sep-06, at 6:16 AM, Laszlo Nagy wrote:
I have had extremely bad performance historically with onboard
SATA chipsets
on Linux. The one exception has been with the Intel based
chipsets (not the
CPU, the I/O chipset).
This board has Intel chipset. I cannot remember the exact type
Hi
I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux.
Some of the insert tests seems to be ver slow
For example: select_join_in
Are there any tuning parameters that can be changed to speed these queries? Or
are these queries
especially tuned to show MySQL's stgrenths?
All of the tuning parameters would affect all queries
shared buffers, wal buffers, effective cache, to name a few
--dc--
On 13-Sep-06, at 8:24 AM, yoav x wrote:
Hi
I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux.
Some of the insert tests seems to be ver slow
For example:
So why are these queries so slow in PG?
--- Dave Cramer [EMAIL PROTECTED] wrote:
All of the tuning parameters would affect all queries
shared buffers, wal buffers, effective cache, to name a few
--dc--
On 13-Sep-06, at 8:24 AM, yoav x wrote:
Hi
I am trying to run sql-bench
First of all you are going to have to show use what these queries are
exactly, what the machine is you are running on (CPU, memory, and
disk) , and how you have tuned it.
slow is a relative term.. we need information to determine what
slow means.
Dave
On 13-Sep-06, at 8:50 AM, yoav x
The last I checked (years ago), sql-bench was very synthetic (i.e.
reflecting no realistic use case). It's the sort of test suite that's
useful for database developers when testing the effects of a particular
code change or optimization, but not so applicable to real-world uses.
Historically
On 9/13/06, Jim C. Nasby [EMAIL PROTECTED] wrote:
On Tue, Sep 12, 2006 at 04:17:34PM -0600, Joshua Marsh wrote:
data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
FROM s.custdate) FROM view_505 v INNER JOIN
yoav x [EMAIL PROTECTED] writes:
Are there any tuning parameters that can be changed to speed these
queries? Or are these queries especially tuned to show MySQL's
stgrenths?
The latter. I've ranted about this before --- there are both obvious
and subtle biases in that benchmark. The last
On Wed, 2006-09-13 at 08:49 -0600, Joshua Marsh wrote:
That seems to have done it. Are there any side effects to this
change? I read about random_page_cost in the documentation and it
seems like this is strictly for planning. All the tables on this
database will be indexed and of a size
On 9/13/06, Jeff Davis [EMAIL PROTECTED] wrote:
On Wed, 2006-09-13 at 08:49 -0600, Joshua Marsh wrote:
That seems to have done it. Are there any side effects to this
change? I read about random_page_cost in the documentation and it
seems like this is strictly for planning. All the tables
On Wed, 2006-09-13 at 10:19 -0600, Joshua Marsh wrote:
Right, it's just used for planning. Avoid setting it too low, if it's
below about 2.0 you would most likely see some very strange plans.
Certainly it doesn't make sense at all to set it below 1.0, since that
is saying it's cheaper to
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Setting to 0.1 finally gave me the result I was looking for. I know
that the index scan is faster though. The seq scan never finished (i
killed it after 24+ hours) and I'm running the query now with indexes
and it's progressing nicely (will probably take 4 hours).
In regards to progressing
Is there anything I'm missing that is preventing it from using the index?
It
just seems weird to me that other joins like this work fine and fast
with indexes,
but this one won't.
Did You consider clustering both tables on the dsiacctno index?
I just checked that for a 4M rows table even
On 9/13/06, Bucky Jordan [EMAIL PROTECTED] wrote:
Setting to 0.1 finally gave me the result I was looking for. I know
that the index scan is faster though. The seq scan never finished (i
killed it after 24+ hours) and I'm running the query now with indexes
and it's progressing nicely (will
Hmm... that sounds bad. I'm sure your system will always choose indexes
with that value.
Is it overestimating the cost of using indexes or underestimating the
cost of a seq scan, or both? Maybe explain with the 0.1 setting will
help?
Regards,
Jeff Davis
data=# explain SELECT
On 9/13/06, Tom Lane [EMAIL PROTECTED] wrote:
IIRC, with these settings PG 8.0 seemed to be about half the speed of
mysql 5.0 w/myisam, which is probably somewhere in the ballpark of the
truth for tests of this nature, ie, single query stream of fairly simple
queries. If you try
Jeff Davis wrote:
Is it overestimating the cost of using indexes or underestimating the
cost of a seq scan, or both? Maybe explain with the 0.1 setting will
help?
If enable_seqscan is off, and cost is still set to 1, it could
be that it's quite simply forcibly underestimating the
On 9/13/06, Terje Elde [EMAIL PROTECTED] wrote:
Jeff Davis wrote:
Is it overestimating the cost of using indexes or underestimating the
cost of a seq scan, or both? Maybe explain with the 0.1 setting will
help?
If enable_seqscan is off, and cost is still set to 1, it could
be that
Merlin Moncure [EMAIL PROTECTED] writes:
another small aside, I caught the sqlite people actually *detuning*
postgresql for performance by turning stats_command_string=on in
postgresql.conf.
Hm, well, that's not unreasonable if a comparable facility is enabled
in the other databases they're
http://www.postgresql.org/community/lists/
http://www.postgresql.org/community/lists/subscribe
--
Until later, Geoffrey
Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin
---(end of
Joshua Marsh [EMAIL PROTECTED] writes:
I have a suspision that pgsql isn't tuned to properly deal with tables
of this size.
Actually, it is. Most of the planner complaints we get are from people
whose tables fit in memory and they find that the default planner
behavior doesn't apply real well
On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote:
On 9/13/06, Tom Lane [EMAIL PROTECTED] wrote:
IIRC, with these settings PG 8.0 seemed to be about half the speed of
mysql 5.0 w/myisam, which is probably somewhere in the ballpark of the
truth for tests of this nature, ie, single query
Are the tables perhaps nearly in order by the dsiacctno fields?
If that were the case, and the planner were missing it for some reason,
these results would be plausible.
BTW, what are you using for work_mem, and how does that compare to your
available RAM?
regards, tom
[EMAIL PROTECTED] wrote:
This board has Intel chipset. I cannot remember the exact type but it
was not in the low end category.
dmesg says:
Intel ICH7 SATA300 controller
kernel: ad4: 152626MB SAMSUNG HD160JJ ZM100-33 at ata2-master SATA150
kernel: ad4: 152627MB SAMSUNG HD160JJ ZM100-33 at
Joshua Marsh [EMAIL PROTECTED] writes:
Are the tables perhaps nearly in order by the dsiacctno fields?
My assumption would be they are in exact order. The text file I used
in the COPY statement had them in order, so if COPY preserves that in
the database, then it is in order.
Ah. So the
Scott Marlowe [EMAIL PROTECTED] writes:
On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote:
another small aside, I caught the sqlite people actually *detuning*
postgresql for performance by turning stats_command_string=on in
postgresql.conf.
They're running autovacuum, which requires that,
Joshua Marsh [EMAIL PROTECTED] writes:
On 9/13/06, Tom Lane [EMAIL PROTECTED] wrote:
Are the tables perhaps nearly in order by the dsiacctno fields?
My assumption would be they are in exact order. The text file I used
in the COPY statement had them in order, so if COPY preserves that in
On 9/14/06, Scott Marlowe [EMAIL PROTECTED] wrote:
On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote:
another small aside, I caught the sqlite people actually *detuning*
postgresql for performance by turning stats_command_string=on in
postgresql.conf. The way it was portrayed it almost
32 matches
Mail list logo
