Tell me more ×
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.
up vote 1 down vote favorite
1

I've got a dedicated production server with multiple cpus and 16GB of ram.

I would like to know how can I configure it better to take advantage of the server resources for better performance because website traffic is increasing and mysql starts using a lot of cpu.

This is my current MySQL Server configuration: http://pastebin.com/PTn1HWQu

I am also planning to optimize the database, and check the queries to improve the slow ones.

Thank you, Alex.

share|improve this question
First, it seems reasonable to follow the hints in your config file and set those parameters accordingly. Second, I think I would start the other way round, and identify bottleneck queries first, improve their performance (including denormalization if necessary) and then fine tune the system resource usage. – dezso Jun 1 '12 at 10:02
Do you primarily use MyISAM or InnoDB? Or both? Is your server running MySQL only (or other services as well, like web-server)? – ypercube Jun 1 '12 at 10:18
OK, just noticed "dedicated", I guess that answers my 2nd question. – ypercube Jun 1 '12 at 10:19
If you have a RAID controller with battery backed cache, I would suggest adding: innodb_flush_method = O_DIRECT to your config. – bobwienholt Jun 2 '12 at 15:00

3 Answers

up vote 1 down vote

Please keep in mind that prior to MySQL 5.1.38, InnoDB was not designed to access multiple CPUs. You have one of two options to get MySQL to take advantage of multiple CPUs.

  • OPTION 1 : Install MySQL 5.5
  • OPTION 2 : Install MySQL 5.1 InnoDB Plugin

There are new options available since MySQL 5.1.38 (InnoDB Plugin Only) that is now fully available in MySQL 5.5

Rather rewrite what I wrote in past posts about this subject, please reads my past posts discussing those new settings:

As for tuning all other aspects of MySQL, please refer to my past post about looking into disk space management and storage engine tuning : MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

share|improve this answer
up vote 1 down vote

Percona, a leading MySQL support and consulting company, offers a MySQL configuration wizard. It asks several questions, and produces my.cnf file based on your answers. It's a good starting point, and should produce somewhat safe values.

Several people from Percona offer wrote a book called "High Performance MySQL". The third edition was recently released, and covers tuning in great detail.

share|improve this answer
up vote 0 down vote

Why don't you give a try with MONyog - MySQL Monitor and Advisor tool? Definitely this tool help you out in setting up optimal MySQL configuration, finding out problem queries and many other things helps in server performance. It comes with 30 days trial

http://webyog.com/en/downloads.php

Feature list is impressive too -

http://webyog.com/en/monyog_feature_list.php

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.