?
Solved

Optimal mysql configuration for 2gb ram server

Posted on 2013-05-13
4
Medium Priority
?
1,869 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
3 Comments
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 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 26

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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

621 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