line gave +~30% in our case (against CFQ). DB was on HP EVA
storage. Not 5-6 fold increase but still "free" +30% is pretty nice.
CentOS 5.5.
Regards,
Mindaugas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
orage vendors (I have confirmation from EMC and HP)
use those in their SAN boxes. I believe that is because they are the
only SLC SSD makers that have supercapacitors on the SSD drive which
allows them to run with write cache enabled. As a side effect - they
are insanely expensive. :)
Mindaugas
storage to respond.
Also off list I was pointed out about covering indexes in MySQL. But they are
not supported in PostgreSQL, aren't they?
Mindaugas
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
high (10+ different values) I doubt that it
would be possible to reach select speed with reasonable number of nodes of
sequential scan nodes.
Mindaugas
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
can be executed -
From and To.
Ideas for improvement? Greenplum or EnterpriseDB? Or I forgot something from
PostgreSQL features.
Thanks,
Mindaugas
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by don
ich I believe is quite critical with 16 cores and
DB usage pattern.
On the other hand Intel's X7350 (2.93GHz) has almost 50% advantage in CPU
frequency against 2GHz Barcelona.
Regards,
Mindaugas
P.S. tweakers.net does not have both of those yet? Test result
reSQL performance should be similar (1.86GHz * 2 * 80% =
~3GHz). And quad core has slightly slower FSB (1066 vs 1333).
So it looks like more likely dual core 5160 Woodrest is the way to go if I
want "ultimate" performance on two sockets?
Besides that I think it should consume a bit less powe
ange in 64 bits?
And I also remember that in PostgreSQL Opteron earlier had huge advantage
over older Xeons. But did Intel manage to change picture now?
Thanks,
Mindaugas
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will i
performance.
PostgreSQL 8.0 is running on two dual core Opterons.
Thanks,
Mindaugas
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's data
lect * from pg_locks" doesn't look to me like it ever has locks
> on more than one table (plus the table's indexes and toast table).
Are there some plans to remove vacuum altogether?
Mindaugas
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
databases where table bloat and constant
vacuuming is killing performance.
Mindaugas
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
tters but database has very many connections to
it (400-600) and clients are doing mostly asynchronous operations.
How to find out where this extra space gone?
Thanks,
Mindaugas
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ccording to pg_class system table? What should be the
reasonable value?
select sum(relpages) from pg_class;
sum
---
77994
(1 row)
Thanks,
Mindaugas
[2005-06-03 09:30:31 EEST] DEBUG: Performing: ANALYZE "queue"
[2005-06-03 09:30:31 EEST] INFO: table name: database.&quo
of your series.
8.0.3
> I don't know much about autovacuum settings, but if you'll show what
> you're using someone can probably comment on them.
And what in vacuum verbose output suggests that vacuum is not done
often enough? Current output (table is 100MB already) is be
sm_pages = 20. Another table has foregn
key which referenced msg_id in this one.
Thanks,
Mindaugas
$ vacuumdb -v -z -U postgres -t queue database
INFO: vacuuming "queue"
INFO: index "queue_msg_id_pk" now contains 110531 row versions in 5304
pages
DETAIL: 31454 index
).
Below is "du -sk" of database dir during the day. On 4:05
full vacuum+reindex runs and database size is once again
reduced.
Thanks,
Mindaugas
Tue May 31 11:00:01 EEST 2005
533808 /ora/pgsql/base/465436/
Tue May 31 11:30:01 EEST 2005
567344 /ora/pgsql/base/465436/
Tue May 31 12:
w if it matters but PG database is on ext3 partition with
data=journal option.
Thanks,
Mindaugas
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
mation about db locks.
Hm. Yes. Number of locks varies quite alot (10-600). Now what to
investigate
further? We do not use explicit locks in our functions. We use quite simple
update/delete where key=something;
Some sample (select * from pg_locks order by pid) is below.
Than
ded some 'vmstat 1'
output.
How to get more even load. As you see neither disk nor cpu looks too busy.
Thanks,
Mindaugas
procs memoryswap io system
cpu
r b w swpd free buff cache si sobibo incs us sy
id
1 0 0
is quiet periods and then suddenly 30+ slow queries appears in
log within the same second. What else could cause such behaviour? WAL log
switch? One WAL file seems to last <1 minute.
And also in slow queries log only function call is shown. Maybe it is
possible
to get exact query which slows ev
performance in our case? Please help
to defend PostgreSQL against Oracle in this case :).
Thanks,
Mindaugas
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatyp
fsm (?) and pg_auovacuum settings for such case?
What I do not like about one table is "unused item pointers" number.
Now I use max_fsm_relations=1000 and max_fsm_pages=20.
pg_autovacuum ran with default settings.
Thanks,
Mindaugas
# VACUUM VERBOSE msq;
INFO: vacuuming
join() operator that lets you do something
like:
>
> $where_cond = join(' AND ', @list_of_tests)
That's not the case. Test may or may not be performed based on
web form values.
Mindaugas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
is the fastest
one?
Thanks,
Mindaugas
---(end of broadcast)---
TIP 8: explain analyze is your friend
below (machine is 2xCPU so 40% load means 80% on one
CPU).
So the solution would be REINDEX before updates and VACUUM at the same
time?
Without REINDEX performance slowly degrades.
Mindaugas
router_db=# explain analyze update ifdata set if
eful for reads, but won't help with
writing.
> You might want to look at wal_buffers and see if increasing that helps,
but I
> couldn't say for sure.
Disk I/O should not be a problem in this case. vmstat shows ~300kb/s write
activity.
Mindaugas
---(end o
improve performance and will version 7.4 bring something
valuable for my task? Rewrite to some other scripting language is not
a problem. Trigger is simple enough.
Postgres v7.3.4, shared_buffers=4096 max_fsm settings also bumped up
10 times.
Thanks,
Mindaugas
patches for Linux kernel to fix that. Sooner rather than later they will
appear
in Linus kernel.
Mindaugas
---(end of broadcast)---
TIP 8: explain analyze is your friend
28 matches
Mail list logo
