![]() Log in to OS X using an administrator-level account (to keep things simple lower down) To set up your own my.cnf, you could just create a file straight in /etc, or do the following (excuse me if I say anything which is obvious to you, but this may help complete OS X beginners who are not familiar with the Unix command line): Mac OS X - MySQL Workbench - Error Opening Configuration File my.cnfīy default, the OS X installation does not use a my.cnf, and MySQL just uses the default values. The last but not the least: innodb_buffer_pool_size has to be put in mysqld section of you MySQL configuration file (/etc/my.conf in RHEL): You can also use this script for optimizing of your MySQL DB other settings. To test how much memory your new configured MySQL would maximum consume, use this mysqltuner Perl script. But it does not know anything about you physical memory! Thus set innodb_buffer_pool_size not more than 15% of RAM size. It would return optimal size calculated as sum of DB stored data and indexes multiple by 1.6. SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPSįROM (SELECT SUM(data_length+index_length) Total_InnoDB_BytesįROM information_schema.tables WHERE engine='InnoDB') A To get optimal value of innodb_buffer_pool_size for your DB you may run this SQL: Of course, you need to be careful with memory consumption, especially for a non-dedicated server. Just today I have got one of a heavy SQL query time changed from 15.5 to 1.2 seconds by changing innodb_buffer_pool_size from 23M to 320M! On a dedicated database server, you may set this to up to 80% of the machine physical memory size. The larger you set this value, the less disk I/O is needed to access data in tables. Innodb_buffer_pool_size is a quite important MySQL configuration parameter which can dramatically increase your DB productivity. MySQL innodb_buffer_pool_size optimal value
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |