Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dUsa3-0001y5-5f for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 10:39:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dUsa2-0007XJ-1r for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 10:39:46 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dUsa0-0007We-Lp for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 10:39:44 +0000 Received: from mail-qk0-x22c.google.com ([2607:f8b0:400d:c09::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dUsZw-0007cn-Ss for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 10:39:43 +0000 Received: by mail-qk0-x22c.google.com with SMTP id v143so97611733qkb.0 for ; Tue, 11 Jul 2017 03:39:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=tQ5S8zZKtPZIUWg8PUPvLdG5FmD2Jgjtb98VOKJ71LY=; b=B/2QssOi8Atel6ylch+P/Hdy1OlU3kX24MgHgmuhau6lxOlWqlHEZhDqvEXrS00vDr Fm3GuQMiH6oo+mxxJkV6shX3VLp8dKc6DndWuYHyhLEVzLcon93GkQ5DJvgtyChz3rrv B3fNzDz0W67KdtjPJLMyZOl1bZHqsq6/Zc/r+BnDWwBt9FU3Ei3AjGZspozKVX0NJH2K mx2U+ikMLpHUC9tPgSQvW9f1k+ZtKDtNIeARt8f/yWLYPWC9j7MFafzlZRhi2VDnEFOQ F9ntN/cxbBfoT4krwt0u87rQp3O1J3XWcIC4NgVjBDppDXWP81xMMQOXxNFTdszMEIwS th8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=tQ5S8zZKtPZIUWg8PUPvLdG5FmD2Jgjtb98VOKJ71LY=; b=MJAIU0UUuNa6lnWvEMItVs+HnzgNM/y1Q4BuTY71Rs8jg3i7M85zn2tPYuvPQuLHFh 8E7Ru52RtIULxOMAg1LAH42blhdEjufKz3/tA2WuEldfZ+5dogITDvSNxt74s+IxxrxD r3A+vQV3q+qMO2Y6Gf6PTBMX/MrhwkUVDI/H19UL6z/3Qfi2NuyENTABzltO9tg0efDY SK09piboPMm1/4UPq6pXae+xtZp6ETz9+fzsIjI6S2+VCFqK+7zk/oATRyiQcQD38VWB KJd+Hj2oVvqY0Od+naKJ4e9WnFI+Ap6ANvSVy/zGhflYEJWw5wN0h4wPy27eew3NygPJ dHsg== X-Gm-Message-State: AIVw111A5I8neITufK4ipuZqCPvDmcVTrDVgLnOh+rvv2YC1k1eFAkKD 9oOVcYACBQiIH3XKiRuoN47/orf4Gg== X-Received: by 10.55.7.8 with SMTP id 8mr9149128qkh.124.1499769580120; Tue, 11 Jul 2017 03:39:40 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Tue, 11 Jul 2017 03:39:19 -0700 (PDT) In-Reply-To: References: From: Charles Nadeau Date: Tue, 11 Jul 2017 12:39:19 +0200 Message-ID: Subject: Re: Very poor read performance, query independent To: Rick Otten Cc: "pgsql-performa." Content-Type: multipart/alternative; boundary="001a114c46f41173410554085123" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a114c46f41173410554085123 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Rick, I applied the change you recommended but it didn't speed up the reads. One thing I forgot to mention earlier is the speed of the backup made with the COPY operations seems almost normal: I have read speed of up to 85MB/s. Thanks for your help! Charles On Mon, Jul 10, 2017 at 5:25 PM, Rick Otten wrote: > Although probably not the root cause, at the least I would set up > hugepages ( https://www.postgresql.org/docs/9.6/static/kernel- > resources.html#LINUX-HUGE-PAGES ), and bump effective_io_concurrency up > quite a bit as well (256 ?). > > > On Mon, Jul 10, 2017 at 10:03 AM, Charles Nadeau > wrote: > >> I=E2=80=99m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-ge= neric). >> Hardware is: >> >> *2x Intel Xeon E5550 >> >> *72GB RAM >> >> *Hardware RAID10 (4 x 146GB SAS 10k) P410i controller with 1GB FBWC (80% >> read/20% write) for Postgresql data only: >> >> Logical Drive: 3 >> >> Size: 273.4 GB >> >> Fault Tolerance: 1+0 >> >> Heads: 255 >> >> Sectors Per Track: 32 >> >> Cylinders: 65535 >> >> Strip Size: 128 KB >> >> Full Stripe Size: 256 KB >> >> Status: OK >> >> Caching: Enabled >> >> Unique Identifier: 600508B1001037383941424344450A00 >> >> Disk Name: /dev/sdc >> >> Mount Points: /mnt/data 273.4 GB >> >> OS Status: LOCKED >> >> Logical Drive Label: A00A194750123456789ABCDE516F >> >> Mirror Group 0: >> >> physicaldrive 2I:1:5 (port 2I:box 1:bay 5, SAS, 146 GB, OK) >> >> physicaldrive 2I:1:6 (port 2I:box 1:bay 6, SAS, 146 GB, OK) >> >> Mirror Group 1: >> >> physicaldrive 2I:1:7 (port 2I:box 1:bay 7, SAS, 146 GB, OK) >> >> physicaldrive 2I:1:8 (port 2I:box 1:bay 8, SAS, 146 GB, OK) >> >> Drive Type: Data >> >> Formatted with ext4 with: sudo mkfs.ext4 -E stride=3D32,stripe_width=3D6= 4 -v >> /dev/sdc1. >> >> Mounted in /etc/fstab with this line: "UUID=3D99fef4ae-51dc-4365-9210-0b= 153b1cbbd0 >> /mnt/data ext4 rw,nodiratime,user_xattr,noatime,nobarrier,errors=3Dremou= nt-ro >> 0 1" >> >> Postgresql is the only application running on this server. >> >> >> Postgresql is used as a mini data warehouse to generate reports and do >> statistical analysis. It is used by at most 2 users and fresh data is ad= ded >> every 10 days. The database has 16 tables: one is 224GB big and the rest >> are between 16kB and 470MB big. >> >> >> My configuration is: >> >> >> name | current_setting | source >> >> ---------------------------------+-------------------------- >> ----------------------+---------------------- >> >> application_name | psql | client >> >> autovacuum_vacuum_scale_factor | 0 | configuration file >> >> autovacuum_vacuum_threshold | 2000 | configuration file >> >> checkpoint_completion_target | 0.9 | configuration file >> >> checkpoint_timeout | 30min | configuration file >> >> client_encoding | UTF8 | client >> >> client_min_messages | log | configuration file >> >> cluster_name | 9.6/main | configuration file >> >> cpu_index_tuple_cost | 0.001 | configuration file >> >> cpu_operator_cost | 0.0005 | configuration file >> >> cpu_tuple_cost | 0.003 | configuration file >> >> DateStyle | ISO, YMD | configuration file >> >> default_statistics_target | 100 | configuration file >> >> default_text_search_config | pg_catalog.english | configuration file >> >> dynamic_shared_memory_type | posix | configuration file >> >> effective_cache_size | 22GB | configuration file >> >> effective_io_concurrency | 4 | configuration file >> >> external_pid_file | /var/run/postgresql/9.6-main.pid | configuration fil= e >> >> lc_messages | C | configuration file >> >> lc_monetary | en_CA.UTF-8 | configuration file >> >> lc_numeric | en_CA.UTF-8 | configuration file >> >> lc_time | en_CA.UTF-8 | configuration file >> >> listen_addresses | * | configuration file >> >> lock_timeout | 100s | configuration file >> >> log_autovacuum_min_duration | 0 | configuration file >> >> log_checkpoints | on | configuration file >> >> log_connections | on | configuration file >> >> log_destination | csvlog | configuration file >> >> log_directory | /mnt/bigzilla/data/toburn/hp/postgresql/pg_log | >> configuration file >> >> log_disconnections | on | configuration file >> >> log_error_verbosity | default | configuration file >> >> log_file_mode | 0600 | configuration file >> >> log_filename | postgresql-%Y-%m-%d_%H%M%S.log | configuration file >> >> log_line_prefix | user=3D%u,db=3D%d,app=3D%aclient=3D%h | configuration = file >> >> log_lock_waits | on | configuration file >> >> log_min_duration_statement | 0 | configuration file >> >> log_min_error_statement | debug1 | configuration file >> >> log_min_messages | debug1 | configuration file >> >> log_rotation_size | 1GB | configuration file >> >> log_temp_files | 0 | configuration file >> >> log_timezone | localtime | configuration file >> >> logging_collector | on | configuration file >> >> maintenance_work_mem | 3GB | configuration file >> >> max_connections | 10 | configuration file >> >> max_locks_per_transaction | 256 | configuration file >> >> max_parallel_workers_per_gather | 14 | configuration file >> >> max_stack_depth | 2MB | environment variable >> >> max_wal_size | 4GB | configuration file >> >> max_worker_processes | 14 | configuration file >> >> min_wal_size | 2GB | configuration file >> >> parallel_setup_cost | 1000 | configuration file >> >> parallel_tuple_cost | 0.012 | configuration file >> >> port | 5432 | configuration file >> >> random_page_cost | 22 | configuration file >> >> seq_page_cost | 1 | configuration file >> >> shared_buffers | 34GB | configuration file >> >> shared_preload_libraries | pg_stat_statements | configuration file >> >> ssl | on | configuration file >> >> ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem | configuration fil= e >> >> ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key | configuration >> file >> >> statement_timeout | 1000000s | configuration file >> >> stats_temp_directory | /var/run/postgresql/9.6-main.pg_stat_tmp | >> configuration file >> >> superuser_reserved_connections | 1 | configuration file >> >> syslog_facility | local1 | configuration file >> >> syslog_ident | postgres | configuration file >> >> syslog_sequence_numbers | on | configuration file >> >> temp_file_limit | 80GB | configuration file >> >> TimeZone | localtime | configuration file >> >> track_activities | on | configuration file >> >> track_counts | on | configuration file >> >> track_functions | all | configuration file >> >> unix_socket_directories | /var/run/postgresql | configuration file >> >> vacuum_cost_delay | 1ms | configuration file >> >> vacuum_cost_limit | 5000 | configuration file >> >> vacuum_cost_page_dirty | 200 | configuration file >> >> vacuum_cost_page_hit | 10 | configuration file >> >> vacuum_cost_page_miss | 100 | configuration file >> >> wal_buffers | 16MB | configuration file >> >> wal_compression | on | configuration file >> >> wal_sync_method | fdatasync | configuration file >> >> work_mem | 1468006kB | configuration file >> >> >> The part of /etc/sysctl.conf I modified is: >> >> vm.swappiness =3D 1 >> >> vm.dirty_background_bytes =3D 134217728 >> >> vm.dirty_bytes =3D 1073741824 >> >> vm.overcommit_ratio =3D 100 >> >> vm.zone_reclaim_mode =3D 0 >> >> kernel.numa_balancing =3D 0 >> >> kernel.sched_autogroup_enabled =3D 0 >> >> kernel.sched_migration_cost_ns =3D 5000000 >> >> >> The problem I have is very poor read. When I benchmark my array with fio >> I get random reads of about 200MB/s and 1100IOPS and sequential reads of >> about 286MB/s and 21000IPS. But when I watch my queries using pg_activit= y, >> I get at best 4MB/s. Also using dstat I can see that iowait time is at >> about 25%. This problem is not query-dependent. >> >> I backed up the database, I reformated the array making sure it is well >> aligned then restored the database and got the same result. >> >> Where should I target my troubleshooting at this stage? I reformatted my >> drive, I tuned my postgresql.conf and OS as much as I could. The hardwar= e >> doesn=E2=80=99t seem to have any issues, I am really puzzled. >> >> Thanks! >> >> >> Charles >> >> -- >> Charles Nadeau Ph.D. >> > > --=20 Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/ --001a114c46f41173410554085123 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Rick,

I applied the change y= ou recommended but it didn't speed up the reads.
One thing I = forgot to mention earlier is the speed of the backup made with the COPY ope= rations seems almost normal: I have read speed of up to 85MB/s.
T= hanks for your help!

Charles

On Mon, Jul 10, 2017 at 5:2= 5 PM, Rick Otten <rottenwindfish@gmail.com> wrote:
Although probably not the = root cause, at the least I would set up hugepages =C2=A0(=C2=A0https://www.postgresql.org/docs/9.6/static/ke= rnel-resources.html#LINUX-HUGE-PAGES ), and bump effective_io= _concurrency up quite a bit as well (256 ?).


On Mon, Jul 10, 2017 at 10:03 AM, Charles Nadeau <char= les.nadeau@gmail.com> wrote:
=09 =09 =09

I=E2=80=99m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-generic). Hardware is:

*2x Intel Xeon E5550

*72GB RAM

*Hardware RAID10 (4 x 146GB SAS 10k) P410i controller with 1GB FBWC (80% read/20% write) for Postgresql data only:

Logical Drive: 3

Size: 273.4 GB

Fault Tolerance: 1+0

Heads: 255

Sectors Per Track: 32

Cylinders: 65535

Strip Size: 128 KB

Full Stripe Size: 256 KB

Status: OK

Caching:=20 Enabled

Unique Identifier: 600508B1001037383941424344450A00

Disk Name: /dev/sdc

Mount Points: /mnt/data 273.4 GB

OS Status: LOCKED

Logical Drive Label: A00A194750123456789ABCDE516F

Mirror Group 0:

=20 physicaldrive 2I:1:5 (port 2I:box 1:bay 5, SAS, 146 GB, OK)

=20 physicaldrive 2I:1:6 (port 2I:box 1:bay 6, SAS, 146 GB, OK)

Mirror Group 1:

=20 physicaldrive 2I:1:7 (port 2I:box 1:bay 7, SAS, 146 GB, OK)

=20 physicaldrive 2I:1:8 (port 2I:box 1:bay 8, SAS, 146 GB, OK)

Drive Type: Data

Formatted with ext4 with: sudo mkfs.ext4 -E stride=3D32,stripe_width=3D64 -v /dev/sdc1.

Mounted in /etc/fstab with this line: "UUID=3D99fef4ae-51dc-4365-9210-0b153b= 1cbbd0 /mnt/data ext4 rw,nodiratime,user_xattr,noatime,nobarrier,errors=3Dremount-ro 0 1&quo= t;

Postgresql is the only application running on this server.


Postgresql is used as a mini data warehouse to generate reports and do statistical analysis. It is used by at most 2 users and fresh data is added every 10 days. The database has 16 tables: one is 224GB big and the rest are between 16kB and 470MB big.


My configuration is:


name =20 | current_setting | =20 source =20

---------------------------= ------+------------------------------------------------+----------------------

application_name =20 | psql | client

autovacuum_vacuum_scale_factor | 0 =20 | configuration file

autovacuum_vacuum_threshold | 2000 =20 | configuration file

checkpoint_completion_target | 0.9 =20 | configuration file

checkpoint_timeout=20 | 30min | configuration file

client_encoding =20 | UTF8 | client

client_min_messages | log | configuration file

cluster_name =20 | 9.6/main | configuration file

cpu_index_tuple_cost | 0.001 =20 | configuration file

cpu_operator_cost =20 | 0.0005 | configuration file

cpu_tuple_cost =20 | 0.003 | configuration file

DateStyle =20 | ISO, YMD | configuration file

default_statistics_target | 100 =20 | configuration file

default_text_search_config | pg_catalog.english =20 | configuration file

dynamic_shared_memory_type | posix =20 | configuration file

effective_cache_size | 22GB =20 | configuration file

effective_io_concurrency | 4 =20 | configuration file

external_pid_file =20 | /var/run/postgresql/9.6-main.pid | configuration file

lc_messages =20 | C | configuration file

lc_monetary =20 | en_CA.UTF-8 | configuration file

lc_numeric =20 | en_CA.UTF-8 | configuration file

lc_time =20 | en_CA.UTF-8 | configuration file

listen_addresses =20 | * | configuration file

lock_timeout =20 | 100s | configuration file

log_autovacuum_min_duration | 0 =20 | configuration file

log_checkpoints =20 | on | configuration file

log_connections =20 | on | configuration file

log_destination =20 | csvlog | configuration file

log_directory =20 | /mnt/bigzilla/data/toburn/hp/postgresql/pg_log | configuration file

log_disconnections=20 | on | configuration file

log_error_verbosity | default | configuration file

log_file_mode =20 | 0600 | configuration file

log_filename =20 | postgresql-%Y-%m-%d_%H%M%S.log | configuration file

log_line_prefix =20 | user=3D%u,db=3D%d,app=3D%aclient=3D%h | configuration file

log_lock_waits =20 | on | configuration file

log_min_duration_statement | 0 =20 | configuration file

log_min_error_statement | debug1 =20 | configuration file

log_min_messages =20 | debug1 | configuration file

log_rotation_size =20 | 1GB | configuration file

log_temp_files =20 | 0 | configuration file

log_timezone =20 | localtime | configuration file

logging_collector =20 | on | configuration file

maintenance_work_mem | 3GB =20 | configuration file

max_connections =20 | 10 | configuration file

max_locks_per_transaction | 256 =20 | configuration file

max_parallel_workers_per_gather | 14 = =20 | configuration file

max_stack_depth =20 | 2MB | environment variable

max_wal_size =20 | 4GB | configuration file

max_worker_processes | 14 =20 | configuration file

min_wal_size =20 | 2GB | configuration file

parallel_setup_cost | 1000 | configuration file

parallel_tuple_cost | 0.012 | configuration file

port =20 | 5432 | configuration file

random_page_cost =20 | 22 | configuration file

seq_page_cost =20 | 1 | configuration file

shared_buffers =20 | 34GB | configuration file

shared_preload_libraries | pg_stat_statements =20 | configuration file

ssl =20 | on | configuration file

ssl_cert_file =20 | /etc/ssl/certs/ssl-cert-snakeoil.pem | configuration file

ssl_key_file =20 | /etc/ssl/private/ssl-cert-snakeoil.key | configuration file

statement_timeout =20 | 1000000s | configuration file

stats_temp_directory | /var/run/postgresql/9.6-main.pg_stat_tmp | configuration file

superuser_reserved_connections | 1 =20 | configuration file

syslog_facility =20 | local1 | configuration file

syslog_ident =20 | postgres | configuration file

syslog_sequence_numbers | on =20 | configuration file

temp_file_limit =20 | 80GB | configuration file

TimeZone =20 | localtime | configuration file

track_activities =20 | on | configuration file

track_counts =20 | on | configuration file

track_functions =20 | all | configuration file

unix_socket_directories | /var/run/postgresql =20 | configuration file

vacuum_cost_delay =20 | 1ms | configuration file

vacuum_cost_limit =20 | 5000 | configuration file

vacuum_cost_page_dirty | 200 =20 | configuration file

vacuum_cost_page_hit | 10 =20 | configuration file

vacuum_cost_page_miss | 100 =20 | configuration file

wal_buffers =20 | 16MB | configuration file

wal_compression =20 | on | configuration file

wal_sync_method =20 | fdatasync | configuration file

work_mem =20 | 1468006kB | configuration file


The part of /etc/sysctl.conf I modified is:

vm.swappiness =3D 1

vm.dirty_background_bytes =3D 134217728

vm.dirty_bytes =3D 1073741824

vm.overcommit_ratio =3D 100

vm.zone_reclaim_mode =3D 0

kernel.numa_balancing =3D 0

kernel.sched_autogroup_enab= led =3D 0

kernel.sched_migration_cost= _ns =3D 5000000


The problem I have is very poor read. When I benchmark my array with fio I get random reads of about 200MB/s and 1100IOPS and sequential reads of about 286MB/s and 21000IPS. But when I watch my queries using pg_activity, I get at best 4MB/s. Also using dstat I can see that iowait time is at about 25%. This problem is not query-dependent.

I backed up the database, I reformated the array making sure it is well aligned then restored the database and got the same result.

Where should I target my troubleshooting at this stage? I reformatted my drive, I tuned my postgresql.conf and OS as much as I could. The hardware doesn=E2=80=99t seem to have any issues, I am really puzzled.

Thanks!


Charles


-= -
Charles Nadeau Ph.D.




--
=
Charles N= adeau Ph.D.
http://charlesnadeau.blogspot.com/
--001a114c46f41173410554085123--