Re: Searching in varchar column having 100M records
*Please recheck with track_io_timing = on in configuration. explain (analyze,buffers) with this option will report how many time we spend during i/o* *> Buffers: shared hit=2 read=31492* *31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD* *Your query reads table data from disks (well, or from OS cache). You need more RAM for shared_buffers or disks with better performance.* Thanks Sergei.. *track_io_timing = on helps.. Following is the result after changing that config.* Aggregate (cost=10075.78..10075.79 rows=1 width=8) (actual time=63088.198..63088.199 rows=1 loops=1) Buffers: shared read=31089 I/O Timings: read=61334.252 -> Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=0) (actual time=69.509..63021.448 rows=31414 loops=1) Recheck Cond: ((field)::text = 'Klein'::text) Heap Blocks: exact=30999 Buffers: shared read=31089 I/O Timings: read=61334.252 -> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586 width=0) (actual time=58.671..58.671 rows=31414 loops=1) Index Cond: ((field)::text = 'Klein'::text) Buffers: shared read=90 I/O Timings: read=45.316 Planning Time: 66.435 ms Execution Time: 63088.774 ms *So try something like* *CREATE INDEX ios_idx ON table (field, user_id);* *and make sure the table is vacuumed often enough (so that the visibility* *map is up to date).* Thanks Tomas... I tried this and result improved but not much. Thanks Andreas, David, Gavin *Any particular reason for using varchar instead of text, for field?* No use UUID for the user_id. Agreed *Regards,Mayank* On Thu, Jul 18, 2019 at 4:25 AM Gavin Flower wrote: > On 17/07/2019 23:03, mayank rupareliya wrote: > [...] > > Table and index are created using following query. > > > > create table fields(user_id varchar(64), field varchar(64)); > > CREATE INDEX index_field ON public.fields USING btree (field); > > [...] > > Any particular reason for using varchar instead of text, for field? > > Also, as Andreas pointed out, use UUID for the user_id. > > > Cheers, > Gavin > > > >
Re: Searching in varchar column having 100M records
On Thu, Jul 18, 2019 at 05:21:49PM +0530, mayank rupareliya wrote: *Please recheck with track_io_timing = on in configuration. explain (analyze,buffers) with this option will report how many time we spend during i/o* *> Buffers: shared hit=2 read=31492* *31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD* *Your query reads table data from disks (well, or from OS cache). You need more RAM for shared_buffers or disks with better performance.* Thanks Sergei.. *track_io_timing = on helps.. Following is the result after changing that config.* Aggregate (cost=10075.78..10075.79 rows=1 width=8) (actual time=63088.198..63088.199 rows=1 loops=1) Buffers: shared read=31089 I/O Timings: read=61334.252 -> Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=0) (actual time=69.509..63021.448 rows=31414 loops=1) Recheck Cond: ((field)::text = 'Klein'::text) Heap Blocks: exact=30999 Buffers: shared read=31089 I/O Timings: read=61334.252 -> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586 width=0) (actual time=58.671..58.671 rows=31414 loops=1) Index Cond: ((field)::text = 'Klein'::text) Buffers: shared read=90 I/O Timings: read=45.316 Planning Time: 66.435 ms Execution Time: 63088.774 ms How did that help? It only gives you insight that it's really the I/O that takes time. You need to reduce that, somehow. *So try something like* *CREATE INDEX ios_idx ON table (field, user_id);* *and make sure the table is vacuumed often enough (so that the visibility* *map is up to date).* Thanks Tomas... I tried this and result improved but not much. Well, you haven't shown us the execution plan, so it's hard to check why it did not help much and give you further advice. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Perplexing, regular decline in performance
On Tue, 25 Jun 2019 at 12:23, Justin Pryzby wrote: > It's possible that the "administrative" queries are using up lots of your > shared_buffers, which are (also/more) needed by the customer-facing > queries. I > would install pg_buffercache to investigate. Or, just pause the admin > queries > and see if that the issue goes away during that interval ? > > SELECT 1.0*COUNT(1)/sum(count(1))OVER(), COUNT(1), > COUNT(nullif(isdirty,'f')), datname, COALESCE(c.relname, > b.relfilenode::text), d.relname TOAST, > 1.0*COUNT(nullif(isdirty,'f'))/count(1) dirtyfrac, avg(usagecount) FROM > pg_buffercache b JOIN pg_database db ON b.reldatabase=db.oid LEFT JOIN > pg_class c ON b.relfilenode=pg_relation_filenode(c.oid) LEFT JOIN pg_class > d ON c.oid=d.reltoastrelid GROUP BY 4,5,6 ORDER BY 1 DESC LIMIT 9; > I've been going by a couple of articles I found about interpreting pg_buffercache ( https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers), and so far shared buffers look okay. Our database is 486 GB, with shared buffers set to 32 GB. The article suggests a query that can provide a guideline for what shared buffers should be: SELECT pg_size_pretty(count(*) * 8192) as ideal_shared_buffers FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) WHERE usagecount >= 3; This comes out to 25 GB, and even dropping the usage count to 1 only raises it to 30 GB. I realise this is only a guideline, and I may bump it to 36 GB, to give a bit more space. I did run some further queries to look at usage (based on the same article), and most of the tables that have very high usage on all the buffered data are 100% buffered, so, if I understand it correctly, there should be little churn there. The others seem to have sufficient less-accessed space to make room for data that they need to buffer: relname | buffered | buffers_percent | percent_of_relation -+--+-+- position| 8301 MB |25.3 |99.2 stat_position_click | 7359 MB |22.5 |76.5 url | 2309 MB | 7.0 | 100.0 pg_toast_19788 | 1954 MB | 6.0 |49.3 (harvested_job) stat_sponsored_position | 1585 MB | 4.8 |92.3 location| 927 MB | 2.8 |98.7 pg_toast_20174 | 866 MB | 2.6 | 0.3 (page) pg_toast_20257 | 678 MB | 2.1 |92.9 (position_index) harvested_job | 656 MB | 2.0 | 100.0 stat_employer_click | 605 MB | 1.8 | 100.0 usagecount >= 5 relname | pg_size_pretty -+ harvested_job | 655 MB location| 924 MB pg_toast_19788 | 502 MB pg_toast_20174 | 215 MB pg_toast_20257 | 677 MB position| 8203 MB stat_employer_click | 605 MB stat_position_click | 79 MB stat_sponsored_position | 304 kB url | 2307 MB usagecount >= 3 relname | pg_size_pretty -+ harvested_job | 656 MB location| 927 MB pg_toast_19788 | 1809 MB pg_toast_20174 | 589 MB pg_toast_20257 | 679 MB position| 8258 MB stat_employer_click | 605 MB stat_position_click | 716 MB stat_sponsored_position | 2608 kB url | 2309 MB usagecount >= 1 relname | pg_size_pretty -+ harvested_job | 656 MB location| 928 MB pg_toast_19788 | 3439 MB pg_toast_20174 | 842 MB pg_toast_20257 | 680 MB position| 8344 MB stat_employer_click | 605 MB stat_position_click | 4557 MB stat_sponsored_position | 86 MB url | 2309 MB If I'm misreading this, please let me know. I know people also asked about query plans and schema, which I'm going to look at next; I've just been knocking off one thing at at time. Thanks, Hugh
Re: Searching in varchar column having 100M records
On 18/07/2019 23:51, mayank rupareliya wrote: [...] Thanks Andreas, David, Gavin /Any particular reason for using varchar instead of text, for field?/ No use UUID for the user_id.Agreed /[...]/ /Use of text is preferred, but I can't see it making any significant difference to performance -- but I could be wrong!/ /Cheers, Gavin /
Re: Perplexing, regular decline in performance
Hi, On 2019-07-18 16:01:46 -0400, Hugh Ranalli wrote: > I've been going by a couple of articles I found about interpreting > pg_buffercache ( > https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers), > and so far shared buffers look okay. Our database is 486 GB, with shared > buffers set to 32 GB. The article suggests a query that can provide a > guideline for what shared buffers should be: > > SELECT > pg_size_pretty(count(*) * 8192) as ideal_shared_buffers > FROM > pg_class c > INNER JOIN > pg_buffercache b ON b.relfilenode = c.relfilenode > INNER JOIN > pg_database d ON (b.reldatabase = d.oid AND d.datname = > current_database()) > WHERE > usagecount >= 3; IMO that's not a meaningful way to determine the ideal size of shared buffers. Except for the case where shared buffers is bigger than the entire working set (not just the hot working set), it's going to give you completely bogus results. Pretty much by definition it cannot give you a shared buffers size bigger than what it's currently set to, given that it starts with the number of shared buffers. And there's plenty scenarios where you'll commonly see many frequently (but not most frequently) used buffers with a usagecount < 3 even = 0. If you e.g. have a shared_buffers size that's just a few megabytes too small, you'll need to throw some buffers out of shared buffers - that means the buffer replacement search will go through all shared buffers and decrement the usagecount by one, until it finds a buffer with a count of 0 (before it has decremented the count). Which means it's extremely likely that there's moments where a substantial number of frequently used buffers have a lowered usagecount (perhaps even 0). Therefore, the above query will commonly give you a lower number than shared buffers, if your working set size is *bigger* than shared memory. I think you can assume that shared buffers is too big if a substantial portion of buffers have relfilenode IS NOT NULL (i.e. are unused); at least if you don't continually also DROP/TRUNCATE relations. If there's a large fluctuation about which parts of buffercache has a high usagecount, then that's a good indication that very frequently new buffers are needed (because that lowers a good portion of buffers to usagecount 0). I've had decent success in the past getting insights with a query like: SELECT ceil(bufferid/(nr_buffers/subdivisions::float))::int AS part, to_char(SUM((relfilenode IS NOT NULL)::int) / count(*)::float * 100, '999D99') AS pct_used, to_char(AVG(usagecount), '9D9') AS avg_usagecount, to_char(SUM((usagecount=0)::int) / SUM((relfilenode IS NOT NULL)::int)::float8 * 100, '999D99') AS pct_0 FROM pg_buffercache, (SELECT 10) AS x(subdivisions), (SELECT setting::int nr_buffers FROM pg_settings WHERE name = 'shared_buffers') s GROUP BY 1 ORDER BY 1; which basically subdivides pg_buffercache's output into 10 parts (or use as much as fit comfortable in one screen / terminal). Here's e.g. the output of a benchmark (pgbench) running against a database that's considerably smaller than shared memory (15GB database, 1.5GB shared_buffers): ┌──┬──┬┬─┐ │ part │ pct_used │ avg_usagecount │ pct_0 │ ├──┼──┼┼─┤ │1 │ 100.00 │ 1.0 │ 42.75 │ │2 │ 100.00 │ .6 │ 47.85 │ │3 │ 100.00 │ .6 │ 47.25 │ │4 │ 100.00 │ .6 │ 47.52 │ │5 │ 100.00 │ .6 │ 47.18 │ │6 │ 100.00 │ .5 │ 48.47 │ │7 │ 100.00 │ .5 │ 49.00 │ │8 │ 100.00 │ .5 │ 48.52 │ │9 │ 100.00 │ .5 │ 49.27 │ │ 10 │ 100.00 │ .5 │ 49.58 │ │ 11 │ 99.98 │ .6 │ 46.88 │ │ 12 │ 100.00 │ .6 │ 45.23 │ │ 13 │ 100.00 │ .6 │ 45.03 │ │ 14 │ 100.00 │ .6 │ 44.90 │ │ 15 │ 100.00 │ .6 │ 46.08 │ │ 16 │ 100.00 │ .6 │ 44.84 │ │ 17 │ 100.00 │ .6 │ 45.88 │ │ 18 │ 100.00 │ .6 │ 46.46 │ │ 19 │ 100.00 │ .6 │ 46.64 │ │ 20 │ 100.00 │ .6 │ 47.05 │ └──┴──┴┴─┘ As you can see usagecounts are pretty low overall. That's because the buffer replacement rate is so high, that the usagecount is very frequently reduced to 0 (to get new buffers). You can infer from that, that unless you add a lot of shared buffers, you're not likely going to make a huge difference (but if you set it 16GB, it'd obviously look much better). In contrast to that, here's pgbench running on a smaller database, that nearly fits into shared buffers (2GB DB, 1.5GB shared_buffers): ┌──┬──┬┬─┐ │ part │ pct_used │ avg_usagecount │ pct_0 │ ├──┼──┼┼─┤ │1 │ 100.00 │ 3.9 │1.45
