Solved

Optimal mysql configuration for 2gb ram server

Posted on 2013-05-13
4
1,663 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

726 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