"Craig A. James" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Given the sizes of the tables involved, you'd likely have to boost up
>> work_mem before the planner would consider a hash join. What nondefault
>> configuration settings do you have, anyway?
> shared_buffers = 2
> work_mem = 3
It's time to build a new white box postgresql test box/workstation. My Athlon
XP system is getting a little long in the tooth. Have any of you performance
folks evaluated the Socket 939 boards on the market these days? I'd like to
find something that doesn't have terrible SATA disk performanc
On Mar 21, 2006, at 12:59 PM, Jim C. Nasby wrote:
atapci1:
And note that this is using FreeBSD gmirror, not the built-in raid
controller.
I get similar counter-intuitive slowdown with gmirror SATA disks on
an IBM e326m I'm evaluating. If/when I buy one I'll get the onboard
SCSI RAID in
Tom Lane wrote:
"Craig A. James" <[EMAIL PROTECTED]> writes:
It looks to me like the problem is the use of nested loops when a hash
join should be used, but I'm no expert at query planning.
Given the sizes of the tables involved, you'd likely have to boost up
work_mem before the planner would
"Craig A. James" <[EMAIL PROTECTED]> writes:
> It looks to me like the problem is the use of nested loops when a hash join
> should be used, but I'm no expert at query planning.
Given the sizes of the tables involved, you'd likely have to boost up
work_mem before the planner would consider a hash
I'm reposting this -- I sent this out a month ago but never got a response, and
hope someone can shed some light on this.
Thanks,
Craig
--
This is a straightforward query that should be fairly quick, but takes about 30
minutes. It's a query across three tables, call t
On Tue, 2006-03-21 at 15:56, Tom Lane wrote:
> Scott Marlowe <[EMAIL PROTECTED]> writes:
> > I've also found that modest increases in commit_siblings and
> > commit_delay help a lot on certain types of imports.
>
> On a data import? Those really should have zero effect on a
> single-process workl
Scott Marlowe <[EMAIL PROTECTED]> writes:
> I've also found that modest increases in commit_siblings and
> commit_delay help a lot on certain types of imports.
On a data import? Those really should have zero effect on a
single-process workload. Or are you doing multiple concurrent imports?
Ron wrote:
> IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of
> 64 - 256 is best for checkpoint_segments as long as you have the RAM
> available.
I think you are confusing checkpoint_segments with wal_buffers.
checkpoint_segments certainly has little to do with available RA
On Mon, 2006-03-20 at 15:17, Ron wrote:
> At 03:44 PM 3/21/2006, Simon Riggs wrote:
> >On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
> >
> > > This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off),
> > compared to the raw dd result (~75.5Mb/s).
> > >
> > > I assume this differe
At 03:44 PM 3/21/2006, Simon Riggs wrote:
On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
> This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off),
compared to the raw dd result (~75.5Mb/s).
>
> I assume this difference is due to:
> - simultaneous WAL write activity (assumed:
On Fri, 2006-03-17 at 11:09 +0100, Guillaume Cottenceau wrote:
> INFO: index "idx_sent_msgs_date_theme_status" now contains 3692284 row
> versions in 88057 pages
> SET effective_cache_size = 1;
SET effective_cache_size > 88057, round up to 10
to ensure the index cost calculation knows
On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
> This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off), compared to the
> raw dd result (~75.5Mb/s).
>
> I assume this difference is due to:
> - simultaneous WAL write activity (assumed: for each byte written to the
> table, at
On Tue, 2006-03-21 at 06:22 -0600, Jim C. Nasby wrote:
> Currently, it appears that SELECT * INTO new_table FROM old_table logs
> each page as it's written to WAL. Is this actually needed? Couldn't the
> database simply log that the SELECT ... INTO statement was executed
> instead? Doing so would l
On Mar 21, 2006, at 2:04 PM, PFC wrote:
especially since I have desktop PCI and the original poster has a
real server with PCI-X I think.
that was me :-)
but yeah, I never seem to get full line speed for some reason. i
don't know if it is because of inadequate measurement tools or what..
Last month I 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 re-ran Gary's test script using the just-committed improvements to
qsort.c, and got pretty nice numbers (attached --- compare to
h
On Wed, 22 Mar 2006, Mark Kirkwood wrote:
Adam Witney wrote:
[EMAIL PROTECTED]:43]~:15>sudo diskinfo -vt /dev/mirror/gm0
Can anyone point me to where I can find diskinfo or an equivalent to run on
my debian system, I have been googling for the last hour but can't find it!
I would like to an
Adam Witney wrote:
[EMAIL PROTECTED]:43]~:15>sudo diskinfo -vt /dev/mirror/gm0
Can anyone point me to where I can find diskinfo or an equivalent to run on
my debian system, I have been googling for the last hour but can't find it!
I would like to analyse my own disk setup for comparison
Expensive SCSI hardware RAID cards with expensive 10Krpm harddisks
should not get humiliated by such a simple (and cheap) setup. (I'm
referring to the 12-drive RAID10 mentioned before, not the other one
which was a simple 2-disk mirror). Toms hardware benchmarked some
hardware RAIDs and
Currently, it appears that SELECT * INTO new_table FROM old_table logs
each page as it's written to WAL. Is this actually needed? Couldn't the
database simply log that the SELECT ... INTO statement was executed
instead? Doing so would likely result in a large performance improvement
in most install
On Tue, 21 Mar 2006, Jim C. Nasby wrote:
> On Tue, Mar 21, 2006 at 11:03:26PM +1200, Mark Kirkwood wrote:
> >
> > So its really all about accounting, in a sense - whether pages end up in
> > the 'Buf' or 'Inactive' queue, they are still cached!
>
> So what's the difference between Buf and Active t
Jim,
On 3/21/06 3:49 AM, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> No, I don't know why the transfer rates for the mirror are 1/2 that as the raw
> device. :(
Well - lessee. Would those drives be attached to a Silicon Image (SII) SATA
controller? A Highpoint?
I found in testing about 2 year
On Mar 20, 2006, at 6:27 PM, PFC wrote:
Expensive SCSI hardware RAID cards with expensive 10Krpm harddisks
should not get humiliated by such a simple (and cheap) setup. (I'm
referring to the 12-drive RAID10 mentioned before, not the other
one which was a simple 2-disk mirror). Toms hardwa
On Tue, Mar 21, 2006 at 12:22:31PM +, Alex Hayward wrote:
> On Tue, 21 Mar 2006, Jim C. Nasby wrote:
>
> > On Tue, Mar 21, 2006 at 11:03:26PM +1200, Mark Kirkwood wrote:
> > >
> > > So its really all about accounting, in a sense - whether pages end up in
> > > the 'Buf' or 'Inactive' queue, th
On Mar 21, 2006, at 6:03 AM, Mark Kirkwood wrote:
The so-called limit (controllable via various sysctl's) is on the
amount of memory used for kvm mapped pages, not cached pages, i.e -
its a subset of the cached pages that are set up for immediate
access (the
Thanks... now that makes sens
Fernando,
If you need to read all the table for example it would be better to read
only the data pages instead of read data and index pages.
Reimer
- Original Message -
From: "Fernando Lujan" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, March 21, 2006 3:08 PM
Subject: [PERFORM] Sequence
Assuming you are joining on "Table 1".id = "Table 2".id - do you have indexes
on both columns? Have you analyzed your tables + indexes (are there statistics
available?) If not those criterias are met, it is unlikely that postgres will
choose an index scan.
-Original Message-
From: [EMAI
On Tue, Mar 21, 2006 at 03:08:07PM -0300, Fernando Lujan wrote:
> I'm trying to figure out when Sequence Scan is better than Index Scan. I
> just want to know this because I disabled the sequence scan in
> postgresql and receive a better result. :)
That is a very broad question, and you're intro
Hi guys,
I'm trying to figure out when Sequence Scan is better than Index Scan. I
just want to know this because I disabled the sequence scan in
postgresql and receive a better result. :)
Two tables.
Table 1 (1 million rows )
---
id
text
table2_id
Table 2 (300 thousand rows)
---
On Tue, Mar 21, 2006 at 07:25:07AM -0800, Luke Lonergan wrote:
> Jim,
>
> On 3/21/06 3:49 AM, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
>
> > No, I don't know why the transfer rates for the mirror are 1/2 that as the
> > raw
> > device. :(
>
> Well - lessee. Would those drives be attached to a
On Tue, Mar 21, 2006 at 02:03:19PM +0100, Guillaume Cottenceau wrote:
> "Jim C. Nasby" writes:
>
> > On Tue, Mar 21, 2006 at 10:40:45PM +1200, Mark Kirkwood wrote:
> > > I was going to recommend higher - but not knowing what else was running,
> > > kept it to quite conservative :-)... and given
On Tue, Mar 21, 2006 at 02:30:22PM +0100, Guillaume Cottenceau wrote:
> "Jim C. Nasby" writes:
>
> > If you feel like running some tests, you need to change
> >
> > run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
> >
> > in src/backend/optimizer/path/costsize.c to somethin
On Tue, 2006-03-21 at 06:46, Edoardo Serra wrote:
> Hi all,
> I'm having a very strange performance
> problems on a fresh install of postgres 8.1.3
> I've just installed it with default option and
> --enable-thread-safety without tweaking config files yet.
>
> The import of a small SQL
On Tue, Mar 21, 2006 at 09:12:08AM -0500, Merlin Moncure wrote:
> > > design 1 is normalized and better
> > > design 2 is denormalized and a bad approach no matter the RDBMS
> >
> > How is design 1 denormalized?
>
> It isn't :)...he said it is normalized. Design 2 may or may not be
> de-normalize
On 3/16/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> Can you try strace'ing some of the backend processes while the system is
> behaving like this? I suspect what you'll find is a whole lot of
> delaying select() calls due to high contention for spinlocks ...
As announced, we have migrated our produ
On Mon, Mar 20, 2006 at 11:12:34AM -0800, Orion Henry wrote:
>
> I have to say I've been really impressed with the quality and diversity
> of tools here to increase performance for PostgreSQL. But I keep seeing
> a lot of the same basic things repeated again and again. Has anyone
> looked int
On Tue, Mar 21, 2006 at 01:10:32PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
> > Are you sure? Metadate changes are probably a lot more common on the WAL
> > partition. In any case, I don't see why there should be a difference.
> > The real i
On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
> Are you sure? Metadate changes are probably a lot more common on the WAL
> partition. In any case, I don't see why there should be a difference.
> The real issue is: is related filesystem metadata sync'd as part of a
> file being fsync
The low end server by chance doesn't have an IDE disk that lies about
write completion, or a battery backed disk controller? Try disabling
fsync on the new server to get comparable figures.
Markus Bertheau
2006/3/21, Edoardo Serra <[EMAIL PROTECTED]>:
> Hi all,
> I'm having a very strang
On Tue, Mar 21, 2006 at 11:13:06AM +0100, Guillaume Cottenceau wrote:
> "Jim C. Nasby" writes:
>
> [...]
>
> > > My point is that the planner's cost estimate is way above the
> > > actual cost of the query, so the planner doesn't use the best
> > > plan. Even if the index returns so much rows, a
> For the record, that's the wrong way round. For the data partitioning
> metadata journaling is enough, and for the WAL partition you don't need any
> FS journaling at all.
Yes, you're right: the data partition shouldn't loose file creation,
deletion, etc., which is not important for the WAL part
On Tue, Mar 21, 2006 at 12:52:46PM +0100, Csaba Nagy wrote:
> They knew however that for the data partitions no FS journaling is
> needed, and for the WAL partition meta data journaling is enough, so I
> guess they tuned ext3 for this.
For the record, that's the wrong way round. For the data parti
On Mon, Mar 20, 2006 at 01:27:56PM -0800, Luke Lonergan wrote:
> >> Transfer rates:
> >>outside: 102400 kbytes in 2.075984 sec =49326 kbytes/sec
> >>middle:102400 kbytes in 2.100510 sec =48750 kbytes/sec
> >>inside:102400 kbytes in 2.04231
On Tue, Mar 21, 2006 at 10:40:45PM +1200, Mark Kirkwood wrote:
> I was going to recommend higher - but not knowing what else was running,
> kept it to quite conservative :-)... and given he's running java, the
> JVM could easily eat 512M all by itself!
Oh, didn't pick up on java being in the mix
> Did you try mounting ext3 whith data=writeback by chance? People have
> found that makes a big difference in performance.
I'm not sure, there's other people here doing the OS stuff - I'm pretty
much ignorant about what "data=writeback" could mean :-D
They knew however that for the data partitio
On Tue, Mar 21, 2006 at 11:03:26PM +1200, Mark Kirkwood wrote:
> Jim C. Nasby wrote:
> >On Mon, Mar 20, 2006 at 02:15:22PM -0500, Vivek Khera wrote:
> >
> >>I think FreeBSD has a hard upper limit on the total ram it will use
> >>for disk cache. I haven't been able to get reliable, irrefutable,
On Mon, Mar 20, 2006 at 09:59:54PM -0800, Amit Soni wrote:
> Hi All,
>
> I want to compare performance of postgresql database with some other
> database.
>
> Somebody must have done some performance testing.
>
> Can you pls. share that data (performance figures) with me? And if
On Tue, Mar 21, 2006 at 12:56:18PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 12:52:46PM +0100, Csaba Nagy wrote:
> > They knew however that for the data partitions no FS journaling is
> > needed, and for the WAL partition meta data journaling is enough, so I
> > guess they tuned
> > design 1 is normalized and better
> > design 2 is denormalized and a bad approach no matter the RDBMS
>
> How is design 1 denormalized?
It isn't :)...he said it is normalized. Design 2 may or may not be
de-normalized (IMO there is not enough information to make that
determination) but as stat
Edoardo Serra writes:
> Hi all,
> I'm having a very strange performance problems on a fresh
> install of postgres 8.1.3
> I've just installed it with default option and --enable-thread-safety
> without tweaking config files yet.
>
> The import of a small SQL files into the DB (6 tables
On 3/21/06, Amit Soni <[EMAIL PROTECTED]> wrote:
> I want to compare performance of postgresql database with some other
> database.
>
> Somebody must have done some performance testing.
>
> Can you pls. share that data (performance figures) with me? And if possibleu
> pls. share procedure also, tha
"Jim C. Nasby" writes:
> If you feel like running some tests, you need to change
>
> run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
>
> in src/backend/optimizer/path/costsize.c to something like
>
> run_cost += max_IO_cost + abs(indexCorrelation) * (min_IO_cost -
>
> [EMAIL PROTECTED]:43]~:15>sudo diskinfo -vt /dev/mirror/gm0
Can anyone point me to where I can find diskinfo or an equivalent to run on
my debian system, I have been googling for the last hour but can't find it!
I would like to analyse my own disk setup for comparison
Thanks for any help
Ad
"Jim C. Nasby" writes:
> On Tue, Mar 21, 2006 at 10:40:45PM +1200, Mark Kirkwood wrote:
> > I was going to recommend higher - but not knowing what else was running,
> > kept it to quite conservative :-)... and given he's running java, the
> > JVM could easily eat 512M all by itself!
>
> Oh, di
On Tue, Mar 21, 2006 at 06:18:39AM -0600, Jim C. Nasby wrote:
> Basically, you need to know for certain that if PostgreSQL creates a
> file and then fsync's it that that file is safely on disk, and that the
> filesystem knows how to find it (ie: the metadata is also on disk in
> some fashion).
It
Hi all,
I'm having a very strange performance
problems on a fresh install of postgres 8.1.3
I've just installed it with default option and
--enable-thread-safety without tweaking config files yet.
The import of a small SQL files into the DB (6
tables with 166.500 total records, INSERT
On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
On Tue, Mar 21, 2006 at 12:56:18PM +0100, Steinar H. Gunderson wrote:
For the record, that's the wrong way round. For the data partitioning
metadata journaling is enough, and for the WAL partition you don't need any
FS journaling at a
On Tue, Mar 21, 2006 at 01:29:54PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 06:18:39AM -0600, Jim C. Nasby wrote:
> > Basically, you need to know for certain that if PostgreSQL creates a
> > file and then fsync's it that that file is safely on disk, and that the
> > filesystem k
On Mon, Mar 20, 2006 at 04:19:12PM +0100, Csaba Nagy wrote:
> What I can add from our experience: ext3 turned out lousy for our
> application, and converting to XFS made a quite big improvement for our
> DB load. I don't have hard figures, but I think it was some 30%
> improvement in overall speed,
On Mon, Mar 20, 2006 at 08:38:15PM -0500, Jaime Casanova wrote:
> On 3/20/06, Craig A. James <[EMAIL PROTECTED]> wrote:
> > Design 1:
> >create table a (
> > id integer,
> > frequently_updated integer);
> >
> >create table b(
> > id integer,
> > infrequently_updated_1 i
Jim C. Nasby wrote:
On Mon, Mar 20, 2006 at 02:15:22PM -0500, Vivek Khera wrote:
I think FreeBSD has a hard upper limit on the total ram it will use
for disk cache. I haven't been able to get reliable, irrefutable,
answers about it, though.
It does not. Any memory in the inactive queue i
Hi,
On Tuesday 21 March 2006 10:56, Marco Furetto wrote:
| ok, I enable query duration logging in postgresql.conf.
|
| where is the instruments for analyze the statistics queries executing on
| my db?
|
| Eg.: Number of query executing, total time for executing a single query,
| etc...
I don't k
Jim C. Nasby wrote:
On Mon, Mar 20, 2006 at 09:35:14AM +0100, Guillaume Cottenceau wrote:
shared_buffer = 12000
effective_cache_size = 25000
This would mean you are reserving 100M for Postgres to cache relation
pages, and informing the planner that it can expect ~200M available
from the disk b
On Mon, Mar 20, 2006 at 02:15:22PM -0500, Vivek Khera wrote:
> I think FreeBSD has a hard upper limit on the total ram it will use
> for disk cache. I haven't been able to get reliable, irrefutable,
> answers about it, though.
It does not. Any memory in the inactive queue is effectively your
On Mon, Mar 20, 2006 at 07:46:13PM +, Alex Hayward wrote:
> On Mon, 20 Mar 2006, Jim C. Nasby wrote:
>
> > No, this is perfectly fine. Inactive memory in FreeBSD isn't the same as
> > Free. It's the same as 'active' memory except that it's pages that
> > haven't been accessed in X amount of ti
"Jim C. Nasby" writes:
[...]
> > My point is that the planner's cost estimate is way above the
> > actual cost of the query, so the planner doesn't use the best
> > plan. Even if the index returns so much rows, actual cost of the
> > query is so that index scan (worst case, all disk cache flushe
On Tue, Mar 21, 2006 at 03:51:35PM +1200, Mark Kirkwood wrote:
> Mark Kirkwood wrote:
> >
> >I think Freebsd 'Inactive' corresponds pretty closely to Linux's
> >'Inactive Dirty'|'Inactive Laundered'|'Inactive Free'.
> >
>
> Hmmm - on second thoughts I think I've got that wrong :-(, since in
> Li
On Mon, Mar 20, 2006 at 08:03:14PM +0800, Qingqing Zhou wrote:
>
> ""Ksenia Marasanova"" <[EMAIL PROTECTED]> wrote
> >
> > The application uses persistant database connection, and when i check
> > the status of the connection, it shows: "idle in transaction". I am
> > pretty sure that every inser
I suspect you've found an issue with how the planner evaluates indexes
for bitmap scans. My guess is that that section of the planner needs to
be taught to look for partial indexes.
You should also try
cast(value as bigint) = 1009
The planner may be getting confused by the '1009'.
On Mon, Mar 2
On Sun, Mar 19, 2006 at 01:31:42PM +0100, Antoine wrote:
> Hi,
> Is there any work on the cards for implementing other partitioning
> strategies? I see mysql 5.1 will have support for hashes and stuff but
> didn't see anything in the todos for postgres.
You'd have to provide a pretty convincing ar
On Mon, Mar 20, 2006 at 09:14:32AM +0100, Guillaume Cottenceau wrote:
> Guillaume,
>
> Thanks for your answer.
>
> > On 17 Mar 2006 11:09:50 +0100, Guillaume Cottenceau
> > wrote:
> > > Reading the documentation and postgresql list archives, I have
> > > run ANALYZE right before my tests, I have
ok, I enable query duration logging in postgresql.conf.
where is the instruments for analyze the statistics queries executing on
my db?
Eg.: Number of query executing, total time for executing a single query,
etc...
Thank's
Marco "Furetto" Berri
Thomas Pundt wrote:
Hi,
On Tuesday 21
On Mon, Mar 20, 2006 at 09:35:14AM +0100, Guillaume Cottenceau wrote:
> > shared_buffer = 12000
> > effective_cache_size = 25000
> >
> > This would mean you are reserving 100M for Postgres to cache relation
> > pages, and informing the planner that it can expect ~200M available
> > from the disk b
Hi,
On Tuesday 21 March 2006 09:25, Marco Furetto wrote:
| I'm managing the db of a "Content Management environment" and I'm
| searching for a "Query analyzer" to improve performance because i don't
| know how many and what type of queries are executing on the system (for
| the "where and join" bl
Hello!
I'm managing the db of a "Content Management environment" and I'm
searching for a "Query analyzer" to improve performance because i don't
know how many and what type of queries are executing on the system (for
the "where and join" block).
If i could have query's stats i could Optimize the
75 matches
Mail list logo
