[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: can anyone tell me the best my.cnf Configuration for dedicated MySQL DB server
From:       Reindl Harald <h.reindl () thelounge ! net>
Date:       2012-02-20 7:42:44
Message-ID: 4F41F974.2070206 () thelounge ! net
[Download RAW message or body]


Am 20.02.2012 06:57, schrieb Nayan Darekar:
> hi members,
> 
> I want install, configure my 8GB Ram 4 core CPU hardware server for
> dedicated MySQL  DB Server with stable version of MySQL. So which version i
> should use and can anyone help me for best my.cnf Configuration.

there does no best exist and will never be!

* how much memory?
* what other processes are running on the machine?
* how much RAM will they cosume?

this is the base to calculate how much memory
is available for mysqld

so and now you need a application profile

* myisam / innodv / both
* many parallel connections
* how many different and how large results do you expect

______________________________________

this is one of TEN different production configs here
but be careful - with low memory it will blow away your
machine and "myisam_use_mmap" will cause instability
before MySQL 5.5.20

[--] Data in MyISAM tables: 635M (Tables: 4376)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 314

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 12h 1m 2s (20M q [31.984 qps], 954K conn, TX: 37B, RX: 4B)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 2.4G global + 3.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 3.0G (37% of installed RAM)
[OK] Slow queries: 0% (5/20M)
[OK] Highest usage of available connections: 13% (27/200)
[OK] Key buffer size / total MyISAM indexes: 256.0M/130.8M
[OK] Key buffer hit rate: 99.8% (169M cached / 268K reads)
[OK] Query cache efficiency: 88.8% (14M cached / 16M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 175K sorts)
[!!] Joins performed without indexes: 3777
[OK] Temporary tables created on disk: 25% (61K on disk / 245K total)
[OK] Thread cache hit rate: 99% (27 created / 954K connections)
[OK] Table cache hit rate: 25% (4K open / 19K opened)
[OK] Open file limit used: 30% (9K/30K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

----------------------------

socket                                  = /var/lib/mysql/mysql.sock
character-set-server                    = latin1
collation-server                        = latin1_german1_ci
default-time-zone                       = "Europe/Vienna"
default-storage-engine                  = myisam
lower_case_table_names                  = 1
port                                    = 3306
old_passwords                           = 0
local-infile                            = 0
thread_concurrency                      = 16

delay-key-write                         = ALL
concurrent_insert                       = 2
open-files-limit                        = 20000
myisam-recover                          = "FORCE"
myisam_use_mmap                         = 1

wait_timeout                            = 300
interactive_timeout                     = 300

max_allowed_packet                      = 200M
max_connections                         = 200
max_tmp_tables                          = 150
max_connect_errors                      = 250
max_delayed_threads                     = 32

flush_time                              = 0

query_cache_limit                       = 220K
query_cache_min_res_unit                = 2K
query_cache_size                        = 1536M
query_cache_type                        = 1

table_cache                             = 15000
thread_cache                            = 200
table_definition_cache                  = 768

tmp_table_size                          = 640M
max_heap_table_size                     = 640M

key_buffer_size                         = 256M
sort_buffer_size                        = 512K
myisam_sort_buffer_size                 = 5M
join_buffer_size                        = 2M
preload_buffer_size                     = 256K
read_buffer_size                        = 256K
read_rnd_buffer_size                    = 256K

large-pages
low-priority-updates
safe-user-create
skip-federated
skip-innodb
skip-name-resolve
skip-partition
skip-archive
skip-blackhole
skip-symbolic-links
slave_compressed_protocol
______________________________________


["signature.asc" (application/pgp-signature)]

[prev in list] [next in list] [prev in thread] [next in thread]