MySQL Tuning

If you only use trade, this article isn't very relevant—trade creates minimal MySQL load. It's most useful for those using rotation with large databases.

This covers standard MySQL optimization principles with specific application to SmartCJ.

Background: Database data lives on disk, and disk operations are slow compared to memory. Indexes solve this. Think of an index like a book's table of contents—instead of reading chapters 1 and 2, you look up chapter 3 and jump straight there.

Background 2: Database tables store data about galleries and thumbnails. For example, thumbnail 1 may have 100 impressions and 20 clicks.

With many thumbnails (500-600k is common), data can reach gigabytes. Two storage engines are relevant: MyISAM and InnoDB. SmartCJ defaults to MyISAM, though it's older.

The key difference:

MyISAM stores indexes and data in separate files. InnoDB stores them together, with indexes built into the data file. When MyISAM queries, MySQL caches the index file (critical to set key_buffer_size large enough to fit all indexes—roughly 15% of data size). It then searches the data file on disk, which is slow and relies on system cache.

InnoDB works differently. The data file contains index info like “IDs 1-5 start here, next section at byte 123784”. MySQL reads only needed sections. InnoDB requires the entire data file in memory via innodb_buffer_pool_size. MySQL's built-in caching beats system caching, so when the data file fits in memory, InnoDB is 25-35% faster than MyISAM. However, once data exceeds memory, InnoDB does far more disk work and performs worse than MyISAM.

InnoDB also supports transactions, making it more resilient to system failures. MyISAM doesn't have this, and in exchange, MyISAM inserts are faster—but gallery additions are infrequent enough that this rarely matters.

Another key advantage: InnoDB uses row-level locking, while MyISAM uses table-level locking. For example, when cron updates thumbnail CTR data, MyISAM locks the entire table. If a visitor arrives and needs thumbnail data, the script waits for the cron job to finish, slowing page generation. InnoDB's row-level locking lets cron update thumbnail 1's CTR while the script simultaneously reads thumbnail 2's data, improving overall site speed.

Because the installation environment (MySQL version, settings, database size) varies, SmartCJ defaults to MyISAM tables.

If you optimize MySQL, you can achieve 40% performance gains by converting to InnoDB. Remember: once your data exceeds available memory, InnoDB performance drops below MyISAM.

Main InnoDB settings:

• innodb_buffer_pool_size - must hold all data files. For example, if you have 5 tables of 500MB each, use 2.5GB (this covers rot_galleries + rot_gallery_data* + rot_gallery_stats tables). For slave setups, convert these tables on the master, not the slave.
• Convert only these 3 tables to InnoDB; others aren't heavily accessed enough to justify memory competition.
• innodb_additional_mem_pool_size - set to 64M
• innodb_log_file_size - transaction log for writes; set to 64M
• innodb_flush_log_at_trx_commit - InnoDB keeps data in memory before writing to disk. Two options: bypass system cache (value 1, immediate safe writes) or use OS cache (values 0 or 2, faster but less safe). Default is 1 (safest). Since this isn't a bank and losing a few seconds of gallery additions is acceptable, use 0 or 2 for better performance.

For both MyISAM and InnoDB:

• table_cache - MySQL must open a table to access it. If you see “opening table” in processlist, increase this. Calculate as: (simultaneous connections) × (tables per connection) × (databases). For example, 10 connections × 10 tables × 10 databases = 1000. Use 1024 as a safe value.
• max_heap_table_size = tmp_table_size = 256-512M - MySQL creates temporary tables for complex queries; it's faster in memory. Optionally create a RAM disk and set it as MySQL's tmp_dir.
• query_cache_size - caches query results. For example, requests for thumbnail 12345 use the cache on repeat. However, this is ineffective here: table changes flush the entire cache, and thumbnail CTR changes every minute, so cache hits are rare. SmartCJ caches thumbnails internally anyway, so this option doesn't benefit SmartCJ.

• Use optimization tools like MySQLTuner to suggest settings.
• For a dedicated MySQL server, 4-8 cores is sufficient; MySQL doesn't scale well beyond that (especially MySQL 5.5). Bigger gains come from more memory (for innodb_buffer_pool_size) or SSD drives.
• Ensure your database server has at least as much RAM as your desktop. It needs to handle hundreds of thousands of daily visits and millions of database queries.

While the guidance above recommends converting only 3 tables, many convert the entire database—not ideal from a memory perspective.

If you convert referral tables to InnoDB, minute-by-minute updates compete for buffer pool space, displacing frequently-used rot_* data. Since referral data is viewed rarely (maybe once daily) but rot_* data is accessed constantly, this is wasteful.

For efficiency, convert only rot_* tables—better yet, just rot_galleries, rot_gallery_stats*, and rot_gallery_data*.