Tagged: mysql-mac-os-x
MySQL innodb_buffer_pool_size optimal value
- by admin
innodb_buffer_pool_size is a quite important MySQL configuration parameter which can dramatically increase your DB productivity. The larger you set this value, the less disk I/O is needed to access data in tables. 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. Of course, you need to be careful with memory consumption, especially for a non-dedicated server.To get optimal value of innodb_buffer_pool_size for your DB you may run this SQL:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
It would return optimal size calculated as sum of DB stored data and indexes multiple by 1.6. But it does not know anything about you physical memory! Thus set innodb_buffer_pool_size not more than 15% of RAM size. To test how much memory your new configured MySQL would maximum consume, use this mysqltuner Perl script. You can also use this script for optimizing of your MySQL DB other settings.
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):
[mysqld]
innodb_buffer_pool_size=2G
Mac OS, MySQL: No such file or directory (trying to connect via unix:///
- by admin
Error on attempt to connect locally to MySQL server DB with PHP on MacOS X Lion 10.7:No such file or directory (trying to connect via unix:///var/mysql/mysql.sock)
Solution 1:
instead of localhost use 127.0.0.1:
mysql_connect ('127.0.0.1', $user, $password);Solution 2:
In /etc/php.ini change
pdo_mysql.default_socket = /var/mysql/mysql.sock
mysql.default_socket = /var/mysql/mysql.sock
mysqli.default_socket = /var/mysql/mysql.sock
to
pdo_mysql.default_socket = /tmp/mysql.sock
mysql.default_socket = /tmp/mysql.sock
mysqli.default_socket = /tmp/mysql.sock
Do not forget to restart Apache after update :-)
FYI: If no /etc/php.ini found just copy /etc/php.ini.default to /etc/php.ini
Mac OS X - MySQL Workbench - Error Opening Configuration File my.cnf
- by admin
By default, the OS X installation does not use a my.cnf, and MySQL just uses the default values.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):
Log in to OS X using an administrator-level account (to keep things simple lower down)
Open Terminal (in Utilities folder under Applications folder)
cd /usr/local/mysql/support-files/
sudo cp my-huge.cnf /etc/my.cnf
and enter your admin password when prompted. You could do this from a non-admin account by using the su command, but that's probably a bit scary for some people ;)
You will now have a copy of my.cnf in /etc (just in case you don't know, that means the etc folder directly under the root folder, not under MySQL's install folder)
You can edit it with a text-editor such as TextWrangler by using File->Open Hidden, or if you are happy to use the command line, use:
cd /etc
sudo nano my.cnf
To exit without saving, press CTRL+X, to Save then exit it's: CTRL+O then CTRL+X
