[PERFORM] FW: performance issue with a 2.5gb joinded table
Hi Listers, we migrated an oracle datawarehouse to postgresql 9.1 ( ppas 9.1.7.12 ) and are facing massive issues with response times in postgres when compared to the oracle system. Both database run on the same hardware and storage ( rhel5.8 64bit ). Oracle memory parameters are: SGA=1gb PGA=200mb Postgres currently runs with 15gb of shared buffers ( that’s because the big table in question is around 2.5gb in size and one suggestion was to increase that much so postgresql will cache the complete table. and this is the case now ). explain (analyze,buffers) SELECT test1.slsales_batch , test1.slsales_checksum , test1.slsales_reg_id , test1.slsales_prod_id , test1.slsales_date_id , test1.slsales_pos_id , test1.slsales_amt_sales_gross , test1.slsales_amt_sales_discount , test1.slsales_units_sales_gross , test1.slsales_amt_returns , test1.slsales_amt_returns_discount , test1.slsales_units_returns , (test1.slsales_amt_sales_gross - test1.slsales_amt_returns) * mgmt_fact_winratio.winratio_ratio AS slsales_amt_est_winnings , mgmt_fact_winratio.winratio_ratio AS slsales_ratio FROM mgmtt_own.test1 LEFT JOIN mgmtt_own.mgmt_fact_winratio ON mgmt_fact_winratio.winratio_date_id = test1.slsales_date_id Oracle’s explain plan looks like this: | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | | 0 | SELECT STATEMENT ||25M| 1527M| | 115K (3)| 00:23:10 | |* 1 | HASH JOIN RIGHT OUTER||25M| 1527M| 4376K| 115K (3)| 00:23:10 | | 2 | TABLE ACCESS FULL | MGMT_FACT_WINRATIO | 159K| 2498K| | 167 (5)| 00:00:03 | | 3 | TABLE ACCESS FULL | TEST1 |25M| 1139M| | 43435 (5)| 00:08:42 | Predicate Information (identified by operation id): --- 1 - access(MGMT_FACT_WINRATIO.WINRATIO_PROD_ID(+)=TEST1.SLSALES_PROD_ID AND MGMT_FACT_WINRATIO.WINRATIO_DATE_ID(+)=TEST1.SLSALES_DATE_ID) Somehow oracle seems to know that a right join is the better way to go. Postgres’s explain plan: QUERY PLAN Hash Left Join (cost=3948.52..13646089.21 rows=25262160 width=61) (actual time=260.642..81240.692 rows=25262549 loops=1) Hash Cond: ((test1.slsales_date_id = mgmt_fact_winratio.winratio_date_id) AND (test1.slsales_prod_id = mgmt_fact_winratio.winratio_prod_id)) Buffers: shared hit=306590 - Seq Scan on test1 (cost=0.00..254148.75 rows=25262160 width=56) (actual time=0.009..15674.535 rows=25262161 loops=1) Buffers: shared hit=305430 - Hash (cost=1582.89..1582.89 rows=157709 width=19) (actual time=260.564..260.564 rows=157709 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 7855kB Buffers: shared hit=1160 - Seq Scan on mgmt_fact_winratio (cost=0.00..1582.89 rows=157709 width=19) (actual time=0.008..114.406 rows=157709 loops=1) Buffers: shared hit=1160 Total runtime: 95762.025 ms (11 rows) Tried to modify the statement according to oracle’s plan, but this did not help: explain (analyze,buffers) SELECT test1.slsales_batch , test1.slsales_checksum , test1.slsales_reg_id , test1.slsales_prod_id , test1.slsales_date_id , test1.slsales_pos_id , test1.slsales_amt_sales_gross , test1.slsales_amt_sales_discount , test1.slsales_units_sales_gross , test1.slsales_amt_returns , test1.slsales_amt_returns_discount , test1.slsales_units_returns , (test1.slsales_amt_sales_gross - test1.slsales_amt_returns) * mgmt_fact_winratio.winratio_ratio AS slsales_amt_est_winnings , mgmt_fact_winratio.winratio_ratio AS slsales_ratio FROM mgmtt_own.test1 , mgmtt_own.mgmt_fact_winratio WHERE mgmt_fact_winratio.winratio_prod_id(+) = test1.slsales_prod_id AND mgmt_fact_winratio.winratio_date_id(+) = test1.slsales_date_id ; QUERY PLAN Hash Left Join (cost=3948.52..13646089.21 rows=25262160 width=61) (actual time=276.605..80629.400 rows=25262549 loops=1) Hash Cond: ((test1.slsales_prod_id = mgmt_fact_winratio.winratio_prod_id) AND (test1.slsales_date_id =
Re: [PERFORM] FW: performance issue with a 2.5gb joinded table
On 03.01.2013 15:30, Daniel Westermann wrote:
What additionally makes me wonder is, that the same table in oracle is taking
much less space than in postgresql:
SQL select sum(bytes) from dba_extents where segment_name = 'TEST1';
SUM(BYTES)
--
1610612736
select pg_relation_size('mgmtt_own.test1');
pg_relation_size
--
2502082560
(1 row)
(sysdba@[local]:) [bi_dwht] \d+ mgmtt_own.test1
Table mgmtt_own.test1
Column| Type | Modifiers | Storage |
Description
--+---+---+-+-
slsales_batch| numeric(8,0) | | main|
slsales_checksum | numeric(8,0) | | main|
slsales_reg_id | numeric(8,0) | | main|
slsales_prod_id | numeric(8,0) | | main|
slsales_date_id | numeric(8,0) | | main|
slsales_pos_id | numeric(8,0) | | main|
slsales_amt_sales_gross | numeric(16,6) | | main|
slsales_amt_sales_discount | numeric(16,6) | | main|
slsales_units_sales_gross| numeric(8,0) | | main|
slsales_amt_returns | numeric(16,6) | | main|
slsales_amt_returns_discount | numeric(16,6) | | main|
slsales_units_returns| numeric(8,0) | | main|
slsales_amt_est_winnings | numeric(16,6) | | main|
Indexes:
itest1 btree (slsales_date_id) CLUSTER, tablespace mgmtt_idx
itest2 btree (slsales_prod_id), tablespace mgmtt_idx
Has OIDs: no
Tablespace: mgmtt_dat
One difference is that numerics are stored more tightly packed on
Oracle. Which is particularly good for Oracle as they don't have other
numeric data types than number. On PostgreSQL, you'll want to use int4
for ID-fields, where possible. An int4 always takes up 4 bytes, while a
numeric holding an integer value in the same range is typically 5-9 bytes.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] FW: performance issue with a 2.5gb joinded table
-Original Message-
From: Heikki Linnakangas [mailto:hlinnakan...@vmware.com]
Sent: Donnerstag, 3. Januar 2013 18:02
To: Daniel Westermann
Cc: 'pgsql-performance@postgresql.org'
Subject: Re: [PERFORM] FW: performance issue with a 2.5gb joinded table
On 03.01.2013 15:30, Daniel Westermann wrote:
What additionally makes me wonder is, that the same table in oracle is taking
much less space than in postgresql:
SQL select sum(bytes) from dba_extents where segment_name =
SQL 'TEST1';
SUM(BYTES)
--
1610612736
select pg_relation_size('mgmtt_own.test1');
pg_relation_size
--
2502082560
(1 row)
(sysdba@[local]:) [bi_dwht] \d+ mgmtt_own.test1
Table mgmtt_own.test1
Column| Type | Modifiers | Storage |
Description
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
--+---+---+-+-
slsales_batch| numeric(8,0) | | main|
slsales_checksum | numeric(8,0) | | main|
slsales_reg_id | numeric(8,0) | | main|
slsales_prod_id | numeric(8,0) | | main|
slsales_date_id | numeric(8,0) | | main|
slsales_pos_id | numeric(8,0) | | main|
slsales_amt_sales_gross | numeric(16,6) | | main|
slsales_amt_sales_discount | numeric(16,6) | | main|
slsales_units_sales_gross| numeric(8,0) | | main|
slsales_amt_returns | numeric(16,6) | | main|
slsales_amt_returns_discount | numeric(16,6) | | main|
slsales_units_returns| numeric(8,0) | | main|
slsales_amt_est_winnings | numeric(16,6) | | main|
Indexes:
itest1 btree (slsales_date_id) CLUSTER, tablespace mgmtt_idx
itest2 btree (slsales_prod_id), tablespace mgmtt_idx
Has OIDs: no
Tablespace: mgmtt_dat
One difference is that numerics are stored more tightly packed on Oracle. Which
is particularly good for Oracle as they don't have other numeric data types
than number. On PostgreSQL, you'll want to use int4 for ID-fields, where
possible. An int4 always takes up 4 bytes, while a numeric holding an integer
value in the same range is typically 5-9 bytes.
- Heikki
Thanks for poiting that out, Heikki.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Simple join doesn't use index
Hi everybody, I have implemented my first app using PG DB and thought for a minute(may be two) that I know something about PG but below problem totally destroyed my confidence :). Please help me to restore it. Here is simple join query. It runs just fine on MS SQL 2008 and uses all available indexes using even bigger overall dataset. select visits.id, views.id from visits join views on visits.id = views.visit_id where visits.created_at = '11/15/2012' and visits.created_at '11/16/2012' Quick performance stat MS SQL: 1 second, 264K rows PG: 158 seconds, 264K rows Explain plan from both DBs PG QUERY PLAN Hash Join (cost=12716.17..1101820.09 rows=248494 width=8) Hash Cond: (views.visit_id = visits.id) - Seq Scan on views (cost=0.00..819136.56 rows=17434456 width=8) - Hash (cost=10549.16..10549.16 rows=132081 width=4) - Index Scan using visits_created_at_index on visits (cost=0.00..10549.16 rows=132081 width=4) Index Cond: ((created_at = '2012-11-15 00:00:00'::timestamp without time zone) AND (created_at '2012-11-16 00:00:00'::timestamp without time zone)) schemaname | tablename |indexname| tablespace | indexdef +---+-++-- public | views | views_pkey || CREATE UNIQUE INDEX views_pkey ON views USING btree (id) public | views | views_visit_id_index|| CREATE INDEX views_visit_id_index ON views USING btree (visit_id) MS SQL Query plan '11/16/2012' |--Parallelism(Gather Streams) |--Nested Loops(Inner Join, OUTER REFERENCES:([visits].[id], [Expr1006]) OPTIMIZED WITH UNORDERED PREFETCH) |--Index Seek(OBJECT:([visits].[test]), SEEK:([visits].[created_at] = '2012-11-15 00:00:00.000' AND [visits].[created_at] '2012-11-16 00:00:00.000') ORDERED FORWARD) |--Index Seek(OBJECT:([views].[views_visit_id_index]), SEEK:([views].[visit_id]=[raw_visits].[id]) ORDERED FORWARD) It is clear that PG does full table scan Seq Scan on views (cost=0.00..819136.56 rows=17434456 width=8) Don't understand why PG doesn't use views_visit_id_index in that query but rather scans whole table. One explanation I have found that when resulting dataset constitutes ~15% of total number of rows in the table then seq scan is used. In this case resulting dataset is just 1.5% of total number of rows. So it must be something different. Any reason why it happens and how to fix it? Postgres 9.2 Ubuntu 12.04.1 LTS shared_buffers = 4GB the rest of the settings are default ones Thanks -Alex
Re: [PERFORM] Simple join doesn't use index
On 01/03/2013 10:54 PM, Alex Vinnik wrote: I have implemented my first app using PG DB and thought for a minute(may be two) that I know something about PG but below problem totally destroyed my confidence :). Please help me to restore it. https://wiki.postgresql.org/wiki/SlowQueryQuestions -- Jeremy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] SMP on a heavy loaded database
Centos 5.X kernel 2.6.18-274 pgsql-9.1 from pgdg-91-centos.repo relatively small database 3.2Gb Lot of insert, update, delete. I see non balanced _User_ usage on 14 CPU, exclusively assigned to the hardware raid controller. What I'm doing wrong, and is it possible somehow to fix? Thanks in advance. Andrew. # top -d 10.00 -b -n 2 -U postgres -c top - 23:18:19 up 453 days, 57 min, 3 users, load average: 0.55, 0.47, 0.42 Tasks: 453 total, 1 running, 452 sleeping, 0 stopped, 0 zombie Cpu0 : 0.6%us, 0.1%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 1.2%us, 0.1%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 2.6%us, 0.4%sy, 0.0%ni, 96.8%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Cpu5 : 0.8%us, 0.0%sy, 0.0%ni, 99.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 5.4%us, 0.2%sy, 0.0%ni, 94.2%id, 0.2%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 3.3%us, 0.4%sy, 0.0%ni, 96.1%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Cpu8 : 1.4%us, 0.3%sy, 0.0%ni, 98.2%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st Cpu9 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu10 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu11 : 1.6%us, 0.6%sy, 0.0%ni, 97.4%id, 0.0%wa, 0.0%hi, 0.4%si, 0.0%st Cpu12 : 0.5%us, 0.1%sy, 0.0%ni, 99.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu13 : 1.4%us, 0.2%sy, 0.0%ni, 98.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu14 : 24.2%us, 0.8%sy, 0.0%ni, 74.5%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st Cpu15 : 0.7%us, 0.1%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.1%hi, 0.1%si, 0.0%st Mem: 16426540k total, 16356772k used,69768k free, 215764k buffers Swap: 4194232k total, 145280k used, 4048952k free, 14434356k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 6513 postgres 16 0 4329m 235m 225m S 3.1 1.5 0:02.24 postgres: _DB [local] idle 6891 postgres 16 0 4331m 223m 213m S 1.7 1.4 0:01.44 postgres: _DB [local] idle 6829 postgres 16 0 4329m 219m 210m S 1.6 1.4 0:01.56 postgres: _DB [local] idle 6539 postgres 16 0 4330m 319m 308m S 1.5 2.0 0:03.64 postgres: _DB [local] idle 6487 postgres 16 0 4329m 234m 224m S 1.2 1.5 0:02.95 postgres: _DB [local] idle 6818 postgres 16 0 4328m 224m 215m S 1.2 1.4 0:02.00 postgres: _DB [local] idle 6831 postgres 16 0 4328m 215m 206m S 1.2 1.3 0:01.41 postgres: _DB [local] idle 6868 postgres 16 0 4330m 223m 213m S 1.2 1.4 0:01.46 postgres: _DB [local] idle 6899 postgres 15 0 4328m 220m 211m S 1.2 1.4 0:01.61 postgres: _DB [local] idle 6515 postgres 15 0 4331m 233m 223m S 1.0 1.5 0:02.66 postgres: _DB [local] idle 6890 postgres 16 0 4331m 279m 268m S 1.0 1.7 0:02.01 postgres: _DB [local] idle 7083 postgres 15 0 4328m 207m 199m S 1.0 1.3 0:00.77 postgres: _DB [local] idle 6374 postgres 16 0 4329m 245m 235m S 0.9 1.5 0:04.30 postgres: _DB [local] idle 6481 postgres 15 0 4328m 293m 285m S 0.9 1.8 0:03.17 postgres: _DB [local] idle 6484 postgres 16 0 4329m 236m 226m S 0.9 1.5 0:02.82 postgres: _DB [local] idle 6509 postgres 16 0 4332m 237m 225m S 0.9 1.5 0:02.90 postgres: _DB [local] idle 6522 postgres 15 0 4330m 238m 228m S 0.9 1.5 0:02.35 postgres: _DB [local] idle 6812 postgres 16 0 4329m 283m 274m S 0.9 1.8 0:02.19 postgres: _DB [local] idle 7086 postgres 15 0 4328m 202m 194m S 0.9 1.3 0:00.70 postgres: _DB [local] idle 6494 postgres 15 0 4329m 317m 306m S 0.8 2.0 0:03.98 postgres: _DB [local] idle 6542 postgres 16 0 4330m 309m 299m S 0.8 1.9 0:02.79 postgres: _DB [local] idle 6550 postgres 15 0 4329m 287m 277m S 0.8 1.8 0:02.80 postgres: _DB [local] idle 6777 postgres 16 0 4329m 229m 219m S 0.8 1.4 0:02.13 postgres: _DB [local] idle 6816 postgres 16 0 4329m 230m 220m S 0.8 1.4 0:01.61 postgres: _DB [local] idle 6822 postgres 15 0 4329m 305m 295m S 0.8 1.9 0:02.09 postgres: _DB [local] idle 6897 postgres 15 0 4328m 219m 210m S 0.8 1.4 0:01.69 postgres: _DB [local] idle 6926 postgres 16 0 4328m 209m 200m S 0.8 1.3 0:00.81 postgres: _DB [local] idle 6473 postgres 16 0 4329m 236m 226m S 0.7 1.5 0:02.81 postgres: _DB [local] idle 6826 postgres 16 0 4330m 226m 216m S 0.7 1.4 0:02.14 postgres: _DB [local] idle 6834 postgres 16 0 4331m 282m 271m S 0.7 1.8 0:03.06 postgres: _DB [local] idle 6882 postgres 15 0 4330m 222m 212m S 0.7 1.4 0:01.83 postgres: _DB [local] idle 6885 postgres 16 0 4328m 104m 96m S 0.6 0.7 0:00.94 postgres: _DB [local] idle 6878 postgres 15 0 4319m 2992 1472 S
Re: [PERFORM] Simple join doesn't use index
On 01/03/2013 11:54 PM, Alex Vinnik wrote:
Don't understand why PG doesn't use views_visit_id_index in that query
but rather scans whole table. One explanation I have found that when
resulting dataset constitutes ~15% of total number of rows in the table
then seq scan is used. In this case resulting dataset is just 1.5% of
total number of rows. So it must be something different. Any reason why
it happens and how to fix it?
But does the query planner know the same? If you added the EXPLAIN
ANALYZE output of the query and something like:
SELECT tablename AS table_name, attname AS column_name,
null_frac, avg_width, n_distinct, correlation
FROM pg_stats
WHERE tablename in ('views', 'visits');
.. one could possibly tell a bit more.
Postgres 9.2
Ubuntu 12.04.1 LTS
shared_buffers = 4GB the rest of the settings are default ones
There are more than just this one memory related value, that need to be
changed for optimal performance. E.g. effective_cache_size can have a
direct effect on use of nested loops. See:
http://www.postgresql.org/docs/9.2/static/runtime-config-query.html
Regards,
Stefan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] serious under-estimation of n_distinct for clustered distributions
On 12/29/2012 10:57 PM, Peter Geoghegan wrote: On 29 December 2012 20:57, Stefan Andreatta s.andrea...@synedra.com wrote: ... The general advice here is: 1) Increase default_statistics_target for the column. I tried that, but to get good estimates under these circumstances, I need to set the statistics_target so high that the whole table gets analyzed. As this problem matters most for all of our large tables, I would have to set default_statistics_target to something like 10 - that's a bit scary for production systems with tables of appr. 100GB, I find. 2) If that doesn't help, consider using the following DDL: alter table foo alter column bar set ( n_distinct = 5.0); Yes, that's probably best - even if it means quite some maintenance work. I do it like that: ALTER TABLE test_1 ALTER COLUMN clustered_random_2000k SET (n_distinct = -0.05); btw: Postgres will never set relative n_distinct values for anything larger than -0.1. If I determine (or know) it to be a constant but lower fraction, could it be a problem to explicitly set this value to between -0.1 and 0? To activate that setting, however, an ANALYZE has to be run. That was not clear to me from the documentation: ANALYZE verbose test_1; To check column options and statistics values: SELECT pg_class.relname AS table_name, pg_attribute.attname AS column_name, pg_attribute.attoptions FROM pg_attribute JOIN pg_class ON pg_attribute.attrelid = pg_class.oid WHERE pg_attribute.attnum 0 AND pg_class.relname = 'test_1' AND pg_attribute.attname = 'clustered_random_2000k'; SELECT tablename AS table_name, attname AS column_name, null_frac, avg_width, n_distinct, correlation FROM pg_stats WHERE tablename = 'test_1' and attname = 'clustered_random_2000k'; And finally, we can undo the whole thing, if necessary: ALTER TABLE test_1 ALTER COLUMN clustered_random_2000k RESET (n_distinct); ANALYZE VERBOSE test_1; Regards, Stefan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SMP on a heavy loaded database
On Thu, Jan 3, 2013 at 4:45 PM, nobody nowhere devn...@mail.ua wrote: Centos 5.X kernel 2.6.18-274 pgsql-9.1 from pgdg-91-centos.repo relatively small database 3.2Gb Lot of insert, update, delete. I see non balanced _User_ usage on 14 CPU, exclusively assigned to the hardware raid controller. What I'm doing wrong, and is it possible somehow to fix? Thanks in advance. Andrew. # top -d 10.00 -b -n 2 -U postgres -c top - 23:18:19 up 453 days, 57 min, 3 users, load average: 0.55, 0.47, 0.42 Tasks: 453 total, 1 running, 452 sleeping, 0 stopped, 0 zombie Cpu0 : 0.6%us, 0.1%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 1.2%us, 0.1%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 2.6%us, 0.4%sy, 0.0%ni, 96.8%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Cpu5 : 0.8%us, 0.0%sy, 0.0%ni, 99.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 5.4%us, 0.2%sy, 0.0%ni, 94.2%id, 0.2%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 3.3%us, 0.4%sy, 0.0%ni, 96.1%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Cpu8 : 1.4%us, 0.3%sy, 0.0%ni, 98.2%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st Cpu9 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu10 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu11 : 1.6%us, 0.6%sy, 0.0%ni, 97.4%id, 0.0%wa, 0.0%hi, 0.4%si, 0.0%st Cpu12 : 0.5%us, 0.1%sy, 0.0%ni, 99.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu13 : 1.4%us, 0.2%sy, 0.0%ni, 98.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu14 : 24.2%us, 0.8%sy, 0.0%ni, 74.5%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st Cpu15 : 0.7%us, 0.1%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.1%hi, 0.1%si, 0.0%st Mem: 16426540k total, 16356772k used,69768k free, 215764k buffers Swap: 4194232k total, 145280k used, 4048952k free, 14434356k cached So how many concurrent users are accessing this db? pgsql assigns one process on one core so to speak. It can't spread load for one user over all cores. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
