SohnDesBetons
Registered User
Hallo
Ich hab viel über Tuning-Primer.sh gelesen und nun hab ich es selber probiert und bräuchte da ein bisschen hilfe , wäre lieb wenn mir da einer helfen könnte.
Die my.cnf richtig einzustellen.
System Info
Prozessor: AMD Opteron 1352
Anzahl CPUs: QuadCore
Taktfrequenz: 4x 2.1 GHz
RAM: 4GB DDR2 ECC (667 MHz)
Betriebssystem: Debian
Festplatte: 2x 500GB
my.cnf
tuning-primer.sh Info
Für jede hilfe ein ganz großes danke!
Grüße
SohnDesBetons
Ich hab viel über Tuning-Primer.sh gelesen und nun hab ich es selber probiert und bräuchte da ein bisschen hilfe , wäre lieb wenn mir da einer helfen könnte.
Die my.cnf richtig einzustellen.
System Info
Prozessor: AMD Opteron 1352
Anzahl CPUs: QuadCore
Taktfrequenz: 4x 2.1 GHz
RAM: 4GB DDR2 ECC (667 MHz)
Betriebssystem: Debian
Festplatte: 2x 500GB
my.cnf
Code:
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
#skip-name-resolve
#set-variable = wait_timeout=60
#set-variable = interactive_timeout=120
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
max_connections = 2500
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
#server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1
#
# * IMPORTANT: Additional settings that can override those from this file!
#
!includedir /etc/mysql/conf.d/tuning-primer.sh Info
Code:
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.32-Debian_7etch8-log i486
Uptime = 126 days 23 hrs 22 min 59 sec
Avg. qps = 15
Total Questions = 168973328
Threads Connected = 10
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 4997 out of 168973349 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.
BINARY UPDATE LOG
The binary update log is enabled
Binlog sync is not enabled, you could loose binlog records during a server crash
WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 0
Current threads_per_sec = 2
Historic threads_per_sec = 0
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size
MAX CONNECTIONS
Current max_connections = 2500
Current threads_connected = 12
Historic max_used_connections = 1077
The number of used connections is 43% of the configured maximum.
Your max_connections variable seems to be fine.
MEMORY USAGE
Max Memory Ever Allocated : 2.83 G
Configured Max Per-thread Buffers : 6.48 G
Configured Max Global Buffers : 42 M
Configured Max Memory Limit : 6.52 G
Physical Memory : 3.21 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 495 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 10
Key buffer free ratio = 0 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.
QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 1 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 10.48 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
OPEN FILES LIMIT
Current open_files_limit = 12500 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 64 tables
You have a total of 31 tables
You have 63 open tables.
Current table_cache hit rate is 0%, while 98% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 15364661 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 86 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 324
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.Für jede hilfe ein ganz großes danke!
Grüße
SohnDesBetons
