Æí¸®ÇÑ È£½ºÆà ¹Ì¼ÒIDC

   
 
 
 

      1Â÷ ³×ÀÓ¼­¹ö :
      ns1.misoidc.com
      101.79.73.101

      2Â÷ ³×ÀÓ¼­¹ö :
      ns2.misoidc.com
      101.79.73.105

      ¾îÁ¦ : 304 ,¿À´Ã : 161
      Àüü : 1,151,207


     

 

 
ÀÛ¼ºÀÏ : 13-03-06 10:01
The 5 minute DBA: Default My.cnf File
 ±Û¾´ÀÌ : ½ÑÀ¥È£½ºÆÃ
Á¶È¸ : 27,352  

Because I was asked¡¦.What should I set my my.cnf parameters to? What are good default values? How much memory should I allocate to the db if I have X amount of ram? What is a good starting point for the mysql config files?

You¡¯re not really Googling for a my.cnf to use are you? You probably are, Shame on you! The best thing to do is to test before you make changes, and find the best configuration for your application. But your not going to are you? oh well I might as well accommodate you. I make no claims these will work for everyone. In fact if you hire me later on I may look at you funny after I analyze your system, and may call you funny names behind your back for using the wrong settings. Because there are a lot of people out their who are only database folks 5 minutes at a time (that¡¯s what these posts are about), they are probably going to stick the my-huge.cnf and go with that anyways. So why not throw out some common my.cnf¡¯s I use. Keep in mind, these are the most common, they may cause weird things to happen in some environments¡¦ that¡¯s why we always say test and benchmark before using. But if your googling for it, chances are your going to put it in anyways:)

And for those who spend more then 5 minutes a day as a DBA, please post your comments and make me look foolish for forgetting your favorite parameter. Remember these are generic starting points, for people who are not going to have a ton of time.

Alright here goes:

32 bit system
2GB of memory
Dedicated DB Box
All innodb tables
32 bit system
4GB of memory
Dedicated DB Box
All Innodb tables
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 64
table_cache = 64
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=16M
max_heap_table_size=16M
query_cache_size=64M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=3
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 64
table_cache = 64
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=16M
max_heap_table_size=16M
query_cache_size=64M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=3
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size =2048M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
32 bit system
8GB+ of memory
Dedicated DB Box
All Innodb tables
64 bit system
8GB of memory
Dedicated DB Box
All innodb tables
****** Go download a 64Bit OS. ****** [mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 128
table_cache = 256
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=2
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 5120M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
64 bit system
16GB of memory
Dedicated DB Box
All Innodb tables
64 bit system
32GB of memory
Dedicated DB Box
All Innodb tables
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 128
table_cache = 512
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=2
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 12288M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 12
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 256
table_cache = 1024
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=2
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size =24676M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 16

NOTE: If you change you log file size, you will get errors unless you move the old ones and allow innodb to recreate them ( do it with the DB down by the way )¡¦ once again I offer no warranty. You may also want to turn of swappiness to avoid swapping (http://www.bigdbahead.com/?p=101).



 
 

Total 78
¹øÈ£ Á¦   ¸ñ ±Û¾´ÀÌ ³¯Â¥ Á¶È¸
48 SSH ±âº»¸í·É¾î ÃÖ°í°ü¸®ÀÚ 06-22 39846
47 Alteon L4½ºÀ§Ä¡ configuration ÃʱâÈ­ ¹× Dump ÀýÂ÷ ½ÑÀ¥È£½ºÆà 11-28 36310
46 Force10 OS Æß¿þ¾î ¾÷±×·¹ÀÌµå ½ÑÀ¥È£½ºÆà 06-28 34742
45 °­·ÂÇÑ ¿ÀǼҽº ÆÄƼ¼Ç º¹±¸ ÇÁ·Î±×·¥ - TestDisk ½ÑÀ¥È£½ºÆà 11-01 33328
44 [CentOS] Tomcat 7.0.4 ¼³Ä¡ (¼öÁ¤1) ½ÑÀ¥È£½ºÆà 11-16 32799
43 CentOS 5.5 ±â¹Ý Oracle 10g ¼³Ä¡(2. ȯ°æ º¯¼ö ¼³Á¤) ½ÑÀ¥È£½ºÆà 07-06 31447
42 mysql Replicating ½ÑÀ¥È£½ºÆà 10-31 30304
41 mod_jk¸¦ ÀÌ¿ëÇÑ Apache2 + Tomcat5.5 ¿¬µ¿ ¹× °¡»óÈ£½ºÆà ½ÑÀ¥È£½ºÆà 11-17 29688
40 Subversion/Trac ¹é¾÷ ¹× º¹±¸ ½ÑÀ¥È£½ºÆà 11-30 29528
39 drbd sync ±úÁ³À»¶§ [solved] ½ÑÀ¥È£½ºÆà 06-28 29013
38 MS-SQL ¸®Çø®ÄÉÀÌ¼Ç (º´ÇÕº¹Á¦) Å×À̺í Ãß°¡ ¹æ¹ý ½ÑÀ¥È£½ºÆà 05-09 28188
37 ¿À¶óŬ DB »ý¼º ½ÑÀ¥È£½ºÆà 07-13 28100
36 ¿À¶óŬ °èÁ¤»ý¼º ½ÑÀ¥È£½ºÆà 11-02 27488
35 The 5 minute DBA: Default My.cnf File ½ÑÀ¥È£½ºÆà 03-06 27353
34 DRBD(Distributed Replicated Block Device) ¼³Ä¡ ¹× ¼³Á¤ ½ÑÀ¥È£½ºÆà 06-24 26922
 1  2  3  4  5  6