Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am just setting up my new Linux Ubuntu 8.04 LTS server now, and getting ready for launch. My application is a PHP built application using the Zend Framework, and there is a MySQL heavy site (as are probably most sites). So far, I only have 1 server, and it is a full LAMP server.

What I am wondering, is what I should set these values up at (defaults shown) to get the best performance (keeping in mind, that this same server is also running apache and PHP) My server has 4 GB Ram. I only clipped the necessary values from the config file, to save mass confusion.

Here is a little more information: Everything so far is the default install settings (database tables included). I am expecting about 100 transactions a second. I am not sure how much ram I can dedicate, as the server itself is also hosting apache and php, I have 1gm ram, and 4gb burstable (hosted on a virtual server). Mostly, my site will be dishing up static values (dating site), however, it will also be handling transactions from different users (saves, edits, etc).

Here are some sections of my my.cnf file, and their respective values...

Any help, or insight would be much appreciated.

# Fine Tuning
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

# Query Cache Configuration
query_cache_limit       = 1M
query_cache_size        = 16M

# Here you can see queries with especially long duration
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

# BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb

# InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb

[isamchk]
key_buffer              = 16M
share|improve this question
    
You've given very little information here. You haven't even told us what table engine you're using! How many transactions/sec are you expecting? How much RAM can you allocate for MySQL (hint: if you are running a webserver too you can't just say this and expect us to know). What sort of workload will your SQL be? Mainly reading static values? Full text search? Many random writes? Without this sort of information you'll only get generic and ineffective advice. –  Alex Jurkiewicz May 30 '09 at 16:10
    
Everything so far is the default install settings. I am expecting about 100 transactions a second. I am not sure how much ram I can dedicate, as the server itself is also hosting apache and php, I have 1gm ram, and 4gb burstable (hosted on a virtual server). Mostly, my site will be dishing up static values (dating site), however, it will also be handling transactions from different users (saves, edits, etc). –  Mike Curry Jun 1 '09 at 12:48
add comment

3 Answers 3

up vote 3 down vote accepted

MySQLTuner:

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.

It's key to remember that MySQLTuner is a script which can assist you with your server, but it is not the solution to a badly performing MySQL server. The best performance gains come from a thorough review of the queries sent to the server, and an evaluation of the MySQL server itself. A qualified developer in your application's programming or scripting language should be able to work with a MySQL database administrator to find improvements for your server. Once the server and application are optimized well, you may need to consider hardware upgrades to the physical server itself.

share|improve this answer
add comment

The defaults are usually fine for the general case. However, I don't know if your case is general or not.

So, how many users, how much data, how many transactions/second, etc... are you expecting to need to be able to process?

share|improve this answer
    
I am expecting initially to have about 1,000 - 10,000 users... It's a dating site. So if all goes well, the number will creep up. –  Mike Curry May 30 '09 at 15:44
add comment

People reading this topic may be interested in the following articles:

  1. What to tune in MySQL Server after installation
  2. see comments (sorry, new user)
share|improve this answer
    
Innodb Performance Optimization Basics: mysqlperformanceblog.com/2007/11/01/… –  vsg Aug 26 '09 at 14:03
add comment

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.