[ZODB-Dev] RelStorage - what tables and indexes should be present?
Hi Shane, I notice that one of my history-free storages only has 'new_oid' and 'object_state' tables while all the others have 'new_oid', 'object_ref', 'object_refs_added', 'object_state' and 'pack_object' tables. What's special about this storage? I also note that while the ones with lots of tables have this schema for object_state: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ ...the one with only two tables has this schema: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ ...which causes the following statement, which gets executed a lot, to be very slow: SELECT tid FROM object_state ORDER BY tid DESC LIMIT 1 cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 01/02/2011 14:42, Stephan Richter wrote: On Tuesday, February 01, 2011, Chris Withers wrote: I also note that while the ones with lots of tables have this schema for object_state: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ ...the one with only two tables has this schema: CREATE TABLE `object_state` ( `zoid` bigint(20) NOT NULL, `tid` bigint(20) NOT NULL, `state` longblob, PRIMARY KEY (`zoid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ Did you paste correctly? They seem identical to me. Nope, I phail... but the above is the incorrect version. Try creating a RelStorage and you'll see. The above ends up being really slow as it has no index on tid... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 02/01/2011 10:16 AM, Chris Withers wrote: I notice that one of my history-free storages only has 'new_oid' and 'object_state' tables while all the others have 'new_oid', 'object_ref', 'object_refs_added', 'object_state' and 'pack_object' tables. What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 01/02/2011 17:33, Shane Hathaway wrote: What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Sounds like something to warn about in the docs for zodbconvert and storage creation... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On 02/01/2011 07:35 PM, Chris Withers wrote: On 01/02/2011 17:33, Shane Hathaway wrote: What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Sounds like something to warn about in the docs for zodbconvert and storage creation... Partial schema creation is specific to MySQL and is not specific to RelStorage. MySQL has many pitfalls and there are far too many of them to list in the RelStorage documentation. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - what tables and indexes should be present?
On Tue, Feb 1, 2011 at 3:52 PM, Shane Hathaway sh...@hathawaymix.org wrote: On 02/01/2011 07:35 PM, Chris Withers wrote: On 01/02/2011 17:33, Shane Hathaway wrote: What's special about this storage? It sounds like RelStorage didn't get a chance to finish creating the schema. In MySQL, DDL statements are not transactional, so errors during schema creation (such as a timeout) leave a partial schema. Sounds like something to warn about in the docs for zodbconvert and storage creation... Partial schema creation is specific to MySQL and is not specific to RelStorage. MySQL has many pitfalls and there are far too many of them to list in the RelStorage documentation. Just tell people to be aware that mysql has many problems, list some of them and don't use it in all the examples. -- Leonardo Santagada ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
