Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Lukas, What is your default engine? In MySQL there are a lot of parameters that configure the engine behaviour. Depends on the engine, I suggest you to add some parameters or others. Also it's important to know the size of your data. Your configuration is minimal and by default is not optimal. Regards, Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Is it a standalone DB server or Application is also hosted on top of it. You can give 50-70% of RAM to memory parameters like Innodb_buffer_pool_size ( Innodb ) and key_cache ( Myisam ) for mysql tables. Below link : http://mysql.rjweb.org/doc.php/memory will give you a brief idea. Thanks
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Antonio all tables use InnoDB. The size is 27 GB (not yet in prod). I guess in prod it will be fast 80GB. thanks On Wed, Feb 12, 2014 at 10:28 AM, Antonio Fernández Pérez antoniofernan...@fabergames.com wrote: Hi Lukas, What is your default engine? In MySQL there are a lot of parameters that configure the engine behaviour. Depends on the engine, I suggest you to add some parameters or others. Also it's important to know the size of your data. Your configuration is minimal and by default is not optimal. Regards, Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi it's also a Tomcat application server. Not dedicated MySQL instance. On Wed, Feb 12, 2014 at 11:28 AM, Adarsh Sharma eddy.ada...@gmail.comwrote: Is it a standalone DB server or Application is also hosted on top of it. You can give 50-70% of RAM to memory parameters like Innodb_buffer_pool_size ( Innodb ) and key_cache ( Myisam ) for mysql tables. Below link : http://mysql.rjweb.org/doc.php/memory will give you a brief idea. Thanks
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
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(PowerOf10240,0,IF(PowerOf10243,0,PowerOf1024)))+0.4),
SUBSTR(' KMG',IF(PowerOf10240,0,
IF(PowerOf10243,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.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
2014-02-12 12:32 GMT+01:00 Lukas Lehner webleh...@gmail.com: Hi Antonio all tables use InnoDB. The size is 27 GB (not yet in prod). I guess in prod it will be fast 80GB. Depending on how your application is going to use MySQL resources you will need to tweak some things (and not only MySQL). If it is going to be CPU bound, IO bound etc...there are different scenarios Anyways, some general things to take a look at: - Use file per table if possible. This won't give you extra performance, but it will be good if you run into disk spaces issues or for future table migrations. - Make sure you have trx_commit and sync_binlog disabled (make sure you understand what this means and what problems you could have during an un expected crash) - If you're expecting lot of temporary tables (filesorts), make sure tmpdir runs over a fast disk. - Use NUMA memory handling - Make sure you test different disk schedulers (depending if you have RAID and which kind of it) and see how they perform. - You might want to take a look to smp irq affinity and see how it could impact in your system. Manuel.
