User Tools

Site Tools


mysql_tunning

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

mysql_tunning [2014/01/29 18:04]
mysql_tunning [2014/01/29 18:04] (current)
Line 1: Line 1:
 +====== 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)