Can tables be converted to innodb?

's Avatar

stian

30 Jan, 2014 08:25 PM

Hi,

Can I convert all tables in the advertpro db to innodb or will that break things somehow?

  • Stian
  1. Support Staff 1 Posted by Mike Cherichett... on 30 Jan, 2014 08:56 PM

    Mike Cherichetti's Avatar

    Hi Stian,

    We don't support using InnoDB because it processes inserts/updates too slowly to keep up with the rate that AdvertPro does them to update your statistics reports. Other than MyISAM the only other storage engine we support is TokuDB. It is transactional like InnoDB, however, TokuDB is significantly faster and scales to handle TB's of data since it uses compression, fractal tree indexes and also commits data in batches. That's really where InnoDB falls down as it must commit every insert/update individually and even at low traffic levels AdvertPro can run 10's of thousands of inserts/updates a minute if you have a healthy number of campaigns, media and zones.

    If you would like to try TokuDB we only recommend using it on the stats and stats_* tables assuming that the tables are rather large. Due to the way the compression and fractal tree indexes work they can have a negative impact on performance for smaller tables. When your database grows beyond 25-30 GB that's usually about the point where you want to consider TokuDB. For that size database you usually get a 1:10 compression ratio so it will shrink down to about 2.5-3 GB and reporting queries will run 2-3x faster on average.

    That being said, before switching if you've upgraded from older versions of AdvertPro you should drop all secondary indexes from the stats and stats_* tables before switching to TokuDB. Only the primary keys are needed to be efficient with fractal tree indexes. In fact in newer versions of AdvertPro we no longer create the secondary indexes on those tables as we made query improvements to remove the need for them.

    We include an SQL script named tokudb.sql in the ~/tomcat/webapps/advertpro/sql directory that you can run to convert all of the stats and stats_* tables to TokuDB format. Should you need to roll back to MyISAM there is a corresponding myisam.sql file in the same directory that you can use to do that. If you don't have either of those files you need to upgrade to AdvertPro 3.5 as previous versions didn't support TokuDB.

    Thanks,
    Mike

  2. Mike Cherichetti closed this discussion on 05 Feb, 2014 03:59 AM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac

Recent Discussions

31 Mar, 2021 03:15 PM
03 Aug, 2020 08:33 PM
22 Jun, 2020 06:51 PM
07 Feb, 2020 05:39 PM
10 Dec, 2019 05:28 PM

Recent Articles