Fixes#181
Mostly a few changes in pragmas. There are also a few actual code
changes:
- remove reduntant `else return` after `if return`
- Change some `x = foo and bar or baz` into equivalent `bar if foo
else baz` forms.
Fixes#169.
We need this version for IMultiCommitStorage and the registerDB fix in
ConflictResolvingStorage. Since these versions run on Python 2.7.8,
there's no motivation for trying to go back farther.
RelStorage storages to be notified of transaction endings for
transactions that don't call the two-phase commit API. This allows
resources to be used more efficiently because it prevents RDBMS
transactions from being held open.
Fixes: https://github.com/zodb/relstorage/issues/147
(At least for ZODB 5.2.)
And truncate them.
For most things this seems to speed up writes by around 10% or more due
to the way the temp tables are allocated.
Tested on both 5.5 and 5.7.
I think the dropping was an attempt to make the tables statement-level
replication compatible, but since they weren't transactianal I don't
think that worked. They were also variable sized so I don't think there
were any speed benefits. It also didn't implicitly end the transaction,
but that's not a concern where this is called.
Here's a complete set of benchmarks against 5.7, comparing zodbshootout
with -c2 and -c6, -n 1000 and '-n 100 -s 256 --test-reps 200'. This
compares 2.0.0 with current master and this change, in that order.
** c=2,s=256 ** 2.0.0 master InnoDB
"Transaction", mysql_hf
"Add 100 Objects", 8883 9199 10157
"Update 100 Objects", 9380 9169 10318
** c=6,s=256 ** 2.0.0 master InnoDB
"Transaction", mysql_hf
"Add 100 Objects", ---- 14913 15524
"Update 100 Objects", ---- 14914 15077
** c=6,s=128 ** 2.0.0 master InnoDB
"Transaction", mysql_hf
"Add 1000 Objects", 29815 31704 38506
"Update 1000 Objects", 28978 29030 29137
In all cases, InnoDB temp tables outperform, in some cases
substantially.
An earlier set of benchmarks taken against 5.5 (but prior to a rebase on
master, so not easily replicated). The absolute value difference in
numbers versus above is that these were taken with Py 3.4 while above
was py 2.7.
zodbshootout -c 2 shootout.conf -n 100 --test-reps 200 -r 1 -s 256
Before
** concurrency=2 **
"Transaction", mysql_hf
"Add 100 Objects", 6493
"Update 100 Objects", 9636
"Read 100 Warm Objects", 9797
"Read 100 Cold Objects", 9336
"Read 100 Hot Objects", 37407
"Read 100 Steamin' Objects", 921761
** concurrency=6 **
"Transaction", mysql_hf
"Add 100 Objects", 10574
"Update 100 Objects", 15492
"Read 100 Warm Objects", 19290
"Read 100 Cold Objects", 17051
"Read 100 Hot Objects", 72304
"Read 100 Steamin' Objects", 1590488
After
** concurrency=2 **
"Transaction", mysql_hf
"Add 100 Objects", 7079
"Update 100 Objects", 9782
"Read 100 Warm Objects", 8726
"Read 100 Cold Objects", 9834
"Read 100 Hot Objects", 40004
"Read 100 Steamin' Objects", 937232
c=6
** concurrency=6 **
"Transaction", mysql_hf
"Add 100 Objects", 10612
"Update 100 Objects", 15789
"Read 100 Warm Objects", 17501
"Read 100 Cold Objects", 16556
"Read 100 Hot Objects", 71868
"Read 100 Steamin' Objects", 1549534
zodbshootout -c 2 shootout.conf -n 100 --test-reps 200 -r 1 -s 96
** concurrency=2 **
"Transaction", mysql_hf
"Add 100 Objects", 15547
"Update 100 Objects", 14337
"Read 100 Warm Objects", 12357
"Read 100 Cold Objects", 13686
"Read 100 Hot Objects", 54559
"Read 100 Steamin' Objects", 945789
"Transaction", mysql_hf
"Add 100 Objects", 16968
"Update 100 Objects", 16184
"Read 100 Warm Objects", 13430
"Read 100 Cold Objects", 13557
"Read 100 Hot Objects", 54625
"Read 100 Steamin' Objects", 966802
They use threads and or time.time/sleep. Windows/Appveyor is flaky when
it comes to these things (see gevent) so the tests can sometimes
randomly fail.
This dates back to some of the earliest windows builds.
Comments about unicode for mysql connector should match reality, I hope.
Remove mysql_connection() and just pass the connection object to the one
place that needs it.
This is a good thing in general.
In MySQL Connectors case, it means we can get away from using the
`buffered=True` parameter when we get a cursor, which is a necessary
step to being able to use prepared cursors (because the two types can't
be combined).
It also *massively* improves performance. Using a buffered cursor,
running the testmysql tests took 173s with the python driver; using the
default unbuffered cursor they go to ~129s, which is comprable to
PyMySQL's ~125s. The C implemenattion goes to 115s (I didn't get a
before number for it) which is comprable to mysqlclient's 111s.
It makes me wonder what using unbuffered queries in those two
implementations might look like. mysqlclient appears to use one API call
to buffer the entire result set (mysql_store_result). PyMySQL seems to
iterate "packet" by packet, while connector/python iterates "row" by
row, which appear to be the same. This requires further testing.
This cuts out a DELETE statement for every object stored. On benchmarks
this is a 15-20% win compared to the last commit:
BEFORE
"Transaction", postgresql
"Add 1000 Objects", 18042
"Update 1000 Objects", 19380
AFTER
"Transaction", postgresql
"Add 1000 Objects", 20705
"Update 1000 Objects", 23568
MySQL and PostgreSQL use a prepared statement to detect conflicts. This
is only noticeable when few objects are written.
Modernize the RowBatcher class by using defaultdict instead of manual
checks. Avoid doing some unnecessary sorting and defer string conversion
of deletes.
- Let there be multiple callables.
- Add a hook for load connections.
- Make subclassing easier. Removes a lot of duplicate code, especially
from the oracle adapter.
- Use the hooks in postgresql to prepare get_latest_tid. This moves the
query back into the place where the call to it is defined.
* format tweaks [skip ci]
* MySQL: Drop the commit lock after fill_object_refs
In pre-pack gc in a history-free storage.
Oracle and PostgreSQL were already doing this, so this should be a safe
change to make.
Fixes#9.
* Tweak error message.
For MySQL and PostgreSQL only. MySQL uses the MAX query, and PostgreSQL
uses its prepared statement.
Also avoid checking self.keep_history at runtime, overriding the correct
methods dynamically.
cf #89
* First basic version of appveyor.yml for testing. Still need to configure databases. Lets just see if we build.
* stdint.h is not available on MSVC before 2010, but we're not using it anyway. myssqlclient won't compile on windows
* Try a define for broken c99 support in MSVC.
* Broader macro.
* Use old form of 0 initializing struct.
* More fixes for broken c99 support of MSVC.
* Attempt to setup the database.
* need /c
* give up on running the DB tests.
* produce wheels
* produce wheels
* one more try for mysql tests.
* Run the db script as part of the test, it seems services aren't started during install phase.
* MySQL tests are running, try adding postgres.
* badges
* right bin directory for postgres
* Skip one blob test on windows/py3