Re: [GENERAL] Slow index performance
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Christian Schröder > Sent: Freitag, 3. Juli 2015 07:36 > To: pgsql-general@postgresql.org > Subject: [GENERAL] Slow index performance > > Hi all, > we have a strange performance issue in one of our databases (using > PostgreSQL 9.1.18). Maybe you can help me understand what’s going on. > > We have two identical tables (rec_isins_current, rec_isins_archive) > with the following structure: > > Table "ts_frontend.rec_isins_current" >Column | Type | Modifiers > +-+--- > attachment | integer | not null > isin | isin| not null > Indexes: > "rec_isins_current_pkey" PRIMARY KEY, btree (attachment, isin), > tablespace "extra" > "rec_isins_current_attachment" btree (attachment), tablespace > "extra" Hello, Are you sure that the column order of the PKs is the same in both tables? (attachment, isin) or (isin, attachment). When isin is at the second place, Postgres will read the whole index to find matching records. regards, Marc Mamin > Foreign-key constraints: > "rec_isins_attachment_fkey" FOREIGN KEY (attachment) REFERENCES > ts_frontend.attachments(id) ON UPDATE RESTRICT ON DELETE CASCADE > Inherits: ts_frontend.rec_isins > > The isin type is a domain type which has char(12) as its base type. > Both tables inherit from ts_frontend.rec_isins, which is empty and is > only used to search both tables in a single query. > > When we search for an isin in both tables (using the parent table, but > the behavior is the same if we directly search in one of the tables), > the primary key index is used. However, while the archive table is > pretty fast, the current table is much slower: > > # explain analyze select * from ts_frontend.rec_isins where isin = > 'foo'; > > QUERY PLAN > --- > --- > --- > Result (cost=0.00..565831.43 rows=501 width=17) (actual > time=6080.778..6080.778 rows=0 loops=1) >-> Append (cost=0.00..565831.43 rows=501 width=17) (actual > time=6080.777..6080.777 rows=0 loops=1) > -> Seq Scan on rec_isins (cost=0.00..0.00 rows=1 width=36) > (actual time=0.001..0.001 rows=0 loops=1) >Filter: ((isin)::bpchar = 'foo'::bpchar) > -> Index Scan using rec_isins_archive_pkey on > rec_isins_archive rec_isins (cost=0.00..621.61 rows=405 width=17) > (actual time=10.335..10.335 rows=0 loops=1) >Index Cond: ((isin)::bpchar = 'foo'::bpchar) > -> Index Scan using rec_isins_current_pkey on > rec_isins_current rec_isins (cost=0.00..565209.82 rows=95 width=17) > (actual time=6070.440..6070.440 rows=0 loops=1) >Index Cond: ((isin)::bpchar = 'foo'::bpchar) Total > runtime: 6080.824 ms > > This is strange, because the archive table is four times larger than > the current table and the archive index is also four times larger than > the current index: > >relname| relfilenode | reltablespace | > pg_table_size > --+-+---+-- > - > --+-+---+ > rec_isins| 514533886 | 0 | > 8192 > rec_isins_pkey | 514533892 | 0 | > 8192 > rec_isins_attachment | 514533899 | 0 | > 8192 > --+-+---+-- > - > --+-+---+ > rec_isins_archive| 507194804 | 0 | > 10923393024 > rec_isins_archive_pkey | 507197615 | 139300915 | > 9048784896 > rec_isins_archive_attachment | 507197692 | 139300915 | > 4706050048 > --+-+---+-- > - > --+-+---+ > rec_isins_current| 631621090 | 0 | > 2696216576 > rec_isins_current_pkey | 631621096 | 139300915 | > 2098552832 > rec_isins_current_attachment | 631621107 | 139300915 | > 1160683520 > > Both tables are in the same tablespace (and thus on the same disk) and > both indexes are also in the same tablespace (but in another than the > tables). > The current table has been vacuumed full and reindexed. > > Can anybody explain the difference? Why is the current table so slow? > And what can we do to improve performance? > > Thanks for your help, > Christian > > > Deriva GmbH Financial IT and Consulting > Christian Schröder > Geschäftsführer > Hans-Böckler-Straße 2 | D-37079 Göttingen > Tel: +49 (0)551 489 500-42 > Fax: +49 (0)551 489 500-91 > http://www.deriva.de > > Amtsgerich
[GENERAL] Slow index performance
Hi all, we have a strange performance issue in one of our databases (using PostgreSQL 9.1.18). Maybe you can help me understand what’s going on. We have two identical tables (rec_isins_current, rec_isins_archive) with the following structure: Table "ts_frontend.rec_isins_current" Column | Type | Modifiers +-+--- attachment | integer | not null isin | isin| not null Indexes: "rec_isins_current_pkey" PRIMARY KEY, btree (attachment, isin), tablespace "extra" "rec_isins_current_attachment" btree (attachment), tablespace "extra" Foreign-key constraints: "rec_isins_attachment_fkey" FOREIGN KEY (attachment) REFERENCES ts_frontend.attachments(id) ON UPDATE RESTRICT ON DELETE CASCADE Inherits: ts_frontend.rec_isins The isin type is a domain type which has char(12) as its base type. Both tables inherit from ts_frontend.rec_isins, which is empty and is only used to search both tables in a single query. When we search for an isin in both tables (using the parent table, but the behavior is the same if we directly search in one of the tables), the primary key index is used. However, while the archive table is pretty fast, the current table is much slower: # explain analyze select * from ts_frontend.rec_isins where isin = 'foo'; QUERY PLAN - Result (cost=0.00..565831.43 rows=501 width=17) (actual time=6080.778..6080.778 rows=0 loops=1) -> Append (cost=0.00..565831.43 rows=501 width=17) (actual time=6080.777..6080.777 rows=0 loops=1) -> Seq Scan on rec_isins (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((isin)::bpchar = 'foo'::bpchar) -> Index Scan using rec_isins_archive_pkey on rec_isins_archive rec_isins (cost=0.00..621.61 rows=405 width=17) (actual time=10.335..10.335 rows=0 loops=1) Index Cond: ((isin)::bpchar = 'foo'::bpchar) -> Index Scan using rec_isins_current_pkey on rec_isins_current rec_isins (cost=0.00..565209.82 rows=95 width=17) (actual time=6070.440..6070.440 rows=0 loops=1) Index Cond: ((isin)::bpchar = 'foo'::bpchar) Total runtime: 6080.824 ms This is strange, because the archive table is four times larger than the current table and the archive index is also four times larger than the current index: relname| relfilenode | reltablespace | pg_table_size --+-+---+--- rec_isins| 514533886 | 0 | 8192 rec_isins_pkey | 514533892 | 0 | 8192 rec_isins_attachment | 514533899 | 0 | 8192 --+-+---+--- rec_isins_archive| 507194804 | 0 | 10923393024 rec_isins_archive_pkey | 507197615 | 139300915 |9048784896 rec_isins_archive_attachment | 507197692 | 139300915 |4706050048 --+-+---+--- rec_isins_current| 631621090 | 0 |2696216576 rec_isins_current_pkey | 631621096 | 139300915 |2098552832 rec_isins_current_attachment | 631621107 | 139300915 |1160683520 Both tables are in the same tablespace (and thus on the same disk) and both indexes are also in the same tablespace (but in another than the tables). The current table has been vacuumed full and reindexed. Can anybody explain the difference? Why is the current table so slow? And what can we do to improve performance? Thanks for your help, Christian Deriva GmbH Financial IT and Consulting Christian Schröder Geschäftsführer Hans-Böckler-Straße 2 | D-37079 Göttingen Tel: +49 (0)551 489 500-42 Fax: +49 (0)551 489 500-91 http://www.deriva.de Amtsgericht Göttingen | HRB 3240 Geschäftsführer: Christian Schröder -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] very slow queries and ineffective vacuum
2015-07-03 7:18 GMT+02:00 Sameer Kumar : > > > On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel < > lukasz.wro...@motorolasolutions.com> wrote: > >> Hello again. >> >> Thank you for all your responses. I will try to clarify more and attempt >> to answer the questions you raised. >> >> I'm attaching the postgresql.conf this time. I cannot supply you guys >> with a proper database schema, so I will try to supply you with some >> obfuscated logs and queries. Sorry for the complication. >> > > You postgresql.conf seems to have some issues. Can you explain about the > choice of parameter values for below parameters? > > maintenance_work_mem = 32MB > bgwriter_lru_maxpages = 0 > synchronous_commit = off > effective_cache_size is left to default > random_page_cost is left to default > > I don't know anything about your hardware- memory, cpu and disk layout > (and IOPS of disk) so can not really say what would be the right setting > but this certainly does not seem right to me. > > >> >> First of all I seem to have misdirected you guys about the pg_stat* >> tables. I have a virtual machine with the database from our test team, >> which was running for a month. When I deploy it, our java application is >> not running, so no queries are being executed. The pg_stat* tables contain >> no data (which is surprising). When I launch the application and queries >> start going, the stats are collected normally and autovacuums are being >> performed. >> > > It is still confusing to me. To help us understand can you specifically > tell if you see anything in pg_stat_user_tables and pg_stat_user_indexes? > > >> >> I attached the output of vacuum verbose command. >> >> Seems like a lot of your tables have bloats > > >> As for the pg_stat_activity, I have no "idle in transaction" records >> there, but I do have some in "idle" state, that don't disappear. Perhaps >> this means some sessions are not closed? I attached the query result as >> activity.txt. >> >> I also have a few "sending cancel to blocking autovacuum" and "canceling >> autovacuum task" messages in syslog. >> >> > Can you share some of these log files? > > > > >> Sample query explain analyze. This was ran after vacuum analyze of the >> entire database. >> >> explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 >> table84 LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT >> JOIN table19 table19 ON table84.col7 = table19.col7; >> >> QUERY PLAN >> >> - >> Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual >> time=4461.686..13457.233 rows=5749 loops=1) >>Hash Cond: (table57.col7 = table84.col7) >>-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963 >> width=57) (actual time=0.040..8981.438 rows=6789 loops=1) >>-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual >> time=4447.731..4447.731 rows=5749 loops=1) >> Buckets: 16384 Batches: 2 Memory Usage: 203kB >> -> Hash Right Join (cost=18080.66..42585.73 rows=189496 >> width=38) (actual time=1675.223..4442.046 rows=5749 loops=1) >>Hash Cond: (table19.col7 = table84.col7) >>-> Seq Scan on table19 table19 (cost=0.00..17788.17 >> rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1) >>-> Hash (cost=14600.96..14600.96 rows=189496 width=20) >> (actual time=1674.940..1674.940 rows=5749 loops=1) >> Buckets: 32768 Batches: 2 Memory Usage: 159kB >> -> Seq Scan on table84 table84 >> (cost=0.00..14600.96 rows=189496 width=20) (actual time=0.059..1661.482 >> rows=5749 loops=1) >> Total runtime: 13458.301 ms >> (12 rows) >> > > You have a lot of issues with this plan- > - The statistics is not updated > - There is a lot of hash join, sequential scan implying you don't have > proper indexes or those are not useful (meaning your indexes are bloated > too, consider reindexing them) > > > > >> >> Thank you again for your advice and I hope that with your help I'll be >> able to solve this issue. >> > I checked a VACUUM log, and it looks well - so maybe you run VACUUM with too small frequency and now some tables needs VACUUM FULL, and some indexes needs REINDEX. When your read 5000 rows 2sec, then some some is strange - probably too less data density in data file. If you do some massive cleaning, more than 30%, it is good idea to run VACUUM FULL, if it is possible manually. Or if you can - use partitioning - then you drop a partition without negative effect on other data. Regards Pavel > >> Best regards. >> Lukasz >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >
Re: [GENERAL] very slow queries and ineffective vacuum
On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel < lukasz.wro...@motorolasolutions.com> wrote: > Hello again. > > Thank you for all your responses. I will try to clarify more and attempt > to answer the questions you raised. > > I'm attaching the postgresql.conf this time. I cannot supply you guys with > a proper database schema, so I will try to supply you with some obfuscated > logs and queries. Sorry for the complication. > You postgresql.conf seems to have some issues. Can you explain about the choice of parameter values for below parameters? maintenance_work_mem = 32MB bgwriter_lru_maxpages = 0 synchronous_commit = off effective_cache_size is left to default random_page_cost is left to default I don't know anything about your hardware- memory, cpu and disk layout (and IOPS of disk) so can not really say what would be the right setting but this certainly does not seem right to me. > > First of all I seem to have misdirected you guys about the pg_stat* > tables. I have a virtual machine with the database from our test team, > which was running for a month. When I deploy it, our java application is > not running, so no queries are being executed. The pg_stat* tables contain > no data (which is surprising). When I launch the application and queries > start going, the stats are collected normally and autovacuums are being > performed. > It is still confusing to me. To help us understand can you specifically tell if you see anything in pg_stat_user_tables and pg_stat_user_indexes? > > I attached the output of vacuum verbose command. > > Seems like a lot of your tables have bloats > As for the pg_stat_activity, I have no "idle in transaction" records > there, but I do have some in "idle" state, that don't disappear. Perhaps > this means some sessions are not closed? I attached the query result as > activity.txt. > > I also have a few "sending cancel to blocking autovacuum" and "canceling > autovacuum task" messages in syslog. > > Can you share some of these log files? > Sample query explain analyze. This was ran after vacuum analyze of the > entire database. > > explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84 > LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN > table19 table19 ON table84.col7 = table19.col7; > QUERY > PLAN > > - > Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual > time=4461.686..13457.233 rows=5749 loops=1) >Hash Cond: (table57.col7 = table84.col7) >-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963 > width=57) (actual time=0.040..8981.438 rows=6789 loops=1) >-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual > time=4447.731..4447.731 rows=5749 loops=1) > Buckets: 16384 Batches: 2 Memory Usage: 203kB > -> Hash Right Join (cost=18080.66..42585.73 rows=189496 > width=38) (actual time=1675.223..4442.046 rows=5749 loops=1) >Hash Cond: (table19.col7 = table84.col7) >-> Seq Scan on table19 table19 (cost=0.00..17788.17 > rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1) >-> Hash (cost=14600.96..14600.96 rows=189496 width=20) > (actual time=1674.940..1674.940 rows=5749 loops=1) > Buckets: 32768 Batches: 2 Memory Usage: 159kB > -> Seq Scan on table84 table84 (cost=0.00..14600.96 > rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1) > Total runtime: 13458.301 ms > (12 rows) > You have a lot of issues with this plan- - The statistics is not updated - There is a lot of hash join, sequential scan implying you don't have proper indexes or those are not useful (meaning your indexes are bloated too, consider reindexing them) > > Thank you again for your advice and I hope that with your help I'll be > able to solve this issue. > > Best regards. > Lukasz > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Strange situation on slave server
On 01-07-2015 13:53, Condor wrote:
Hello,
I have master - slave replication hot standby. Both server are linux
slackware64 current with postgresql 9.4.4.
Today when I logged to check some things on slave server I see on top
memory taken 26%. That was strange for me and I restart server.
Well, I checked after 40 minute and I see again replication process is
little growing. For 10 min about 1.0.
postgres 14162 0.0 0.7 6514656 190684 ? S11:50 0:00
/usr/bin/postgres -D /var/lib/postgres/database -F
postgres 14163 0.4 5.4 6517856 1341268 ? Ss 11:50 0:26 \_
postgres: startup process recovering 000105E5005E
postgres 14166 0.1 5.4 6517876 1335980 ? Ss 11:50 0:09 \_
postgres: checkpointer process
postgres 14168 0.0 0.2 6517732 53276 ? Ss 11:50 0:01 \_
postgres: writer process
postgres 14180 0.0 0.0 29888 2864 ?Ss 11:50 0:01 \_
postgres: stats collector process
postgres 14183 0.2 0.0 6522488 5584 ?Ss 11:50 0:15 \_
postgres: wal receiver process streaming 5E5/5EE36000
Today our system is little bit average but slave server receive one
wal file every minute. As you can see checkpointer process and
recovering is 5.4% and until I write the email,
I check them again and now is 6.0
postgres 14162 0.0 0.7 6514656 190684 ? S11:50 0:00
/usr/bin/postgres -D /var/lib/postgres/database -F
postgres 14163 0.4 6.0 6517856 1499868 ? Ss 11:50 0:30 \_
postgres: startup process recovering 000105E50072
postgres 14166 0.1 6.0 6517876 1497064 ? Ss 11:50 0:10 \_
postgres: checkpointer process
postgres 14168 0.0 0.2 6517732 53276 ? Ss 11:50 0:01 \_
postgres: writer process
postgres 14180 0.0 0.0 29888 2864 ?Ss 11:50 0:01 \_
postgres: stats collector process
postgres 14183 0.2 0.0 6522488 5584 ?Ss 11:50 0:18 \_
postgres: wal receiver process streaming 5E5/72B49718
I have free memory:
Mem: 24634464k total, 14674532k used, 9959932k free, 324108k
buffers
Swap: 6589196k total, 1872k used, 6587324k free, 11819140k cached
I try to use strace on process:
# strace -p 14163
lseek(9, 0, SEEK_END) = 381943808
lseek(23, 0, SEEK_END) = 376832
lseek(5, 3563520, SEEK_SET) = 3563520
read(5,
"~\320\5\0\1\0\0\0\0`6C\345\5\0\0\344\7\0\0\0\0\0\0\0\0\0\0\333I\f\0"...,
8192) = 8192
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(9, 0, SEEK_END) = 381943808
lseek(5, 3571712, SEEK_SET) = 3571712
read(5,
"~\320\5\0\1\0\0\0\0\2006C\345\5\0\0^\6\0\0\0\0\0\0Ja.\0\20\0\220h"...,
8192) = 8192
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(10, 0, SEEK_END) = 186392576
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(11, 0, SEEK_END) = 182566912
read(3, 0x7ffd2d7a60d0, 16) = -1 EAGAIN (Resource
temporarily unavailable)
poll([{fd=3, events=POLLIN}], 1, 5000) = ? ERESTART_RESTARTBLOCK
(Interrupted by signal)
--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=14183,
si_uid=1000} ---
write(4, "\0", 1) = 1
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system
call)
read(3, "\0", 16) = 1
read(6, 0x7ffd2d7a611f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(5, 3579904, SEEK_SET) = 3579904
read(5,
"~\320\5\0\1\0\0\0\0\2406C\345\5\0\0\232\5\0\0\0\0\0\0\0\0\0\0*\231\1\0"...,
8192) = 8192
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(12, 0, SEEK_END) = 203612160
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(13, 0, SEEK_END) = 331071488
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(14, 0, SEEK_END) = 193331200
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(15, 0, SEEK_END) = 271171584
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(16, 0, SEEK_END) = 187580416
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(17, 0, SEEK_END) = 193257472
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(18, 0, SEEK_END) = 277381120
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(19, 0, SEEK_END) = 199884800
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(20, 0, SEEK_END) = 193396736
read(6, 0x7ffd2d7a672f, 1) = -1 EAGAIN (Resource
temporarily unavailable)
lseek(21,
Re: [GENERAL] very slow queries and ineffective vacuum
On Thu, 2 Jul 2015 12:58:18 +0200 Lukasz Wrobel wrote: > Hello again. > > Thank you for all your responses. I will try to clarify more and attempt to > answer the questions you raised. > > I'm attaching the postgresql.conf this time. I cannot supply you guys with > a proper database schema, so I will try to supply you with some obfuscated > logs and queries. Sorry for the complication. > > First of all I seem to have misdirected you guys about the pg_stat* tables. > I have a virtual machine with the database from our test team, which was > running for a month. When I deploy it, our java application is not running, > so no queries are being executed. The pg_stat* tables contain no data > (which is surprising). When I launch the application and queries start > going, the stats are collected normally and autovacuums are being performed. > > I attached the output of vacuum verbose command. > > As for the pg_stat_activity, I have no "idle in transaction" records there, > but I do have some in "idle" state, that don't disappear. Perhaps this > means some sessions are not closed? I attached the query result as > activity.txt. > > I also have a few "sending cancel to blocking autovacuum" and "canceling > autovacuum task" messages in syslog. > > Sample query explain analyze. This was ran after vacuum analyze of the > entire database. The analyze doesn't seem to be working terribly well. Looking at the explain, it expects 337963 rows in table57, but there are only 6789. There are similar discrepencies with table19 and table84. I don't know if indexes are your problem. Those three tables are pretty small, so the sequential scans should be pretty quick (probably faster than index scans, since it looks like most of the rows are returned from all the tables. I'm somewhat confused by your description of the situation. Is the performance problem happening on the virtual machine? Because VMs are notorious for being on oversubscribed hosts and exhibiting performance far below what is expected. It would be worthwhile to do some disk speed and CPU speed tests on the VM to see what kind of performance it's actually capable of ... if the VM is performing poorly, there's not much you can do with PostgreSQL to improve things. > explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84 > LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN > table19 table19 ON table84.col7 = table19.col7; > QUERY > PLAN > - > Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual > time=4461.686..13457.233 rows=5749 loops=1) >Hash Cond: (table57.col7 = table84.col7) >-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963 > width=57) (actual time=0.040..8981.438 rows=6789 loops=1) >-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual > time=4447.731..4447.731 rows=5749 loops=1) > Buckets: 16384 Batches: 2 Memory Usage: 203kB > -> Hash Right Join (cost=18080.66..42585.73 rows=189496 > width=38) (actual time=1675.223..4442.046 rows=5749 loops=1) >Hash Cond: (table19.col7 = table84.col7) >-> Seq Scan on table19 table19 (cost=0.00..17788.17 > rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1) >-> Hash (cost=14600.96..14600.96 rows=189496 width=20) > (actual time=1674.940..1674.940 rows=5749 loops=1) > Buckets: 32768 Batches: 2 Memory Usage: 159kB > -> Seq Scan on table84 table84 (cost=0.00..14600.96 > rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1) > Total runtime: 13458.301 ms > (12 rows) > > Thank you again for your advice and I hope that with your help I'll be able > to solve this issue. > > Best regards. > Lukasz -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running PostgreSQL with ZFS ZIL
On Thu, Jul 2, 2015 at 11:50 AM, Vick Khera wrote: > > On Thu, Jul 2, 2015 at 10:56 AM, Joseph Kregloh > wrote: > >> With my dataset I have been able to take advantage of the L2ARC. >> Currently using about 80GB on ARC and 260GB on L2ARC. With the ARC >> currently having the greater Hit ratio. >> > > Did you tell postgres that the effective_cache_size = Shared Buffers + > ARC? I don't include in postgres the L2ARC size as it is not really in > memory. Also, I lie to postgres about the random_page_cost > and seq_page_cost and set them both to 0.3 since there is so much cached. > In my current test config, I set the effective_cache_size to half the available ram, so roughly 50GBs. I have not tweaked the random_page_cost or seq_page_cost yet. > Set full_page_writes = off since ZFS already takes care of that for you, > too. > I currently have the full_page_writes on. I'll disable it and test. Thanks, -Joseph Kregloh
[GENERAL] Re: Getting the value of the old_tuple using the test_decoding extension and BDR
2015-07-02 16:11 GMT+02:00 Sylvain MARECHAL : > Hello all, > > [...] > > What I can see is that new values are present, and also old key when the > modification concerns the primary key. But the other old values do not seem > to be accessible. > > Is it a limitation of the test_decoding plugin, or old values are simply > not available? > I guess I simply should use the REPLICA IDENTITY ( http://www.postgresql.org/docs/9.4/static/sql-altertable.html) <<< ALTER TABLE [...] REPLICA IDENTITY [...] FULL records the old values of all columns in the row. >>> Sylvain
Re: [GENERAL] Running PostgreSQL with ZFS ZIL
On Thu, Jul 2, 2015 at 10:56 AM, Joseph Kregloh wrote: > With my dataset I have been able to take advantage of the L2ARC. Currently > using about 80GB on ARC and 260GB on L2ARC. With the ARC currently having > the greater Hit ratio. > Did you tell postgres that the effective_cache_size = Shared Buffers + ARC? I don't include in postgres the L2ARC size as it is not really in memory. Also, I lie to postgres about the random_page_cost and seq_page_cost and set them both to 0.3 since there is so much cached. Set full_page_writes = off since ZFS already takes care of that for you, too.
Re: [GENERAL] record from plpgsql function performance
Thanks, David. Works perfect. Best Regards, Alexander Shereshevsky +972-52-7460635 On Thu, Jul 2, 2015 at 5:47 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, July 2, 2015, Alexander Shereshevsky > wrote: > >> Hello, >> >> I have some simple function. The returned data set is generated based on >> view (dynamic - can be changed on daily basis). >> So the function was defined this way: >> >> 1. returns setof some_view as ... >> 2. inside the function I'm generating dynamic SQL into v_sql variable. >> 3. return query execute v_sql >> >> Everything works fine if I'm running single function, like: >> select * from function (param1, param2) >> I'm getting the delimited fields in desired order. >> >> But if I want to run the function in inline mode, like: >> select function(param1, param2) from some_table; >> so the returned datatype is record. >> >> To return multiple fields, I'm trying to use: >> select (function(param1, param2)).* from some_table; >> But this operation has a huge performance impact, IMHO runtime multiplied >> by number of columns - the function is executed for each column separately. >> In my case normal inline run is about 2 seconds for 300-400 records, but >> with ().* it's increased to 90-120 seconds. >> >> Thank you in advance if you can suggest me the better way. >> >> BR, >> Alexander Shereshevsky >> > > Use LATERAL. > > If that is not an option you place the unexpanded function call in a CTE > (with) and the expand it within the main query. > > With funccall as ( select func(arg) from tbl ) > Select (func).* from funccall; > > Because, yes the function is called once for each column due to the star > expansion. You have to keep the result as a composite type during function > execution and then expand the composite type. > > David J. >
Re: [GENERAL] very slow queries and ineffective vacuum
Well, right off the bat, it looks like you do not have indexes on table84.col7 table57.col7 table19.col7 At least a quick review of the query plan shows they are not being used if they do exist. So perhaps that is one of the chief causes for slow performance. On Thu, Jul 2, 2015 at 6:58 AM, Lukasz Wrobel < lukasz.wro...@motorolasolutions.com> wrote: > Hello again. > > Thank you for all your responses. I will try to clarify more and attempt > to answer the questions you raised. > > I'm attaching the postgresql.conf this time. I cannot supply you guys with > a proper database schema, so I will try to supply you with some obfuscated > logs and queries. Sorry for the complication. > > First of all I seem to have misdirected you guys about the pg_stat* > tables. I have a virtual machine with the database from our test team, > which was running for a month. When I deploy it, our java application is > not running, so no queries are being executed. The pg_stat* tables contain > no data (which is surprising). When I launch the application and queries > start going, the stats are collected normally and autovacuums are being > performed. > > I attached the output of vacuum verbose command. > > As for the pg_stat_activity, I have no "idle in transaction" records > there, but I do have some in "idle" state, that don't disappear. Perhaps > this means some sessions are not closed? I attached the query result as > activity.txt. > > I also have a few "sending cancel to blocking autovacuum" and "canceling > autovacuum task" messages in syslog. > > Sample query explain analyze. This was ran after vacuum analyze of the > entire database. > > explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84 > LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN > table19 table19 ON table84.col7 = table19.col7; > QUERY > PLAN > > - > Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual > time=4461.686..13457.233 rows=5749 loops=1) >Hash Cond: (table57.col7 = table84.col7) >-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963 > width=57) (actual time=0.040..8981.438 rows=6789 loops=1) >-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual > time=4447.731..4447.731 rows=5749 loops=1) > Buckets: 16384 Batches: 2 Memory Usage: 203kB > -> Hash Right Join (cost=18080.66..42585.73 rows=189496 > width=38) (actual time=1675.223..4442.046 rows=5749 loops=1) >Hash Cond: (table19.col7 = table84.col7) >-> Seq Scan on table19 table19 (cost=0.00..17788.17 > rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1) >-> Hash (cost=14600.96..14600.96 rows=189496 width=20) > (actual time=1674.940..1674.940 rows=5749 loops=1) > Buckets: 32768 Batches: 2 Memory Usage: 159kB > -> Seq Scan on table84 table84 (cost=0.00..14600.96 > rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1) > Total runtime: 13458.301 ms > (12 rows) > > Thank you again for your advice and I hope that with your help I'll be > able to solve this issue. > > Best regards. > Lukasz > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Running PostgreSQL with ZFS ZIL
On Thu, Jul 2, 2015 at 9:35 AM, Vick Khera wrote: > > On Wed, Jul 1, 2015 at 5:07 PM, Joseph Kregloh > wrote: > >> We recently built a new server for our Production database. The machine >> is top of the line with 128GB of RAM, dual E5-2650. We also included NVME >> drives for ZIL and L2ARC. Currently we have 3 zpools. First one holds the >> FreeBSD install. Second holds the jails, and third holds all of the >> database data. Needless to say it's fast. >> > > FWIW I did not find having a ZIL beneficial for my workload on a similarly > huge servers, also running FreeBSD 10. I do have the L2ARC on the SSD, but > the size of my data set usually leaves the ARC as sufficient to handle > almost all requests. That is, the L2ARC is mostly empty most of the time > (or at least never gets re-fetched from). > With my dataset I have been able to take advantage of the L2ARC. Currently using about 80GB on ARC and 260GB on L2ARC. With the ARC currently having the greater Hit ratio. ARC Size: 66.77% 82.41 GiB Target Size: (Adaptive) 66.79% 82.44 GiB Min Size (Hard Limit): 12.50% 15.43 GiB Max Size (High Water): 8:1 123.44 GiB ARC Efficiency: 424.85m Cache Hit Ratio:97.39% 413.76m Cache Miss Ratio: 2.61% 11.09m Actual Hit Ratio: 93.08% 395.43m L2 ARC Size: (Adaptive) 264.37 GiB Header Size:0.18% 485.87 MiB L2 ARC Breakdown: 11.09m Hit Ratio: 7.13% 790.96k Miss Ratio: 92.87% 10.30m Feeds: 122.76k > I'd start by testing the speed of the driver running the NVME drive. Does > it show up as a normal drive in FreeBSD? I've only ever used regular Intel > SSDs. I don't know if NVME devices connect differently. I hear if you ask > very nicely to the right people you can get special drivers for some fancy > PCI-e based "drives" which make them really fast. > Both SSDs are the same part for the ZIL and L2ARC. I am currently testing some of our most heavy procedures with ZIL enabled and with ZIL disabled. Thanks, -Joseph Kregloh
Re: [GENERAL] record from plpgsql function performance
On Thursday, July 2, 2015, Alexander Shereshevsky wrote: > Hello, > > I have some simple function. The returned data set is generated based on > view (dynamic - can be changed on daily basis). > So the function was defined this way: > > 1. returns setof some_view as ... > 2. inside the function I'm generating dynamic SQL into v_sql variable. > 3. return query execute v_sql > > Everything works fine if I'm running single function, like: > select * from function (param1, param2) > I'm getting the delimited fields in desired order. > > But if I want to run the function in inline mode, like: > select function(param1, param2) from some_table; > so the returned datatype is record. > > To return multiple fields, I'm trying to use: > select (function(param1, param2)).* from some_table; > But this operation has a huge performance impact, IMHO runtime multiplied > by number of columns - the function is executed for each column separately. > In my case normal inline run is about 2 seconds for 300-400 records, but > with ().* it's increased to 90-120 seconds. > > Thank you in advance if you can suggest me the better way. > > BR, > Alexander Shereshevsky > Use LATERAL. If that is not an option you place the unexpanded function call in a CTE (with) and the expand it within the main query. With funccall as ( select func(arg) from tbl ) Select (func).* from funccall; Because, yes the function is called once for each column due to the star expansion. You have to keep the result as a composite type during function execution and then expand the composite type. David J.
[GENERAL] record from plpgsql function performance
Hello, I have some simple function. The returned data set is generated based on view (dynamic - can be changed on daily basis). So the function was defined this way: 1. returns setof some_view as ... 2. inside the function I'm generating dynamic SQL into v_sql variable. 3. return query execute v_sql Everything works fine if I'm running single function, like: select * from function (param1, param2) I'm getting the delimited fields in desired order. But if I want to run the function in inline mode, like: select function(param1, param2) from some_table; so the returned datatype is record. To return multiple fields, I'm trying to use: select (function(param1, param2)).* from some_table; But this operation has a huge performance impact, IMHO runtime multiplied by number of columns - the function is executed for each column separately. In my case normal inline run is about 2 seconds for 300-400 records, but with ().* it's increased to 90-120 seconds. Thank you in advance if you can suggest me the better way. BR, Alexander Shereshevsky
[GENERAL] Getting the value of the old_tuple using the test_decoding extension and BDR
Hello all, I am playing with the test_decoding extension to be notified on database changes in a couple of databases replicated with BDR. What I can see is that new values are present, and also old key when the modification concerns the primary key. But the other old values do not seem to be accessible. Is it a limitation of the test_decoding plugin, or old values are simply not available? Thanks, Sylvain
Re: [GENERAL] Running PostgreSQL with ZFS ZIL
On Wed, Jul 1, 2015 at 5:07 PM, Joseph Kregloh wrote: > We recently built a new server for our Production database. The machine is > top of the line with 128GB of RAM, dual E5-2650. We also included NVME > drives for ZIL and L2ARC. Currently we have 3 zpools. First one holds the > FreeBSD install. Second holds the jails, and third holds all of the > database data. Needless to say it's fast. > FWIW I did not find having a ZIL beneficial for my workload on a similarly huge servers, also running FreeBSD 10. I do have the L2ARC on the SSD, but the size of my data set usually leaves the ARC as sufficient to handle almost all requests. That is, the L2ARC is mostly empty most of the time (or at least never gets re-fetched from). I'd start by testing the speed of the driver running the NVME drive. Does it show up as a normal drive in FreeBSD? I've only ever used regular Intel SSDs. I don't know if NVME devices connect differently. I hear if you ask very nicely to the right people you can get special drivers for some fancy PCI-e based "drives" which make them really fast.
Re: [GENERAL] Ubuntu 14.04 LTS install problem
solved... wrong ubuntu-version (unicorn instead of trusty) in /pgdg.list thanks to all Urs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
