logo

MMBase tips

Blobs to disk recipe

tip #23 / Mon 29 Oct 2012 / André / 0 comments

MMBase has the option to serve large binary files - like images and other big files - from disk in stead of from the database. The default configuration is to save all binaries or blobs in the database, but can lead to hefty database backups. This recipe is about saving binaries on your filesystem.

The last couple of months I did this exercise a few times in a row for some rather long running MMBase installations. Database backups took over two hours or more, because of a large number of big images and other blobs like videos. The documentation about this procedure is a bit scarse, here are my notes.

MMBase has various database description files for MySQL as well as PostgreSQL, Oracle etc. These files live in /config/storage/databases and contain templates for typical SQL procedures that differ. This example prepares and an older MySQL database from the latin1 era to use blobs from disk.

Recipe

1. Stop your web application server, e.g. Tomcat.

2. Make sure there is  /WEB-INF/config/storage/databases/mysql-binaryasfile-latin1.xml. To create it, copy the existing mysql-latin1.xml, rename it 'mysql-binaryasfile-latin1.xml' and modify it. Right below the following line:

<option name="database-supports-blob" />

 Add a line declaring you will serve binaries from disk from now on.

<option name="database-stores-binary-as-file" value="true" />

Don't mind the location where all files are saved, this is set in 'modules/mmbaseroot.xml' (step 4).

3. In /WEB-INF/config/modules/mmbaseroot.xml change the database property:

<property name="database">mysql</property>

To the exact same name as your new database storage file:

<property name="database">mysql-binaryasfile-latin1</property>

4. Make sure the property 'datadir' is set in that same file 'mmbaseroot.xml' if you want your files to end up somewhere else but the webapp directory. In my case:

<property name="datadir">/usr/share/data</property>

Ensure this directory is 'writable' for your web application server, e.g. Tomcat, since all binaries will end up here.

5. Adapt your database. In MySQL you need these lines to change some rows in the table for icaches (image caches) and images:

mysql> DROP TABLE install_icaches;
mysql> ALTER TABLE install_icaches add unique (ckey);
mysql> ALTER TABLE install_images MODIFY handle longblob NULL DEFAULT NULL;

6. Make sure the field 'handle' in tables that contain binaries is not NULL or you will get exceptions during export of your data. To check enter on the MySQL console: 

mysql> SELECT * FROM install_icaches WHERE handle IS NULL;

And to make all binary fields NULL that empty:

mysql> UPDATE install_attachments SET handle=0 WHERE handle IS NULL;
mysql> UPDATE install_icaches SET handle=0 WHERE handle IS NULL;
mysql> UPDATE install_images SET handle=0 WHERE handle IS NULL;  

7. If you want you can change these logging settings to follow progress during export. Typically in /WEB-INF/config/log/log4j.xml:

<logger name="org.mmbase.storage.implementation.database.DatabaseStorageManager" additivity="false">
<level class="&mmlevel;" value ="service" />
<appender-ref ref="sqllog" /> </logger>

8. Start Tomcat again and hit the 'Blobs to disk' button in MMBase Admin. You can follow the export in the logs.

When the export is finished you wil find all your files in a directory named after your database, ordered per table. And you have started serving binaries from your filesystem in stead of your database.

Look for double icaches

All image caches, 'icaches', in your database should be unique. These caches are altered versions, typically scaled down smaller versions, of the original large uploaded image. This MySQL command counts, the number of non unique icaches, the ones with the same 'ckey', the template that created the image cache.

mysql> select number,ckey,count(ckey) as cnt from install_icaches 
group by ckey having cnt > 1 order by cnt;

0 comments | leave a comment

Your comment





* = Required.
Email addresses will never be published.
HTML is not possible, although a bit of Wiki syntax - like *bold* f.e. - is supported. You can make links when they are easily recognized as urls, f.e. www.toly.nl or http://tomcat.apache.org.
Off-topic or inappropriate comments will be deleted.

Streams app: upload and transcode video

The MMBase Streams applications converts uploaded video (and audio and images) to different other formats, like webm, ogg or h.264. It is developed as an extension to the mmbase media application used by a few Dutch broadcasters.

Read more »» za 5 mei 2012 / door André / 0 comments

Cron jobs

MMBase has a simple but effective application for cron jobs named MMBase Crontab. Cron jobs can be scheduled via a configuration file, or just by creating cronjob nodes.

Read more »» vr 14 okt 2011 / door André / 0 comments

Properties on nodes

Properties are a simple way of adding extra values to nodes, often as configuration sometimes when you are short of fields. The default mmbase property implementation can contain key/value pairs.

Read more »» do 22 sep 2011 / door André / 0 comments

Logging with mm:log

The mm:log tag can print messages from your templates in the MMBase log file.

Read more »» vr 1 jul 2011 / door André / 0 comments

RSS feed

Most mentioned links:

MMBase documentation
MMBase API
MMBase SVN
Bugtracker
MMBase Taglib reference
The reference for all tags mm