MySQL table types

When running OpenX on MySQL, users must decide whether to use the MyISAM or the InnoDB storage engine. This section offers advice on which solution is best according to your needs.

• General recommendation
• The MyISAM storage engine
  • Transaction Support Issues
  • Table-Level Locking
  • Further Reading
• The InnoDB storage engine
  • Further reading
• Has the recommended type changed?

General recommendation

In general, using the MyISAM storage engine is recommended. However, if performance is important to you and you want to get the most out of your OpenX installation, some knowledge and thought is required. The aim of this section is to provide OpenX administrators with all the information needed to make an informed decision regarding which MySQL storage engine to use.

The MyISAM storage engine

The MyISAM storage engine has one big advantage over InnoDB: speed. The MyISAM storage engine is about as fast as it gets, which makes it ideal for simple databases where you want to read and write data very quickly. This is obviously a good thing for OpenX, because fast read speeds mean that data relating to banner delivery can be obtained quickly. Fast write speeds mean that banner delivery logging is quick as well. Both of these add up to fast banner delivery to your website(s).

However, this speed comes at a cost. Specifically:

• there is no transaction support
• write operations result in table-level locking.

Transaction support issues

As a general rule, OpenX has been written to operate without needing transaction support. In areas of the code where transactions would normally be employed, the code itself has been written to emulate them as far as is possible when using the MyISAM storage engine.

However, if you are using the MyISAM storage engine for your OpenX tables and your database crashes while the maintenance script is running, this might result in a situation where the statistics and priority values are inconsistent. If this occurs, once the database is running again delivery might not work correctly until the next time maintenance runs.

Note: If your database crashes then you already have problems with your server. Perfect delivery isn't something that can be guaranteed in this situation.

Table-level locking

The issue of table-level locking is the main downside to using the MyISAM storage engine for OpenX. Whenever OpenX needs to write to a table it becomes locked, and no other writes can be made to the table at the same time. This means:

• When a banner is being delivered and the impression is being logged to the database, all other banners being delivered at the same time will have to wait before they can try to log their impression to the database. Thus, the higher your volume of banners the more the speed of delivery will decrease as more and more banners have to wait for their turn to write to the database.
• More importantly, when the maintenance engine runs to summarize your delivery statistics, all banner delivery will be delayed while the maintenance engine has the raw logging table(s) locked. If the maintenance engine takes minutes to run, especially when summarizing your statistics or performing conversion tracking, then your OpenX ad server may effectively be 'down' for this period!

Of course, for the average OpenX installation this will never be an issue. Most OpenX installations don't deliver enough banners per second to see the effect of table-level locking, and their maintenance script only takes a few seconds to summarize their statistics. In these instances, banner delivery is barely affected when maintenance runs.

This is why we recommended that the average OpenX administrator should use the MyISAM storage engine.

Further reading

It is possible to perform appropriate performance monitoring of your server(s) and tuning of OpenX to ensure that, for example, the maintenance engine does not take too long to run. If you intend to use the MyISAM storage engine it is recommended that you read our Performance Tuning section.

See also the MySQL documentation regarding the MyISAM storage engine.

The InnoDB Storage Engine

Reading and writing using the InnoDB storage engine is much slower than with MyISAM. However, there are still some good reasons to consider using InnoDB for your OpenX database tables:

• With appropriate hardware (plenty of power and fast disk drives, preferably in a striped RAID configuration) InnoDB can be more than fast enough for rapid banner delivery, albeit at increased hardware cost compared with using the MyISAM storage engine.
• The fact that InnoDB supports row-level locking means that delivery of banners from OpenX will not suffer in the event that the maintenance process takes a 'long' time to run. See the discussion on table-level locking above (in the MyISAM section).

If you intend to run an OpenX installation that is going to:

• serve several million banner impressions per day
• will have a dedicated database server attached to a cluster of web servers
• will be used to carry out conversion tracking

then it is recommended that you consider whether the InnoDB storage engine might be more suitable for your needs.

Ultimately, if you are going to be running a high volume system it is recommended that you prepare a staging environment. This will allow you to experiment with both storage engines alongside your hardware and your delivery configuration to see how well they perform for you.

Further reading

See the MySQL documentation regarding the InnoDB storage engine.

Has the recommended type changed?

Since Max Media Manager was merged into the main OpenX branded code base and released to the general public for use by the OpenX community, the recommended table type has been updated to take the average user's requirements into account.

If you are a high volume user please read the section above on the InnoDB storage engine, as you might find that the default recommendation does not meet your needs.