> On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> >> Take a look at the set of partitioning functions I wrote shortly
after
> >> the 8.1 release:
> >>
> >> http://www.studenter.hb.se/~arch/files/part_functions.sql
> >>
> &
Take a look at the set of partitioning functions I wrote shortly after
the 8.1 release:
http://www.studenter.hb.se/~arch/files/part_functions.sql
You could probably work something out using those functions (as-is, or
as inspiration) together with pgAgent
(http://www.pgadmin.org/docs/1.4/pgagent.h
That query is generated by hibernate, right?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Pallav
Kalva
Sent: den 15 september 2006 17:10
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Optimize SQL
Hi,
Is there anyway we can optimize this
> WRT seek performance, we're doing 2500 seeks per second on the
Sun/Thumper on 36 disks.
Luke,
Have you had time to run benchmarksql against it yet? I'm just curious
about the IO seeks/s vs. transactions/minute correlation...
/Mikael
---(end of broadcast)---
uke Lonergan [mailto:[EMAIL PROTECTED]
Sent: den 28 juli 2006 11:17
To: Mikael Carneholm; Kjell Tore Fossbakk;
pgsql-performance@postgresql.org
Subject: RE: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig
Mikael,
> -Original Message-
> From: Mikael Carneholm [mailto:[EMAIL PROTE
I would be interested in what numbers you would get out of bonnie++
(http://www.coker.com.au/bonnie++) and BenchmarkSQL
(http://sourceforge.net/projects/benchmarksql) on that hardware, for
comparison with our DL385 (2xOpteron 280, 16Gb ram) and MSA1500. If you
need help building benchmarksql, I can
> This is a relatively low end HBA with 1 4Gb FC on it. Max sustained
IO on it is going to be ~320MBps. Or ~ enough for an 8 HD RAID 10 set
made of 75MBps ASTR HD's.
Looking at http://h30094.www3.hp.com/product.asp?sku=2260908&extended=1,
I notice that the controller has a Ultra160 SCSI interfac
>Unless I'm missing something, the only FC or SCSI HDs of ~147GB capacity are
>15K, not 10K.
In the spec we got from HP, they are listed as model 286716-B22
(http://www.dealtime.com/xPF-Compaq_HP_146_8_GB_286716_B22) which seems to run
at 10K. Don't know how old those are, but that's what we go
>> This is something I'd also would like to test, as a common
>> best-practice these days is to go for a SAME (stripe all, mirror
everything) setup.
>> From a development perspective it's easier to use SAME as the
>> developers won't have to think about physical location for new
>> tables/indice
>I think the main difference is that the WAL activity is mostly linear,
where the normal data activity is rather random access.
That was what I was expecting, and after reading
http://www.pcguide.com/ref/hdd/perf/raid/concepts/perfStripe-c.html I
figured that a different stripe size for the WAL s
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Michael
Stone
Sent: den 17 juli 2006 02:04
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] RAID stripe size question
On Mon, Jul 17, 2006 at 12:52:17AM +0200, Mikael Carneholm wrote:
>I have final
Title: RAID stripe size question
I have finally gotten my hands on the MSA1500 that we ordered some time ago. It has 28 x 10K 146Gb drives, currently grouped as 10 (for wal) + 18 (for data). There's only one controller (an emulex), but I hope performance won't suffer too much from that. Raid
> For my application there is very little info I can share. Maybe less
than 10 on 100 actually so I not sure it worth it ...
Ok, so 90% of the tables are being written to - this either means that
your application uses very little constants, or that it has access to
constans that are stored somewh
Do you really need to create one *DB* per client - that is, is one
schema (in the same DB) per client out of the question? If not, I would
look into moving all reference tables (read-only data, constants and
such) into a common schema (with read permission granted to each
client/role), that way red
We've seen similar results with our EMC CX200 (fully equipped) when
compared to a single (1) SCSI disk machine. For sequential reads/writes
(import, export, updates on 5-10 30M+ row tables), performance is
downright awful. A big DB update took 5-6h in pre-prod (single SCSI),
and 10-14?h (don't reca
What filesystem are you using - ext2/etx3/xfs/jfs/...? Does the SCSI
controller have a battery backed cache? For ext3, mounting it with
data=writeback should give you quite a boost in write performance.
What benchmark tool are you using - is it by any chance BenchmarkSQL?
(since you mention that i
>My server is the HP DL585 (quad, dual-core Opteron, 16GB RAM) with 4 HD
bays run by a HP SmartArray 5i controller. I have 15 10K 300GB >drives
and 1 15K 150GB drive (don't ask how that happened).
Our server will be a DL385 (dual, dual-core Opteron, 16Gb RAM), and the
28 disks(10K 146Gb)in the MS
>FWIW, my own experiments with tests like this suggest that PG is at
worst about 2x slower than mysql for trivial queries. If you'd reported
a result in that ballpark I'd have accepted it as probably real. 6x I
don't believe though ...
OTOH, my tests using BenchmarkSQL
(http://sourceforge.net/pr
vantage of battery backed write cache...
Regards,
Mikael
-Original Message-
From: Guoping Zhang [mailto:[EMAIL PROTECTED]
Sent: den 28 april 2006 07:35
To: Mikael Carneholm; pgsql-performance@postgresql.org
Cc: Guoping Zhang (E-mail)
Subject: RE: [PERFORM] how unsafe (or worst scena
Get a SCSI controller with a battery backed cache, and mount the disks
with data=writeback (if you use ext3). If you loose power in the middle
of a transaction, the battery will ensure that the write operation still
completes. With asynch writing setup like this, fsync operations will
return almost
> If I'm reading the original post correctly, the biggest issue is
> likely to be that the 14 disks on each 2Gbit fibre channel will be
> throttled to 200Mb/s by the channel , when in fact you could expect
> (in RAID 10
> arrangement) to get about 7 * 70 Mb/s = 490 Mb/s.
> The two controllers a
MAIL PROTECTED] On Behalf Of
Alex Hayward
Sent: den 21 april 2006 17:25
To: Mikael Carneholm
Cc: Pgsql performance
Subject: Re: [PERFORM] Hardware: HP StorageWorks MSA 1500
On Thu, 20 Apr 2006, Mikael Carneholm wrote:
> We're going to get one for evaluation next week (equipped with dual
&
We're going to get one for evaluation next week (equipped with dual
2Gbit HBA:s and 2x14 disks, iirc). Anyone with experience from them,
performance wise?
Regards,
Mikael
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
>This should be fixed by the changes I made recently in
choose_bitmap_and
>--- it wasn't being aggressive about pruning overlapping AND conditions
when a sub-OR was involved. It's possible the new coding is >*too*
aggressive, and will reject indexes that it'd be profitable to include;
but at least
Since my first post in this series, data has been successfully exported
from the proprietary db (in CSV format) and imported into postgres
(PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.4
20050721 (Red Hat 3.4.4-2)) using
COPY. The tablespace holding the tables+indexes is no
s weekend, if I can spare some time.
- Mikael
-Original Message-
From: Matthew T. O'Connor [mailto:[EMAIL PROTECTED]
Sent: den 28 mars 2006 00:43
To: Mikael Carneholm
Cc: Postgresql Performance
Subject: Re: [PERFORM] count(*) performance
Mikael Carneholm wrote:
> This is where
This is where a "last_vacuumed" (and "last_analyzed") column in
pg_statistic(?) would come in handy. Each time vacuum or analyze has
finished, update the row for the specific table that was
vacuumed/analyzed with a timestamp in the last_vacuumed/last_analyzed
column. No more guessing "maybe I haven
6 13:55
To: Mikael Carneholm
Cc: Simon Riggs; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Migration study, step 1: bulk write
performanceoptimization
On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
> It does ("LOG: checkpoints are occurring too frequentl
>>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
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
Ok, here's the deal:
I am responisble for an exciting project of evaluating migration of a
medium/large application for a well-known swedish car&truck manufacturer from a
proprietary DB to Postgres. The size of the database is currently about 50Gb,
annual growth depending on sales, but probably
31 matches
Mail list logo
