Hallo,
ich betreibe ein Browsergame und benutze als Backend eine Postgresql Datenbank.
Von der Serverlast läuft es eigentlich ganz gut, load overage liegt bei etwa 0.15, CPU Auslastung bei ~10% und 6GB Ram stecken drin.
Sobald aber die Punkte der Spieler aktualisiert werden (etwa 2000 Stück) steht für ein paar Sekunden der Server lahm :-(.
Logging:
→ (352.027ms.) SELECT v.*, u.people FROM villages AS v LEFT JOIN users u ON u.id = v.userid
→ (602.979ms.) SELECT id, ally, points, villages_points, ores_points FROM users
→ (116.105ms.) UPDATE users SET points = 1225, villages_points = 554, ores_points = 671
WHERE id = 1390
→ (67.0870000003ms.) UPDATE allys SET points_total = (SELECT SUM(points) AS allypoints FROM users WHERE ally = allys.id),
points = (SELECT SUM(points) FROM users WHERE id IN( (SELECT id FROM users WHERE ally = allys
.id ORDER BY points Limit 40) ) )
→ (44.1040000003ms.) DROP INDEX users_rank
→ (352.457ms.) SELECT id, rank FROM users ORDER BY points DESC, id
→ (13.8730000001ms.) UPDATE users SET rank = 203 WHERE id = 1390
→ (11.1519999998ms.) UPDATE users SET rank = 274 WHERE id = 475
→ (1415.935ms.) CREATE INDEX users_rank ON users USING BTREE (rank)
→ (12.6489999997ms.) UPDATE allys SET rank = 1 WHERE id = 14
→ (10.9869999997ms.) CREATE INDEX allys_rank ON allys USING BTREE (rank)
→ (379.694ms.) UPDATE users SET kill_all = kill_all_new, kill_def = kill_def_new, kill_att = kill_att_new
→ (316.385ms.) SELECT id, kill_att_rank AS rank FROM users ORDER by kill_att_new desc
→ (2398.263ms.) UPDATE users SET kill_att_rank = 93 WHERE id = 491
→ (61.8969999999ms.) UPDATE users SET kill_att_rank = 94 WHERE id = 1373
→ (16.426ms.) UPDATE users SET kill_att_rank = 139 WHERE id = 1315
→ (14.98ms.) UPDATE users SET kill_att_rank = 215 WHERE id = 935
→ (33.4129999997ms.) UPDATE users SET kill_att_rank = 219 WHERE id = 944
→ (11.284ms.) UPDATE users SET kill_att_rank = 248 WHERE id = 506
→ (26.5980000002ms.) UPDATE users SET kill_att_rank = 258 WHERE id = 722
→ (16.9040000001ms.) UPDATE users SET kill_att_rank = 462 WHERE id = 1177
→ (533.456ms.) SELECT id, kill_def_rank AS rank FROM users ORDER by kill_def_new desc
→ (474.271ms.) UPDATE users SET kill_def_rank = 72 WHERE id = 438
→ (17.784ms.) UPDATE users SET kill_def_rank = 225 WHERE id = 979
→ (16.7789999996ms.) UPDATE users SET kill_def_rank = 229 WHERE id = 729
→ (10.0700000003ms.) UPDATE users SET kill_def_rank = 528 WHERE id = 912
→ (552.764ms.) SELECT id, kill_all_rank AS rank FROM users ORDER by kill_all_new desc
→ (205.369ms.) UPDATE users SET kill_all_rank = 198 WHERE id = 861
→ (11.3590000002ms.) UPDATE users SET kill_all_rank = 387 WHERE id = 575
→ (11.6980000003ms.) UPDATE users SET kill_all_rank = 528 WHERE id = 875
→ (64.3599999999ms.) UPDATE users SET kill_all_rank = 735 WHERE id = 929
→ (15.7170000002ms.) UPDATE allys SET kill_all = kill_all_new, kill_def = kill_def_new, kill_att = kill_att_new
Hier sieht man, dass eine normale UPDATE Query plötzlich 2 Sekunden dauert. Nach dem Update läuft wieder alles normal.
Was kann ich dagegen tun?
Config File:
#---------------------------------------------------------------------------
FILE LOCATIONS
#---------------------------------------------------------------------------
The default values of these variables are driven from the -D command line
switch or PGDATA environment variable, represented here as ConfigDir.
#data_directory = ‘ConfigDir’ # use data in another directory
hba_file = ‘/etc/postgresql/8.1/main/pg_hba.conf’ # host-based authentication file
ident_file = ‘/etc/postgresql/8.1/main/pg_ident.conf’ # IDENT configuration fileIf external_pid_file is not explicitly set, no extra pid file is written.
external_pid_file = ‘/var/run/postgresql/8.1-main.pid’ # write an extra pid file
#---------------------------------------------------------------------------CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
- Connection Settings -
#listen_addresses = ‘localhost’ # what IP address(es) to listen on;
comma-separated list of addresses;
defaults to ‘localhost’, ‘*’ = all
port = 5432
max_connections = 300#superuser_reserved_connections = 2
unix_socket_directory = ‘/var/run/postgresql’
#unix_socket_group = ‘’
#bonjour_name = ‘’ # defaults to the computer name- Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
ssl = false
#password_encryption = on
#db_user_namespace = offKerberos
#krb_server_keyfile = ‘’
#krb_srvname = ‘postgres’
#krb_server_hostname = ‘’ # empty string matches any keytab entry
#krb_caseins_users = off- TCP Keepalives -
see ‘man 7 tcp’ for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
0 selects the system default
#---------------------------------------------------------------------------RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
- Memory -
shared_buffers = 20000 # min 16 or max_connections*2, 8KB each
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # can be 0 or morenote: increasing max_prepared_transactions costs ~600 bytes of shared memory
per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 10024 # min 64, size in KB
maintenance_work_mem = 256000 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB- Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each- Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ‘’- Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round- Background writer -
#---------------------------------------------------------------------------
WRITE AHEAD LOG
#---------------------------------------------------------------------------
- Settings -
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first optionsupported by the operating system:
open_datasync
fdatasync
fsync
fsync_writethrough
open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000- Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off- Archiving -
#archive_command = ‘’ # command to use to archive a logfile
segment
#---------------------------------------------------------------------------QUERY TUNING
#---------------------------------------------------------------------------
- Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on- Planner Cost Constants -
#effective_cache_size = 5000 # typically 8KB each
#random_page_cost = 3 # units are one sequential page fetchcost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)- Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0- Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#constraint_exclusion = off
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicitJOINs
Danke