Fwd: ** PROBLEM Service: pgdb01.app.xmid.rz1.xtrav.de/Disk_/var/lib/pgsql is WARNING **
Alright, our session table blows up from time to time... How to discover and solve. First: df -h /dev/vde 394G 301G 74G 81% /var/lib/pgsql Alright, Posgres partition looks full. Get into database: psql -U postgres (attention, u are root now) \l+ # gives you a list of all databases, with size and stuff... Find the large guy, like: travelius4auth | trav_live | UTF8 | de_DE.utf8 | de_DE.utf8 | | 174 GB | pg_default | Alright, 174GB for a "config"-Database is way too much... Go into that database: \c travelius4auth After that, list all tables in that database with: \d+ public | remark_company_identification | Tabelle | trav_live | 40 kB | public | session | Tabelle | trav_live | 174 GB | public | socket_connection | Tabelle | trav_live | 0 bytes | Alright, the session table blows up. from here it gets a bit quirky... Looking at that table makes no sense, bc u will see nothing, except giant base64 encoded string dumps... Get an idea of how large the the data really is: travelius4auth=# select char_length(vars),changed, corp_auth_id, sess_id from session order by char_length(vars) desc; char_length | changed | corp_auth_id | sess_id -------------+------------------------+--------------+---------------------------- 250744 | 2019-09-02 22:14:22+02 | 3742 | c3gm76itkg7d0go1670ptq18e0 110540 | 2019-09-02 18:36:36+02 | 3790 | d9cdgpi57tsd0orvbt3agl5m24 98308 | 2019-09-02 21:23:10+02 | 3753 | 8pn268475n03he8idu04d7q2m2 63340 | 2019-09-02 18:24:32+02 | 3592 | tiku8k6nr6g83lbk6c0d77p4c2 59160 | 2019-09-02 19:12:47+02 | 3733 | gngid7r8vv3c133lnt1945rm60 Before the cleanup there were some with like 6 million chars, but even with that 174GB is way to much for ~1000 entries. So, feel free to remove entries where changed is older than 2 hours: delete from session where changed < '2019-09-02 18:10'; Also remove the largest entries if they are not active (selector is sess_id...). Afterwards it looks like you need to Vacuum Full the table. I tried various vacuum options, but only full recovered the disk space (thats why it blows up over time). So: Keep in mind: Vacuum Full Lock the Table or Database for the whole time, so do this out of business hours if possible. Also first vacuum to prepare unlocked and then vacuum full. Here the command: vacuum full session; Thats it. \d+ shows: public | session | Tabelle | trav_live | 2464 kB | And df -h says: /dev/vde 394G 128G 247G 35% /var/lib/pgsql Nice! Stay safe, Felix -------- Weitergeleitete Nachricht -------- Betreff: ** PROBLEM Service: pgdb01.app.xmid.rz1.xtrav.de/Disk_/var/lib/pgsql is WARNING ** Datum: Mon, 2 Sep 2019 21:19:56 +0200 (CEST) Von: nagios@monitor01.app.infra.rz1.xtrav.de An: f.seidel@traso.de Service: Disk_/var/lib/pgsql Host: pgdb01.app.xmid.rz1.xtrav.de Hostalias: pgdb01.xmid State: WARNING Address: 10.23.1.31 Info: DISK WARNING - free space: /var/lib/pgsql 75485 MB (19% inode=98%): Date/Time: Mon Sept 2 21:19:56 CEST 2019
participants (1)
-
Felix Seidel