Обсуждение: Postgres 9.2 CPU Usage
Good day!
Sometimes ago we order new Dell (Dell PowerEdge T720 DX290) server, with this configuration:
1. 128 Gb DDR3 ECC RAM
2. Dual Intel Xeon E5-2620 Hexa Core incl. Hyper-Thrreading Tecknology
3. 2x 600Gb SSD With PERC H710 mini RAID 1
We store a not big database (around 50 Gb) at postgresql, config:
archive_command = 'cp %p /var/lib/postgresql/9.2/main/archive/%f'
archive_mode = on
autovacuum = on
autovacuum_max_workers = '1'
checkpoint_completion_target = '0.75'
checkpoint_segments = '256'
checkpoint_timeout = '45min'
constraint_exclusion = on
data_directory = '/var/lib/postgresql/9.2/main'
datestyle = 'iso, mdy'
default_statistics_target = '50'
default_text_search_config = 'pg_catalog.english'
effective_cache_size = '72GB'
enable_seqscan = off
external_pid_file = '/var/run/postgresql/9.2-main.pid'
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
listen_addresses = '*'
log_checkpoints = on
log_connections = on
log_directory = '/var/log/postgresql/'
log_duration = off
log_line_prefix = '%t '
log_lock_waits = on
log_min_duration_statement = '50ms'
log_min_error_statement = 'notice'
log_min_messages = 'notice'
log_statement = 'none'
log_timezone = 'GMT-4'
logging_collector = on
maintenance_work_mem = '1GB'
max_connections = '150'
max_wal_senders = '3'
port = '56432'
shared_buffers = '24GB'
ssl = off
timezone = 'GMT-4'
track_activities = on
track_counts = on
track_functions = 'all'
unix_socket_directory = '/var/run/postgresql'
wal_buffers = '8MB'
wal_level = 'hot_standby'
work_mem = ‘32MB'
But we have a strange cpu usage by postgresql (LA about 4). But there is no a lot of requests or heavy requests. We use pgbouncer, so postgresql doesn’t accept connections from users, only from pgbouncer. And the number of active requests per second is about 5:
postgres=# select count(datid) from pg_stat_activity where state='active';
count
-------
5
(1 row)
postgres=#
But the time spent to request is about 500 ms (from postgresql log).
I tried to strace postgresql and I see the following:
root@server:/var/lib/postgresql# strace -c -f -p 41236
Process 41236 attached - interrupt to quit
^CProcess 41236 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
50.80 0.000508 3 201 fdatasync
25.40 0.000254 0 82860 lseek
14.60 0.000146 0 1525 sendto
9.20 0.000092 0 737 recvfrom
0.00 0.000000 0 12 read
0.00 0.000000 0 321 write
0.00 0.000000 0 19 3 open
0.00 0.000000 0 17 close
0.00 0.000000 0 2 brk
0.00 0.000000 0 2 setitimer
0.00 0.000000 0 1 kill
0.00 0.000000 0 153 semop
------ ----------- ----------- --------- --------- ----------------
100.00 0.001000 85850 3 total
I think it is some misconfiguration issue, so can you help me with some config/sysctl options for such server?
Thank you!
--
Vasiliy I Ozerov
On Fri, Dec 20, 2013 at 12:56:47AM +0400, Vasiliy I Ozerov wrote: > Good day! > > Sometimes ago we order new Dell (Dell PowerEdge T720 DX290) server, with this configuration: > > 1. 128 Gb DDR3 ECC RAM > 2. Dual Intel Xeon E5-2620 Hexa Core incl. Hyper-Thrreading Tecknology > 3. 2x 600Gb SSD With PERC H710 mini RAID 1 > > ... > > But we have a strange cpu usage by postgresql (LA about 4). But there is no a lot of requests or heavy requests. We usepgbouncer, so postgresql doesn’t accept connections from users, only from pgbouncer. And the number of active requestsper second is about 5: > > ... > > I think it is some misconfiguration issue, so can you help me with some config/sysctl options for such server? > > Thank you! > Hi Vasiliy, I think this may not be a PostgreSQL related problem, but a BIOS configuration problem. Dell used to ship servers with the BIOS set for performance mode, i.e. 100% at all times, now they ship with more energy thrifty defaults. We had a similar problem and by setting the BIOS to performance mode and rebooting, the phantom load vanished. Regards, Ken
Hmm.. Thank you! I will try it!
--
Vasiliy I Ozerov
пятница, 20 декабря 2013 г. в 1:20, ktm@rice.edu написал:
On Fri, Dec 20, 2013 at 12:56:47AM +0400, Vasiliy I Ozerov wrote:Good day!Sometimes ago we order new Dell (Dell PowerEdge T720 DX290) server, with this configuration:1. 128 Gb DDR3 ECC RAM2. Dual Intel Xeon E5-2620 Hexa Core incl. Hyper-Thrreading Tecknology3. 2x 600Gb SSD With PERC H710 mini RAID 1...But we have a strange cpu usage by postgresql (LA about 4). But there is no a lot of requests or heavy requests. We use pgbouncer, so postgresql doesn’t accept connections from users, only from pgbouncer. And the number of active requests per second is about 5:...I think it is some misconfiguration issue, so can you help me with some config/sysctl options for such server?Thank you!Hi Vasiliy,I think this may not be a PostgreSQL related problem, but a BIOS configurationproblem. Dell used to ship servers with the BIOS set for performance mode, i.e.100% at all times, now they ship with more energy thrifty defaults. We had asimilar problem and by setting the BIOS to performance mode and rebooting, thephantom load vanished.Regards,Ken