MySQL Forums
Forum List  »  Performance

MySQL server configuration for a 8 cpu+20gb RAM server
Posted by: josé renato milanez
Date: May 13, 2010 10:17AM

Hello friends, how are you?

I have a MySQL server with 3064 databases and growing every week. I want to optimize the server to have max performance for my web systems. This server is only for MySQL and don't have any apache/php and other internet services on it.

The server configuration is:
============================

Processors: 2
Model: Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
Memory: 20 Gbytes
Hard disk: 146GB SAS 15K RPM in a RAID Mirror config under a IBM ServeRAID 8k controller.

The system configuration is:
============================

OS: Mandriva Linux 2009.1 x64
Kernel: 2.6.29.6-server-3mnb (SMP)
Filesystem: 4GB of SWAP and the rest is ReiserFS (/)
MySQL version: mysql Ver 14.14 Distrib 5.1.42, for mandriva-linux-gnu (x86_64) using readline 6.0

My databases are:
=================

Storage engines: MyISAM and INNODB
Number of databases: 3064
Disk space usage: 6,3 GB

My MySQL configurations are:
============================

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 16

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

A complete information about my MySQL server (from phpMyAdmin):
===============================================================

auto increment increment 1
auto increment offset 1
autocommit ON
automatic sp privileges ON
back log 50
basedir /
big tables OFF
binlog cache size 32,768
binlog format STATEMENT
bulk insert buffer size 8,388,608
character set client utf8
(Valor global) latin1
character set connection utf8
(Valor global) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Valor global) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_general_ci
(Valor global) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
completion type 0
concurrent insert 1
connect timeout 10
datadir /var/lib/mysql/
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
div precision increment 4
engine condition pushdown ON
error count 0
event scheduler OFF
expire logs days 0
flush OFF
flush time 0
foreign key checks ON
ft boolean syntax + -><()~*:""&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
general log OFF
general log file /var/lib/mysql/bdead.log
group concat max len 1,024
have community features YES
have compress YES
have crypt YES
have csv YES
have dynamic loading YES
have geometry YES
have innodb YES
have ndbcluster NO
have openssl DISABLED
have partitioning NO
have query cache YES
have rtree keys YES
have ssl DISABLED
have symlink YES
hostname bdead
identity 0
ignore builtin innodb OFF
init connect
init file
init slave
innodb adaptive hash index ON
innodb additional mem pool size 20,971,520
innodb autoextend increment 8
innodb autoinc lock mode 1
innodb buffer pool size 1,073,741,824
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir /var/lib/mysql/
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table OFF
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log buffer size 8,388,608
innodb log file size 5,242,880
innodb log files in group 2
innodb log group home dir /var/lib/mysql/
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb rollback on timeout OFF
innodb stats on metadata ON
innodb support xa ON
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 8
innodb thread sleep delay 10,000
innodb use legacy cardinality algorithm ON
insert id 0
interactive timeout 28,800
join buffer size 131,072
keep files on create OFF
key buffer size 402,653,184
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language /usr/share/mysql/english/
large files support ON
large page size 0
large pages OFF
last insert id 0
lc time names en_US
license GPL
local infile ON
locked in memory OFF
log OFF
log bin ON
log bin trust function creators OFF
log bin trust routine creators OFF
log error
log output FILE
log queries not using indexes OFF
log slave updates OFF
log slow queries OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 1,048,576
max binlog cache size 18446744073709547520
max binlog size 1,073,741,824
max connect errors 10
max connections 151
max delayed threads 20
max error count 64
max heap table size 16,777,216
max insert delayed threads 20
max join size 18446744073709551615
max length for sort data 1,024
max prepared stmt count 16,382
max relay log size 0
max seeks for key 18446744073709551615
max sort length 1,024
max sp recursion depth 0
max tmp tables 32
max user connections 0
max write lock count 18446744073709551615
min examined row limit 0
multi range count 256
myisam data pointer size 6
myisam max sort file size 9223372036853727232
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 67,108,864
myisam stats method nulls_unequal
myisam use mmap OFF
net buffer length 16,384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old OFF
old alter table OFF
old passwords OFF
open files limit 1,024
optimizer prune level 1
optimizer search depth 62
optimizer switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
pid file /var/lib/mysql/bdead.pid
plugin dir /usr/lib64/mysql/plugin
port 3,306
preload buffer size 32,768
profiling OFF
profiling history size 15
protocol version 10
pseudo thread id 103,861
(Valor global) 0
query alloc block size 8,192
query cache limit 1,048,576
query cache min res unit 4,096
query cache size 33,554,432
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
rand seed1
rand seed2
range alloc block size 4,096
read buffer size 2,097,152
read only OFF
read rnd buffer size 8,388,608
relay log
relay log index
relay log info file relay-log.info
relay log purge ON
relay log space limit 0
report host
report password
report port 3,306
report user
rpl recovery rank 0
secure auth OFF
secure file priv
server id 1
skip external locking ON
skip networking OFF
skip show database OFF
slave compressed protocol OFF
slave exec mode STRICT
slave load tmpdir /var/tmp
slave net timeout 3,600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
slow query log OFF
slow query log file /var/lib/mysql/bdead-slow.log
socket /var/lib/mysql/mysql.sock
sort buffer size 2,097,152
sql auto is null ON
sql big selects ON
sql big tables OFF
sql buffer result OFF
sql log bin ON
sql log off OFF
sql log update ON
sql low priority updates OFF
sql max join size 18446744073709551615
sql mode
sql notes ON
sql quote show create ON
sql safe updates OFF
sql select limit 18446744073709551615
sql slave skip counter
sql warnings OFF
ssl ca
ssl capath
ssl cert
ssl cipher
ssl key
storage engine MyISAM
sync binlog 0
sync frm ON
system time zone BRT
table definition cache 256
table lock wait timeout 50
table open cache 431
table type MyISAM
thread cache size 8
thread handling one-thread-per-connection
thread stack 262,144
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
timestamp 1,273,768,071
tmp table size 16,777,216
tmpdir /var/tmp
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
unique checks ON
updatable views with limit YES
version 5.1.42-log
version comment Mandriva Linux - MySQL Standard Edition (GPL)
version compile machine x86_64
version compile os mandriva-linux-gnu
wait timeout 28,800
warning count 0

If somebody can analize these information and help me to improve performance, please contribute.

Thanks in advice.

Jose Renato Milanez
Brazil

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL server configuration for a 8 cpu+20gb RAM server
15794
May 13, 2010 10:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.