Re: [PERFORM] Perform scan on Toast table
classical_89 wrote: > Hi everyone ,I have a question. I have a table with large data (i was used > bytea datatype and insert a binary content to table ) so that Postgres help > me get a TOAST table to storage out-of-line values . > Assume that my table is " tbl_test " and toast table oid is 16816 > > When i peform EXPLAIN ANALYZE select query on tbl_test ( EXPLAIN ANALYZE > SELECT * FROM tbl_test).It show that sequential scan was performed on > tbl_test ,but when i check pg_toast table with this query : > > > SELECT > relid, > schemaname, > relname, > seq_scan, > seq_tup_read, > idx_scan, > FROM pg_stat_all_tables > WHERE relid IN ( SELECT oid > FROM pg_class > WHERE relkind = 't' ) AND relid = 16816 > > I saw that seq_tup_read = 0 and the seq_scan is always is 1 .idx_scan is > increase arcording to the number of query on tbl_test > > I was wordering : Do have a sequential scan perform on tbl_test and other > index scan will be peforming on TOAST after this sequential scan ? > Can you explain this dump question to me ,please ? The entries in the TOAST table need not be in the same order as the entries in the main table. So if you'd fetch them sequentially, you'd have to reorder them afterwards. It seems logical that access via the TOAST index is cheaper. Yours, Laurenz Albe -- 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] Slow query: bitmap scan troubles
> Greg's book is awesome. It really gives a lot of informations/tips/whatever > on performances. I mostly remember all the informations about hardware, OS, > PostgreSQL configuration, and such. Not much on the EXPLAIN part. Arrived this morning :) > http://www.pgcon.org/2010/audio/15%20The%20PostgreSQL%20Query% > https://sites.google.com/site/robertmhaas/presentations > http://momjian.us/main/presentations/internals.html > http://www.dalibo.org/_media/understanding_explain.pdf Well that is my evenings occupied for the next week. Thank you kindly. - Phil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Do I have a hardware or a software problem?
Pitch ## I previously posted this question http://archives.postgresql.org/pgsql-performance/2012-11/msg00289.php about a performance issue with an update query. The question evolved into a more general discussion about my setup, and about a lot of I/O wait that I was encountering. Since then, I have gotten a whole lot more familiar with measuring things, and now I "just" need some experienced eyes to judge which direction I should go in - do I have a hardware issue, or a software issue - and what action should I take? # My setup # The use case: At night time we are doing a LOT of data maintenance, and hence the load on the database is very different from the day time. However we would like to be able to do some of it in the daytime, it's simply just too "heavy" on the database as is right now. The stats shown below is from one of those "heavy" load times. Hardware: - 32Gb ram - 8 core Xeon E3-1245 processor - Two SEAGATE ST33000650NS drives (called sdc and sdd in the stats) in a softeware RAID1 array (called md2 in the stats) - Two INTEL SSDSC2CW240A3 SSD drives (called sda and sdb in the stats) in a software RAID1 (called md3 in the stats) Software: Postgres 9.2 running on 64bit ubuntu 12.04 with kernel 3.2 Configuration: # postgresql.conf (a shortlist of everything changed from the default) data_directory = '/var/lib/postgresql/9.2/main' hba_file = '/etc/postgresql/9.2/main/pg_hba.conf' ident_file = '/etc/postgresql/9.2/main/pg_ident.conf' external_pid_file = '/var/run/postgresql/9.2-main.pid' listen_addresses = '192.168.0.2, localhost' port = 5432 max_connections = 300 unix_socket_directory = '/var/run/postgresql' wal_level = hot_standby synchronous_commit = off archive_mode = on archive_command = 'rsync -a %p postgres@192.168.0.4:/var/lib/postgresql/9.2/wals/%f https://rpm.newrelic.com/public/charts/6ewGRle6bmc 2) When the database is loaded like this, I see a lot of queries talking up to 1000 times as long, as they would when the database is not loaded so heavily. 3) sudo iostat -dmx (typical usage) Linux 3.2.0-33-generic (master-db) 12/10/2012 _x86_64_(8 CPU) Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 6.523.59 26.61 0.22 0.7465.49 0.010.400.770.35 0.14 0.43 sdb 0.00 8.310.03 28.38 0.00 0.9769.63 0.010.520.270.52 0.15 0.43 sdc 1.7146.01 34.83 116.62 0.56 4.0662.47 1.90 12.57 21.819.81 1.89 28.66 sdd 1.6746.14 34.89 116.49 0.56 4.0662.46 1.58 10.43 21.667.07 1.89 28.60 md1 0.00 0.000.000.00 0.00 0.00 2.69 0.000.000.000.00 0.00 0.00 md0 0.00 0.000.110.24 0.00 0.00 8.00 0.000.000.000.00 0.00 0.00 md2 0.00 0.00 72.99 161.95 1.11 4.0645.10 0.000.000.000.00 0.00 0.00 md3 0.00 0.000.05 32.32 0.00 0.7447.00 0.000.000.000.00 0.00 0.00 3) sudo iotop -oa (running for about a minute or so) TID PRIO USER DISK READ DISK WRITE SWAPIN IO>COMMAND 292be/4 root 0.00 B 0.00 B0.00 % 99.33 % [md2_raid1] 2815 be/4 postgres 19.51 M 25.90 M 0.00 % 45.49 % postgres: autovacuum worker process production 32553 be/4 postgres 45.74 M 9.38 M 0.00 % 37.89 % postgres: user production 192.168.0.3(58866) UPDATE 32570 be/4 postgres 6.91 M 35.02 M 0.00 % 16.71 % postgres: user production 192.168.0.3(35547) idle 32575 be/4 postgres 4.06 M 43.90 M 0.00 % 16.62 % postgres: user production 192.168.0.3(35561) SELECT 31673 be/4 postgres 4.14 M 52.16 M 0.00 % 16.24 % postgres: user production 192.168.0.3(39112) idle 32566 be/4 postgres 4.73 M 44.95 M 0.00 % 15.66 % postgres: user production 192.168.0.3(35531) idle 32568 be/4 postgres 4.50 M 33.84 M 0.00 % 14.62 % postgres: user production 192.168.0.3(35543) SELECT 32573 be/4 postgres 3.20 M 34.44 M 0.00 % 13.98 % postgres: user production 192.168.0.3(35559) idle 31590 be/4 postgres 3.23 M 29.72 M 0.00 % 13.90 % postgres: user production 192.168.0.3(50690) idle in transaction 32577 be/4 postgres 5.09 M 25.54 M 0.00 % 13.63 % postgres: user production 192.168.0.3(35563) idle 32565 be/4 postgres 2.06 M 35.93 M 0.00 % 13.41 % postgres: user production 192.168.0.3(35529) SELECT 32546 be/4 postgres 4.48 M 36.49 M 0.00 % 13.39 % postgres: user production 192.168.0.3(56927) UPDATE waitin
Re: [PERFORM] Do I have a hardware or a software problem?
On Dec 11, 2012, at 2:51 AM, Niels Kristian Schjødt wrote: > Pitch > ## > I previously posted this question > http://archives.postgresql.org/pgsql-performance/2012-11/msg00289.php about a > performance issue with an update query. > The question evolved into a more general discussion about my setup, and about > a lot of I/O wait that I was encountering. Since then, I have gotten a whole > lot more familiar with measuring things, and now I "just" need some > experienced eyes to judge which direction I should go in - do I have a > hardware issue, or a software issue - and what action should I take? > > # My setup > # > The use case: > At night time we are doing a LOT of data maintenance, and hence the load on > the database is very different from the day time. However we would like to be > able to do some of it in the daytime, it's simply just too "heavy" on the > database as is right now. The stats shown below is from one of those "heavy" > load times. > > Hardware: > - 32Gb ram > - 8 core Xeon E3-1245 processor > - Two SEAGATE ST33000650NS drives (called sdc and sdd in the stats) in a > softeware RAID1 array (called md2 in the stats) > - Two INTEL SSDSC2CW240A3 SSD drives (called sda and sdb in the stats) in a > software RAID1 (called md3 in the stats) > > Software: > Postgres 9.2 running on 64bit ubuntu 12.04 with kernel 3.2 > > Configuration: > # postgresql.conf (a shortlist of everything changed from the default) > data_directory = '/var/lib/postgresql/9.2/main' > hba_file = '/etc/postgresql/9.2/main/pg_hba.conf' > ident_file = '/etc/postgresql/9.2/main/pg_ident.conf' > external_pid_file = '/var/run/postgresql/9.2-main.pid' > listen_addresses = '192.168.0.2, localhost' > port = 5432 > max_connections = 300 > unix_socket_directory = '/var/run/postgresql' > wal_level = hot_standby > synchronous_commit = off > archive_mode = on > archive_command = 'rsync -a %p > postgres@192.168.0.4:/var/lib/postgresql/9.2/wals/%f max_wal_senders = 1 > wal_keep_segments = 32 > log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d ' > datestyle = 'iso, mdy' > lc_monetary = 'en_US.UTF-8' > lc_numeric = 'en_US.UTF-8' > lc_time = 'en_US.UTF-8' > default_text_search_config = 'pg_catalog.english' > default_statistics_target = 100 > maintenance_work_mem = 1GB > checkpoint_completion_target = 0.9 > effective_cache_size = 22GB > work_mem = 160MB > wal_buffers = 4MB > checkpoint_segments = 100 > shared_buffers = 4GB > checkpoint_timeout = 10min > > The kernel has bee tweaked like so: > vm.dirty_ratio = 10 > vm.dirty_background_ratio = 1 > kernel.shmmax = 8589934592 > kernel.shmall = 17179869184 > > The pg_xlog folder has been moved onto the SSD array (md3), and symlinked > back into the postgres dir. > Actually, you should move xlog to rotating drives, since wal logs written sequentially, and everything else to ssd, because of random io pattern. > # The stats > ### > These are the typical observations/stats I see in one of these periods: > > 1) > At top level this is what I see in new relic: > https://rpm.newrelic.com/public/charts/6ewGRle6bmc > > 2) > When the database is loaded like this, I see a lot of queries talking up to > 1000 times as long, as they would when the database is not loaded so heavily. > > 3) > sudo iostat -dmx (typical usage) > Linux 3.2.0-33-generic (master-db)12/10/2012 _x86_64_(8 CPU) > > Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz > avgqu-sz await r_await w_await svctm %util > sda 0.00 6.523.59 26.61 0.22 0.7465.49 > 0.010.400.770.35 0.14 0.43 > sdb 0.00 8.310.03 28.38 0.00 0.9769.63 > 0.010.520.270.52 0.15 0.43 > sdc 1.7146.01 34.83 116.62 0.56 4.0662.47 > 1.90 12.57 21.819.81 1.89 28.66 > sdd 1.6746.14 34.89 116.49 0.56 4.0662.46 > 1.58 10.43 21.667.07 1.89 28.60 > md1 0.00 0.000.000.00 0.00 0.00 2.69 > 0.000.000.000.00 0.00 0.00 > md0 0.00 0.000.110.24 0.00 0.00 8.00 > 0.000.000.000.00 0.00 0.00 > md2 0.00 0.00 72.99 161.95 1.11 4.0645.10 > 0.000.000.000.00 0.00 0.00 > md3 0.00 0.000.05 32.32 0.00 0.7447.00 > 0.000.000.000.00 0.00 0.00 > > 3) > sudo iotop -oa (running for about a minute or so) > TID PRIO USER DISK READ DISK WRITE SWAPIN IO>COMMAND > 292be/4 root 0.00 B 0.00 B0.00 % 99.33 % > [md2_raid1]
Re: [PERFORM] Do I have a hardware or a software problem?
On Mon, Dec 10, 2012 at 2:51 PM, Niels Kristian Schjødt wrote: > synchronous_commit = off > > The pg_xlog folder has been moved onto the SSD array (md3), and symlinked > back into the postgres dir. With synchronous_commit = off, or with large transactions, there is probably no advantage to moving those to SSD. > 2) > When the database is loaded like this, I see a lot of queries talking up to > 1000 times as long, as they would when the database is not loaded so > heavily. What kinds of queries are they? single-row look-ups, full table scans, etc. > > Notes and thoughts > ## > > As you can see, even though I have moved the pg_xlog folder to the SSD array > (md3) the by far largest amount of writes still goes to the regular HDD's > (md2), which puzzles me - what can that be? Every row you insert or non-HOT update has to do maintenance on all indexes of that table. If the rows are not inserted/updated in index order, this means you every row inserted/updated dirties a randomly scattered 8KB for each of the indexes. If you have lots of indexes per table, that adds up fast. The fact that there is much more writing than reading tells me that most of your indexes are in RAM. The amount of index you are rapidly reading and dirtying is large enough to fit in RAM, but is not large enough to fit in shared_buffers + kernel's dirty-buffer comfort level. So you are redirtying the same blocks over and over, PG is desperately dumping them to the kernel (because shared_buffers it too small to hold them) and the kernel is desperately dumping them to disk, because vm.dirty_background_ratio is so low. There is little opportunity for write-combining, because they don't sit in memory long enough to accumulate neighbors. How big are your indexes? You could really crank up shared_buffers or vm.dirty_background_ratio, but doing so might cause problems with checkpoints stalling and latency spikes. That would probably not be a problem during the night, but could be during the day. Rather than moving maintenance to the day and hoping it doesn't interfere with normal operations, I'd focus on making night-time maintenance more efficient, for example by dropping indexes (either just at night, or if some indexes are not useful, just get rid of them altogether), or cranking up shared_buffers at night, or maybe partitioning or look into pg_bulkload. > From stat 3) (the iostat) I notice that the SSD's doesn't seem to be > something near fully utilized - maybe something else than just pg_xlog could > be moved her? I don't know how big each disk is, or how big your various categories of data are. Could you move everything to SSD? Could you move all your actively updated indexes there? Or, more fundamentally, it looks like you spent too much on CPUs (86% idle) and not nearly enough on disks. Maybe you can fix that for less money than it will cost you in your optimization time to make the best of the disks you already have. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
