4

This is a general question about optimal setup for a MySQL environment really, but our current MySQL servers are setup on machines with 16GB RAM, we've recently upgraded to newer servers (more powerful all around, newer Dell Poweredge Servers), but with 128GB RAM available.

We've got a clean slate, so to speak, but are wondering how to correctly optimise everything so it'll work to its peak on the newer hardware. Any recommendations on:

  • filesystem (and blocksize/fs setup)
  • my.cnf (query size, buffer size and associated setup)

All I've got at the moment is the current my.cnf files which were tweaked for the last servers, but I can't find much guidance online to say that any of the settings should be any higher than they are.

kwiksand
  • 463
  • 1
  • 8
  • 16

2 Answers2

3

You might want to run mysqltuner.pl to get some recommendations on configuring for your database.

http://mysqltuner.pl/mysqltuner.pl

Cakemox
  • 24,141
  • 6
  • 41
  • 67
1

Here is how you can setup the key_buffer_size and innodb_buffer_pool_size

Setting key buffer size for MyISAM

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,(SELECT 3 pw) B;

BTW The maximum key_buffer_size that works properly is 4G

So set key_buffer_size to 4G or recommended setting from previous query, whichever is smaller.

Setting innodb_buffer_pool_size

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+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 pw) B;

You may also want to resize the ib_logfile0 and ib_logfile1. Here is how:

Step 1) service mysql stop
Step 2) add this line to /etc/my.cnf
innodb_log_file_size=2047M (or 25% of recommended_innodb_buffer_pool_size, whichever is smaller)
Step 3) rm -f /var/log/mysql/ib_logfile[01]
Step 4) service mysql start

To see why these settings make sense run this query

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

If you are using InnoDB, you should enable innodb_file_per_table and Cleanup Your InnoDB Storage

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80