[Checkins] SVN: relstorage/trunk/ Added a mysql ZConfig section.
Shane Hathaway
shane at hathawaymix.org
Thu Feb 7 04:33:50 EST 2008
Log message for revision 83606:
Added a mysql ZConfig section.
Sped up packing on all 3 databases using another temporary table.
Updated the benchmark graphs; all 3 adapters are now included.
The default name of the storage now includes the name of the adapter.
Changed the speed tests to start the ZEO server only once.
Changed:
U relstorage/trunk/README.txt
U relstorage/trunk/notes/oracle_notes.txt
U relstorage/trunk/relstorage/adapters/mysql.py
U relstorage/trunk/relstorage/adapters/oracle.py
U relstorage/trunk/relstorage/adapters/postgresql.py
U relstorage/trunk/relstorage/component.xml
U relstorage/trunk/relstorage/config.py
U relstorage/trunk/relstorage/relstorage.py
U relstorage/trunk/relstorage/tests/comparison.ods
U relstorage/trunk/relstorage/tests/speedtest.py
U relstorage/trunk/relstorage/tests/testpostgresql.py
-=-
Modified: relstorage/trunk/README.txt
===================================================================
--- relstorage/trunk/README.txt 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/README.txt 2008-02-07 09:33:49 UTC (rev 83606)
@@ -29,3 +29,15 @@
</relstorage>
</zodb_db>
+
+For MySQL, use this in etc/zope.conf:
+
+%import relstorage
+<zodb_db main>
+ <relstorage>
+ <mysql>
+ db zodb
+ </mysql>
+ </relstorage>
+ mount-point /
+</zodb_db>
Modified: relstorage/trunk/notes/oracle_notes.txt
===================================================================
--- relstorage/trunk/notes/oracle_notes.txt 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/notes/oracle_notes.txt 2008-02-07 09:33:49 UTC (rev 83606)
@@ -2,7 +2,14 @@
Docs:
http://www.oracle.com/pls/db102/homepage
+Excellent setup instructions:
+ http://www.davidpashley.com/articles/oracle-install.html
+Work around session limit (fixes ORA-12520):
+ ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE
+ ALTER SYSTEM SET SESSIONS=150 SCOPE=SPFILE
+ (then restart Oracle)
+
Manually rollback an in-dispute transaction:
select local_tran_id, state from DBA_2PC_PENDING;
rollback force '$local_tran_id';
Modified: relstorage/trunk/relstorage/adapters/mysql.py
===================================================================
--- relstorage/trunk/relstorage/adapters/mysql.py 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/adapters/mysql.py 2008-02-07 09:33:49 UTC (rev 83606)
@@ -783,6 +783,14 @@
"""
cursor.execute(stmt, args)
+ # Create a small workspace
+ stmt = """
+ CREATE TEMPORARY TABLE temp_pack_visit (
+ zoid BIGINT NOT NULL PRIMARY KEY
+ )
+ """
+ cursor.execute(stmt)
+
# Each of the packable objects to be kept might
# refer to other objects. If some of those references
# include objects currently set to be removed, keep
@@ -790,6 +798,18 @@
# repeatedly until all references have been satisfied.
while True:
+ # Make a list of all parent objects that still need
+ # to be visited.
+ cursor.execute("DELETE FROM temp_pack_visit")
+ stmt = """
+ INSERT INTO temp_pack_visit (zoid)
+ SELECT zoid
+ FROM pack_object
+ WHERE keep = true
+ AND keep_tid IS NULL
+ """
+ cursor.execute(stmt)
+
# Set keep_tid for all pack_object rows with keep = 'Y'.
# This must be done before _fill_pack_object_refs examines
# references.
@@ -803,23 +823,22 @@
ORDER BY tid DESC
LIMIT 1
)
- WHERE keep = true AND keep_tid IS NULL
+ WHERE keep = true
+ AND keep_tid IS NULL
"""
cursor.execute(stmt, args)
self._fill_pack_object_refs(cursor, get_references)
+ # Visit the children of all parent objects that were
+ # just visited.
stmt = """
UPDATE pack_object SET keep = true
WHERE keep = false
AND zoid IN (
- SELECT to_zoid FROM (
- SELECT DISTINCT to_zoid
- FROM object_ref
- JOIN pack_object parent ON (
- object_ref.zoid = parent.zoid)
- WHERE parent.keep = true
- ) AS all_references
+ SELECT DISTINCT to_zoid
+ FROM object_ref
+ JOIN temp_pack_visit USING (zoid)
)
"""
cursor.execute(stmt)
Modified: relstorage/trunk/relstorage/adapters/oracle.py
===================================================================
--- relstorage/trunk/relstorage/adapters/oracle.py 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/adapters/oracle.py 2008-02-07 09:33:49 UTC (rev 83606)
@@ -125,6 +125,12 @@
keep_tid NUMBER(20)
);
CREATE INDEX pack_object_keep_zoid ON pack_object (keep, zoid);
+
+ -- Temporary state during packing: a list of objects
+ -- whose references need to be examined.
+ CREATE GLOBAL TEMPORARY TABLE temp_pack_visit (
+ zoid NUMBER(20) NOT NULL PRIMARY KEY
+ );
"""
self._run_script(cursor, stmt)
@@ -805,6 +811,18 @@
# repeatedly until all references have been satisfied.
while True:
+ # Make a list of all parent objects that still need
+ # to be visited.
+ cursor.execute("DELETE FROM temp_pack_visit")
+ stmt = """
+ INSERT INTO temp_pack_visit (zoid)
+ SELECT zoid
+ FROM pack_object
+ WHERE keep = 'Y'
+ AND keep_tid IS NULL
+ """
+ cursor.execute(stmt)
+
# Set keep_tid for all pack_object rows with keep = 'Y'.
# This must be done before _fill_pack_object_refs examines
# references.
@@ -816,21 +834,22 @@
AND tid > 0
AND tid <= :pack_tid
)
- WHERE keep = 'Y' AND keep_tid IS NULL
+ WHERE keep = 'Y'
+ AND keep_tid IS NULL
"""
cursor.execute(stmt, args)
self._fill_pack_object_refs(cursor, get_references)
+ # Visit the children of all parent objects that were
+ # just visited.
stmt = """
UPDATE pack_object SET keep = 'Y'
WHERE keep = 'N'
AND zoid IN (
SELECT DISTINCT to_zoid
FROM object_ref
- JOIN pack_object parent ON (
- object_ref.zoid = parent.zoid)
- WHERE parent.keep = 'Y'
+ JOIN temp_pack_visit USING (zoid)
)
"""
cursor.execute(stmt)
Modified: relstorage/trunk/relstorage/adapters/postgresql.py
===================================================================
--- relstorage/trunk/relstorage/adapters/postgresql.py 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/adapters/postgresql.py 2008-02-07 09:33:49 UTC (rev 83606)
@@ -805,6 +805,15 @@
"""
cursor.execute(stmt, args)
+ # Create a small workspace
+ stmt = """
+ CREATE TEMPORARY TABLE temp_pack_visit (
+ zoid BIGINT NOT NULL
+ );
+ CREATE UNIQUE INDEX temp_pack_visit_zoid ON temp_pack_visit (zoid)
+ """
+ cursor.execute(stmt)
+
# Each of the packable objects to be kept might
# refer to other objects. If some of those references
# include objects currently set to be removed, keep
@@ -812,6 +821,18 @@
# repeatedly until all references have been satisfied.
while True:
+ # Make a list of all parent objects that still need
+ # to be visited.
+ stmt = """
+ TRUNCATE temp_pack_visit;
+ INSERT INTO temp_pack_visit (zoid)
+ SELECT zoid
+ FROM pack_object
+ WHERE keep = true
+ AND keep_tid IS NULL
+ """
+ cursor.execute(stmt)
+
# Set keep_tid for all pack_object rows with keep = 'Y'.
# This must be done before _fill_pack_object_refs examines
# references.
@@ -831,15 +852,15 @@
self._fill_pack_object_refs(cursor, get_references)
+ # Visit the children of all parent objects that were
+ # just visited.
stmt = """
UPDATE pack_object SET keep = true
WHERE keep = false
AND zoid IN (
SELECT DISTINCT to_zoid
FROM object_ref
- JOIN pack_object parent ON (
- object_ref.zoid = parent.zoid)
- WHERE parent.keep = true
+ JOIN temp_pack_visit USING (zoid)
)
"""
cursor.execute(stmt)
Modified: relstorage/trunk/relstorage/component.xml
===================================================================
--- relstorage/trunk/relstorage/component.xml 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/component.xml 2008-02-07 09:33:49 UTC (rev 83606)
@@ -10,7 +10,7 @@
<sectiontype name="relstorage" implements="ZODB.storage"
datatype=".RelStorageFactory">
<section type="relstorage.adapter" name="*" attribute="adapter"/>
- <key name="name" default="RelStorage"/>
+ <key name="name" datatype="string" required="no"/>
<key name="create" datatype="boolean" default="true">
<description>
Flag that indicates whether the storage should be initialized if
@@ -61,4 +61,75 @@
</key>
</sectiontype>
+ <sectiontype name="mysql" implements="relstorage.adapter"
+ datatype=".MySQLAdapterFactory">
+
+ <key name="host" datatype="string" required="no">
+ <description>
+ host to connect
+ </description>
+ </key>
+
+ <key name="user" datatype="string" required="no">
+ <description>
+ user to connect as
+ </description>
+ </key>
+
+ <key name="passwd" datatype="string" required="no">
+ <description>
+ password to use
+ </description>
+ </key>
+
+ <key name="db" datatype="string" required="no">
+ <description>
+ database to use
+ </description>
+ </key>
+
+ <key name="port" datatype="integer" required="no">
+ <description>
+ TCP/IP port to connect to
+ </description>
+ </key>
+
+ <key name="unix_socket" datatype="string" required="no">
+ <description>
+ location of unix_socket (UNIX-ish only)
+ </description>
+ </key>
+
+ <key name="connect_timeout" datatype="integer" required="no">
+ <description>
+ number of seconds to wait before the connection attempt fails.
+ </description>
+ </key>
+
+ <key name="compress" datatype="boolean" required="no">
+ <description>
+ if set, gzip compression is enabled
+ </description>
+ </key>
+
+ <key name="named_pipe" datatype="boolean" required="no">
+ <description>
+ if set, connect to server via named pipe (Windows only)
+ </description>
+ </key>
+
+ <key name="read_default_file" datatype="string" required="no">
+ <description>
+ see the MySQL documentation for mysql_options()
+ </description>
+ </key>
+
+ <key name="read_default_group" datatype="string" required="no">
+ <description>
+ see the MySQL documentation for mysql_options()
+ </description>
+ </key>
+
+ </sectiontype>
+
</component>
Modified: relstorage/trunk/relstorage/config.py
===================================================================
--- relstorage/trunk/relstorage/config.py 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/config.py 2008-02-07 09:33:49 UTC (rev 83606)
@@ -39,3 +39,14 @@
config = self.config
return OracleAdapter(config.user, config.password, config.dsn)
+
+class MySQLAdapterFactory(BaseConfig):
+ def open(self):
+ from adapters.mysql import MySQLAdapter
+ options = {}
+ for key in self.config.getSectionAttributes():
+ value = getattr(self.config, key)
+ if value is not None:
+ options[key] = value
+ return MySQLAdapter(**options)
+
Modified: relstorage/trunk/relstorage/relstorage.py
===================================================================
--- relstorage/trunk/relstorage/relstorage.py 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/relstorage.py 2008-02-07 09:33:49 UTC (rev 83606)
@@ -42,8 +42,11 @@
ConflictResolution.ConflictResolvingStorage):
"""Storage to a relational database, based on invalidation polling"""
- def __init__(self, adapter, name='RelStorage', create=True,
+ def __init__(self, adapter, name=None, create=True,
read_only=False):
+ if name is None:
+ name = 'RelStorage on %s' % adapter.__class__.__name__
+
self._adapter = adapter
self._name = name
self._is_read_only = read_only
Modified: relstorage/trunk/relstorage/tests/comparison.ods
===================================================================
(Binary files differ)
Modified: relstorage/trunk/relstorage/tests/speedtest.py
===================================================================
--- relstorage/trunk/relstorage/tests/speedtest.py 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/tests/speedtest.py 2008-02-07 09:33:49 UTC (rev 83606)
@@ -33,14 +33,10 @@
from ZODB.Connection import Connection
from relstorage.relstorage import RelStorage
-from relstorage.adapters.mysql import MySQLAdapter
-from relstorage.adapters.postgresql import PostgreSQLAdapter
-from relstorage.adapters.oracle import OracleAdapter
-from relstorage.tests.testoracle import getOracleParams
debug = False
txn_count = 10
-object_counts = [100] # [1, 100, 10000]
+object_counts = [10000] # [1, 100, 10000]
concurrency_levels = range(1, 16, 2)
contenders = [
('ZEO + FileStorage', 'zeofs_test'),
@@ -80,11 +76,46 @@
return pid
+class ZEOServerRunner(object):
+
+ def __init__(self):
+ self.dir = tempfile.mkdtemp()
+ self.store_fn = os.path.join(self.dir, 'storage')
+ self.sock_fn = os.path.join(self.dir, 'sock')
+ self.pid = None
+
+ def run(self):
+ from ZODB.FileStorage import FileStorage
+ from ZEO.StorageServer import StorageServer
+
+ fs = FileStorage(self.store_fn, create=True)
+ ss = StorageServer(self.sock_fn, {'1': fs})
+
+ import ThreadedAsync.LoopCallback
+ ThreadedAsync.LoopCallback.loop()
+
+ def start(self):
+ self.pid = run_in_child(False, self.run)
+ # parent
+ sys.stderr.write('Waiting for ZEO server to start...')
+ while not os.path.exists(self.sock_fn):
+ sys.stderr.write('.')
+ sys.stderr.flush()
+ time.sleep(0.1)
+ sys.stderr.write(' started.\n')
+ sys.stderr.flush()
+
+ def stop(self):
+ os.kill(self.pid, signal.SIGTERM)
+ shutil.rmtree(self.dir)
+
+
class SpeedTest:
- def __init__(self, concurrency, objects_per_txn):
+ def __init__(self, concurrency, objects_per_txn, zeo_runner):
self.concurrency = concurrency
self.data_to_store = dict((n, 1) for n in range(objects_per_txn))
+ self.zeo_runner = zeo_runner
def populate(self, make_storage):
# initialize the database
@@ -92,6 +123,13 @@
db = DB(storage)
conn = db.open()
root = conn.root()
+
+ # clear the database
+ root['speedtest'] = None
+ transaction.commit()
+ db.pack()
+
+ # put a tree in the database
root['speedtest'] = t = IOBTree()
for i in range(self.concurrency):
t[i] = IOBTree()
@@ -149,55 +187,23 @@
count = float(self.concurrency * txn_count)
return (sum(write_times) / count, sum(read_times) / count)
- def run_zeo_server(self, store_fn, sock_fn):
- from ZODB.FileStorage import FileStorage
- from ZEO.StorageServer import StorageServer
-
- fs = FileStorage(store_fn, create=True)
- ss = StorageServer(sock_fn, {'1': fs})
-
- import ThreadedAsync.LoopCallback
- ThreadedAsync.LoopCallback.loop()
-
- def start_zeo_server(self, store_fn, sock_fn):
- pid = run_in_child(False, self.run_zeo_server, store_fn, sock_fn)
- # parent
- if debug:
- sys.stderr.write('Waiting for ZEO server to start...')
- while not os.path.exists(sock_fn):
- if debug:
- sys.stderr.write('.')
- sys.stderr.flush()
- time.sleep(0.1)
- if debug:
- sys.stderr.write(' started.\n')
- sys.stderr.flush()
- return pid
-
def zeofs_test(self):
- dir = tempfile.mkdtemp()
- try:
- store_fn = os.path.join(dir, 'storage')
- sock_fn = os.path.join(dir, 'sock')
- zeo_pid = self.start_zeo_server(store_fn, sock_fn)
- try:
- def make_storage():
- from ZEO.ClientStorage import ClientStorage
- return ClientStorage(sock_fn)
- return self.run_tests(make_storage)
- finally:
- os.kill(zeo_pid, signal.SIGTERM)
- finally:
- shutil.rmtree(dir)
+ def make_storage():
+ from ZEO.ClientStorage import ClientStorage
+ return ClientStorage(self.zeo_runner.sock_fn)
+ return self.run_tests(make_storage)
def postgres_test(self):
- adapter = PostgreSQLAdapter()
+ from relstorage.adapters.postgresql import PostgreSQLAdapter
+ adapter = PostgreSQLAdapter('dbname=relstoragetest')
adapter.zap()
def make_storage():
return RelStorage(adapter)
return self.run_tests(make_storage)
def oracle_test(self):
+ from relstorage.adapters.oracle import OracleAdapter
+ from relstorage.tests.testoracle import getOracleParams
user, password, dsn = getOracleParams()
adapter = OracleAdapter(user, password, dsn)
adapter.zap()
@@ -206,6 +212,7 @@
return self.run_tests(make_storage)
def mysql_test(self):
+ from relstorage.adapters.mysql import MySQLAdapter
adapter = MySQLAdapter(db='relstoragetest')
adapter.zap()
def make_storage():
@@ -213,7 +220,6 @@
return self.run_tests(make_storage)
-
def distribute(func, param_iter):
"""Call a function in separate processes concurrently.
@@ -289,6 +295,8 @@
def main():
+ zeo_runner = ZEOServerRunner()
+ zeo_runner.start()
# results: {(objects_per_txn, concurrency, contender, direction): [time]}}
results = {}
@@ -303,7 +311,7 @@
try:
for objects_per_txn in object_counts:
for concurrency in concurrency_levels:
- test = SpeedTest(concurrency, objects_per_txn)
+ test = SpeedTest(concurrency, objects_per_txn, zeo_runner)
for contender_name, method_name in contenders:
print >> sys.stderr, (
'Testing %s with objects_per_txn=%d and concurrency=%d'
@@ -332,6 +340,8 @@
# The finally clause causes test results to print even if the tests
# stop early.
finally:
+ zeo_runner.stop()
+
# show the results in CSV format
print >> sys.stderr, (
'Average time per transaction in seconds. Best of 3.')
Modified: relstorage/trunk/relstorage/tests/testpostgresql.py
===================================================================
--- relstorage/trunk/relstorage/tests/testpostgresql.py 2008-02-07 03:40:11 UTC (rev 83605)
+++ relstorage/trunk/relstorage/tests/testpostgresql.py 2008-02-07 09:33:49 UTC (rev 83606)
@@ -22,7 +22,7 @@
class PostgreSQLTests(RelStorageTests):
def make_adapter(self):
- return PostgreSQLAdapter()
+ return PostgreSQLAdapter('dbname=relstoragetest')
def test_suite():
suite = unittest.TestSuite()
More information about the Checkins
mailing list