User Tools

Site Tools

Translations of this page:


Documentation index


Mysql Tunning

If you use only trade part of the script - it's not really important. Trade part doesn't really load mysql. This article is for those who use built-in rotation, especially with big databases.

Mysql, as almost any other DB, stores data on disk. Disk operations are slow (compared to in-memory operations) and that's is why very important to use indexes. There are basically 2 storage engines (mysql has more then 11 actually but only 2 are important in our case) - MyISAM and InnoDB.

MyISAM can load only indexes in memory while innodb can load both indexes and data itself. Thus we have two points to consider: if we don't have enough memory - we'd batter use myisam, but if we do have enough memory to load both indexes and data - we can get a great improvement in terns of performance. But if we don't have enough memory and try to use Innodb - it will be slower then myisam. Conclusion: InnoDB is batter if you have enough memory. If you don't - use MyIsam.

As we don't know what kind of server you gonna use - default engine is MyISAM

Here's what you need to move to InnoDB:

  • set innodb_buffer_pool_size in mysql config - should be big enough to store all your data from the following tables - rot_galleries + rot_gallery_data* + rot_gallery_stats*. Ie if those tables summarize to 500Mb in 1 database and you have 5 such databases on this server - set you have innodb_buffer_pool_size to 2.5 Gb
  • convert to Innodb mentioned tables
  • set innodb_additional_mem_pool_size - 64M
  • innodb_log_file_size - 64M
  • innodb_flush_log_at_trx_commit = 2

For MyISAM and InnoDB both:

  • table_cache - if you have 10 databases with 10 tables each and each has 10 simultaneous connections - set it to 10*10*10 = 1000. So 1024 is a good value.
  • max_heap_table_size = tmp_table_size = 256-512M
  • query_cache_size - some admin offer to set high value fot this setting. It doesn't work if our case and script does not need it. You can leave it as is.

Mysql Table Engine : all in InnoDB

While you can convert all tables into Innodb , I would not recommend it as it's waste of memory that could be use by system to cache files, thumbs for example. It really makes sense to convert rot_* tables and that's it.

mysql_tunning.txt · Last modified: 2014/01/29 18:04 (external edit)