How to calculate innodb_buffer_pool_size of mysql

A lot of people suggestions that innodb_buffer_pool_size should be up to %80 of the total memory.

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size based on all InnoDB Data and Indexes with an additional 60%.

For Example

mysql> 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;
+——-+
| RIBPS |
+——-+
| 8 |
+——-+
1 row in set (4.31 sec)

With this output, you would set the following in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=8G
Next, service mysql restart

After the restart, run mysql for a week or two. Then, run this query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name=’Innodb_buffer_pool_pages_data’) A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name=’Innodb_page_size’) B;

This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.

how to source install MySQL 5.5.15 on centos

yum install gcc gcc-c++
yum install ncurses-devel

mkdir -p /tmp
cd /tmp
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.15.tar.gz/from/http://mysql.he.net/
wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
wget http://ftp.gnu.org/gnu/bison/bison-2.5.tar.gz

cd /tmp
tar zxvf cmake-2.8.4.tar.gz
cd cmake-2.8.4
./configure
make
make install

cd /tmp
tar zxvf bison-2.5.tar.gz
cd bison-2.5
./configure
make
make install

/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql
cd /tmp
tar xvf mysql-5.5.15.tar.gz
cd mysql-5.5.15/
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/var/mysql/data \
-DMYSQL_USER=mysql

This is my case ,you can ignore

cmake -DCMAKE_INSTALL_PREFIX=/ssdc/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql5.5.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/ssdc/mysql/data

 

make
make install

other ready jobs you need to do:
chmod +w /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
ln -s /usr/local/mysql/lib/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16
mkdir -p /var/mysql/
mkdir -p /var/mysql/data/
mkdir -p /var/mysql/log/
chown -R mysql:mysql /var/mysql/
cd support-files/
cp my-large.cnf /var/mysql/my.cnf
cp mysql.server /etc/rc.d/init.d/mysqld

/usr/local/mysql/scripts/mysql_install_db \
–defaults-file=/var/mysql/my.cnf \
–basedir=/usr/local/mysql \
–datadir=/var/mysql/data \
–user=mysql

chmod +x /etc/init.d/mysqld

chkconfig –add mysqld
chkconfig –level 345 mysqld on

Error installing mysql2: Failed to build gem native extension

you want running bundle install or gem install mysql2 it gives the following error:

Error installing mysql2: ERROR: Failed to build gem native extension.

How to fix this and successfully install mysql2?

On Ubuntu/Debian and other distributions using aptitude:

sudo apt-get install libmysql-ruby libmysqlclient-dev
Package libmysql-ruby has been phased out and replaced by ruby-mysql. This is where I found the solution.

If the above command doesn't work because libmysql-ruby cannot be found, the following should be sufficient:

sudo apt-get install libmysqlclient-dev
On Red Hat/CentOS and other distributions using yum:

sudo yum install mysql-devel
On Mac OS X with Homebrew:

brew install mysql