eZPublish, MySQL, bad Database Design and worse DB-API
Lately i got really confusing results from the CMS i use. Within further investigation i fell over slightly misleading MySQL errormessages that had nothing todo with the main cause.
This is a story of badly written database api’s, a worse database (table) design and wrong errormessages.
Introduction
eZPublish is a “enterprise content management system”… yea whatever. The Version i use is 2011-12, it has about 11 contentclasses defined. Each class has from 4 to 15 Attributes. Most of the attributes is ezstring, some eztext, few ezxml and most ezobjectrelation (which is a list datatype). The contentobject overall count is about 345.000, additionally there are 260.000 image objects stored in the database (which points to a file on the filesystem, image is not in the database!) On the top the customers system had those objects in approx. 13 languages.
To speed up a few things i created a ramdisk with 256 MB in size. See the mysql configuration file (excerpt)
# cat /etc/mysql/conf.d/my.cnf
# tmp dir on ramdisk
tmpdir = /var/lib/mysql-tmp
Mount shows the following disks and mountpoints:
# mount
/dev/vda1 on / type ext4 (rw)
tmpfs on /var/lib/mysql-tmp type tmpfs (rw,size=256M,mode=01777)
/dev/vdb1 on /var/lib/mysql type ext4 (rw)
/dev/vdd1 on /opt type ext4 (rw)
/dev/vdc1 on /home type ext4 (rw)
So we have the following mountpoints:
- / as ext4
- /var/lib/mysql as tmpfs (256mb)
- /opt
- /home
The problem
During development of customer specific extensions utilizing the cms’ kernel based database API encountered a few errors on a braindead easy script I wrote.
The script does nothing spectacular. It fetches the first 100 objects from the cms as array (with those 100 objects in it). The script is written in php as the cms is in php too. I supplied the cms’ kernel API a few aparameters to work with:
- class should be of type image
- limit 0, 100
- ignore object visibility (get all objects, ignore hidden/visible status)
- …
This is an excerpt of the script with the most important area:
$mainNodeId = 1;
$subTreeParams = array(
'Limitation' => array(),
'MainNodeOnly' => true,
'LoadDataMap' => false,
'IgnoreVisibility' => true,
'ClassFilterType' => 'include',
'ClassFilterArray' => array('image'),
'Offset' = 0,
'Limit' = 100);
$nodeList = eZContentObjectTreeNode::subTreeByNodeID( $subTreeParams, $mainNodeId );
exit;
This is nothing special if you are familiar with the cms’ API. Drupal has similar functions/classes. Neverthless, on my setup with the customers database, this fails bigtime! I got database exceptions thrown within the cms’ database API.
Examining the mysql errorlog, i found this:
120130 15:05:30 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/var/lib/mysql-tmp/#sql_5477_0.MYI'; try to repair it
120130 15:05:52 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/var/lib/mysql-tmp/#sql_5477_0.MYI'; try to repair it
It goes on:
Query error (126): Incorrect key file for table '/var/lib/mysql-tmp/#sql_5477_0.MYI'; try to repair it. Query:
SELECT DISTINCT
ezcontentobject.*,
ezcontentobject_tree.*,
ezcontentclass.serialized_name_list as class_serialized_name_list,
ezcontentclass.identifier as class_identifier,
ezcontentclass.is_container as is_container,
ezcontentobject_name.name as name,
ezcontentobject_name.real_translation
FROM
ezcontentobject_tree,
ezcontentobject,ezcontentclass,
ezcontentobject_name
WHERE
ezcontentobject_tree.path_string like '/1/%' and
ezcontentclass.version=0 AND
ezcontentobject_tree.node_id != 1 AND
ezcontentobject_tree.contentobject_id = ezcontentobject.id AND
ezcontentclass.id = ezcontentobject.contentclass_id AND
ezcontentobject_tree.node_id = ezcontentobject_tree.main_node_id AND
ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and
( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 8192 ) >> 12 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 4096 ) >> 10 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 8 ) )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 64 ) >> 2 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 65536 ) >> 11 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 256 ) >> 2 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 32 ) << 2 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 512 ) >> 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 16384 ) >> 5 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 16 ) << 6 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1024 ) << 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2048 ) << 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 4 ) << 11 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 32768 ) >> 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 128 ) << 8 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) << 15 )
<
( ezcontentobject_name.language_id & 1 )
+ ( ( ezcontentobject_name.language_id & 8192 ) >> 12 )
+ ( ( ezcontentobject_name.language_id & 4096 ) >> 10 )
+ ( ( ezcontentobject_name.language_id & 8 ) )
+ ( ( ezcontentobject_name.language_id & 64 ) >> 2 )
+ ( ( ezcontentobject_name.language_id & 65536 ) >> 11 )
+ ( ( ezcontentobject_name.language_id & 256 ) >> 2 )
+ ( ( ezcontentobject_name.language_id & 32 ) << 2 )
+ ( ( ezcontentobject_name.language_id & 512 ) >> 1 )
+ ( ( ezcontentobject_name.language_id & 16384 ) >> 5 )
+ ( ( ezcontentobject_name.language_id & 16 ) << 6 )
+ ( ( ezcontentobject_name.language_id & 1024 ) << 1 )
+ ( ( ezcontentobject_name.language_id & 2048 ) << 1 )
+ ( ( ezcontentobject_name.language_id & 4 ) << 11 )
+ ( ( ezcontentobject_name.language_id & 32768 ) >> 1 )
+ ( ( ezcontentobject_name.language_id & 128 ) << 8 )
+ ( ( ezcontentobject_name.language_id & 2 ) << 15 )
) AND
ezcontentobject.language_mask & 131071 > 0
ORDER BY path_string ASC
LIMIT 0, 100
Well, this query will force mysql to create a temporary table, which i configured to be located on the ramdisk (tmpfs). I could not find any reason why this should be any problem or cause the temporarily created (ramdisk) table to be corrupt, since it is created by mysql itself. MySql should know what it is doing!?!
I then reconfigured the mysql-server to create those tables on a slow disk instead, on /tmp and voila: the temporary table will be over 1Gig in size! This is the diskusage when running the query:
# ls -lh /tmp
-rw-rw---- 1 mysql mysql 776M 2012-01-30 17:19 #sql_54eb_0.MYD
-rw-rw---- 1 mysql mysql 2,0K 2012-01-30 17:19 #sql_54eb_0.MYI
WTF?
After running a mysql explain with the query above, i got some insights of what went wrong. I spare you the details, instead give you a summary:
- ezcontentclass = Using temporary; Using filesort
- ezcontentobject = Using where
- ezcontentobject_name = Using where
- ezcontentobject_tree = Using where
Error #1
SELECT DISTINCT
....
Turns out having select distinct … is a bad idea! MySQL fist has to fetch ALL the rows and AFTER that returns distinct values. If the developers would be intelligent enough, they would have wrote this query to use a standard select … with group by clauses instead. This would cause the query analyzer to make proper filtering when fetching the results and the resulting temporary table would be significant smaller.
Do NOT use select distinct, create proper group by clauses in your query!
Error #2
SELECT
ezcontentobject.*,
ezcontentobject_tree.*,
....
Turns out the querydesign is very poor. The cms’ database API creates baaaad queries. Everyone knows, having a select * is baaaad for performance.
Why?
Because MySQL has to fetch and return ANY column (int, char, varchar, blob, text, …) from the table to RAM and/or to temporary disk. This is the main bottleneck the cms has hit.
Do NOT use select *, specifiy columns us want!
Error #2.5
The content of an article itself is stored in TEXT fields using xml to describe the formatting. Well, this is nice for programming but a nightmare for an sql driven relational database. If the database engine encounters TEXT fields during a query, the result is directly written to disk - or in my case into a temporary table.
That behaviour is hardcoded into MySQL. It is ok for MySQL (and other relational databases too) to do so. Everyone knows that. Or at least should know that. TEXT fields can be up to several Megabytes in size.
Imagine having a fairly huge enterprise scale website using this cms with over 1 mio objects and each with a few of those TEXT fields. Together with those badly generated sql-querys…
It would be much more intelligent to fetch a list of articles without the payload. If one programmer or API needs the ugly TEXT payload, it is lazy-loaded afterwards when requested. This minimizes the first query’s payload. On the other hand it MIGHT raise the number of querys. BUT those querys can be cached efficiently by either MySQL itself (using query cache) or by a caching layer in the cms or the application layer.
First select article metadata:
SELECT id, foo, bar
FROM sometable
WHERE ...
After that, only for a small amount of items, get the content from the TEXT based column:
SELECT ugly_payload
FROM sometable
WHERE id = xxx
Error #3
Why does MySQL fail to create a proper errormessage stating missing diskspace?