Search Postgresql Archives

Huge number of INSERTs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux