List:General Discussion« Previous MessageNext Message »
From:Antonio Fernández Pérez Date:February 12 2014 12:07pm
Subject:Re: DBA: please review my.cnf [for Java Hibernate application] and
give suggestions
View as plain text  
Hi Lukas,

In that case, such as Adarsh has said, you can configure until 70% of your
RAM for innodb_buffer_pool_size.
In your case, with 3GB RAM, I suggest you to configure until 2GB for MySQL:
Minimal for MyISAM (Maybe 32MB), and the rest for InnoDB. Your problem will
be loading data. Maybe your application will work slowly loading data
because there are more data than RAM memory.

Executing the following script, you can see your optimal buffer size for
InnoDB with your data.

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM
information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

Good luck!

Regards,

Antonio.

Thread
DBA: please review my.cnf [for Java Hibernate application] and give suggestionsLukas Lehner12 Feb 2014
  • Re: DBA: please review my.cnf [for Java Hibernate application] andgive suggestionsAntonio Fernández Pérez12 Feb 2014
    • Re: DBA: please review my.cnf [for Java Hibernate application] andgive suggestionsAdarsh Sharma12 Feb 2014
      • Re: DBA: please review my.cnf [for Java Hibernate application] andgive suggestionsLukas Lehner12 Feb 2014
    • Re: DBA: please review my.cnf [for Java Hibernate application] andgive suggestionsLukas Lehner12 Feb 2014
      • Re: DBA: please review my.cnf [for Java Hibernate application] andgive suggestionsAntonio Fernández Pérez12 Feb 2014
      • Re: DBA: please review my.cnf [for Java Hibernate application] andgive suggestionsManuel Arostegui12 Feb 2014