I'm not getting something about the best way to set up a server using
PostgreSQL as a backend for a busy web server running drupal.
The postgresql performance folks
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
say that in a server with more that 1GB of ram
"a reasonable starting value for shared_buffers is 1/4 of the memory in your
system."
Okay, this server has 4GB of ram. pgtune suggests the following values for
predominately web based usage:
maintenance_work_mem = 240MB # pgtune wizard 2010-01-27
checkpoint_completion_target = 0.7 # pgtune wizard 2010-01-27
effective_cache_size = 2816MB # pgtune wizard 2010-01-27
work_mem = 18MB # pgtune wizard 2010-01-27
wal_buffers = 4MB # pgtune wizard 2010-01-27
checkpoint_segments = 8 # pgtune wizard 2010-01-27
shared_buffers = 960MB # pgtune wizard 2010-01-27
max_connections = 200 # pgtune wizard 2010-01-27
Here is where I'm not doing something right. With my shared_buffers at 960MB,
I need to adjust kern.shminfo.shmmax to 1GB (1073741824) to get postgres to
start. I thought I'd need to also adjust kern.shminfo.shmmall value as well
but that seems to change automatically whenever I adjust kern.shminfo.shmmax.
$ sysctl -a | grep kern.s
kern.securelevel=1
kern.saved_ids=1
kern.somaxconn=128
kern.sominconn=80
kern.sysvmsg=1
kern.sysvsem=1
kern.sysvshm=1
kern.stackgap_random=262144
kern.splassert=1
kern.seminfo.semmni=256
kern.seminfo.semmns=2048
kern.seminfo.semmnu=30
kern.seminfo.semmsl=60
kern.seminfo.semopm=100
kern.seminfo.semume=10
kern.seminfo.semusz=100
kern.seminfo.semvmx=32767
kern.seminfo.semaem=16384
kern.shminfo.shmmax=1073741824
kern.shminfo.shmmin=1
kern.shminfo.shmmni=128
kern.shminfo.shmseg=128
kern.shminfo.shmall=262144
At these values postgres will start and top shows a large amount of memory
still free:
Memory: Real: 55M/465M act/tot Free: 3433M Swap: 0K/8197M used/tot
Running a simple select only pgbench test against it will fail with an out of
memory error as it tries to vacuum --analyze the newly created database with
7500000 tuples.
pgbench -i -s 75 -h varley.openvistas.net -U _postgresql pgbench
vacuumdb --analyze -h varley.openvistas.net U _postgresql pgbench
When I run this and have top refreshing every second, I never see the free
memory drop below 3400M, so I'm not sure what memory we are running out of.
systat -i shows similar amounts of memory yet free.
The postgresql logs can perhaps shed some light on this for someone more
knowledgeable than myself. Here is what is in the log immediately prior to
the out of memory error:
2010-01-27 14:07:26.326319500 TopMemoryContext: 60712 total in 7 blocks; 4488
free (8 chunks); 56224 used
2010-01-27 14:07:26.326374500 TopTransactionContext: 8192 total in 1 blocks;
5408 free (0 chunks); 2784 used
2010-01-27 14:07:26.326389500 Operator class cache: 8192 total in 1 blocks;
3848 free (0 chunks); 4344 used
2010-01-27 14:07:26.326412500 MessageContext: 8192 total in 1 blocks; 5488
free (1 chunks); 2704 used
2010-01-27 14:07:26.326434500 smgr relation table: 8192 total in 1 blocks;
2816 free (0 chunks); 5376 used
2010-01-27 14:07:26.326440500 TransactionAbortContext: 32768 total in 1
blocks; 32752 free (0 chunks); 16 used
2010-01-27 14:07:26.326462500 Portal hash: 8192 total in 1 blocks; 3912 free
(0 chunks); 4280 used
2010-01-27 14:07:26.326469500 PortalMemory: 8192 total in 1 blocks; 8040
free (0 chunks); 152 used
2010-01-27 14:07:26.326490500 PortalHeapMemory: 15360 total in 4 blocks;
7944 free (12 chunks); 7416 used
2010-01-27 14:07:26.326496500 ExecutorState: 8192 total in 1 blocks;
7928 free (0 chunks); 264 used
2010-01-27 14:07:26.326517500 ExprContext: 0 total in 0 blocks; 0 free
(0 chunks); 0 used
2010-01-27 14:07:26.326539500 TupleSort: 24600 total in 2 blocks; 7584
free (0 chunks); 17016 used
2010-01-27 14:07:26.326562500 TupleSort: 92266520 total in 17 blocks;
8379568 free (9 chunks); 83886952 used
2010-01-27 14:07:26.326584500 Relcache by OID: 8192 total in 1 blocks; 2856
free (0 chunks); 5336 used
2010-01-27 14:07:26.326624500 CacheMemoryContext: 667696 total in 20 blocks;
235240 free (7 chunks); 432456 used
2010-01-27 14:07:26.326646500 pgbench_accounts_pkey: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.326652500 pg_constraint_contypid_index: 1024 total in
1 blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.326674500 pg_constraint_conrelid_index: 1024 total in
1 blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.326681500 pg_constraint_conname_nsp_index: 1024 total
in 1 blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.326703500 pg_shdepend_reference_index: 1024 total in 1
blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.326724500 pg_index_indrelid_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.326730500 pg_inherits_relid_seqno_index: 1024 total in
1 blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.326752500 pg_description_o_c_o_index: 1024 total in 1
blocks; 152 free (0 chunks); 872 used
2010-01-27 14:07:26.326758500 pg_shdepend_depender_index: 1024 total in 1
blocks; 88 free (0 chunks); 936 used
2010-01-27 14:07:26.326780500 pg_depend_reference_index: 1024 total in 1
blocks; 152 free (0 chunks); 872 used
2010-01-27 14:07:26.326802500 pg_depend_depender_index: 1024 total in 1
blocks; 152 free (0 chunks); 872 used
2010-01-27 14:07:26.326809500 pg_opclass_am_name_nsp_index: 1024 total in
1 blocks; 152 free (0 chunks); 872 used
2010-01-27 14:07:26.326830500 pg_foreign_data_wrapper_name_index: 1024
total in 1 blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.326837500 pg_enum_oid_index: 1024 total in 1 blocks;
344 free (0 chunks); 680 used
2010-01-27 14:07:26.326860500 pg_class_relname_nsp_index: 1024 total in 1
blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.326866500 pg_foreign_server_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.326888500 pg_statistic_relid_att_index: 1024 total in
1 blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.326909500 pg_cast_source_target_index: 1024 total in 1
blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.326915500 pg_language_name_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.326937500 pg_authid_oid_index: 1024 total in 1 blocks;
304 free (0 chunks); 720 used
2010-01-27 14:07:26.326943500 pg_amop_fam_strat_index: 1024 total in 1
blocks; 88 free (0 chunks); 936 used
2010-01-27 14:07:26.326966500 pg_index_indexrelid_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.326986500 pg_ts_template_tmplname_index: 1024 total in
1 blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.326993500 pg_ts_config_map_index: 1024 total in 1
blocks; 192 free (0 chunks); 832 used
2010-01-27 14:07:26.327014500 pg_opclass_oid_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.327021500 pg_foreign_data_wrapper_oid_index: 1024
total in 1 blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327044500 pg_auth_members_member_role_index: 1024
total in 1 blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327065500 pg_ts_dict_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327072500 pg_conversion_default_index: 1024 total in 1
blocks; 128 free (0 chunks); 896 used
2010-01-27 14:07:26.327095500 pg_operator_oprname_l_r_n_index: 1024 total
in 1 blocks; 128 free (0 chunks); 896 used
2010-01-27 14:07:26.327116500 pg_trigger_tgrelid_tgname_index: 1024 total
in 1 blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.327123500 pg_enum_typid_label_index: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327144500 pg_ts_config_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327151500 pg_user_mapping_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327173500 pg_opfamily_am_name_nsp_index: 1024 total in
1 blocks; 192 free (0 chunks); 832 used
2010-01-27 14:07:26.327194500 pg_type_oid_index: 1024 total in 1 blocks;
304 free (0 chunks); 720 used
2010-01-27 14:07:26.327201500 pg_aggregate_fnoid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327222500 pg_constraint_oid_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.327228500 pg_rewrite_rel_rulename_index: 1024 total in
1 blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327251500 pg_ts_parser_prsname_index: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327272500 pg_ts_config_cfgname_index: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327278500 pg_ts_parser_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327299500 pg_operator_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327306500 pg_namespace_nspname_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.327329500 pg_ts_template_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327335500 pg_amop_opr_fam_index: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327357500 pg_ts_dict_dictname_index: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327377500 pg_auth_members_role_member_index: 1024
total in 1 blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327384500 pg_type_typname_nsp_index: 1024 total in 1
blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.327405500 pg_opfamily_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327412500 pg_class_oid_index: 1024 total in 1 blocks;
304 free (0 chunks); 720 used
2010-01-27 14:07:26.327449500 pg_proc_proname_args_nsp_index: 1024 total
in 1 blocks; 192 free (0 chunks); 832 used
2010-01-27 14:07:26.327456500 pg_attribute_relid_attnum_index: 1024 total
in 1 blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.327479500 pg_proc_oid_index: 1024 total in 1 blocks;
344 free (0 chunks); 680 used
2010-01-27 14:07:26.327499500 pg_language_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327506500 pg_namespace_oid_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.327527500 pg_database_oid_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.327534500 pg_amproc_fam_proc_index: 1024 total in 1
blocks; 88 free (0 chunks); 936 used
2010-01-27 14:07:26.327556500 pg_authid_rolname_index: 1024 total in 1
blocks; 304 free (0 chunks); 720 used
2010-01-27 14:07:26.327577500 pg_foreign_server_name_index: 1024 total in
1 blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327584500 pg_attribute_relid_attnam_index: 1024 total
in 1 blocks; 240 free (0 chunks); 784 used
2010-01-27 14:07:26.327605500 pg_conversion_oid_index: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
2010-01-27 14:07:26.327612500 pg_user_mapping_user_server_index: 1024
total in 1 blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327634500 pg_conversion_name_nsp_index: 1024 total in
1 blocks; 280 free (0 chunks); 744 used
2010-01-27 14:07:26.327664500 MdSmgr: 8192 total in 1 blocks; 7960 free (0
chunks); 232 used
2010-01-27 14:07:26.327671500 LOCALLOCK hash: 8192 total in 1 blocks; 3912
free (0 chunks); 4280 used
2010-01-27 14:07:26.327723500 Timezones: 78520 total in 2 blocks; 5968 free
(0 chunks); 72552 used
2010-01-27 14:07:26.327729500 ErrorContext: 8192 total in 1 blocks; 8176
free (6 chunks); 16 used
2010-01-27 14:07:26.327874500
172.16.0.1(22842):_postgresql@pgbench:[17225]:ERROR: out of memory
2010-01-27 14:07:26.327881500
172.16.0.1(22842):_postgresql@pgbench:[17225]:DETAIL: Failed on request of
size 67108864.
Jeff Ross
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
