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 and the default InnoDB buffers are small, 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 all active rot_galleries + rot_gallery_data* + rot_gallery_stats tables). A slave's rot_gallery_statsX data is stored in the master's database, so convert that table on the master, not on the slave.
• Convert only these table families to InnoDB; the other tables are not heavily accessed enough to justify competing for buffer-pool memory.
• 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 updates and additions in memory before writing them to disk. Writes can go directly to disk or first to the operating system's cache, which the OS later flushes to disk. The default, 1, writes to disk as soon as data arrives and is the safest option. For this workload, losing the last few seconds of gallery additions after a server failure may be acceptable. Use 2 to write to the OS cache immediately after adding data, or 0 to let MySQL decide when to write to the OS cache. A value of 0 is considered suitable for this use case.

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 invalidate cached results for that table, and thumbnail CTR changes every minute, so cache hits are rare. SmartCJ caches thumbnails internally anyway. Allocate extra memory to this setting only if other scripts need it; SmartCJ itself does not benefit from it.

• 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*.