UPDATE Optimierung

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 file

If 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 = off

Kerberos

#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 more

note: 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 option

supported 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 fetch

cost

#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 explicit

JOINs

Danke

Welche PostgreSQL Version ist das?

Was sagt ein EXPLAIN und ein EXPLAIN ANALYZE für diese eine Anfrage?

Nach Deinem Log nach aber nicht bei allen id-Werten, dasselbe Update läuft manchmal auch deutlich schneller.

Entweder ist da grad, warum auch immer, ein Lock, oder PG entscheidet sich für einen Seq-Scan indiesem Falle. Ist auf ID ein Index, ist ID unique?

Deine (etwa 2000 Stück), auf was beziehen die sich? Unterschiedliche IDs oder trifft diese Abfrage etwa 2000 Zeilen?

Ansonsten: siehe ads, zeige uns ein explain analyse.


Andreas

du dropst mal eben nen Index …
dann erstellst du neue … einen oder zwei … und das auf einer table wo nur ein paar tausen datensaetze sind - nur wegen den paar updates ?

da musst du dich dann auch nicht wundern, dass dat DBMS (sobald notwendig) danach mal 2 sek in files fuer alle DS die indices erzeugen muss.

sieht nach hausbackenem problem aus.

s1=# EXPLAIN UPDATE users SET kill_att_rank = 93 WHERE id = 491;
QUERY PLAN

Bitmap Heap Scan on users (cost=3.00…7.02 rows=1 width=733)
Recheck Cond: (id = 491)
→ Bitmap Index Scan on users_id (cost=0.00…3.00 rows=1 width=0)
Index Cond: (id = 491)
(4 Zeilen)

Der Server und auch Postgresql lahmt nur während das Update läuft. Danach ist wider alles normal. Kann ich vll. etwas an der Postgresql Konfig optimieren?

Warum dropst du die Indices … und erstellst die neu ? In deinem 1. Beispiel …
Lass das einfach mal weg, ich denke, dann gibt es auch keinen Grund für das DBMS bei dem 1. Update danach zu “lahmen” … weil ich vermute, dass bei diesem 1. Update danach PG anfaengt den Index zu erzeugen …

Guter Einwand, ist mir nicht aufgefallen.

da musst du dich dann auch nicht wundern, dass dat DBMS (sobald notwendig) danach mal 2 sek in files fuer alle DS die indices erzeugen muss.

sieht nach hausbackenem problem aus.

Ähm, nein. Der Index ist ja an der Stelle erzeugt.

Andreas

Gut, aber was heisst das, “Der Index ist erzeugt” … ich koennte mir vorstellen, dass das “verzögerbar” ist. Aber hast recht - bei mir erzeugt der auch sofort.

Jedoch kann es ein, dass das OS dann erstmal ein paar nicht wenige Bytes wegschreiben muss … oder was auch immer.

Ich wuerde den ganzen unsinn !? einfach mal lassen … Das kommt dabei raus, wenn man Leuten sagt, das UPDATES auf Indizierte Tabellen potentiell “langsam” sind. :slight_smile: Was ja auch stimmt … aber das da oben scheint mit eine Überreaktion zu sein g

Ob ich es raus lösche oder nicht, es verändert sich kaum was an der Geschwindigkeit.

Gut, und wir haben immer noch keinen EXPLAIN und EXPLAIN ANALYZE Output von dir gesehen. Das wurde schon viel weiter vorn im Thread gefordert.

Als ich anfänglich über PG stolperte, war mich auch nicht bewusst, dass es neben einem “EXPLAIN blubb” auch noch das statement “EXPLAIN ANALYZE blubb” gibt. Es lohnt sich, hin und wieder daraufhin zu weisen, wenn man merkt dass der Fragende kein Experte ist.

@Problem:
Vielleicht solltest du mal darueber nachdenken, diese Summe (die wohl staendig irgendwo selektiert wird) schon bei jedem update der punkte des einzelnen spielers zu aktualisieren … also dass du die gesamt-punktzahl eines spielers immer aktualisierst hast, sobald sich bei ihm irgendwo ein puenktchen aendert. Ich denke, dass der “Aufwand” fuer PG dieses fuer einen Spieler der gerade spielt zu berechnen unwesentlich ist.

Trigger sind dafür wie gemacht :slight_smile:

dann kannst du diesen ganzen chronjob fuer obsolete erklaeren …