MySQL Server Tweaking Basics

BartVB

Bokt.nl
Joined
Oct 20, 2004
Messages
728
How big is this table? What happens if you don't update the record after returning it to the user? Why did you comment out the thread concurrency setting? Do you need a binlog? How big is this table? How many queries per second are we talking about?

I've switched all my tables to either InnoDB or Memory, MyISAM is a tiny bit faster when you are mainly reading but you're writing a lot to this table too. InnoDB is quite a bit smarter/faster when it comes to workloads with a lot of writing.
 

pavemen

Adherent
Joined
May 21, 2010
Messages
380
i did not read the entire thread, but i found that the biggest performance gain for large sites on dedicated boxes was to have a second HDD and move mysql tables/paths to the second drive. let the OS/apache run on the primary drive and mysql on the secondary.

increased responsiveness quite a bit.
 

echipvina

Neophyte
Joined
Jun 9, 2010
Messages
1
Hello my dedicated
Dual Intel e5620
Powered by Intel Processors

* 8 cores @ 2.4ghz (16 with HT) CPU
* 12gb ram
* 80G SAS
*DA
*WP
This is my file my.cnf
[mysqld]
user=mysql
key_buffer=1024M
max_user_connections=700
max_connections=700
max_connect_errors = 10
socket = /var/lib/mysql/mysql.sock
table_cache = 2048
#open_files_limit = 8192
join_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 8M
tmp_table_size=512M
read_rnd_buffer_size=16M
max_heap_table_size=256M
#myisam_sort_buffer_size=256M
thread_cache_size=512
#thread_cache=32
query_cache_type=1
query_cache_limit=1024M
query_cache_size=1024M
thread_concurrency = 16
wait_timeout = 10
connect_timeout = 10
interactive_timeout = 10
long_query_time=2
log-slow-queries = /var/log/mysqlslowqueries.log
log-queries-not-using-indexes
max_allowed_packet=16M
skip-innodb
[myisamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 32M
write_buffer =32M

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
Please help me optimizing mySQL server
 

kratigupta

Neophyte
Joined
Oct 28, 2010
Messages
1
This is a basic guide to understanding what the directives in your my.cnf mean, and what they do. We'll also try to give some general advise to help you get the most out of these settings. We will not cover every directive, only those that can generally be changed to give better performance.

Here is an exerpt of a my.cnf we run on a dual xeon with 2 GB's of ram, this is a shared hosting machine that runs MySQL and web, so all memory is not allocated to MySQL.

Code:
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=900
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
#key_buffer=16M
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=1536
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
#log-bin
server-id=1
Query caching was added as of MySQL version 4, the following three directives will greatly enhance mysql server performance.

query_cache_limit=1M
query_cache_size=32M
query_cache_type=1

Query caching is a server wide variable, so set these generous. I have found the above levels are generally best if you server has at least 512 ram. If you run a server just for DBs with a lot of ram, you can up these quite a bit, like 2m limit and a 64+M cache size.

The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys. (Most do)

key_buffer=150M

The next set of buffers are at a per client level. It is important to play around with these and get them just right for your machine. With the setting below, every active mysql client will have close to 3 MB's in buffers. So 100 clients = almost 300 MB. Giving too much to these buffers will be worse than giving too little. Nothing kills a server quite like memory swapping will.

sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K

The following directive should be set to 2X the number of processors in your machine for best performance.

thread_concurrency=2

Heres a few example configurations for servers running MySQL and web for common memory sizes. These are not perfect, but good starting points.

Server with 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

For servers with 1 GB ram:

thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2

We hope you have found this TAZ Tutorial helpfull :)
HI ,

My database server has 4 G B of RAM , so whether I should double the current settings or I can go for the same and i m using MY SQL 5.1.51 version .
 

Smith0011

Neophyte
Joined
Dec 23, 2010
Messages
1
This is a basic guide to understanding what the directives in your my.cnf mean, and what they do. We'll also try to give some general advise to help you get the most out of these settings. We will not cover every directive, only those that can generally be changed to give better performance.

Here is an exerpt of a my.cnf we run on a dual xeon with 2 GB's of ram, this is a shared hosting machine that runs MySQL and web, so all memory is not allocated to MySQL.

Code:
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=900
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
#key_buffer=16M
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=1536
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
#log-bin
server-id=1
Query caching was added as of MySQL version 4, the following three directives will greatly enhance mysql server performance.

query_cache_limit=1M
query_cache_size=32M
query_cache_type=1

Query caching is a server wide variable, so set these generous. I have found the above levels are generally best if you server has at least 512 ram. If you run a server just for DBs with a lot of ram, you can up these quite a bit, like 2m limit and a 64+M cache size.

The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys. (Most do)

key_buffer=150M

The next set of buffers are at a per client level. It is important to play around with these and get them just right for, remove duplicates your machine. With the setting below, every active mysql client will have close to 3 MB's in buffers. So 100 clients = almost 300 MB. Giving too much to these buffers will be worse than giving too little. Nothing kills a server quite like memory swapping will.

sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K

The following directive should be set to 2X the number of processors in your machine for best performance.

thread_concurrency=2

Heres a few example configurations for servers running MySQL and web for common memory sizes. These are not perfect, but good starting points.

Server with 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

For servers with 1 GB ram:

thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2

We hope you have found this TAZ Tutorial helpfull :)
Really Its Amazing To Hear this thanks A Lot !!!
 

BHH

Enthusiast
Joined
Feb 1, 2010
Messages
196
Just to update this tutorial a bit, thread_concurrency only works on Solaris. It does not work on linux so there's no point in adding it.
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking

^ the above can also be removed.
 

Invisible

Neophyte
Joined
Jan 28, 2008
Messages
9
My server is 12GB RAM and would like to get someone to tweak it accordingly...

Thanks for the tip anyway
 

jackdorsey

Neophyte
Joined
Jun 28, 2013
Messages
1
Quote:
Originally Posted by PerfectSQL View Post
This is a basic guide to understanding what the directives in your my.cnf mean, and what they do. We'll also try to give some general advise to help you get the most out of these settings. We will not cover every directive, only those that can generally be changed to give better performance.

Here is an exerpt of a my.cnf we run on a dual xeon with 2 GB's of ram, this is a shared hosting machine that runs MySQL and web, so all memory is not allocated to MySQL.

Code:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=900
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
#key_buffer=16M
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=1536
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
#log-bin
server-id=1

Query caching was added as of MySQL version 4, the following three directives will greatly enhance mysql server performance.

query_cache_limit=1M
query_cache_size=32M
query_cache_type=1

Query caching is a server wide variable, so set these generous. I have found the above levels are generally best if you server has at least 512 ram. If you run a server just for DBs with a lot of ram, you can up these quite a bit, like 2m limit and a 64+M cache size.

The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys. (Most do)

key_buffer=150M

The next set of buffers are at a per client level. It is important to play around with these and get them just right for, remove duplicates your machine. With the setting below, every active mysql client will have close to 3 MB's in buffers. So 100 clients = almost 300 MB. Giving too much to these buffers will be worse than giving too little. Nothing kills a server quite like memory swapping will.

sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K

The following duplicate finder directive should be set to 2X the number of processors in your machine for best performance.

thread_concurrency=2

Heres a few example configurations for servers running MySQL and web for common memory sizes. These are not perfect, but good starting points.

Server with 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

For servers with 1 GB ram:

thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2

We hope you have found this TAZ Tutorial helpfull
Really Its Amazing To Hear this thanks A Lot !!!

Thank you this is exactly what I was looking for too!
 

simdol8080

Aspirant
Joined
Feb 9, 2014
Messages
12
Also,

You can't forget about Percona MySQL server >;)
It's a great replacement for MySQL server (I see huge improvement in performance)
 

Dewlance

Aspirant
Joined
Jul 28, 2015
Messages
17
Oh, Very nice.

I never use this settings, It can work with normal cPanel without affecting server performance? Sometimes I face MySQL related issue like crash or heavy load and wanted to set some limit.

Can you post simple config for limiting resource usage? (Your configuration is to much big)
 

I A 1

Enthusiast
Joined
Jun 7, 2015
Messages
134
Are the settings in first post still applicable as is? It is an old post from 2005.

I have VPS with 2 CPU, 4 GB RAM and this is my.cnf file:
Code:
[mysqld]
bind-address = ::
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=64M
wait_timeout=500
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

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

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
/etc/my.cnf.d has 3 files:
client.cnf mysql-clients.cnf server.cnf

All three files do not have any settings. They just have some labels in them with blanks underneath.
 

Solidus

Stupid machines!
Joined
Jan 23, 2012
Messages
605
There's a lot of optimization to be done there. I'll post my config file shortly.
 

Niktator

Aspirant
Joined
Oct 30, 2013
Messages
48
It is really hard to just use some random config file. Some still use myisam, most innodb. Having peaks of 400 users is a different topic then having 25000 users online at the same time. Is the server doing http and mysql? Is it split? Are there db slaves?

There is no final config.
 
Top