Split database overburdened postgres #5492

wilkis3 · 2022-03-28T21:55:20Z

wilkis3
Mar 28, 2022

Hi there,

We used one Etherpad database for 20 web portals and split it into 20 separate databases. After I successfully imported all the separate databases, the load increased dramatically and some large portal databases (~28 GB) timed out.

Some relevant postgresql settings(configured with PGTune):

shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 20971kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 14
max_parallel_workers_per_gather = 4
max_parallel_workers = 14
max_parallel_maintenance_workers = 4

As I describe in Discussion: ueberdb_insert_or_update always idle #5489 the postgres activities always show me: select * from pg_stat_activity;

If I execute srv/bin/run.sh with a current etherpad version I get the following output:

[2022-03-28 23:12:04.262] [WARN] client - Error: socket.io connection error: {"type":"TransportError","description":400} -- {
  errorId: 'G98ylarDcV4XIPpcvTjO',
  type: 'Uncaught exception',
  msg: 'Error: socket.io connection error: {"type":"TransportError","description":400}',
  url: 'https://pad.wachstumswende.de/p/g.XXXXXXXX%24XXXXXXXXX',
  source: 'https://pad.wachstumswende.de/javascripts/lib/ep_etherpad-lite/static/js/pad.js?callback=require.define&v=290445e7',
  linenumber: 3,
  userAgent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.2 Safari/605.1.15',
  stack: 'https://pad.wachstumswende.de/javascripts/lib/ep_etherpad-lite/static/js/pad.js?callback=require.define&v=290445e7:3:3898\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:8:9139\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:22876\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:25658\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:8:12359\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:5425\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:606\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:14606\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:8877\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:10709\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:10289'
}
[2022-03-28 23:12:32.329] [WARN] client - Error: socket.io connection error: {"type":"TransportError","description":400} -- {
  errorId: 'QMF4sZtjY7KlkBvaKmpy',
  type: 'Uncaught exception',
  msg: 'Error: socket.io connection error: {"type":"TransportError","description":400}',
  url: 'https://pad.wachstumswende.de/p/g.XXXXXXX%24XXXXXXX',
  source: 'https://pad.wachstumswende.de/javascripts/lib/ep_etherpad-lite/static/js/pad.js?callback=require.define&v=290445e7',
  linenumber: 3,
  userAgent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.2 Safari/605.1.15',
  stack: 'https://pad.wachstumswende.de/javascripts/lib/ep_etherpad-lite/static/js/pad.js?callback=require.define&v=290445e7:3:3898\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:8:9139\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:22876\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:25658\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:8:12359\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:5425\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:606\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:14606\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:8694\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:10709\n' +
    'https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:10289'
}
[2022-03-28 23:12:53.243] [WARN] client - Error: socket.io connection error: {"type":"TransportError","description":{"isTrusted":true}} -- {
  errorId: 'h89x116SdK6iirZNjhQk',
  type: 'Uncaught exception',
  msg: 'Error: socket.io connection error: {"type":"TransportError","description":{"isTrusted":true}}',
  url: 'https://pad.wachstumswende.de/p/g.XXXXXXXXX%24XXXXXXXX',
  source: 'https://pad.wachstumswende.de/javascripts/lib/ep_etherpad-lite/static/js/pad.js?callback=require.define&v=290445e7',
  linenumber: 3,
  userAgent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.3 Safari/605.1.15',
  stack: '@https://pad.wachstumswende.de/javascripts/lib/ep_etherpad-lite/static/js/pad.js?callback=require.define&v=290445e7:3:3898\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:8:9139\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:22876\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:25658\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:8:12359\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:5425\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:606\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:6:19844\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:7:14606\n' +
    '@https://pad.wachstumswende.de/socket.io/socket.io.js?v=290445e7:8:6647'
}
^C[2022-03-28 23:16:50.261] [ERROR] server - Metrics at time of fatal error:
{
  "httpStartTime": 1648501601706,
  "memoryUsage": 130252800,
  "memoryUsageHeap": 42670512,
  "ueberdb_lockAwaits": 27,
  "ueberdb_lockAcquires": 184,
  "ueberdb_lockReleases": 184,
  "ueberdb_reads": 127,
  "ueberdb_readsFailed": 0,
  "ueberdb_readsFinished": 127,
  "ueberdb_readsFromCache": 99,
  "ueberdb_readsFromDb": 28,
  "ueberdb_readsFromDbFailed": 0,
  "ueberdb_readsFromDbFinished": 28,
  "ueberdb_writes": 64,
  "ueberdb_writesFailed": 0,
  "ueberdb_writesFinished": 60,
  "ueberdb_writesObsoleted": 3,
  "ueberdb_writesToDb": 61,
  "ueberdb_writesToDbFailed": 0,
  "ueberdb_writesToDbFinished": 57,
  "ueberdb_writesToDbRetried": 0,
  "totalUsers": 1,
  "activePads": 1,
  "httpRequests": {
    "meter": {
      "mean": 0.08216763217199066,
      "count": 50,
      "currentRate": 0.08216762826379775,
      "1MinuteRate": 0.061984862712657476,
      "5MinuteRate": 0.06352084676998973,
      "15MinuteRate": 0.03820295718631701
    },
    "histogram": {
      "min": 0.670811653137207,
      "max": 44472.50541114807,
      "sum": 121513.12508964539,
      "variance": 81493435.18026558,
      "mean": 2430.2625017929076,
      "stddev": 9027.371443574568,
      "count": 50,
      "median": 1.2063241004943848,
      "p75": 1.9188241958618164,
      "p95": 32011.100024890857,
      "p99": 44472.50541114807,
      "p999": 44472.50541114807
    }
  },
  "connects": {
    "mean": 0.026916391174997164,
    "count": 16,
    "currentRate": 0.0269163912466811,
    "1MinuteRate": 0.007848473861079121,
    "5MinuteRate": 0.018996061221368515,
    "15MinuteRate": 0.012218733923727293
  },
  "lastDisconnect": 1648502210261,
  "disconnects": {
    "mean": 0.02663368960290227,
    "count": 15,
    "currentRate": 0.02663368970284271,
    "1MinuteRate": 0.013222785619926182,
    "5MinuteRate": 0.021093007119784582,
    "15MinuteRate": 0.012174178565200406
  }
}

Some other specs:

Ubuntu 20.04
64 GB RAM
AMD Ryzen 7 3700X 8-Core Processor (16 CPUs)
Postgres 12.9
Etherpad 1.8.7
Etherpad Integration: wechange etherpad

Does anyone have an idea how I can cope with the large databases and reduce the load on the server?

Answered by wilkis3

Apr 11, 2022

The creation of an index is the solution.
CREATE INDEX idx ON etherpad_XXXX.store(key);

I have no resource problems anymore. Without update and increased cache.

View full answer

webzwo0i · 2022-03-28T22:53:37Z

webzwo0i
Mar 28, 2022
Maintainer

If possible, try updating Etherpad. Various timeout issues should be handled better in recent versions and it should default to pooling now when using postgres. Does the timeout happen during startup?

Do I read #5489 (comment) correctly and you only use a single connection? You might try out connection pooling with postgrespool database backend.

You might also try increasing the buffer size in src/node_modules/ueberdb/lib/CacheAndBufferLayer.js (cache variable).

The errors you posted are not related to database problems, but iirc reconnecting and should be fixed in recent versions, too.

1 reply

wilkis3 Apr 5, 2022
Author

Thanks for you good comment.

I updated Etherpad to the last version and I increased the cache variable in src/node_modules/ueberdb/lib/CacheAndBufferLayer.js, nothing helped :(

wilkis3 · 2022-04-11T15:08:01Z

wilkis3
Apr 11, 2022
Author

The creation of an index is the solution.
CREATE INDEX idx ON etherpad_XXXX.store(key);

I have no resource problems anymore. Without update and increased cache.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

The Etherpad Foundation

Split database overburdened postgres #5492

{{title}}

Replies: 2 comments 1 reply

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Select a reply

The Etherpad Foundation

Split database overburdened postgres #5492

wilkis3 Mar 28, 2022

Replies: 2 comments · 1 reply

webzwo0i Mar 28, 2022 Maintainer

wilkis3 Apr 5, 2022 Author

wilkis3 Apr 11, 2022 Author

wilkis3
Mar 28, 2022

Replies: 2 comments 1 reply

webzwo0i
Mar 28, 2022
Maintainer

wilkis3 Apr 5, 2022
Author

wilkis3
Apr 11, 2022
Author