5 ms·

I mentioned something about this the last time a discussion involving Django and Postgres came up, but it bears repeating. If your environment is set up such t

by rosser 14y ago

I mentioned something about this the last time a discussion involving Django and Postgres came up, but it bears repeating. If your environment is set up such that database connections are long-lived, please double-check that you're not using SQLAlchemy's default behavior to open an explicit transaction (e.g., "BEGIN TRANSACTION") upon connection. (It may no longer be the default, but it was last I worked with a Django shop, ~two years ago, now.) That behavior interferes with VACUUM's ability to do its job, your tables can bloat linearly with how "write-hot" they are, and performance of queries using those tables will reflect that. Briefly, this is a consequence of Postgres' MVCC (multi-version concurrency control) architecture. Physically, an UPDATE statement is an atomic INSERT/DELETE operation; each version of every row is stored on disk, and Postgres keeps track of which versions of which rows are "visible" in the context of which transactions. Obviously, that's not a sustainable approach, both in terms of performance, and resource consumption. You'll fill up your storage with "dead" tuples, and you'll have to trawl through disk page after disk page of them to find a single "live" one. Enter VACUUM and later, the autovacuum daemon. They know which transactions are open, and which versions of rows were modified by which transactions. "Dead" tuples older than any open transaction are truly dead, and their disk footprint can be re-used by subsequent INSERT — or UPDATE — operations. SQLAlchemy's (former?) default behavior to open an explicit transaction upon connection isn't ideally suited for use with MVCC-based databases (so if you're using InnoDB, you'll probably have some flavor of this same problem) — particularly if your environment has connections open for days at a time, which was the case at that former gig. When I found this problem in their stack, tables with hundreds, or perhaps a few thousand rows had disk footprints in the gibibytes. They were among the hottest tables in the system and performance, consequently, sucked. The only thing that kept them alive was that the people they had working on this stuff before me had a weekly maintenance window (site outage) where they ran a VACUUM FULL and REINDEX. How no-one twigged to the fact that app performance followed a perfect sawtooth pattern with a period of exactly one week, lock-step in phase with the maintenance cycle, I'll never understand... You can check whether your environment is configured this way with: SELECT * FROM pg_stat_activity WHERE current_query = '<IDLE> in transaction' ORDER BY xact_start; If any of those guys are older than ... not very old at all (seconds, perhaps minutes at the outside, but that's a function of how your stack works, and only you can really say what's abnormal) then, Houston, you have a problem. A caveat: this test is fairly specific, but somewhat less sensitive, with false negatives being possible. What you're really looking for is Postgres backends with an xact_start — current transaction start time — that is (potentially significantly) out of line with how long your normal database operations should take. Those usually show up as "<IDLE> in transaction" but you could very well happen to run the above query exactly when one of these offending connections is busy servicing a query, itself... EDIT: clarifications

zzzeek 14y ago
wow, where to begin with the factual errors in this post - most of this post is incorrect. Just to get it out of the way, SQLAlchemy does not emit the "BEGIN" statement, nor does it call any kind of database function that directly emits "BEGIN", ever. Feel free to grep for it, start at version 0.1.0 and go all the way up to the 0.8 tip - you won't see it. It's not a default, it's nothing SQLAlchemy has any kind of option for - it doesn't do it. SQLAlchemy, like virtually all Python libraries and frameworks that talk to relational databases, uses an API known as the Python database API, or DBAPI. With Postgresql, you're usually using a DBAPI implementation known as psycopg2. The DBAPI is organized in such a way that transactions are implicit. This means, when you first get a DBAPI connection, it's per specification required to be in a transaction, or at least it has to be as soon as you do something with that connection. The DBAPI has a `commit()` method as well as a `rollback()`, but has no begin() method. Why is that? Again, because a DBAPI connection is always in a transaction - there is nothing to BEGIN, it is already begun the moment you've connected or the moment the previous transaction has been closed via rollback or commit (or at least, the next time you emit a statement on that connection, it needs to be in a new transaction). So when you use psycopg2 by itself, as soon as you connect and emit a statement, the abovementioned "BEGIN TRANSACTION" has been emitted (see the big pink box here: http://initd.org/psycopg/docs/usage.html#transactions-control http://initd.org/psycopg/docs/usage.html#transactions-contro...). If you want to disable that behavior, you can do so by setting psycopg2's [autocommit](http://initd.org/psycopg/docs/connection.html#connection.autocommit http://initd.org/psycopg/docs/connection.html#connection.aut...) flag. It's also easy enough to set this flag when you're using psycopg2 via SQLAlchemy, and in fact things will work just fine - unless you actually need some degree of transaction isolation and/or need ROLLBACK to actually work. So setting psycopg2's autocommit flag is really not something you'd normally want to do unless, as the docs say, you're emitting explicit VACUUM statements on your psycopg2 connection, which of course we don't have to these days now that vacuuming is automatic (and manually calling VACUUM is a database administration task in any case which you'd be calling from your psql console). So we've established that 1. SQLAlchemy has nothing to do with "BEGIN TRANSACTION" and 2. psycopg2 and all DBAPIs are required to maintain transactional scope by default when a connection is first procured. What about the supposed issues with VACUUM ? To put it simply, VACUUM has no problems with connections being open. What you're thinking of here are locks, and locks only occur once you're in a transaction and have accessed some table rows, which are now subject to various isolation rules. If you open a bunch of connections, and access/update a bunch of table rows, you'll have a lot of locks on hand, and that will get in the way of autovacuuming and such. However, as soon as you roll back the transactions, those locks are gone. When you use a database library like SQLAlchemy, a handful of connections are kept open in a pool, but the transactions are not. When you check out a connection, do a few things with it, then return it to the pool, any remaining transactional state is rolled back. edit: this of course assumes your application was written correctly enough that it closes out session/connection resources at the end of an operation - no different than when using the DBAPI directly. What I would gather was your experience was that you either were using an extremely old version of SQLAlchemy, like an early 0.3 version when we probably weren't calling rollback() correctly (this would be like pre-2008), or your application wasn't handling the scope of connections/sessions appropriately (edit: as django's ORM typically autocommits by default, this detail about SQLAlchemy's more explicit transactional nature may not have been apparent to your dev team; we offer autocommit as an option, though it is discouraged). It goes without saying that psycopg2 is used in a tremendous number of high volume environments, without forcing the "autocommit" flag on. Postgresql's auto vacuuming works just fine regardless. SQLAlchemy is just a client of psycopg2.

by rosser 14y ago

zzzeek 14y ago

wow, where to begin with the factual errors in this post - most of this post is incorrect. Just to get it out of the way, SQLAlchemy does not emit the "BEGIN" statement, nor does it call any kind of database function that directly emits "BEGIN", ever. Feel free to grep for it, start at version 0.1.0 and go all the way up to the 0.8 tip - you won't see it. It's not a default, it's nothing SQLAlchemy has any kind of option for - it doesn't do it. SQLAlchemy, like virtually all Python libraries and frameworks that talk to relational databases, uses an API known as the Python database API, or DBAPI. With Postgresql, you're usually using a DBAPI implementation known as psycopg2. The DBAPI is organized in such a way that transactions are implicit. This means, when you first get a DBAPI connection, it's per specification required to be in a transaction, or at least it has to be as soon as you do something with that connection. The DBAPI has a `commit()` method as well as a `rollback()`, but has no begin() method. Why is that? Again, because a DBAPI connection is always in a transaction - there is nothing to BEGIN, it is already begun the moment you've connected or the moment the previous transaction has been closed via rollback or commit (or at least, the next time you emit a statement on that connection, it needs to be in a new transaction). So when you use psycopg2 by itself, as soon as you connect and emit a statement, the abovementioned "BEGIN TRANSACTION" has been emitted (see the big pink box here: http://initd.org/psycopg/docs/usage.html#transactions-control http://initd.org/psycopg/docs/usage.html#transactions-contro...). If you want to disable that behavior, you can do so by setting psycopg2's [autocommit](http://initd.org/psycopg/docs/connection.html#connection.autocommit http://initd.org/psycopg/docs/connection.html#connection.aut...) flag. It's also easy enough to set this flag when you're using psycopg2 via SQLAlchemy, and in fact things will work just fine - unless you actually need some degree of transaction isolation and/or need ROLLBACK to actually work. So setting psycopg2's autocommit flag is really not something you'd normally want to do unless, as the docs say, you're emitting explicit VACUUM statements on your psycopg2 connection, which of course we don't have to these days now that vacuuming is automatic (and manually calling VACUUM is a database administration task in any case which you'd be calling from your psql console). So we've established that 1. SQLAlchemy has nothing to do with "BEGIN TRANSACTION" and 2. psycopg2 and all DBAPIs are required to maintain transactional scope by default when a connection is first procured. What about the supposed issues with VACUUM ? To put it simply, VACUUM has no problems with connections being open. What you're thinking of here are locks, and locks only occur once you're in a transaction and have accessed some table rows, which are now subject to various isolation rules. If you open a bunch of connections, and access/update a bunch of table rows, you'll have a lot of locks on hand, and that will get in the way of autovacuuming and such. However, as soon as you roll back the transactions, those locks are gone. When you use a database library like SQLAlchemy, a handful of connections are kept open in a pool, but the transactions are not. When you check out a connection, do a few things with it, then return it to the pool, any remaining transactional state is rolled back. edit: this of course assumes your application was written correctly enough that it closes out session/connection resources at the end of an operation - no different than when using the DBAPI directly. What I would gather was your experience was that you either were using an extremely old version of SQLAlchemy, like an early 0.3 version when we probably weren't calling rollback() correctly (this would be like pre-2008), or your application wasn't handling the scope of connections/sessions appropriately (edit: as django's ORM typically autocommits by default, this detail about SQLAlchemy's more explicit transactional nature may not have been apparent to your dev team; we offer autocommit as an option, though it is discouraged). It goes without saying that psycopg2 is used in a tremendous number of high volume environments, without forcing the "autocommit" flag on. Postgresql's auto vacuuming works just fine regardless. SQLAlchemy is just a client of psycopg2.