On 5/23/06, Dan Gorman [EMAIL PROTECTED] wrote:
In any other DB (oracle, mysql) I know how many queries (selects) per second
the database is executing. How do I get this
number out of postgres?
Mysql does AFAIR only count the number of queries and then uses the
seconds since startup to
I have a system that currently inserts ~ 250 million rows per day (I
have about 10k more raw data than that, but I'm at the limit of my
ability to get useful insert performance out of postgres).
Things I've already done that have made a big difference:
- modified postgresql.conf
If you can live with possible database corruption, you could try turning
Fsync off. For example if you could just reinsert the data on the off
chance a hardware failure corrupts the database, you might get a decent
improvement.
Also have you tried creating the index after you have inserted all
On May 24, 2006, at 4:02 PM, Dave Dutcher wrote:
If you can live with possible database corruption, you could try
turning
Fsync off. For example if you could just reinsert the data on the off
chance a hardware failure corrupts the database, you might get a
decent
improvement.
I tried,
On May 24, 2006, at 4:03 PM, Steinar H. Gunderson wrote:
Have you tried fiddling with the checkpointing settings? Check your
logs --
if you get a warning about checkpoints being too close together,
that should
give you quite some boost.
no warnings in the log (I did change the checkpoint
Daniel J. Luke wrote:
On May 24, 2006, at 4:02 PM, Dave Dutcher wrote:
If you can live with possible database corruption, you could try
turning Fsync off. For example if you could just reinsert the data
on the off chance a hardware failure corrupts the database, you
might get a decent
On Wed, May 24, 2006 at 04:09:54PM -0400, Daniel J. Luke wrote:
no warnings in the log (I did change the checkpoint settings when I
set up the database, but didn't notice an appreciable difference in
insert performance).
How about wal_buffers? Upping it might not help all that much if only
The data gets inserted in batches every 5 minutes and I potentially
have people querying it constantly, so I can't remove and re-create
the index.
How live does your data need to be? One possibility would be to use a
separate table for each batch instead of a separate table per day,
On May 24, 2006, at 4:13 PM, Steinar H. Gunderson wrote:
On Wed, May 24, 2006 at 04:09:54PM -0400, Daniel J. Luke wrote:
no warnings in the log (I did change the checkpoint settings when I
set up the database, but didn't notice an appreciable difference in
insert performance).
How about
On May 24, 2006, at 4:12 PM, Larry Rosenman wrote:
are the batches single insert's, or within a big transaction?
If the former, the latter is a big win.
One big transaction every 5 minutes using 'COPY FROM' (instead of
inserts).
Also, what release(s) are you running?
8.1.x (I think
Daniel J. Luke wrote:
On May 24, 2006, at 4:12 PM, Larry Rosenman wrote:
are the batches single insert's, or within a big transaction?
If the former, the latter is a big win.
One big transaction every 5 minutes using 'COPY FROM' (instead of
inserts).
Also, what release(s) are you
On May 24, 2006, at 4:24 PM, Larry Rosenman wrote:
Also, is pg_xlog on the same or different spindles from the rest of
the
PG Data directory?
It's sitting on the same disk array (but I'm doing 1 transaction
every 5 minutes, and I'm not near the array's sustained write
capacity, so I
On Wed, May 24, 2006 at 04:09:54PM -0400, Daniel J. Luke wrote:
On May 24, 2006, at 4:03 PM, Steinar H. Gunderson wrote:
Have you tried fiddling with the checkpointing settings? Check your
logs --
if you get a warning about checkpoints being too close together,
that should
give you quite
On Wed, May 24, 2006 at 12:27:41PM +0200, Robin Ericsson wrote:
On 5/23/06, Dan Gorman [EMAIL PROTECTED] wrote:
In any other DB (oracle, mysql) I know how many queries (selects) per
second
the database is executing. How do I get this
number out of postgres?
Mysql does AFAIR only count
I want to optimize this simple join:
SELECT * FROM huge_table h, tiny_table t WHERE UPPER( h.id ) = UPPER( t.id )
huge_table has about 2.5 million records, can be assumed as fixed, and
has the following index:
CREATE INDEX huge_table_index ON huge_table( UPPER( id ) );
...while tiny_table
kj
PS: FWIW, the query plan for the query with LIMIT 6000 is this:
What is the explain analyze?
QUERY PLAN
-
Limit (cost=19676.75..21327.99 rows=6000 width=84)
-
[EMAIL PROTECTED] writes:
Limit (cost=19676.75..21327.99 rows=6000 width=84)
- Hash Join (cost=19676.75..1062244.81 rows=3788315 width=84)
Hash Cond: (upper((outer.id)::text) = upper((inner.id)::text))
- Seq Scan on huge_table h (cost=0.00..51292.43 rows=2525543
We were able to achieve 2B (small) rows per day sustained with
very little latency. It is beefy hardware, but things that did
help include WAL on its own I/O channel, XFS, binary copy,
and tuning bgwriter and checkpoint settings for the application
and hardware. Things that didn't help much were
18 matches
Mail list logo
