I run a small Facebook game at a CentOS 6.3 quad core machine with PostgreSQL 8.4.3 + few PHP scripts (doing mostly select queries) + 1 Perl daemon and even though the server worked ok, I've suggested my users to double up the RAM to 32 GB and they have collected money for that.
Now my problem is that I don't know, which knob to turn and how to really use the additional memory to speed up the server.
Below is my top output at the peak time (evenings) - as you see, 27 GB of RAM aren't used:
# top - 18:47:55 up 23:12, 2 users, load average: 2.17, 2.31, 2.56
Tasks: 246 total, 2 running, 244 sleeping, 0 stopped, 0 zombie
Cpu(s): 12.1%us, 0.2%sy, 0.0%ni, 87.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32790380k total, 5296664k used, 27493716k free, 197132k buffers
Swap: 2096056k total, 0k used, 2096056k free, 3815840k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12363 postgres 20 0 4376m 717m 710m S 40.6 2.2 2:03.48 postmaster
3842 nobody 20 0 118m 23m 3920 S 12.0 0.1 46:24.68 pref.pl
9178 postgres 20 0 4375m 518m 514m S 11.3 1.6 53:50.63 postmaster
12380 postgres 20 0 4377m 668m 660m S 11.3 2.1 2:33.26 postmaster
12243 postgres 20 0 4377m 668m 662m S 9.3 2.1 2:20.49 postmaster
12438 postgres 20 0 4374m 502m 498m S 6.3 1.6 1:03.34 postmaster
12249 postgres 20 0 4384m 852m 839m S 3.0 2.7 3:59.11 postmaster
12241 postgres 20 0 4378m 632m 625m S 1.7 2.0 2:48.62 postmaster
12156 apache 20 0 366m 27m 17m S 1.0 0.1 0:05.12 httpd
36 root 20 0 0 0 0 S 0.3 0.0 0:01.32 events/1
100 root 39 19 0 0 0 S 0.3 0.0 0:06.04 khugepaged
9217 postgres 20 0 21976 1036 516 S 0.3 0.0 1:01.07 pgbouncer
12010 apache 20 0 376m 37m 17m S 0.3 0.1 0:07.58 httpd
12280 apache 20 0 370m 30m 16m S 0.3 0.1 0:03.17 httpd
12362 apache 20 0 365m 15m 6816 R 0.3 0.0 0:01.90 httpd
12457 apache 20 0 360m 9.8m 3456 S 0.3 0.0 0:00.14 httpd
1 root 20 0 19352 1584 1284 S 0.0 0.0 0:01.03 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
# vmstat 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
6 2 0 27323416 196988 3852536 0 0 3 165 15 9 14 0 84 2 0
1 0 0 27422148 197012 3814860 0 0 0 5284 4784 2104 46 1 51 2 0
1 0 0 27454748 197020 3826656 0 0 4 1734 2021 1200 16 0 83 1 0
0 2 0 27514008 197028 3813464 0 0 0 702 1475 1208 8 0 90 2 0
0 0 0 27465612 197040 3813968 0 0 0 1435 1764 1725 10 0 85 5 0
1 0 0 27459260 197060 3814248 0 0 0 2032 2667 1304 22 0 76 1 0
1 0 0 27440076 197064 3827064 0 0 0 1604 3146 2109 27 0 72 1 0
1 0 0 27466796 197068 3814868 0 0 2 1241 2014 1637 13 0 83 3 0
4 0 0 27380104 197072 3848256 0 0 0 1064 2375 894 20 0 79 1 0
1 0 0 27488168 197096 3815296 0 0 0 2075 2697 2220 23 0 75 1 0
1 0 0 27462168 197116 3821380 0 0 0 871 1750 943 13 0 86 1 0
4 0 0 27432100 197128 3822320 0 0 0 3980 4767 2340 46 1 53 1 0
0 0 0 27493716 197132 3815844 0 0 0 1871 3209 2078 27 0 72 1 0
3 0 0 27424284 197132 3827036 0 0 0 1452 2551 1487 18 0 78 3 0
3 0 0 27435428 197160 3824116 0 0 0 2066 3430 2082 29 0 70 1 0
2 0 0 27452004 197172 3817440 0 0 0 1356 2722 1895 23 0 76 1 0
2 0 0 27436668 197176 3826648 0 0 0 1633 3629 2162 30 0 69 1 0
1 0 0 27439924 197204 3823124 0 0 0 1502 1786 1293 14 0 86 0 0
0 0 0 27466696 197212 3816780 0 0 0 1200 1701 1164 13 0 86 0 0
3 0 0 27432204 197212 3818344 0 0 0 2587 2098 2154 16 0 83 1 0
2 0 0 27421088 197224 3827224 0 0 0 1229 2635 1421 21 0 75 3 0
3 0 0 27319136 197232 3832088 0 0 13 2965 4220 1951 40 0 59 1 0
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
What I've already done:
/var/lib/pgsql/data/postgresql.conf (local connections only)
max_connections = 100
shared_buffers = 4096MB
work_mem = 16M
/etc/php.ini
memory_limit = 300M
[PostgresSQL]
pgsql.allow_persistent = Off # because I use pgbouncer
/etc/pgbouncer.ini (local connections only too)
max_client_conn = 600
default_pool_size = 80
/etc/httpd/conf/httpd.conf (didn't modify yet):
<IfModule prefork.c>
StartServers 10
MinSpareServers 8
MaxSpareServers 30
ServerLimit 512
MaxClients 512
MaxRequestsPerChild 4000
</IfModule>
Any suggestions please on how to speed up my Linux Apache PostgreSQL PHP (aka "LAPP") server?
I was actually hoping that Linux would use the additional memory for caching disks, but this doesn't seem to happen?
UPDATE:
I've installed iotop and it show PostreSQL doing SELECT's and my Perl daemon (for the Facebook game):
Total DISK READ: 0.00 B/s | Total DISK WRITE: 101.72 K/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
20555 be/4 postgres 0.00 B/s 78.25 K/s 0.00 % 0.28 % postgres: pref pref [local] SELECT
26397 be/4 postgres 0.00 B/s 1674.51 K/s 0.00 % 0.00 % postgres: pref pref [local] SELECT
26392 be/4 apache 0.00 B/s 3.91 K/s 0.00 % 0.00 % httpd
26402 be/4 postgres 0.00 B/s 3.22 M/s 0.00 % 0.00 % postgres: pref pref [local] SELECT
26448 be/4 apache 0.00 B/s 62.60 K/s 0.00 % 0.00 % httpd
26486 be/4 postgres 0.00 B/s 7.82 K/s 0.00 % 0.00 % postgres: pref pref [local] SELECT
26524 be/4 apache 0.00 B/s 3.91 K/s 0.00 % 0.00 % httpd
15392 be/4 nobody 0.00 B/s 3.91 K/s 0.00 % 0.00 % perl -w /usr/local/pref/pref.pl
1 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % init
2 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kthreadd]
3 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/0]
4 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/0]
5 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/0]
6 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [watchdog/0]
7 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/1]
8 rt/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/1]
9 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/1]
It seems that disk caching works ok, since the read is always 0 ?
Caching is done on more of a demand basis. Say whenever you do a read() operation on a file, the file is cached. So, if your database size and program size is low, then it would cache as much as it need to. Upon demand, when more queries will be done, it will be cached automatically.
You are not hitting any swapping which is awesome. There is a parameter vm.vfs_cache_pressure which determines how the cached memory will be reclaimed compared to reclamation of swap. But, you haven't reached the phase of memory usage where you should start to tweak the VM.
My 2 cents ;)