1

Mysql – calculate InnoDB buffer pool

Recently I was investigating how big my innodb buffer pool should be. While looking for more detailed info I came across this really handy mysql query which gives in result number of GB to which you should set your innodb_buffer_pool_size

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;

 

To query what is your current data usage you can use the following

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;

With this knowledge you should not have any problems with tuning your mysql 😉

 

 

0

MySql SSL – require client certificate for user

When working with MySql database where you have setup encryption following one of many guides on internet you then have choice between just requires SSL to be used or that the client also has certificate. I followed the complete guide from mysql dev which allowed me to quickly get the certificates and SSL setup for my database.

Then depending on your choice you can create users using snippets below :