On Thu, Aug 1, 2019 at 1:22 AM Amit Kapila <amit.kapil...@gmail.com> wrote:
> On Wed, Jul 31, 2019 at 10:13 AM Amit Kapila <amit.kapil...@gmail.com> wrote:
> > On Tue, Jul 30, 2019 at 5:26 PM Thomas Munro <thomas.mu...@gmail.com> wrote:
> > > but
> > > here's a small thing: I managed to reach an LWLock self-deadlock in
> > > the undo worker launcher:
> > >
> >
> > I could see the problem, will fix in next version.
>
> Fixed both of these problems in the patch just posted by me [1].
I reran the script that found that problem, so I could play with the
linger logic. It creates N databases, and then it creates tables in
random databases (because I'm testing with the orphaned table cleanup
patch) and commits or rolls back at (say) 100 tx/sec. While it's
doing that, you can look at the pg_stat_undo_logs view to see the
discard and insert pointers whizzing along nicely, but if you look at
the process table with htop or similar you can see that it's forking
undo apply workers at 100/sec (the pid keeps changing), whenever there
is more than one database involved. With a single database it lingers
as I was expecting (and then creates problems when you want to drop
the database). What I was expecting to see is that if you configure
the test to generate undo work in 2, 3 or 4 dbs, and you have
max_undo_workers set to 4, then you should finish up with 4 undo apply
workers hanging around to service the work calmly without any new
forking happening. If you generate undo work in more than 4
databases, I was expecting to see the undo workers exiting and being
forked so that a total of 4 workers (at any time) can work their way
around the more-than-4 databases, but not switching as fast as they
can, so that we don't waste all our energy on forking and setup (how
fast exactly they should switch, I don't know, that's what I wanted to
see). A more advanced thing to worry about, not yet tested, is how
well they'll handle asymmetrical work distributions (not enough
workers, but some databases producing a lot and some a little undo
work). Script attached.
--
Thomas Munro
https://enterprisedb.com
# Install: python-psycopg2 (Debian/Ubuntu)
# Run with python2 ./test_undo_worker_local_balancing.py
import psycopg2 as pg
import random
import time
def make_conn(dbname):
return pg.connect("dbname=" + dbname)
def run_test(dbs, txs_per_sec, commit_ratio, runtime):
# first, create a control connection that we'll use to create databases
conn = make_conn("postgres")
conn.set_isolation_level(pg.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
# recreate all the databases
for n in range(dbs):
cursor.execute("drop database if exists db%d" % n)
cursor.execute("create database db%d" % n)
# next, open a separate session to each database
conns = [make_conn("db%d" % n) for n in range(dbs)]
cursors = [conn.cursor() for conn in conns]
# set up interesting GUCs in each session
for cursor in cursors:
cursor.execute("set rollback_overflow_size = '0kB'")
cursor.connection.commit()
# now do random work at the requested rate until our time runs out
start = time.time()
finish_at = start + runtime
txs = 0
table_number = 0
now = start
while now < finish_at:
# choose a random session, and run a transaction
cursor = random.choice(cursors)
cursor.execute("create table t%d ()" % table_number)
# decide whether to commit or roll back
if random.uniform(0.0, 1.0) < commit_ratio:
cursor.connection.commit()
else:
cursor.connection.rollback()
table_number += 1
# wait until it's time to start the next transaction
txs += 1
next_tx_at = (txs / txs_per_sec) + start
if next_tx_at < now:
print "can't run transactions fast enough, not sleeping"
else:
time.sleep(next_tx_at - now)
now = time.time()
if __name__ == "__main__":
dbs = 4
txs_per_sec = 1 #100.0
commit_ratio = 0.0 # 0.0 = always roll back, 1.0 = always commit
runtime = 120 # how long to run for, in seconds
run_test(dbs, txs_per_sec, commit_ratio, runtime)
