Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

OperationalError: database is locked with SQLite on PythonAnywhere #515

Closed
Avasam opened this issue Jan 30, 2022 · 27 comments
Closed

OperationalError: database is locked with SQLite on PythonAnywhere #515

Avasam opened this issue Jan 30, 2022 · 27 comments
Labels
bug question

Comments

@Avasam
Copy link

Avasam commented Jan 30, 2022 •
edited

Recently in my app I have reduced the quantity of embedded data I ask from an API. This leads to smaller requests (and was necessary in some cases as their endpoint would time out), but also a lot of new small requests to get the extra information. Which often ends up being identical and can be triggered near-simultaneously.

In theory this wouldn't be an issue, as all those extra calls would simply use the cache, (in fact, it should lead to less non-cached requests, which is a net-gain for me), but instead I find myself suddenly hitting a lot of sqlite3.OperationalError: database is locked. Which I'm assuming might be due to simultaneous write attempts.

I need my cache to work across web-workers, store potentially very large responses (so no DynamoDB) and I don't have access to Redis on my host.
How should I go about resolving my issue?
My session object:

session = CachedSession(
    expire_after=timedelta(days=1),
    allowable_codes=(200, 500),
    ignored_parameters=["status", "video-only", "embed"],
    backend=configs.cached_session_backend,  # None, so the default SQLite
    fast_save=True,
    use_temp=True)
session.mount("https://", HTTPAdapter(pool_maxsize=RATE_LIMIT + 1))  # 101

Relates to #514

Related question, I see there's a new filesystem backend. How does it compare with SQLite in terms of performance (looking up hundreds if not thousands of cached requests) and size? The documentation doesn't really tell why I'd want that over SQLite.
@JWCook
Copy link
Member

JWCook commented Feb 2, 2022

I need my cache to work across web-workers

Are those workers running from separate threads, or from separate processes?

How should I go about resolving my issue?

SQLite (with the settings that requests-cache uses by default) is supposed to be safe for concurrent writes. Internally, SQLite will queue them up and write them in serial. It's possible that fast_save is the problem here. Here are the SQLite docs for that behavior (PRAGMA synchronous = 0): https://www.sqlite.org/pragma.html#pragma_synchronous

This comes with the risk of possible data loss, but I'm not sure whether this can also introduce race conditions.

Try turning fast_save off and let me know if you still get errors. If that was the problem, then that's useful info we can add to the docs. Otherwise, I'll probably need more info about your application to figure out what's going on.

@Avasam
Copy link
Author

Avasam commented Feb 3, 2022 •
edited

I believe each web worker is a different process.
I should've mentioned: I had already tested with fast_save=False.

I'm currently unable to replicate it anymore (with and without session.cache_disabled()). I'm not sure what else I could've changed. I will let you know if it happens again.

@JWCook
Copy link
Member

JWCook commented Feb 3, 2022

Alright, I'll close this for now, but we can reopen it if it comes up again.

@JWCook JWCook closed this as completed Feb 3, 2022
@Avasam
Copy link
Author

Avasam commented Feb 15, 2022 •
edited

Once the database deadlocks, I have to restart the server (although it seems I may not have to delete .sqlite or .sqlite-journal files). Once again, this also happens with fast_save=False

Error log:

2022-02-15 05:29:52,526: Clearing all items from the cache
2022-02-15 05:29:52,734: Removing expired responses.
2022-02-15 05:32:17,199: Clearing all items from the cache
2022-02-15 05:32:17,513: Removing expired responses.
2022-02-15 05:32:22,531: Exception on /api/players/18vk7vjl/update [POST]
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 2051, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1501, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1499, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1485, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/home/Avasam/mysite/backend/./api/global_scoreboard_api.py", line 48, in update_player
    return __do_update_player(name_or_id)  # type: ignore # TODO: Raise this issue upstream
  File "/home/Avasam/mysite/backend/./api/api_wrappers.py", line 41, in _verify
    response = fn(player, *args, **kwargs)
  File "/home/Avasam/mysite/backend/./api/global_scoreboard_api.py", line 93, in __do_update_player
    return __do_update_player_bypass_restrictions(name_or_id, current_user=current_user)
  File "/home/Avasam/mysite/backend/./api/global_scoreboard_api.py", line 59, in __do_update_player_bypass_restrictions
    result = get_updated_user(name_or_id)
  File "/home/Avasam/mysite/backend/./services/user_updater.py", line 75, in get_updated_user
    clear_cache_for_user(user._id)
  File "/home/Avasam/mysite/backend/./services/cached_requests.py", line 37, in clear_cache_for_user
    use_session().cache.remove_expired_responses()
  File "/home/Avasam/.local/lib/python3.9/site-packages/requests_cache/backends/base.py", line 176, in remove_expired_responses
    for key, response in self._get_valid_responses(delete=True):
  File "/home/Avasam/.local/lib/python3.9/site-packages/requests_cache/backends/base.py", line 216, in _get_valid_responses
    for key in self.responses.keys():
  File "/usr/local/lib/python3.9/_collections_abc.py", line 825, in __iter__
    yield from self._mapping
  File "/home/Avasam/.local/lib/python3.9/site-packages/requests_cache/backends/sqlite.py", line 228, in __iter__
    for row in con.execute(f'SELECT key FROM {self.table_name}'):
sqlite3.OperationalError: database is locked

(related "cleanup" code that prints the 4 last lines of my logs below)

def clear_cache_for_user(user_id: str):
    try:
        print(f"Deleting cache specific to user '{user_id}'...")
        use_session(user_id).cache.clear()
        print("Done")
    except BaseException:
        print(f"Something went wrong while deleting cache for '{user_id}'")
        raise
    try:
        print("Removing expired responses...")
        http_cache_session.cache.remove_expired_responses() # This one sometimes just fails with above error log
        print("Done")
    except BaseException:
        print(f"Something went wrong while removing expired responses after '{user_id}'")
        raise
Server log (my prints, it just shows that it starts happening randomly): 
2022-02-15 05:52:42 Deleting cache specific to user '7j42kvx1'...
2022-02-15 05:52:43 Done
2022-02-15 05:52:43 Removing expired responses...
2022-02-15 05:52:50 Done
2022-02-15 05:53:12 Update request for: v81v54q8
2022-02-15 05:53:12 [ GET ] https://www.speedrun.com/api/v1/users/v81v54q8
2022-02-15 05:53:26 [ GET ] https://www.speedrun.com/api/v1/runs?user=v81v54q8&status=verified&embed=game.variables&max=200
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/runs?user=v81v54q8&status=verified&embed=game.variables&max=200&offset=200
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/games/o6gnn812/levels?max=200
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/jdz7646k?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=21ddpv51
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/games/o6gnn812/levels?max=200
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6gnn812/level/5wk83exw/zdnx9rx2?video-only=True&embed=players
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/games/o6gnn812/levels?max=200
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6gnn812/level/xd16nyy9/zdnx9rx2?video-only=True&embed=players
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/games/j1lqnj6g/levels?max=200
2022-02-15 05:53:43 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/jdz7646k?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=z19gw4l4
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/n2y39ezd?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=z19gw4l4
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/nd2n3vd0/category/9kv53edg?video-only=True&embed=players&var-ql670jx8=zqoo3xgq&var-yn25ozen=5q880krq
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6gnn812/level/5d7lgxe9/zdnx9rx2?video-only=True&embed=players
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/w203goj2?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=21ddpv51
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d757n67/category/7dg8v9l2?video-only=True&embed=players&var-ylqmjvzn=p12mwvvq&var-dloyj1d8=gq7d45nq
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q&var-ql6gwoj8=klrz6yj1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pd0qq31e/category/wkp1nm0k?video-only=True&embed=players&var-dlo3kjel=81446rk1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d79qvl1/category/wkpm8jwk?video-only=True&embed=players&var-dlo323el=jqzxko21
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/nd2n3vd0/category/zd3n4ekn?video-only=True&embed=players&var-6nj4jg6n=jq6rx5nq&var-yn25ozen=5q880krq
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/v1pxlz68/category/jdre870k?video-only=True&embed=players&var-jlz590yl=4qy8zr71&var-onvj99rn=zqowrmgl
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q&var-ql6gwoj8=81p760g1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/mkerqxd6?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=z19gw4l4
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6gnk812/category/824r1wd5?video-only=True&embed=players&var-yn2d9d85=8149vmvq&var-rn10v2p8=4lxonerl
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pdv29k6w/category/vdo17y12?video-only=True&embed=players&var-wledjd2n=jqze0jgq&var-p85p55lg=81w0e9l4
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/yd4krg6e/category/z27olzd0?video-only=True&embed=players&var-m68k2q82=gq759v1p&var-7896op68=p12mw9vq
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d757n67/category/7dg8v9l2?video-only=True&embed=players&var-ylqmjvzn=p12mwvvq&var-dloyj1d8=21gd4nx1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/nd2e0rrd/category/ndx9ll5d?video-only=True&embed=players&var-9l716y9l=zqod85x1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1n29w1p/category/wdmzmeo2?video-only=True&embed=players&var-wl36g46l=jqz07841
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/games/j1lqnj6g/levels?max=200
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/category/zdn1ennk?video-only=True&embed=players&var-0nwk11kn=z19rezy1&var-78963368=814e7kwl
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/nd2e0rrd/category/jdz90432?video-only=True&embed=players&var-9l716y9l=zqod85x1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1l30z1g/category/9d8j0nqk?video-only=True&embed=players
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/yd4krg6e/category/z27olzd0?video-only=True&embed=players&var-m68k2q82=gq759v1p&var-7896op68=81po0eeq
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/vdo413o2?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=z19gw4l4
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o1y7q41q/category/9d8xe532?video-only=True&embed=players&var-ylqkqeml=81wdp4mq&var-gnxrwyjn=5le4r2m1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pd0qn31e/category/9kvyojkg?video-only=True&embed=players&var-0nwvwk8q=5q8jr4rl
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/7dg6njgk?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=z19gw4l4
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pd0qq31e/category/q2577vdo?video-only=True&embed=players
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d79o2g1/category/q25myvyd?video-only=True&embed=players&var-9l71e37l=5q8r23k1&var-yn259rjn=xqk0d5k1
2022-02-15 05:53:44 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1n29w1p/category/7kjz7q3d?video-only=True&embed=players&var-wl36g46l=jqz07841
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/w203goj2?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=z19gw4l4
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/mker4y6d?video-only=True&embed=players&var-789vq6nw=rqvv667q
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/level/rdnzqndm/5dw6poe2?video-only=True&embed=players
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/268w996p/category/5dw87ndg?video-only=True&embed=players&var-onv0pr8m=klrx0oml
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/76ro4468/category/ndx75wv2?video-only=True&embed=players&var-0nw2rp5n=klr5gw21&var-ql67wrx8=jq6kzynl
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/yd4krg6e/category/9kv7x0kg?video-only=True&embed=players&var-ylpej2v8=4lxo85jl&var-7896op68=p12mw9vq
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/wdm6xjek?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=z19gw4l4
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6gnn812/category/7dg9nxk4?video-only=True&embed=players&var-ylq00z8r=4lxg874q
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/mkerqxd6?video-only=True&embed=players&var-38dzek0n=klrwydo1&var-6nj40pn4=21ddpv51
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d79o2g1/category/wkp1my0k?video-only=True&embed=players&var-9l71e37l=5q8r23k1&var-9l71wyql=jqzyy44l&var-2lgr51en=xqkkg4dq
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/games/j1lqnj6g/levels?max=200
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/v1pxlz68/category/jdzv7gkv?video-only=True&embed=players&var-jlz590yl=4qy8zr71&var-onvj99rn=zqowrmgl
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d794qg1/category/mkerp49d?video-only=True&embed=players&var-ylq911z8=5q8zg63q&var-5ly7o0yl=rqvokyyl
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q&var-ql6gwoj8=5lmxwgj1
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d794qg1/category/mkerp49d?video-only=True&embed=players&var-ylq911z8=5q8zg63q&var-5ly7o0yl=klrnkyj1
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/46w22l6r/category/wk6oork1?video-only=True&embed=players&var-38dyy180=013xmnx1
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/3698w3dl/category/rkljlqdn?video-only=True&embed=players
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/m1mx7e36/category/zd363rnd?video-only=True&embed=players
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/level/rdnzqndm/zdnzrndq?video-only=True&embed=players
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/level/gdrxy8dz/zdnzrndq?video-only=True&embed=players
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/games/o6gnre51/levels?max=200
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/m1mne3d2/category/7kjq14d3?video-only=True&embed=players&var-e8m0mel6=z19xrr01
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/rdnppy69/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/nwl77rg9/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6glzend/category/jdz8893d?video-only=True&embed=players&var-68km4y3l=5lm83pjq
2022-02-15 05:53:45 [ GET ] https://www.speedrun.com/api/v1/leaderboards/y655576e/category/wdm8lx2q?video-only=True&embed=players&var-9l7v44l1=4lxg824q&var-p854g25l=z19yn8kl
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/ewpx7v4d/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/xd433lp9/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/category/zd37wy2n?video-only=True&embed=players&var-ylpvm5kl=p12opo4l&var-78963368=814e7kwl
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6gnre51/level/xd1rxqq9/8248lxwk?video-only=True&embed=players&var-wle93rr8=81pm3ng1
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/k6qwpm6g/category/mkerqxd6?video-only=True&embed=players&var-38dzek0n=jqz7xw2l&var-6nj40pn4=21ddpv51
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/games/j1lqnj6g/levels?max=200
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/9dowy731/category/7dg6p5gk?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pdv29k6w/category/7dg6pjpk?video-only=True&embed=players&var-68k4j4kl=21dg2041&var-p85p55lg=81w0e9l4
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1n29w1p/category/n2yjm372?video-only=True&embed=players&var-wl36g46l=jqz07841
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/z9888xl9/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/category/9kv76g3k?video-only=True&embed=players&var-2lgg5d7l=klr0p0jl&var-78963368=p12ex5v1
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1n29w1p/category/z2739god?video-only=True&embed=players&var-wl36g46l=jqz07841
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/nd2n3vd0/category/zd3n4ekn?video-only=True&embed=players&var-6nj4jg6n=jq6rx5nq&var-yn25ozen=21dgyk31
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/level/z987m7wl/5dw6poe2?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1n29w1p/category/w20pel5k?video-only=True&embed=players&var-wl36g46l=jqz07841
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/rw6nnz7d/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/v1pxlz68/category/rklv388d?video-only=True&embed=players&var-jlz590yl=4qy8zr71&var-onvj99rn=zqowrmgl
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/3698w3dl/category/824lj9nd?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdk4ol1m/category/02qjz6yd?video-only=True&embed=players&var-j84ze4n9=rqvv527q
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q&var-ql6gwoj8=814x4v0q
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pd0qn31e/category/9kvyojkg?video-only=True&embed=players&var-0nwvwk8q=5q840w3l
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1n8pkx1/category/5dw148lk?video-only=True&embed=players
2022-02-15 05:53:46 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q&var-ql6gwoj8=5lekv45l
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/76r34v68/category/zd38omrk?video-only=True&embed=players&var-789j199n=21g484n1&var-rn10ggk8=4lxwexj1
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/nd2n3vd0/category/zd3n4ekn?video-only=True&embed=players&var-6nj4jg6n=9qjn4701&var-yn25ozen=21dgyk31
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/ldyppozd/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/yd4krg6e/category/z27olzd0?video-only=True&embed=players&var-m68k2q82=xqkeonq9&var-7896op68=p12mw9vq
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqqj6g/category/xk9nn620?video-only=True&embed=players&var-wl32kon1=81pykwv1
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/games/j1lqnj6g/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/r9g45r29/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/76ro4468/category/xd17jgrd?video-only=True&embed=players&var-0nw2rp5n=klr5gw21&var-ql67wrx8=jq6kzynl
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/z9888x19/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/46w3kvq1/category/jdro095d?video-only=True&embed=players&var-dlo7pp58=21g83onl
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/76r95468/category/824qxnk5?video-only=True&embed=players&var-e8mkkvn6=5lmokey1
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d794qg1/category/mkerp49d?video-only=True&embed=players&var-ylq911z8=5q8zg63q&var-5ly7o0yl=xqk762kl
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/level/nwlo2pwv/zdnzrndq?video-only=True&embed=players
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/xd07781w/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:47 [ GET ] https://www.speedrun.com/api/v1/leaderboards/yd4krg6e/category/z27olzd0?video-only=True&embed=players&var-m68k2q82=xqkeonq9&var-7896op68=81po0eeq
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/ywe8864w/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/rdnppyv9/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pd0qn31e/category/mkeqj69k?video-only=True&embed=players&var-rn1z04kl=810g96jl
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/z98nepg9/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/category/9kv76g3k?video-only=True&embed=players&var-2lgg5d7l=klr0p0jl&var-78963368=z19g84jl
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/gdrqq5k9/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/ldejwj13/category/wk6ovrk1?video-only=True&embed=players&var-789w46lw=4qy7yg2q
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/7dggreld?video-only=True&embed=players&var-789vq6nw=rqvv667q
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1n8pkx1/category/mkex9r6d?video-only=True&embed=players
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/category/9d8p36qk?video-only=True&embed=players&var-p854pp0l=z19g8zjl&var-78963368=814e7kwl
2022-02-15 05:53:48 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pdv29k6w/category/vdo17y12?video-only=True&embed=players&var-wledjd2n=jqze0jgq&var-p85p55lg=0136wk15
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/games/j1lqnj6g/levels?max=200
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/yd4krg6e/category/9kv7x0kg?video-only=True&embed=players&var-ylpej2v8=0q5z5vrq&var-7896op68=p12mw9vq
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/category/xk9jw94d?video-only=True&embed=players&var-9l73mqpn=013vj73l&var-78963368=814e7kwl
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/pd0qn31e/category/xd14vmrd?video-only=True&embed=players&var-r8r71o2n=5lmvw2yl
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q&var-ql6gwoj8=0q5o75m1
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d79qvl1/category/jdrlv9xd?video-only=True&embed=players&var-rn10g6o8=p12exkd1&var-gnxv33pl=jqz7x0kl
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/category/9kv76g3k?video-only=True&embed=players&var-2lgg5d7l=klr0p0jl&var-78963368=814e7kwl
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/games/kdkpol1m/levels?max=200
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/category/5dw5x3ed?video-only=True&embed=players&var-789vq6nw=rqvv667q&var-ql6gwoj8=9qjzexe1
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/36988n2d/category/mkev6oj2?video-only=True&embed=players
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/games/o6gnre51/levels?max=200
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d794qg1/category/mkerp49d?video-only=True&embed=players&var-ylq911z8=5q8zg63q&var-5ly7o0yl=zqoo8v4q
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1l30z1g/category/zd3l1rnd?video-only=True&embed=players
2022-02-15 05:53:49 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:49 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/ldyppo7d/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:50 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:50 [ GET ] https://www.speedrun.com/api/v1/leaderboards/4d79o2g1/category/wkp1my0k?video-only=True&embed=players&var-9l71e37l=5q8r23k1&var-9l71wyql=jqzyy44l&var-2lgr51en=gq7ev4p1
2022-02-15 05:53:50 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:50 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/r9g2lr2d/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:54 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:54 [ GET ] https://www.speedrun.com/api/v1/games/j1lqnj6g/levels?max=200
2022-02-15 05:53:54 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:54 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:54 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:54 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/n93ggrrd/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:54 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:54 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/level/z987m7wl/zdnzrndq?video-only=True&embed=players
2022-02-15 05:53:54 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/o9xngk7w/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:54 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:54 [ GET ] https://www.speedrun.com/api/v1/leaderboards/kdkpol1m/level/n93ggred/824mmlg2?video-only=True&embed=players
2022-02-15 05:53:54 [ GET ] https://www.speedrun.com/api/v1/leaderboards/o6gnre51/level/495g412w/8248lxwk?video-only=True&embed=players&var-wle93rr8=81pm3ng1
2022-02-15 05:53:59 Ignoring cache for this request because of OperationalError: database is locked
2022-02-15 05:53:59 [ GET ] https://www.speedrun.com/api/v1/leaderboards/j1lqnj6g/level/ldyg3jd3/zdnzrndq?video-only=True&embed=players
2022-02-15 05:53:59 Deleting cache specific to user 'v81v54q8'...
2022-02-15 05:53:59 Done
2022-02-15 05:53:59 Removing expired responses...
2022-02-15 05:54:04 Something went wrong while removing expired responses after 'v81v54q8'

@Avasam
Copy link
Author

Avasam commented Feb 15, 2022

Note: I don't think this happens in 0.7.3dev0. I just reverted and haven't seen the issue in a while. I believe that's what I did the other day when it stopped happening. I don't have time right now, but I'll try to bisect the exact version

@Avasam
Copy link
Author

Avasam commented Feb 17, 2022 •
edited

0.8.1 is the last version that does not exhibit this behavior. It starts happening at 0.8.2dev0. I would like to reopen this issue as a bug now that I'm able to replicate it confidently.
(Probably one of those changes in requests_cache/cache_keys.py 5a907bc..e9f425d)

@JWCook JWCook reopened this Feb 18, 2022
@JWCook JWCook added bug and removed question labels Feb 21, 2022
@JWCook
Copy link
Member

JWCook commented Feb 21, 2022 •
edited

Thanks for narrowing it down. That's curious, though, I wouldn't expect any of those changes in the cache_keys module to have any impact on concurrent behavior. The only thing I can think of is that the improvements to request normalization and matching can lead to more cache hits, increasing the probability of hitting an existing race condition. In that case, the problem could have existed before 0.8.2 but with a lower probability of happening.

There are existing tests for multithreaded requests. That runs with a stress test multiplier that's set in GitHub Actions, which currently runs up to 20 iterations with 10 threads each, for each backend, and for each supported python version (so 80 iterations total just for SQLite). I would think that would be more than enough to expose any race conditions in typical multithreaded usage. Maybe I need to do more testing with multiprocess usage?

I'm still not able to reproduce this myself, and your application is currently the only way to reproduce this. Would you be able to come up with a minimal example that I could run on my own machine to reproduce this?

Also, what service are you using for hosting? Have you been able to reproduce this locally as well, or only on the remote host? Just wondering if there's something specific to the host's environment that could be contributing to this.

@Avasam
Copy link
Author

Avasam commented Feb 21, 2022 •
edited

It was easy to narrow it down to a specific version, but I'm not sure if I can do so to a specific commit. Anything else is just a wild guess looking at the changes.

Just wondering if there's something specific to the host's environment that could be contributing to this.

To be honest, I wouldn't be surprised. The host is PythonAnywhere so it's a WSGI server, but they do some things in the background I might not be aware of. Each connection is handled by a single process called a webworker. I multithread using a concurrent.futures.ThreadPoolExecutor with a pool size of 121 which is the max the host will allow me per webworker. Although realistically no more than 100 of them should access the cache at the same time since I now also use https://pypi.org/project/ratelimiter/ to respect an API limit of 100 requests/minute.

I'll try to replicate locally. I do have a dev server if you need me to test anything on the host.

@JWCook JWCook mentioned this issue Feb 21, 2022
Merged
@JWCook
Copy link
Member

JWCook commented Feb 21, 2022

Okay, so it's multiple processes, and up to 100 threads per process? Unrelated to this issue: with that level of concurrency, if your site picks up more traffic in the future, I'd definitely recommend looking into either AWS or a different cloud provider that supports one of the more scalable backends (ideally Redis). That would take a bit of work to migrate, though, so I understand if you want to stick with your current setup for now.

Also, since you brought up rate-limiting, you may want to check out requests-ratelimiter, which integrates nicely with requests-cache (see the last example in the readme).

Meanwhile, I'll work on improving the tests.

@Avasam
Copy link
Author

Avasam commented Feb 21, 2022 •
edited

I'm definitely starting to have issues with scaling due to restrictions in place. Cheap (and even free for single process) is nice but it does have its drawbacks. Like all things it started as a small project just for fun and grew !

Do note that my issue was tested on my single webworker/process dev server.

I tried using asyncio to go singlethread (coroutines). Worked great locally, but Async Flask is not supported, neither are websockets and FastApi (they've been "working on it" since 2017). Might be time to look for something a bit more modern and robust.

In the mean time, when requests-cache works with requests-ratelimiter, does it take into account that a response may be taken from cache and never hit the API when applying the rate limit?

@JWCook
Copy link
Member

JWCook commented Feb 22, 2022

In the mean time, when requests-cache works with requests-ratelimiter, does it take into account that a response may be taken from cache and never hit the API when applying the rate limit?

Exactly, it will only count requests sent over the network, not cache hits.

@JWCook
Copy link
Member

JWCook commented Feb 23, 2022

Just released another patch today. It includes a few thread safety improvements that may or may not be related to your issue. Give v0.9.3 a try and let me know if you still get OperationalErrors.

@JWCook
Copy link
Member

JWCook commented Mar 11, 2022 •
edited

@Avasam Were you able to test whether that patch fixed your issue?

@Avasam
Copy link
Author

Avasam commented Mar 11, 2022

@Avasam Were you able to test whether that patch fixed your issue?

Sorry I forgot to answer. I did. And I did not see any noticeable difference in my case. Only reproducible on the host so idk what they're doing special.

Perusing their forums, I should be able to use RedisLite. If not, I'll look into communicating with an external Redis server. It should hopefully solve both my cache size and reliability issue.

@mjpieters
Copy link

Related question, I see there's a new filesystem backend. How does it compare with SQLite in terms of performance (looking up hundreds if not thousands of cached requests) and size? The documentation doesn't really tell why I'd want that over SQLite.

The file cache puts all files in a single directory, which can lead to performance issues when storing a large number of items on filesystems that don't use Btree-based lookups. This issue could be mitigated by using a nested directory structure based on the MD5 hash of the key (or the key directly if it is a hex string, split by hex digits). E.g. instead of writing to cachedir/acff8b7368be2167.pkl, write to cachedir/ac/ff/8b/73/68/be/21/67/acff8b7368be2167.pkl.

The filesystem cache also uses a SQLite database to store redirect information. If you have issues with the SQLite backend, then the filesystem backend could easily have the same issues.

@JWCook
Copy link
Member

JWCook commented Mar 17, 2022

Sorry, I thought I had responded to that question earlier, but looks like I didn't actually send it.

You're correct, the filesystem backend isn't going to be the best fit for this use case. The main purpose of that is for some extra convenience in cases where you want to easily view cached responses (with no additional tools aside from a text editor), or otherwise use them outside of requests-cache. It comes at the cost of slightly reduced performance, mainly depending on the serializer used (in the range of roughly 1-3 milliseconds per write). And, as you pointed out, a large number of cached requests will also result in reduced performance on most filesystems.

@JWCook
Copy link
Member

JWCook commented Mar 17, 2022 •
edited

This issue could be mitigated by using a nested directory structure based on the MD5 hash of the key

A fan-out file cache like you're describing is definitely something I'd be willing to add (or accept a PR for), if there's a need for it. That should be an additional backend, a subclass of the existing FileCache class rather than a replacement for it, since the current single-directory storage is a bit more convenient for the case where you want to manually view your response data.

The use case for that would basically be when you want to both:
A) Use cached response data outside of requests-cache
B) Efficiently store a large number of responses

If B is all you need, SQLite is still going to be faster.

Edit: Made a separate issue for this, for reference: #604

@JWCook JWCook changed the title [Help needed] Many concurrent identical requests leading to sqlite3.OperationalError: database is locked Getting OperationalError: database is locked with SQLite on PythonAnywhere Mar 17, 2022
@JWCook JWCook changed the title Getting OperationalError: database is locked with SQLite on PythonAnywhere OperationalError: database is locked with SQLite on PythonAnywhere Mar 17, 2022
@dholth
Copy link
Contributor

dholth commented Apr 11, 2022

Check whether executing PRAGMA journal_mode=wal against your sqlite database helps

@Avasam
Copy link
Author

Avasam commented Apr 11, 2022 •
edited

PRAGMA journal_mode=wal

(Relevant documentation: https://sqlite.org/wal.html )
Sounds like something worth trying!
https://github.com/reclosedev/requests-cache/blob/2bd683658e17602a826a26b864a399b68181ee33/requests_cache/backends/sqlite.py#L231
It seems all I have to do to use that mode is to add the wal=True kwarg when creating my CachedSession

@dholth
Copy link
Contributor

dholth commented Apr 11, 2022 •
edited

That feature was added very recently, see also #564 (comment) for how to do it in older versions of requests-cache. wal mode is saved, so you could also issue it once with sqlite3 against the cache database and see if it helps.

@Avasam
Copy link
Author

Avasam commented Apr 11, 2022 •
edited

PRAGMA journal_mode=WAL makes the uncached requests slow down to a crawl:
PRAGMA journal_mode=WAL;.txt
For comparison, when I turned back to PRAGMA journal_mode=DELETE:
PRAGMA journal_mode=DELETE.txt

Those are logs from my dev server. You can see the first one timing out (after 5 minutes the worker is killed) and every request takes much longer. I made sure to delete all my cache files between tries. I've repeated the process twice to make sure it was not a fluke.
requests-cache v0.9.3

__adapter = HTTPAdapter(pool_maxsize=RATE_LIMIT - 1)
def __make_cache_session(user_id: str = "http_cache"):
    session = CachedSession(
        cache_name=user_id,
        expire_after=timedelta(days=1),
        allowable_codes=(200, 500),
        ignored_parameters=["status", "video-only", "embed"],
        backend=configs.cached_session_backend,
        fast_save=True,
        use_temp=True)
    session.mount("https://", __adapter)
    with session.cache.responses.connection() as conn:
        conn.execute("PRAGMA journal_mode=WAL;")
    return session

in a way you could say I no longer get OperationalError: database is locked with WAL 😋, but it's also unusable 😅

@Avasam
Copy link
Author

Avasam commented Apr 11, 2022

To stay with the theme of this report:
Going back to 0.8.1 fixes the performance issue with WAL.
And locally I don't experience the slowdowns with either 0.8.1 or 0.9.3 (if I do it's negligible)

@JWCook
Copy link
Member

JWCook commented Apr 11, 2022

Are you, by chance, creating a new CachedSession object for each request? Do those web workers run as a pool of processes that are reused, or is a new one created for each request? The changes to the SQLite backend in 0.9.3 were specific to initialization and wouldn't affect usage after initialization.

Otherwise, this is almost certainly a problem with whatever filesystem PythonAnywhere is using.

@JWCook
Copy link
Member

JWCook commented Apr 11, 2022

Aha! This blog post indicates it's using a NFS under the hood.

From the SQLite FAQ:

...use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.

@Avasam
Copy link
Author

Avasam commented Apr 11, 2022 •
edited

Are you, by chance, creating a new CachedSession object for each request?

Unless I messed something up, I shouldn't (hopefully not, I still fall in some Python noob trap). I have a few different caches that I create dynamically to scope them and more easily delete all at once. And I do memoize them to ensure I only create it once. Anyway I've started doing this some time after this whole issue started.

__cached_sessions: dict[str, CachedSession] = {}

def use_session(user_id: Union[str, Literal[False]] = "http_cache"):
    """
    @param user_id: User specific cache. Omit or "http_cache" for global. False for uncached.
    """
    if not user_id:
        return cast(CachedSession, __uncached_session)
    existing_session = __cached_sessions.get(user_id, None)
    if existing_session is None:
        existing_session = __make_cache_session(user_id)
        __cached_sessions[user_id] = existing_session
    return existing_session

Aha! This blog post indicates it's using a NFS under the hood.

😮 That might be it. Sounds pretty damming. I'll start trying out redislite or an external redis server. If you don't see any workaround or possible configuration change, and I manage to make it work with another backend, then I think we'd just close this issue.

image

@Avasam
Copy link
Author

Avasam commented Apr 12, 2022 •
edited

redislite was so easy to implement I feel dumb not trying it sooner... Props to you for making an interface that's so easy to swap backends.
And this way I can use a Redis backend on PythonAnywhere, and an SQLite one on my local machine
Avasam/speedrun.com_global_scoreboard_webapp@f8967a4

It seems to be working great, and quickly, at first glance, I'll go ahead and stress-test this.

@JWCook JWCook mentioned this issue Apr 12, 2022
Merged
@JWCook
Copy link
Member

JWCook commented Apr 12, 2022

Great!

redislite was so easy to implement I feel dumb not trying it sooner...

Well, we learned a bit more from doing things the hard way, eh? I added some notes to the SQLite backend docs based on this. Also added an example of using redislite, for others' reference.

@Avasam Avasam closed this as completed Apr 12, 2022
@amotl amotl mentioned this issue Dec 3, 2023
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug question
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@mjpieters @dholth @JWCook @Avasam