This shows you the differences between two versions of the page.
— |
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' | ||
+ | |||
+ | 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 | ||
+ | * query_cache_size - some admin offer to set high value fot this setting. It doesn' | ||
+ | |||
+ | |||
+ | ===== Mysql Table Engine : all in InnoDB ===== | ||
+ | |||
+ | While you can convert all tables |