BMS-HOSxP Community
HOSxP => MySQL => ข้อความที่เริ่มโดย: siemens ที่ เมษายน 15, 2010, 09:39:16 AM
-
ลอง serach หา innodb_support_xa ไปเจอ link อันนี้เข้า เลยเอามาแปะไว้ครับเพื่อมีประโยชน์บ้าง
http://ftp.ku.ac.th/pub/mirror/mysql/doc/refman/5.0/en/innodb-parameters.html
บาง parameter อจ. MN ได้อธิบายไว้แล้วครับ
-
;D ;D ;D ขอบคุณครับ ;D ;D ;D
-
ขอบคุณมากครับพี่ ;D
-
จาก link ข้างต้น ไปเจอ script ที่เหมือนเขียนสำหรับแก้ my.cnf สำหรับ 5.1.x ดังนี้
#At file:///home/tsmith/m/bzr/bugteam/mrg-build/51/ based on revid:ignacio.galarza@stripped
2850 Timothy Smith 2009-04-01 [merge]
auto-merge -build into -bugteam
modified:
support-files/my-huge.cnf.sh
support-files/my-innodb-heavy-4G.cnf.sh
support-files/my-large.cnf.sh
support-files/my-medium.cnf.sh
support-files/my-small.cnf.sh
=== modified file 'support-files/my-huge.cnf.sh'
--- a/support-files/my-huge.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-huge.cnf.sh 2009-03-31 14:04:01 +0000
@@ -26,9 +26,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 384M
+key_buffer_size = 384M
max_allowed_packet = 1M
-table_cache = 512
+table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
@@ -46,9 +46,6 @@ thread_concurrency = 8
#
#skip-networking
-# Disable Federated by default
-skip-federated
-
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
@@ -143,14 +140,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 256M
-sort_buffer_size = 256M
-read_buffer = 2M
-write_buffer = 2M
-
[myisamchk]
-key_buffer = 256M
+key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
=== modified file 'support-files/my-innodb-heavy-4G.cnf.sh'
--- a/support-files/my-innodb-heavy-4G.cnf.sh 2008-10-03 12:24:19 +0000
+++ b/support-files/my-innodb-heavy-4G.cnf.sh 2009-03-31 14:04:01 +0000
@@ -80,7 +80,7 @@ max_connect_errors = 10
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
-table_cache = 2048
+table_open_cache = 2048
# Enable external file level locking. Enabled file locking will have a
# negative impact on performance, so only use it in case you have
@@ -167,7 +167,7 @@ ft_min_word_len = 4
# Table type which is used by default when creating new tables, if not
# specified differently during the CREATE TABLE statement.
-default_table_type = MYISAM
+default-storage-engine = MYISAM
# Thread stack size to use. This amount of memory is always reserved at
# connection time. MySQL itself usually needs no more than 64K of
@@ -211,10 +211,10 @@ binlog_format=mixed
# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
-# indexes well, if log_long_format is enabled. It is normally good idea
+# indexes well, if log_short_format is not enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
-log_slow_queries
+slow_query_log
# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
@@ -222,11 +222,6 @@ log_slow_queries
# currently measures time with second accuracy only).
long_query_time = 2
-# Log more information in the slow query log. Normally it is good to
-# have this turned on. This will enable logging of queries that are not
-# using indexes in addition to long running queries.
-log_long_format
-
# The directory used by MySQL for storing temporary files. For example,
# it is used to perform disk based large sorts, as well as for internal
# and explicit temporary tables. It might be good to put it on a
@@ -345,12 +340,6 @@ myisam_sort_buffer_size = 128M
# through the key cache (which is slower).
myisam_max_sort_file_size = 10G
-# If the temporary file used for fast index creation would be bigger
-# than using the key cache by the amount specified here, then prefer the
-# key cache method. This is mainly used to force long character keys in
-# large tables to use the slower key cache method to create the index.
-myisam_max_extra_sort_file_size = 10G
-
# If a table has more than one index, MyISAM can use more than one
# thread to repair them by sorting in parallel. This makes sense if you
# have multiple CPUs and plenty of memory.
@@ -359,7 +348,6 @@ myisam_repair_threads = 1
# Automatically check and repair not properly closed MyISAM tables.
myisam_recover
-
# *** INNODB Specific options ***
# Use this option if you have a MySQL server with InnoDB support enabled
@@ -482,14 +470,8 @@ no-auto-rehash
# Only allow UPDATEs and DELETEs that use keys.
#safe-updates
-[isamchk]
-key_buffer = 512M
-sort_buffer_size = 512M
-read_buffer = 8M
-write_buffer = 8M
-
[myisamchk]
-key_buffer = 512M
+key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
=== modified file 'support-files/my-large.cnf.sh'
--- a/support-files/my-large.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-large.cnf.sh 2009-03-31 14:04:01 +0000
@@ -26,9 +26,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 256M
+key_buffer_size = 256M
max_allowed_packet = 1M
-table_cache = 256
+table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
@@ -46,9 +46,6 @@ thread_concurrency = 8
#
#skip-networking
-# Disable Federated by default
-skip-federated
-
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
@@ -143,14 +140,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 128M
-sort_buffer_size = 128M
-read_buffer = 2M
-write_buffer = 2M
-
[myisamchk]
-key_buffer = 128M
+key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
=== modified file 'support-files/my-medium.cnf.sh'
--- a/support-files/my-medium.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-medium.cnf.sh 2009-03-31 14:04:01 +0000
@@ -27,9 +27,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 16M
+key_buffer_size = 16M
max_allowed_packet = 1M
-table_cache = 64
+table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
@@ -44,9 +44,6 @@ myisam_sort_buffer_size = 8M
#
#skip-networking
-# Disable Federated by default
-skip-federated
-
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
@@ -141,14 +138,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 20M
-sort_buffer_size = 20M
-read_buffer = 2M
-write_buffer = 2M
-
[myisamchk]
-key_buffer = 20M
+key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
=== modified file 'support-files/my-small.cnf.sh'
--- a/support-files/my-small.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-small.cnf.sh 2009-03-31 14:04:01 +0000
@@ -27,9 +27,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 16K
+key_buffer_size = 16K
max_allowed_packet = 1M
-table_cache = 4
+table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
@@ -74,12 +74,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 8M
-sort_buffer_size = 8M
-
[myisamchk]
-key_buffer = 8M
+key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
เลยลองเปิด my.cnf ของ server โรงพยาบาลดู พบว่ายังเป็น
table_cache = 1000
key_buffer = 500M
คำถามคือ จำเป็นต้องแก้ทันทีไหมครับ และหากผมแก้ด้วย notepad เวลานำเข้าใน /etc/my.cnf จะได้ไหมครับ (เพราะใช้ vi ไม่เป็น และตอนนี้ผมอยู่ที่บ้าน.............. :( )
-
จาก link ข้างต้น ไปเจอ script ที่เหมือนเขียนสำหรับแก้ my.cnf สำหรับ 5.1.x ดังนี้
#At file:///home/tsmith/m/bzr/bugteam/mrg-build/51/ based on revid:ignacio.galarza@stripped
2850 Timothy Smith 2009-04-01 [merge]
auto-merge -build into -bugteam
modified:
support-files/my-huge.cnf.sh
support-files/my-innodb-heavy-4G.cnf.sh
support-files/my-large.cnf.sh
support-files/my-medium.cnf.sh
support-files/my-small.cnf.sh
=== modified file 'support-files/my-huge.cnf.sh'
--- a/support-files/my-huge.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-huge.cnf.sh 2009-03-31 14:04:01 +0000
@@ -26,9 +26,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 384M
+key_buffer_size = 384M
max_allowed_packet = 1M
-table_cache = 512
+table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
@@ -46,9 +46,6 @@ thread_concurrency = 8
#
#skip-networking
-# Disable Federated by default
-skip-federated
-
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
@@ -143,14 +140,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 256M
-sort_buffer_size = 256M
-read_buffer = 2M
-write_buffer = 2M
-
[myisamchk]
-key_buffer = 256M
+key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
=== modified file 'support-files/my-innodb-heavy-4G.cnf.sh'
--- a/support-files/my-innodb-heavy-4G.cnf.sh 2008-10-03 12:24:19 +0000
+++ b/support-files/my-innodb-heavy-4G.cnf.sh 2009-03-31 14:04:01 +0000
@@ -80,7 +80,7 @@ max_connect_errors = 10
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
-table_cache = 2048
+table_open_cache = 2048
# Enable external file level locking. Enabled file locking will have a
# negative impact on performance, so only use it in case you have
@@ -167,7 +167,7 @@ ft_min_word_len = 4
# Table type which is used by default when creating new tables, if not
# specified differently during the CREATE TABLE statement.
-default_table_type = MYISAM
+default-storage-engine = MYISAM
# Thread stack size to use. This amount of memory is always reserved at
# connection time. MySQL itself usually needs no more than 64K of
@@ -211,10 +211,10 @@ binlog_format=mixed
# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
-# indexes well, if log_long_format is enabled. It is normally good idea
+# indexes well, if log_short_format is not enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
-log_slow_queries
+slow_query_log
# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
@@ -222,11 +222,6 @@ log_slow_queries
# currently measures time with second accuracy only).
long_query_time = 2
-# Log more information in the slow query log. Normally it is good to
-# have this turned on. This will enable logging of queries that are not
-# using indexes in addition to long running queries.
-log_long_format
-
# The directory used by MySQL for storing temporary files. For example,
# it is used to perform disk based large sorts, as well as for internal
# and explicit temporary tables. It might be good to put it on a
@@ -345,12 +340,6 @@ myisam_sort_buffer_size = 128M
# through the key cache (which is slower).
myisam_max_sort_file_size = 10G
-# If the temporary file used for fast index creation would be bigger
-# than using the key cache by the amount specified here, then prefer the
-# key cache method. This is mainly used to force long character keys in
-# large tables to use the slower key cache method to create the index.
-myisam_max_extra_sort_file_size = 10G
-
# If a table has more than one index, MyISAM can use more than one
# thread to repair them by sorting in parallel. This makes sense if you
# have multiple CPUs and plenty of memory.
@@ -359,7 +348,6 @@ myisam_repair_threads = 1
# Automatically check and repair not properly closed MyISAM tables.
myisam_recover
-
# *** INNODB Specific options ***
# Use this option if you have a MySQL server with InnoDB support enabled
@@ -482,14 +470,8 @@ no-auto-rehash
# Only allow UPDATEs and DELETEs that use keys.
#safe-updates
-[isamchk]
-key_buffer = 512M
-sort_buffer_size = 512M
-read_buffer = 8M
-write_buffer = 8M
-
[myisamchk]
-key_buffer = 512M
+key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
=== modified file 'support-files/my-large.cnf.sh'
--- a/support-files/my-large.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-large.cnf.sh 2009-03-31 14:04:01 +0000
@@ -26,9 +26,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 256M
+key_buffer_size = 256M
max_allowed_packet = 1M
-table_cache = 256
+table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
@@ -46,9 +46,6 @@ thread_concurrency = 8
#
#skip-networking
-# Disable Federated by default
-skip-federated
-
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
@@ -143,14 +140,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 128M
-sort_buffer_size = 128M
-read_buffer = 2M
-write_buffer = 2M
-
[myisamchk]
-key_buffer = 128M
+key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
=== modified file 'support-files/my-medium.cnf.sh'
--- a/support-files/my-medium.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-medium.cnf.sh 2009-03-31 14:04:01 +0000
@@ -27,9 +27,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 16M
+key_buffer_size = 16M
max_allowed_packet = 1M
-table_cache = 64
+table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
@@ -44,9 +44,6 @@ myisam_sort_buffer_size = 8M
#
#skip-networking
-# Disable Federated by default
-skip-federated
-
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
@@ -141,14 +138,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 20M
-sort_buffer_size = 20M
-read_buffer = 2M
-write_buffer = 2M
-
[myisamchk]
-key_buffer = 20M
+key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
=== modified file 'support-files/my-small.cnf.sh'
--- a/support-files/my-small.cnf.sh 2009-02-12 22:32:37 +0000
+++ b/support-files/my-small.cnf.sh 2009-03-31 14:04:01 +0000
@@ -27,9 +27,9 @@ socket = @MYSQL_UNIX_ADDR@
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
skip-locking
-key_buffer = 16K
+key_buffer_size = 16K
max_allowed_packet = 1M
-table_cache = 4
+table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
@@ -74,12 +74,8 @@ no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
-[isamchk]
-key_buffer = 8M
-sort_buffer_size = 8M
-
[myisamchk]
-key_buffer = 8M
+key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
เลยลองเปิด my.cnf ของ server โรงพยาบาลดู พบว่ายังเป็น
table_cache = 1000
key_buffer = 500M
คำถามคือ จำเป็นต้องแก้ทันทีไหมครับ และหากผมแก้ด้วย notepad เวลานำเข้าใน /etc/my.cnf จะได้ไหมครับ (เพราะใช้ vi ไม่เป็น และตอนนี้ผมอยู่ที่บ้าน.............. :( )
ใช้ตัวนี้ดีกว่าครับ editplus ครับ
ถ้าต้องการเอา my.cnf ไปแทนที่เลย
-
ขอบคุณ อ.อ๊อด ครับ................. ;D
-
;) ;) ;) ;) ;)
-
งานเข้าเลย................ :-\
stop mysql...
cp my_edit /etc/my.cnf
start mysql...
เปิด hosxp ไม่ได้ ฟ้อง database error ......database บางส่วน ---> myisam
เลยต้องวิ่งมาโรงพยาบาล ทำ database ใหม่จาก slave
คำถามคือปัญหานี้เกิดจากอะไรครับ ผมเจอมา2-3 ครั้งแล้ว......................... ???
-
งานเข้าเลย................ :-\
stop mysql...
cp my_edit /etc/my.cnf
start mysql...
เปิด hosxp ไม่ได้ ฟ้อง database error ......database บางส่วน ---> myisam
เลยต้องวิ่งมาโรงพยาบาล ทำ database ใหม่จาก slave
คำถามคือปัญหานี้เกิดจากอะไรครับ ผมเจอมา2-3 ครั้งแล้ว......................... ???
ยินดีกับประสบการณ์แก้ My.cnf แบบไม่รู้
ผมมักจะสอนว่าถ้ามีคำว่า log ห้ามแก้โดยเด็ดขาด ถ้าแก้ก็จะเจอแบบที่ท่าน siemens เจอ
ธรรมดา สำหรับผมที่ต้องแก้ Server ที่มีคนทำไว้ แต่เป็น My.cnf 512 M ทั้งหมดบางทีก้อต่ำกว่านั้น
ขนาดนี้ยัง.....อีก
-
ก้อคิดว่าแก้แค่ table_cache ---> table_open_cache กับ key_buffer ---> key_buffer_size แค่นี้เอง.............. :-[
เลยลง centos 4.7 ซะเลย..................... ;D
-
ก้อคิดว่าแก้แค่ table_cache ---> table_open_cache กับ key_buffer ---> key_buffer_size แค่นี้เอง.............. :-[
เลยลง centos 4.7 ซะเลย..................... ;D
ลองเพิ่มเข้าก่อน โดยค่าเดิมยังไม่ต้องทำอะไรเลย
-
ก้อคิดว่าแก้แค่ table_cache ---> table_open_cache กับ key_buffer ---> key_buffer_size แค่นี้เอง.............. :-[
เลยลง centos 4.7 ซะเลย..................... ;D
ลองเพิ่มเข้าก่อน โดยค่าเดิมยังไม่ต้องทำอะไรเลย
ก้อเพิ่มโดยไม่ได้แก้ค่าใดๆ เลยครับ............ ???
ตามนี้ครับ
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=tis620
[mysqld]
#innodb_force_recovery = 4
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
# You can adjust key_buffer to 10% off Physical memory (for innodb base engine)
# or adjust key_buffer to 60% off Physical memory (for myisam base engine)
key_buffer_size = 500M
max_allowed_packet = 1024M
table_open_cache = 1500
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 128M
thread_cache = 8
query_cache_size= 256M
thread_concurrency = 8
default-character-set=tis620
skip-name-resolve
innodb_file_per_table
skip-character-set-client-handshake
init_connect = 'SET NAMES tis620'
#max_heap_table_size = 32M
max_heap_table_size = 512M
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
# You can adjust innodb_buffer_pool_size to 70% off Physical memory (for innodb base engine)
# or adjust innodb_buffer_pool_size to 10% off Physical memory (for myisam base engine)
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_thread_concurrency = 0
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_doublewrite=0
innodb_support_xa = 0
#innodb_open_files = 900
innodb_open_files = 2000
innodb_locks_unsafe_for_binlog=1
join_buffer_size = 2M
concurrent_insert = 2
max_connections=300
innodb_adaptive_checkpoint = 1
innodb_io_capacity = 10000
innodb_write_io_threads = 16
innodb_read_io_threads = 16
[mysqldump]
quick
max_allowed_packet = 1024M
allow-keywords
[mysql]
no-auto-rehash
default-character-set=tis620
[isamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
-
ลองดู .err
-
ลองดู .err
ลองเปิดดูแล้ว แต่ลืม save ไว้ครับ ไปตอน format HDD แล้ว...................