Huge number of INSERTs

From: Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Huge number of INSERTs
Date: 2011-11-18 00:44:27
Message-ID: CAFWfU=tJ4AyMbFsgdpYi5C-WEa+Fj0d-_Tknq5-LK-rPA4PfxQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. I have a massive traffic website.

I keep getting "FATAL: Sorry, too many clients already" problems.

It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10,
with RAM of 8GB.

Server is Nginx backed by Apache for the php.

Postgresql just has to do about 1000 SELECTs a minute, and about 200
INSERTs a minute. Maybe 10-20 UPDATEs.

My conf file is below. My vmstat + top are below too.

What else can I do?

max_connections = 350
shared_buffers = 256MB
effective_cache_size = 1400MB # Nov 11 2011, was 1500MB
temp_buffers = 16MB # min 800kB
maintenance_work_mem = 256MB # min 1MB
wal_buffers = 12MB # min 32kB
fsync = on # turns forced synchronization on or off
checkpoint_segments = 128 # was 128
checkpoint_timeout = 1000 # was 1000
enable_indexscan = on

#------------------------- LOGGING ----------------------
log_directory = 'pg_log'
log_filename = 'pglog.postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_min_messages = 'error'
log_min_error_statement = 'error'
log_min_duration_statement = 5000 # In milliseconds
client_min_messages = 'warning'
log_duration = off

#------------------------- AUTOVAC ----------------------
autovacuum = on
autovacuum_max_workers = 5 # max number of autovacuum subprocesses
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 350

vmstat
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
6 1 4044 101396 84376 5569592 0 0 168 221 326 200 55
22 21 1 0

top - 19:43:49 up 7:33, 3 users, load average: 19.63, 19.61, 19.25
Tasks: 663 total, 19 running, 644 sleeping, 0 stopped, 0 zombie
Cpu(s): 65.8%us, 15.5%sy, 0.0%ni, 1.7%id, 0.1%wa, 0.0%hi, 17.0%si, 0.0%st
Mem: 8177444k total, 8062608k used, 114836k free, 84440k buffers
Swap: 2096440k total, 4044k used, 2092396k free, 5572456k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6337 postgres 15 0 397m 100m 97m S 2.3 1.3 0:16.56 postgres:
MYDB_MYDB MYDB 127.0.0.1(60118) SELECT
424 postgres 15 0 397m 101m 98m S 2.0 1.3 1:01.79 postgres:
MYDB_MYDB MYDB 127.0.0.1(37036) SELECT
2887 postgres 15 0 397m 100m 98m S 2.0 1.3 0:34.55 postgres:
MYDB_MYDB MYDB 127.0.0.1(57710) SELECT
3030 postgres 15 0 397m 101m 98m S 2.0 1.3 0:32.35 postgres:
MYDB_MYDB MYDB 127.0.0.1(45574) SELECT
5273 postgres 15 0 397m 100m 98m S 2.0 1.3 0:22.38 postgres:
MYDB_MYDB MYDB 127.0.0.1(52143) SELECT
5560 postgres 15 0 397m 100m 98m S 2.0 1.3 0:20.05 postgres:
MYDB_MYDB MYDB 127.0.0.1(56767) SELECT
5613 postgres 16 0 397m 100m 98m S 2.0 1.3 0:19.51 postgres:
MYDB_MYDB MYDB 127.0.0.1(57745) SELECT
5652 postgres 15 0 397m 100m 98m S 2.0 1.3 0:19.76 postgres:
MYDB_MYDB MYDB 127.0.0.1(58464) SELECT
32062 postgres 15 0 397m 101m 98m S 2.0 1.3 1:55.79 postgres:
MYDB_MYDB MYDB 127.0.0.1(55341) SELECT
358 postgres 15 0 397m 101m 98m S 1.6 1.3 1:04.11 postgres:
MYDB_MYDB MYDB 127.0.0.1(35841) SELECT
744 postgres 15 0 397m 101m 98m S 1.6 1.3 0:53.01 postgres:
MYDB_MYDB MYDB 127.0.0.1(50058) SELECT
903 postgres 15 0 397m 101m 98m S 1.6 1.3 0:50.79 postgres:
MYDB_MYDB MYDB 127.0.0.1(51258) SELECT
976 postgres 15 0 397m 101m 98m S 1.6 1.3 0:48.24 postgres:
MYDB_MYDB MYDB 127.0.0.1(52828) SELECT
1011 postgres 15 0 397m 101m 98m S 1.6 1.3 0:48.20 postgres:
MYDB_MYDB MYDB 127.0.0.1(53503) SELECT
2446 postgres 15 0 397m 101m 98m S 1.6 1.3 0:38.97 postgres:
MYDB_MYDB MYDB 127.0.0.1(51982) SELECT
2806 postgres 16 0 397m 100m 98m R 1.6 1.3 0:34.83 postgres:
MYDB_MYDB MYDB 127.0.0.1(57204) SELECT
3361 postgres 15 0 397m 101m 98m R 1.6 1.3 0:30.32 postgres:
MYDB_MYDB MYDB 127.0.0.1(48782) idle
3577 postgres 15 0 397m 100m 98m S 1.6 1.3 0:27.92 postgres:
MYDB_MYDB MYDB 127.0.0.1(52019) SELECT
3618 postgres 15 0 397m 101m 98m S 1.6 1.3 0:27.53 postgres:
MYDB_MYDB MYDB 127.0.0.1(41291) SELECT
3704 postgres 15 0 397m 100m 98m S 1.6 1.3 0:25.70 postgres:
MYDB_MYDB MYDB 127.0.0.1(43642) SELECT
5073 postgres 15 0 397m 100m 98m S 1.6 1.3 0:23.92 postgres:
MYDB_MYDB MYDB 127.0.0.1(47398) SELECT
5185 postgres 15 0 397m 100m 98m S 1.6 1.3 0:23.03 postgres:
MYDB_MYDB MYDB 127.0.0.1(49137) SELECT
5528 postgres 15 0 397m 100m 98m S 1.6 1.3 0:20.81 postgres:
MYDB_MYDB MYDB 127.0.0.1(55531) SELECT
5549 postgres 15 0 397m 100m 98m S 1.6 1.3 0:20.71 postgres:
MYDB_MYDB MYDB 127.0.0.1(56391) SELECT
5976 postgres 16 0 397m 100m 98m R 1.6 1.3 0:17.47 postgres:
MYDB_MYDB MYDB 127.0.0.1(57053) idle
6301 postgres 15 0 397m 100m 97m S 1.6 1.3 0:16.58 postgres:
MYDB_MYDB MYDB 127.0.0.1(59544) SELECT
32318 postgres 15 0 397m 101m 98m S 1.6 1.3 1:24.09 postgres:
MYDB_MYDB MYDB 127.0.0.1(32942) SELECT
32728 postgres 15 0 397m 101m 98m S 1.6 1.3 1:09.87 postgres:
MYDB_MYDB MYDB 127.0.0.1(33792) SELECT
377 postgres 16 0 397m 101m 98m S 1.3 1.3 1:03.51 postgres:
MYDB_MYDB MYDB 127.0.0.1(35925) SELECT

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2011-11-18 01:03:49 Re: Huge number of INSERTs
Previous Message Phoenix Kiula 2011-11-18 00:38:54 Re: Incremental backup with RSYNC or something?