Posted by mmilano on October 8, 2009 at 10:34pm
Does anyone have a my.cnf example I could use as a starting point for a dedicated DB server with 4 processors and 16 GB of memory? The only other dedicated servers with these stats I have access to are running postgres.
Comments
my-huge.cnf
If you download the MySQL source tarball it has a couple of example configurations in it. Use the one called my-huge.cnf as a starting point.
have a look at the
have a look at the http://www.mysqlperformanceblog.com/
there is a lot of cool stuff, like testings and setups.
Stempel
Ankauf Mercedes
8 core, 16GB
Here are the relevant portions from the my.cnf on my 8 core/16GB dedicated DB server. They're probably not perfect or well-optimized, but we're seeing good performance and haven't had reason to tweak them further.
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
character-set-server = utf8
default-character-set = utf8
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr/
datadir = /var/lib/mysql
back_log = 50
max_connections = 400
max_connect_errors = 9999999
table_cache = 5000
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 128M
sort_buffer_size = 500K
join_buffer_size = 500K
thread_cache_size = 100
thread_concurrency = 16
query_cache_size = 512M
query_cache_limit = 8M
query_cache_min_res_unit = 2K
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 384M
log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /tmp
memlock
# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size = 12G
innodb_data_file_path = ibdata1:10G:autoextend
innodb_flush_method = O_DIRECT
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 20M
innodb_log_file_size = 800M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
Query cache...
We have a couple of servers with million+++ node Drupal installations, running with dual quad core and 8 to 16GB of memory. And the one thing we don't use, is query cache. We have experienced that from time to time, query cache will completely lock up the database. Query cache in mode 1 is a two edged sword... So be wary using it. Query cache really started to create trouble after we started testing Apache Solr on a 1.5 million node installation.
I restarted MySQL on one of the servers 11 hours ago. Here are some stats:
__ Key _________________________________________________________________
Buffer used 56.29M of 200.00M %Used: 28.15
Current 56.29M %Usage: 28.14
Write hit 66.79%
Read hit 99.91%
__ Questions ___________________________________________________________
Total 123.45M 574.3/s
DMS 121.44M 565.0/s %Total: 98.38
Com_ 1.67M 7.8/s 1.36
COM_QUIT 393.69k 1.8/s 0.32
-Unknown 64.66k 0.3/s 0.05
Slow (3) 615 0.0/s 0.00 %DMS: 0.00 Log: ON
DMS 121.44M 565.0/s 98.38
SELECT 118.97M 553.5/s 96.37 97.96
UPDATE 1.26M 5.8/s 1.02 1.03
INSERT 633.71k 2.9/s 0.51 0.52
DELETE 584.42k 2.7/s 0.47 0.48
REPLACE 1.97k 0.0/s 0.00 0.00
Com_ 1.67M 7.8/s 1.36
unlock_tabl 495.17k 2.3/s 0.40
lock_tables 495.17k 2.3/s 0.40
set_option 404.72k 1.9/s 0.33
__ SELECT and Sort _____________________________________________________
Scan 7.10M 33.1/s %SELECT: 5.97
Range 957.41k 4.5/s 0.80
Full join 61.48k 0.3/s 0.05
Range check 0 0/s 0.00
Full rng join 893 0.0/s 0.00
Sort scan 8.40M 39.1/s
Sort range 10.40M 48.4/s
Sort mrg pass 3.56k 0.0/s
And the configuration:
key_buffer = 200M
max_allowed_packet= 16M
thread_stack = 128K
thread_cache_size = 8
max_connections = 250
table_cache = 2K
long_query_time = 3
log_slow_queries = /var/log/mysql/mysql-slow.log
Erlend Stromsvik - erlend@nymedia.no - erlendstromsvik @twitter
Ny Media AS - http://www.nymedia.no
Query cache is locking db?
Hmm, can anyone confirm this?... Sounds strange, but plausible.
p.s. just ~6% of all queries are SELECTs?
drupal+me: jeweler portfolio
6 % - that might be the
6 % - that might be the reason. Every time you insert/update, query cache is deleted...
I believe you read the stats
I believe you read the stats wrong. What you are referring to are selects -with- sort.
SELECT 118.97M 553.5/s 96.37 97.96
96.37% of all operations are selects. 97.96% of all queries are selects.
As for query cache, it might work great in mode = 1 on a very static database with a low number of writes/updates. For any large sites with a large volume of updates/inserts it's a real show stopper.
Erlend Stromsvik - erlend@nymedia.no - erlendstromsvik @twitter
Ny Media AS - http://www.nymedia.no
And from these stats it looks
And from these stats it looks like you are using MyISAM and not InnoDB (or something more esoteric). I would guess that you'd have a lot of issues with table locks if only 6% of queries are selects.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Haven't anyone here used
Haven't anyone here used mysqlreport before? :)
"Houston, we have a problem"...
Erlend Stromsvik - erlend@nymedia.no - erlendstromsvik @twitter
Ny Media AS - http://www.nymedia.no