Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1948
  • Last Modified:

Optimal mysql configuration for 2gb ram server

Hi,

can you take a look and let me know what can I tweak to make this mysql server fly on my linux box.

I use both MyISAM and InnoDb

[mysqld]
default-character-set=utf8

# LOGGING #
log-error=/var/log/mysqld.log
log_slow_queries=/var/log/mysqld_slow_queries.log
log_queries_not_using_indexes  = 1
long_query_time = 1

bind-address=127.0.0.1
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
symbolic-links=0

# http://www.experts-exchange.com/Database/MySQL/Q_21008684.html
# http://nakuls77.wordpress.com/2008/09/14/standard-mysql-mycnf-configuration/

query_cache_limit=1M
query_cache_size=32M
query_cache_type=1

max_connections=500
##max_user_connections=600
max_allowed_packet             = 16M
max_connect_errors             = 1000000

interactive_timeout=100
wait_timeout=100
connect_timeout=10
key_buffer_size=128M # 64M for 1GB, 128M for 2GB,256 for 4GB
join_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB

table_cache=4096
tmp_table_size                 = 32M
max_heap_table_size            = 32M
open_files_limit               = 65535

sort_buffer_size=2MB # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_rnd_buffer_size=1536K # 768K for 1GB, 1536K for 2GB, 3072K for 4GB

thread_concurrency=4
thread_cache_size=128

myisam_sort_buffer_size=64M
skip-locking
server-id=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Open in new window

0
svetoslavm
Asked:
svetoslavm
  • 2
1 Solution
 
Tomas Helgi JohannssonCommented:
Hi!

I recommend using the mysqltuner tool which should be available in your linux repository
or here
https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner
http://rtcamp.com/tutorials/using-mysqltuner-to-optimize-mysql-configuration/

That little script is very useful and gives you recommended values based on the data and available memory. Sometimes though you will need to be careful as those values can cause the linux system to page out which is not what you want.

Regards,
    Tomas Helgi
0
 
svetoslavmAuthor Commented:
I've requested that this question be deleted for the following reason:

I specifically asked if the my configuration is optimal, no for links
0
 
Tomas Helgi JohannssonCommented:
Hi!

The best way to tweak mysql on a system is to run and use mysqltuner as that tool gives you an idea on how to tune the database based on the workload that has and is currently running on the computer.

Best regards,
    Tomas Helgi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now