We have about 5-6 different internal webapps (staff use only) that we will be transferring across to a new virtual server, however I need to setup our my.cnf settings first and I am lost (I am web designer – so this is a little out of my league!).
Our internal systems have about 100 tables apiece, using primarily InnoDB but in some cases using Myisam. Also, 1 of our webapps has quite a lot of data (100,000 records+) and the SQLs used to select the data are quite inefficient (few indexes used, inner SELECTs etc). Roughly about 50 users at any one time.
Our new server will be the HS-250 from HeartInternet.
Many thanks for the help,
Best Answer
InnoDB
You need to run this query:
This will tell you what to set for innodb_buffer_pool_size or 75% of the DB Server's total memory, whichever is smaller.
See also
Jan 13, 2012
: What to set innodb_buffer_pool and why..?MyISAM
You need to run this query:
This will tell you what to set for key_buffer_size.
CAVEAT #1
Use common sense about using both buffers in relation to the total installed RAM (See my answer to What are the main differences between InnoDB and MyISAM?)
CAVEAT #2
Once you set these values and start mysql, please download
mysqltuner.pl
and run it. It will forecast how much RAM is need for all buffers and for connections (it terms of buffers joins, sort, reads)CAVEAT #3
As for InnoDB, there are other settings you need to set for getting InnoDB to use
Multiple CPUs/cores:
May 26, 2011
: About single threaded versus multithreaded databases performanceSep 12, 2011
: Possible to make MySQL use more than one core?Sep 20, 2011
: Multi cores and MySQL PerformanceInnoDB Tablespaces for Individual Tables
Dec 09, 2011
: What is the best way to reduce the size of ibdata in mysql?Feb 22, 2012
: Ibdata usage and Recommendations?Apr 01, 2012
: Is innodb_file_per_table advisable?Apr 19, 2012
: MySql - Clean ibdata1Oct 22, 2012
: How large should be mysql innodb_buffer_pool_size?and so much more