Re: Performance of INSERT into temporary tables using psqlODBC driver
Hello Tim, I have tried the suggestions provided to the best of my knowledge, but I did not see any improvement in the INSERT performance for temporary tables. The Linux host on which PostgreSQL database is installed has 32 GB RAM. Following are current settings I have in postgresql.conf file: shared_buffers = 8GB temp_buffers = 256MB work_mem = 256MB maintenance_work_mem = 256MB wal_buffers = 256MB checkpoint_timeout = 30min checkpoint_completion_target = 0.75 max_wal_size = 1GB effective_cache_size = 16GB >>- Increase work_mem to reduce use of temp files. Need it to be 2 to 3 >> times largest temp file (but use common sense) >I have already increased the work_mem and maintenance_work_mem to 256MB. I >will check on the temp file sizes and adjust the work_mem parameter as you >suggested. >- Tweak wal checkpoint parameters to prevent wal checkpoints occurring > too frequently. Note that there is a play off here between frequency > of checkpoints and boot time after a crash. Fewer wal checkpoints will > usually improve performance, but recovery time is longer. >How effectively you can increase insert times will depend on what the >memory and cpu profile of the system is. More memory, less use of temp >files, faster system, so spend a bit of time to make sure your system is >configured to squeeze as much out of that RAM as you can! Please let me know if there are any other suggestions that I can try. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Select count(*) on a 2B Rows Tables Takes ~20 Hours
Based on my research in the forums and Google , it is described in multiple places that ‘select count(*)’ is expected to be slow in Postgres because of the MVCC controls imposed upon the query leading a table scan. Also, the elapsed time increase linearly with table size. However, I do not know if elapsed time I’m getting is to be expected. Table reltuples in pg_class = 2,266,649,344 (pretty close) Query = select count(*) from jim.sttyations ; Elapsed time (ET) = 18.5 hrs This is an Aurora cluster running on r4.2xlarge (8 vCPU, 61g). CPU usage during count run hovers around 20% with 20g of freeable memory. Is this ET expected? If not, what could be slowing it down? I’m currently running explain analyze and I’ll share the final output when done. I’m familiar with the ideas listed here https://www.citusdata.com/blog/2016/10/12/count-performance/ Table "jim.sttyations" Column | Type | Modifiers | Storage | Stats target | Description ---+--++--+--+- stty_id| bigint | not null | plain| | stty_hitlist_line | text | not null | extended | | stty_status| text | not null default 'Y'::text | extended | | stty_status_date | timestamp with time zone | not null | plain| | vs_number | integer | not null | plain| | stty_date_created | timestamp with time zone | not null | plain| | stty_stty_id| bigint || plain| | stty_position | bigint || plain| | mstty_id | bigint || plain| | vsr_number| integer || plain| | stty_date_modified | timestamp with time zone || plain| | stty_stored| text | not null default 'N'::text | extended | | stty_sequence | text || extended | | stty_hash | text || extended | | Indexes: "stty_pk" PRIMARY KEY, btree (stty_id) "stty_indx_fk01" btree (stty_stty_id) "stty_indx_fk03" btree (vsr_number) "stty_indx_fk04" btree (vs_number) "stty_indx_pr01" btree (mstty_id, stty_id) Check constraints: "stty_cnst_ck01" CHECK (stty_status = ANY (ARRAY['Y'::text, 'N'::text])) "stty_cnst_ck02" CHECK (stty_stored = ANY (ARRAY['N'::text, 'Y'::text])) Foreign-key constraints: "stty_cnst_fk01" FOREIGN KEY (stty_stty_id) REFERENCES sttyations(stty_id) NOT VALID "stty_cnst_fk02" FOREIGN KEY (mstty_id) REFERENCES master_sttyations(mstty_id) "stty_cnst_fk03" FOREIGN KEY (vsr_number) REFERENCES valid_status_reasons(vsr_number) Thank you refpep-> select count(*) from jim.sttyations; QUERY PLAN -- Aggregate (cost=73451291.77..73451291.78 rows=1 width=8) Output: count(*) -> Index Only Scan using stty_indx_fk03 on jim.sttyations (cost=0.58..67784668.41 rows=2266649344 width=0) Output: vsr_number (4 rows)
Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
On Thu, Sep 13, 2018 at 01:33:54PM -0400, Fd Habash wrote: > Is this ET expected? If not, what could be slowing it down? I’m currently > running explain analyze and I’ll share the final output when done. explain(analyze,BUFFERS) is what's probably interesting You're getting an index-only-scan, but maybe still making many accesses to the heap (table) for pages which aren't all-visible. You can maybe improve by vacuuming (perhaps by daily cronjob or by ALTER TABLE SET autovacuum threshold or scale factor). -- Justin Pryzby System Administrator Telsasoft +1-952-707-8581
Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Fd Habash writes: > Based on my research in the forums and Google , it is described in multiple > places that ‘select count(*)’ is expected to be slow in Postgres because of > the MVCC controls imposed upon the query leading a table scan. Also, the > elapsed time increase linearly with table size. > However, I do not know if elapsed time I’m getting is to be expected. > Table reltuples in pg_class = 2,266,649,344 (pretty close) > Query = select count(*) from jim.sttyations ; > Elapsed time (ET) = 18.5 hrs That's pretty awful. My recollection is that in recent PG releases, SELECT COUNT(*) runs at something on the order of 100ns/row given an all-in-memory table. Evidently you're rather badly I/O bound. > This is an Aurora cluster running on r4.2xlarge (8 vCPU, 61g). Don't know much about Aurora, but I wonder whether you paid for guaranteed (provisioned) IOPS, and if so what service level. > refpep-> select count(*) from jim.sttyations; > QUERY PLAN > > -- > Aggregate (cost=73451291.77..73451291.78 rows=1 width=8) >Output: count(*) >-> Index Only Scan using stty_indx_fk03 on jim.sttyations > (cost=0.58..67784668.41 rows=2266649344 width=0) > Output: vsr_number > (4 rows) Oh, hmm ... the 100ns figure I mentioned was for a seqscan. IOS could be a lot worse for a number of reasons, foremost being that if the table isn't mostly all-visible then it'd involve a lot of random heap access. It might be interesting to try forcing a seqscan plan (see enable_indexscan). regards, tom lane
RE: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Just checked metrics while the count was running … Read latency < 3.5 ms Write latency < 4 ms Read throughput ~ 40 MB/sec with sporadic peaks at 100 Read IOPS ~ 5000 QDepth < 3 Thank you From: Tom Lane Sent: Thursday, September 13, 2018 2:12 PM To: Fd Habash Cc: [email protected] Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours Fd Habash writes: > Based on my research in the forums and Google , it is described in multiple > places that ‘select count(*)’ is expected to be slow in Postgres because of > the MVCC controls imposed upon the query leading a table scan. Also, the > elapsed time increase linearly with table size. > However, I do not know if elapsed time I’m getting is to be expected. > Table reltuples in pg_class = 2,266,649,344 (pretty close) > Query = select count(*) from jim.sttyations ; > Elapsed time (ET) = 18.5 hrs That's pretty awful. My recollection is that in recent PG releases, SELECT COUNT(*) runs at something on the order of 100ns/row given an all-in-memory table. Evidently you're rather badly I/O bound. > This is an Aurora cluster running on r4.2xlarge (8 vCPU, 61g). Don't know much about Aurora, but I wonder whether you paid for guaranteed (provisioned) IOPS, and if so what service level. > refpep-> select count(*) from jim.sttyations; > QUERY PLAN > > -- > Aggregate (cost=73451291.77..73451291.78 rows=1 width=8) >Output: count(*) >-> Index Only Scan using stty_indx_fk03 on jim.sttyations > (cost=0.58..67784668.41 rows=2266649344 width=0) > Output: vsr_number > (4 rows) Oh, hmm ... the 100ns figure I mentioned was for a seqscan. IOS could be a lot worse for a number of reasons, foremost being that if the table isn't mostly all-visible then it'd involve a lot of random heap access. It might be interesting to try forcing a seqscan plan (see enable_indexscan). regards, tom lane
Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Hi, On 2018-09-13 14:12:02 -0400, Tom Lane wrote: > > This is an Aurora cluster running on r4.2xlarge (8 vCPU, 61g). > > Don't know much about Aurora, but I wonder whether you paid for > guaranteed (provisioned) IOPS, and if so what service level. Given that aurora uses direct-io and has the storage layer largely completely replaced, I'm not sure how much we can help here. My understanding is that access to blocks can require page-level "log reconciliation", which can cause adverse IO patterns. The direct-IO means that cache configuration / prefetching is much more crucial. If a lot of those tuples aren't frozen (don't quite know how that works there), the clog accesses will also kill you if the table was filled over many transactions, since clog's access characteristics to a lot of xids is pretty bad with DIO. Greetings, Andres Freund
How Do You Associate a Query With its Invoking Procedure?
In API function may invoke 10 queries. Ideally, I would like to know what queries are invoked by it and how long each took. I’m using pg_stat_statement. I can see the API function statement, but how do I deterministically identify all queries invoked by it? Thank you
Re: Performance of INSERT into temporary tables using psqlODBC driver
padusuma writes: > Hello Tim, > > I have tried the suggestions provided to the best of my knowledge, but I did > not see any improvement in the INSERT performance for temporary tables. The > Linux host on which PostgreSQL database is installed has 32 GB RAM. > Following are current settings I have in postgresql.conf file: > shared_buffers = 8GB > temp_buffers = 256MB > work_mem = 256MB > maintenance_work_mem = 256MB > wal_buffers = 256MB > > checkpoint_timeout = 30min > checkpoint_completion_target = 0.75 > max_wal_size = 1GB > > effective_cache_size = 16GB > >>>- Increase work_mem to reduce use of temp files. Need it to be 2 to 3 >>> times largest temp file (but use common sense) > >>I have already increased the work_mem and maintenance_work_mem to 256MB. I >>will check on the temp file sizes and adjust the work_mem parameter as you >>suggested. > >>- Tweak wal checkpoint parameters to prevent wal checkpoints occurring >> too frequently. Note that there is a play off here between frequency >> of checkpoints and boot time after a crash. Fewer wal checkpoints will >> usually improve performance, but recovery time is longer. > >>How effectively you can increase insert times will depend on what the >>memory and cpu profile of the system is. More memory, less use of temp >>files, faster system, so spend a bit of time to make sure your system is >>configured to squeeze as much out of that RAM as you can! > > Please let me know if there are any other suggestions that I can try. How are you gathering metrics to determine if performance has improved or not? Have you seen any change in your explain (analyze, buffers) plans? Make sure your table statistics are all up-to-date before performing each benchmark test. I often turn off autovacuum when doing this sort of testing so that I know exactly when tables get vacuumed and statistics get updated (just ensure you remember to turn it back on when your finished!). Are the wal checkpoints being triggered every 30 mins or more frequently? Are you still seeing the system use lots of temp files? Do you have any indexes on the tables your inserting into? As mentioned previously, there are no simple/quick fixes here - you cannot just change a setting and see performance improve. It will be necessary to do a lot of experimentation, gathering statistics and investigate how postgres is using buffers, disk IO etc. All of these parameters interact with each other, so it is critical you have good metrics to see exactly what your changes do. It is complex and time consuming. Highly recommend PostgreSQL: High Performance (Ahmed & SMith) and Mastering Postgres (Shonig) for valuable background/tips - there really is just far too much to communicate effectively via email. Tim -- Tim Cross
