Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

share|improve this question

2 Answers 2

up vote 0 down vote accepted

InnoDB

You need to run this query:

SELECT
    CONCAT(IB_BB/POWER(1024,1),'K') IB_KB,
    CONCAT(IB_BB/POWER(1024,2),'M') IB_MB,
    CONCAT(IB_BB/POWER(1024,3),'G') IB_GB
FROM (SELECT SUM(data_length+index_length) IB_BB
FROM information_schema.tables WHERE engine='InnoDB') A;

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

MyISAM

You need to run this query:

SELECT
    CONCAT(KeyBuffer_BB/POWER(1024,1),'K') KeyBuffer_KB,
    CONCAT(KeyBuffer_BB/POWER(1024,2),'M') KeyBuffer_MB,
    CONCAT(KeyBuffer_BB/POWER(1024,3),'G') KeyBuffer_GB
FROM (SELECT SUM(index_length) KeyBuffer_BB FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

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:

InnoDB Tablespaces for Individual Tables

and so much more

share|improve this answer

You could start with the percona wizard, if only to narrow down what to look into

https://tools.percona.com/wizard

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.