Solved

Optimal mysql configuration for 2gb ram server

Posted on 2013-05-13
4
1,520 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

Title # Comments Views Activity
How to remove numeric and alpha from an alphanumeric field? 5 71
ODBC Question 3 27
PHP: concatenate query 12 35
constraint check 2 0
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This video teaches users how to migrate an existing Wordpress website to a new domain.
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.

911 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

18 Experts available now in Live!

Get 1:1 Help Now