[MTOS-dev] Performance

[Alvar Freude]

Hi,

-- Brad Choate <brad at sixapart.com> wrote:

>> ...
>> id INTEGER DEFAULT nextval('sequence_name') NOT NULL,
>> ...
>
> What versions of PostgreSQL support that syntax? All 7.x and 8.x?

all 7.x and 8.x versions support this.

The SERIAL data type exists since an old 7.x version (I used it in 7.1, I 
guess it is there at least since 7.0), but before 7.3 the created 
sequence will not dropped when dropping the table. But 7.2 is quite old 
...

<http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL>
<http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-SERIAL>


> Also,
> I think 'unique' or 'primary key' should also be specified to ensure
> the column is unique.

yes, sure! I only forgot to mention them.


>> Such statements seem to appear for nearly everything, e.g.
>> mt_category, mt_author, mt_permission, mt_session, ...
>
> The separate queries are a mechanism of Data::ObjectDriver. It gathers
> IDs of objects first and then checks internally for any cached objects
> for those IDs. Then, for objects that are not cached, it will fetch
> those from the database with a subsequent SELECT. So if it gathers a
> list of IDs like 4, 8, 15, 16, 23, 42 and finds that IDs 23 and 42 are
> cached, it will only SELECT from the database rows with the IDs 4, 8,
> 15 and 16. The order of the returned objects from the second query is
> irrelevant; the object driver uses the order from the first to return
> the objects in the right order.
>
> Changing all that into a query with a subselect would defeat the
> caching mechanisms in D::OD. In other words, D::OD doesn't always
> optimize for the database.

OK, I understand.
But: fetching the necessary field with the first query is nearly as fast 
as fetching only the ID.


>> When adding a category, the new category is inserted first, and
>> after this there is an update for the category basename. (similar
>> when inserting a new entry)
>
> MT first saves the category (and entry, and soon, authors), then
> attempts to calculate a unique basename. For locales where the dirified
> category label returns an empty string (ie, a Japanese label), the ID
> of the category is used to set the basename. So the object has to be
> saved first to have the id, then it is updated with the basename value.

hmmm ...
But the unique basename may calculated earlyer, and when there is no 
suitable ascii string, the value may be:

  currval('sequence_name')::text

(for PostgreSQL, in MySQL the last_insert_id stuff may work)


>> As the queries are all single transactions (AutoCommit), this is not
>> only slow, but also dangerous: when the process crashes or gets
>> killed after the insert and before the update, then the database
>> consistency is broken.
>>
>> In total there are 24 queries for adding a category and 26 "SELECT
>> 'DBD::Pg ping test'". I think the minimum would be two queries: one
>> for the session data and one insert.
>
> Agreed, but I believe this is a behavior in DBD::Pg and out of our
> control.

yes and no; there is a $dbh->ping request in D::OD::Driver::DBI for each 
query (Line 68, in rw_handle). The ping should only be called when there 
is a second HTTP request (FastCGI, mod_perl) when "reconnecting" an old 
connection.
With a short look I didn't found the place where the connection gets 
hold. The $driver object, will this be destroyed after a HTTP request and 
recreated with a cached $dbh? If so, it will be easy to ->ping only when 
getting a cached $dbh or to safe a flag "already pinged" in the $driver 
object.

And also when there is no visible SQL query to the database: the ->ping 
call takes some time for *each* query.

DBD::mysql internally calls the MySQL API function mysql_ping, but the 
MySQL docs doesn't say how they check the connection:

  <http://dev.mysql.com/doc/refman/5.0/en/mysql-ping.html>


But according to the description, it is not optimized for speed, so also 
with MySQL it may be good not to call this too often ;)


>> * reducing the queries for in template processing
>
> We're working on this.

:-)


> Transaction support is interesting and we'll consider it. There are
> very few cases where it is actually warranted, but cascading deletions
> is one of them (ie, removing all related objects of an entry record,
> then removing the entry itself).

I'm usually a fan of doing such things in the database itself, because it 
is the only place where the data consistency can be guaranteed.
MySQL supports some basic stuff with InnoDB and only some *really* old 
versions of MySQL only support MyISAM.


> And thanks for the feedback!

thanks for Movable Type! :-)


Ciao
  Alvar


-- 
** Alvar C.H. Freude, http://alvar.a-blast.org/
** http://www.assoziations-blaster.de/
** http://www.wen-waehlen.de/
** http://odem.org/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
Url : http://www.sixapart.com/pipermail/mtos-dev/attachments/20080310/7ddac54f/attachment.bin