Solved

Optimal mysql configuration for 2gb ram server

Posted on 2013-05-13
4
1,551 Views
Last Modified: 2013-11-19
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
Comment
Question by:svetoslavm
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 39163723
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
 

Author Comment

by:svetoslavm
ID: 39176364
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39176365
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now