Re: [PERFORM] 60 core performance with 9.3
On 01/07/14 22:13, Andres Freund wrote: On 2014-07-01 21:48:35 +1200, Mark Kirkwood wrote: - cherry picking the last 5 commits into 9.4 branch and building a package from that and retesting: Clients | 9.4 tps 60 cores (rwlock) +-- 6 | 70189 12 | 128894 24 | 233542 48 | 422754 96 | 590796 192 | 630672 Wow - that is more like it! Andres that is some nice work, we definitely owe you some beers for that :-) I am aware that I need to retest with an unpatched 9.4 src - as it is not clear from this data how much is due to Andres's patches and how much to the steady stream of 9.4 development. I'll post an update on that later, but figured this was interesting enough to note for now. Cool. That's what I like (and expect) to see :). I don't think unpatched 9.4 will show significantly different results than 9.3, but it'd be good to validate that. If you do so, could you post the results in the -hackers thread I just CCed you on? That'll help the work to get into 9.5. So we seem to have nailed read only performance. Going back and revisiting read write performance finds: Postgres 9.4 beta rwlock patch pgbench scale = 2000 max_connections = 200; shared_buffers = 10GB; maintenance_work_mem = 1GB; effective_io_concurrency = 10; wal_buffers = 32MB; checkpoint_segments = 192; checkpoint_completion_target = 0.8; clients | tps (32 cores) | tps -++- 6| 8313 | 8175 12 | 11012 | 14409 24 | 16151 | 17191 48 | 21153 | 23122 96 | 21977 | 22308 192 | 22917 | 23109 So we are back to not doing significantly better than 32 cores. Hmmm. Doing quite a few more tweaks gets some better numbers: kernel.sched_autogroup_enabled=0 kernel.sched_migration_cost_ns=500 net.core.somaxconn=1024 /sys/kernel/mm/transparent_hugepage/enabled [never] +checkpoint_segments = 1920 +wal_buffers = 256MB; clients | tps -+- 6| 8366 12 | 15988 24 | 19828 48 | 30315 96 | 31649 192 | 29497 One more: +wal__sync_method = open_datasync clients | tps -+- 6| 9566 12 | 17129 24 | 22962 48 | 34564 96 | 32584 192 | 28367 So this looks better - however I suspect 32 core performance would improve with these as well! The problem does *not* look to be connected with IO (I will include some iostat below). So time to get the profiler out (192 clients for 1 minute): Full report http://paste.ubuntu.com/886/ # # captured on: Fri Jul 11 03:09:06 2014 # hostname : ncel-prod-db3 # os release : 3.13.0-24-generic # perf version : 3.13.9 # arch : x86_64 # nrcpus online : 60 # nrcpus avail : 60 # cpudesc : Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz # cpuid : GenuineIntel,6,62,7 # total memory : 1056692116 kB # cmdline : /usr/lib/linux-tools-3.13.0-24/perf record -ag # event : name = cycles, type = 0, config = 0x0, config1 = 0x0, config2 = 0x0, excl_usr = 0, excl_kern = 0, excl_host = 0, excl_guest = 1, precise_ip = 0, attr_mmap2 = 0, attr_mmap = 1, attr_mmap_data = 0 # HEADER_CPU_TOPOLOGY info available, use -I to display # HEADER_NUMA_TOPOLOGY info available, use -I to display # pmu mappings: cpu = 4, uncore_cbox_10 = 17, uncore_cbox_11 = 18, uncore_cbox_12 = 19, uncore_cbox_13 = 20, uncore_cbox_14 = 21, software = 1, uncore_irp = 33, uncore_pcu = 22, tracepoint = 2, uncore_imc_0 = 25, uncore_imc_1 = 26, uncore_imc_2 = 27, uncore_imc_3 = 28, uncore_imc_4 = 29, uncore_imc_5 = 30, uncore_imc_6 = 31, uncore_imc_7 = 32, uncore_qpi_0 = 34, uncore_qpi_1 = 35, uncore_qpi_2 = 36, uncore_cbox_0 = 7, uncore_cbox_1 = 8, uncore_cbox_2 = 9, uncore_cbox_3 = 10, uncore_cbox_4 = 11, uncore_cbox_5 = 12, uncore_cbox_6 = 13, uncore_cbox_7 = 14, uncore_cbox_8 = 15, uncore_cbox_9 = 16, uncore_r2pcie = 37, uncore_r3qpi_0 = 38, uncore_r3qpi_1 = 39, breakpoint = 5, uncore_ha_0 = 23, uncore_ha_1 = 24, uncore_ubox = 6 # # # Samples: 1M of event 'cycles' # Event count (approx.): 359906321606 # # Overhead CommandShared Object Symbol # .. ... . # 8.82%postgres [kernel.kallsyms][k] _raw_spin_lock_irqsave | --- _raw_spin_lock_irqsave | |--75.69%-- pagevec_lru_move_fn | __lru_cache_add | lru_cache_add | putback_lru_page | migrate_pages | migrate_misplaced_page | do_numa_page | handle_mm_fault | __do_page_fault | do_page_fault
[PERFORM] GIN index not used
Hi,
I've got a table with GIN index on integer[] type. While doing a query with
filter criteria on that column has GIN index created, it's not using index at
all, still do the full table scan. Wondering why?
Table is analyzed.
dev=# \d+ booking_weekly
Table booking_weekly
Column| Type | Modifiers | Storage | Stats target |
Description
--++---+--+--+-
date | date | | plain| |
id | character varying(256) | | extended | |
t_wei | double precision | | plain| |
booking_ts | integer[] | | extended | |
Indexes:
idx_booking_weekly_1_1 btree (id), tablespace tbs_data
idx_booking_weekly_1_2 gin (booking_ts), tablespace tbs_data
dev=# select * from booking_weekly limit 1;
-[ RECORD 1
date | 2014-05-03
id| 148f8ecbf40
t_wei | 0.892571268670041
booking_ts |
{2446685,4365133,5021137,2772581,1304970,6603422,262511,5635455,4637460,5250119,3037711,6273424,3198590,3581767,6612741,5813035,3074851}
dev=# explain analyze select * FROM booking_weekly
WHERE date = '2014-05-03' AND
booking_ts@array[2446685];
-[ RECORD 1
]--
QUERY PLAN | Seq Scan on booking_weekly (cost=100.00..1344953.64
rows=1288 width=1233) (actual time=0.015..1905.657 rows=1 loops=1)
-[ RECORD 2
]--
QUERY PLAN | Filter: ((booking_ts @ '{2446685}'::integer[]) AND (date =
'2014-05-03'::date))
-[ RECORD 3
]--
QUERY PLAN | Rows Removed by Filter: 1288402
-[ RECORD 4
]--
QUERY PLAN | Total runtime: 1905.687 ms
Thanks,
Suya
Re: [PERFORM] GIN index not used
Huang, Suya suya.hu...@au.experian.com wrote:
Hi,
I’ve got a table with GIN index on integer[] type. While doing a query with
filter criteria on that column has GIN index created, it’s not using index at
all, still do the full table scan. Wondering why?
Try to add an index on the date-column.
Btw.: works for me:
,
| test=*# \d foo;
|Table public.foo
| Column | Type| Modifiers
| +---+---
| id | integer |
| ts | integer[] |
| Indexes:
| idx_foo gin (ts)
|
| test=*# set enable_seqscan to off;
| SET
| Time: 0,049 ms
| test=*# select * from foo;
| id | ts
| +
| 1 | {1,2,3}
| 2 | {10,20,30}
| (2 rows)
|
| Time: 0,230 ms
| test=*# explain select * from foo where ts @ array[2];
| QUERY PLAN
| --
| Bitmap Heap Scan on foo (cost=8.00..12.01 rows=1 width=36)
|Recheck Cond: (ts @ '{2}'::integer[])
|- Bitmap Index Scan on idx_foo (cost=0.00..8.00 rows=1 width=0)
| Index Cond: (ts @ '{2}'::integer[])
| (4 rows)
`
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] GIN index not used
Andreas Kretschmer akretsch...@spamfence.net writes:
Huang, Suya suya.hu...@au.experian.com wrote:
Iâve got a table with GIN index on integer[] type. While doing a query with
filter criteria on that column has GIN index created, itâs not using index
at
all, still do the full table scan. Wondering why?
Btw.: works for me:
Yeah, me too:
regression=# create table booking_weekly(booking_ts int[]);
CREATE TABLE
regression=# create index on booking_weekly using gin (booking_ts);
CREATE INDEX
regression=# explain select * from booking_weekly where
booking_ts@array[2446685];
QUERY PLAN
Bitmap Heap Scan on booking_weekly (cost=8.05..18.20 rows=7 width=32)
Recheck Cond: (booking_ts @ '{2446685}'::integer[])
- Bitmap Index Scan on booking_weekly_booking_ts_idx (cost=0.00..8.05
rows=7 width=0)
Index Cond: (booking_ts @ '{2446685}'::integer[])
Planning time: 0.862 ms
(5 rows)
What PG version is this? What non-default planner parameter settings are
you using? (Don't say none, because I can see you've got enable_seqscan
turned off.)
regards, tom lane
--
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] GIN index not used
Tom Lane t...@sss.pgh.pa.us wrote: What PG version is this? What non-default planner parameter settings are you using? (Don't say none, because I can see you've got enable_seqscan turned off.) LOL, right ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] GIN index not used
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Friday, July 11, 2014 2:56 PM
To: Andreas Kretschmer
Cc: Huang, Suya; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] GIN index not used
Andreas Kretschmer akretsch...@spamfence.net writes:
Huang, Suya suya.hu...@au.experian.com wrote:
I’ve got a table with GIN index on integer[] type. While doing a
query with filter criteria on that column has GIN index created, it’s
not using index at all, still do the full table scan. Wondering why?
Btw.: works for me:
Yeah, me too:
regression=# create table booking_weekly(booking_ts int[]); CREATE TABLE
regression=# create index on booking_weekly using gin (booking_ts); CREATE
INDEX regression=# explain select * from booking_weekly where
booking_ts@array[2446685];
QUERY PLAN
Bitmap Heap Scan on booking_weekly (cost=8.05..18.20 rows=7 width=32)
Recheck Cond: (booking_ts @ '{2446685}'::integer[])
- Bitmap Index Scan on booking_weekly_booking_ts_idx (cost=0.00..8.05
rows=7 width=0)
Index Cond: (booking_ts @ '{2446685}'::integer[]) Planning time:
0.862 ms
(5 rows)
What PG version is this? What non-default planner parameter settings are you
using? (Don't say none, because I can see you've got enable_seqscan turned
off.)
regards, tom lane
Just found out something here
http://www.postgresql.org/message-id/17021.1234474...@sss.pgh.pa.us
So I dropped the index and recreate it by specifying: using gin(terms_ts
gin__int_ops) and the index works.
My PG version is 9.3.4, none-default planner settings:
enable_mergejoin = off
enable_nestloop = off
enable_seqscan is turned off for session while trying to figure out why the GIN
index is not used.
--
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] GIN index not used
Huang, Suya suya.hu...@au.experian.com writes: Just found out something here http://www.postgresql.org/message-id/17021.1234474...@sss.pgh.pa.us So I dropped the index and recreate it by specifying: using gin(terms_ts gin__int_ops) and the index works. Oh, you're using contrib/intarray? Pursuant to the thread you mention above, we removed intarray's @ and @ operators (commit 65e758a4d3) but then reverted that (commit 156475a589) because of backwards-compatibility worries. It doesn't look like anything got done about it since then. Perhaps the extension upgrade infrastructure would offer a solution now. My PG version is 9.3.4, none-default planner settings: enable_mergejoin = off enable_nestloop = off [ raised eyebrow... ] It's pretty hard to see how those would be a good idea. Not all problems are best solved by hash joins. regards, tom lane -- 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] GIN index not used
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, July 11, 2014 3:43 PM To: Huang, Suya Cc: Andreas Kretschmer; pgsql-performance@postgresql.org Subject: Re: [PERFORM] GIN index not used Huang, Suya suya.hu...@au.experian.com writes: Just found out something here http://www.postgresql.org/message-id/17021.1234474...@sss.pgh.pa.us So I dropped the index and recreate it by specifying: using gin(terms_ts gin__int_ops) and the index works. Oh, you're using contrib/intarray? Pursuant to the thread you mention above, we removed intarray's @ and @ operators (commit 65e758a4d3) but then reverted that (commit 156475a589) because of backwards-compatibility worries. It doesn't look like anything got done about it since then. Perhaps the extension upgrade infrastructure would offer a solution now. My PG version is 9.3.4, none-default planner settings: enable_mergejoin = off enable_nestloop = off [ raised eyebrow... ] It's pretty hard to see how those would be a good idea. Not all problems are best solved by hash joins. regards, tom lane About the contrib/intarray, do I have other choices not using that one? About the join, yeah, in our testing for DW-like queries, hash join does improved the performance greatly... Thanks, Suya -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
