MySQL Performance Tuning mit Tuning-Primer.sh Script

Huschi · Nov 6, 2010

Der Ansatz ist doch schon fast richtig.
Du hast lediglich den Punkt von open_files_limit übersehen.

Bei 8GB Speicher kann man nicht viel verhunzen.

huschi.

kobold345 · Nov 6, 2010

wärst du so nett. mir zu verraten wie das aussehen söllte mit dem open_files?
ich habe da keine idee zu, alos was den wert dafür angeht.

open_files_limit 12.000 ?

wäre das korrekt als abschluss

query_cache_size = 32M
thread_cache = 32
table_cache = 5120
query_cache_limit = 64M
query_cache_size = 20M
open_files_limit= 12.000

Und thx für die super schnellen antworten hier

Huschi · Nov 6, 2010

Diesen Satz hast Du überlesen:

kobold345 said:
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.

Und jetzt nehme ich Dich noch an die Hand:
Du setzte die Werte ein, startest MySQL neu (ein Reload reicht hier nicht), wartest 2 Tage und startest nochmals Tuning-Primer. Falls er weitere Tipps ausspuckt, kannst Du die ebenfalls wieder umsetzten.

huschi.

distanzcheck · Nov 6, 2010

huschi said:
MOD: Full-Quote gekürzt!
Lösung: Halte den Apache im Rahmen des Hauptspeichers.
Und genau das regulierst Du mit MaxClients.

Was würdest Du hier empfehlen, also welchen Wert ?

lg Dirk

voodoo44 · Nov 6, 2010

Stell die Hälfte ein und schau, was passiert ....

Alternativ: Rechne es dir aus

michael24179 · Nov 19, 2010

Ich habe vor einigen Tagen mein Root server gestellt bekommen, und habe hiermit Probleme mit der Einstellung der MYSQL. Ich habe 16GB RAM + lighttpd und sobald meine SQL etwas zu tun bekommt, ist meine seite sehr langsam unterwegs.

Über tuning-primer.sh bekomme ich folgende aussage

Code:

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 3 out of 1591205 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See [url]http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html[/url]

WORKER THREADS
Current thread_cache_size = 256
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 125
Current threads_connected = 2
Historic max_used_connections = 2
The number of used connections is 1% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 11 M
Current InnoDB data space = 5 M
Current InnoDB buffer pool free = 56 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 59 M
Configured Max Per-thread Buffers : 93 M
Configured Max Global Buffers : 58 M
Configured Max Memory Limit : 151 M
Physical Memory : 15.70 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 137 M
Current key_buffer_size = 24 M
Key cache miss rate is 1 : 59
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 = 24 M
Current query_cache_used = 1 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 5.64 %
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 = 256 K
Current read_rnd_buffer_size = 128 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 = 4231 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_open_cache = 2048 tables
Current table_definition_cache = 256 tables
You have a total of 700 tables
You have 701 open tables.
The table_cache value seems to be fine
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 1078 temp tables, 9% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 14 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 1591460
Your table locking seems to be fine

Ich weis das sie fürchterlich ausschaut

. Was würde man mir empfehlen, um das ganze optimal zu nutzen

Newb · Nov 23, 2010

also bei mir schafft er es nicht die mysql.sock auszulesen:

Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found Sockets: /tmp/mysql.sock
Using: /tmp/mysql.sock

Huschi · Nov 24, 2010

Hallo Newb,
willkommen an Board.

Es geht nicht um den Socket, sondern um das Login. Er findet keine Login-Daten, bzw. die gefundenen sind fehlerhaft.
Schreib sie einfach in die ~/.my.cnf rein, oder lösche sie von dort. Dann fragt er nach.

huschi.

frogfire · Dec 5, 2010

Ich brauch hilfe beim Einstellen:

1.20+ GHz

2 GB Ram

Apache
MySql
Php

dämnächst TS 3 Server

tuning ausgabe:

Code:

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.1.49-1ubuntu8.1 x86_64

Uptime = 0 days 0 hrs 15 min 27 sec
Avg. qps = 2
Total Questions = 2481
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/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.000000 sec.
You have 0 out of 2502 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 2
Historic max_used_connections = 2
The number of used connections is 1% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 0 bytes
Current InnoDB data space = 0 bytes
Current InnoDB buffer pool free = 96 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 47 M
Configured Max Per-thread Buffers : 405 M
Configured Max Global Buffers : 42 M
Configured Max Memory Limit : 447 M
Physical Memory : 1.91 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 5 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 147
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 432 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 2.63 %
Current query_cache_min_res_unit = 4 K
Query Cache is 31 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
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 = 1024 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_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 230 tables
You have 64 open tables.
Current table_cache hit rate is 2%
, while 100% 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 = 16 M
Of 600 temp tables, 15% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 50 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 2757
Your table locking seems to be fine

Apache Config:

Code:

#
# Based upon the NCSA server configuration files originally by Rob McCool.
#
# This is the main Apache server configuration file.  It contains the
# configuration directives that give the server its instructions.
# See http://httpd.apache.org/docs/2.2/ for detailed information about
# the directives.
#
# Do NOT simply read the instructions in here without understanding
# what they do.  They're here only as hints or reminders.  If you are unsure
# consult the online docs. You have been warned.  
#
# The configuration directives are grouped into three basic sections:
#  1. Directives that control the operation of the Apache server process as a
#     whole (the 'global environment').
#  2. Directives that define the parameters of the 'main' or 'default' server,
#     which responds to requests that aren't handled by a virtual host.
#     These directives also provide default values for the settings
#     of all virtual hosts.
#  3. Settings for virtual hosts, which allow Web requests to be sent to
#     different IP addresses or hostnames and have them handled by the
#     same Apache server process.
#
# Configuration and logfile names: If the filenames you specify for many
# of the server's control files begin with "/" (or "drive:/" for Win32), the
# server will use that explicit path.  If the filenames do *not* begin
# with "/", the value of ServerRoot is prepended -- so "foo.log"
# with ServerRoot set to "/etc/apache2" will be interpreted by the
# server as "/etc/apache2/foo.log".
#

### Section 1: Global Environment
#
# The directives in this section affect the overall operation of Apache,
# such as the number of concurrent requests it can handle or where it
# can find its configuration files.
#

#
# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
#
# NOTE!  If you intend to place this on an NFS (or otherwise network)
# mounted filesystem then please read the LockFile documentation (available
# at <URL:http://httpd.apache.org/docs/2.2/mod/mpm_common.html#lockfile>);
# you will save yourself a lot of trouble.
#
# Do NOT add a slash at the end of the directory path.
#
#ServerRoot "/etc/apache2"

#
# The accept serialization lock file MUST BE STORED ON A LOCAL DISK.
#
LockFile ${APACHE_LOCK_DIR}/accept.lock

#
# PidFile: The file in which the server should record its process
# identification number when it starts.
# This needs to be set in /etc/apache2/envvars
#
PidFile ${APACHE_PID_FILE}

#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 300

#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive On

#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100

#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 1

##
## Server-Pool Size Regulation (MPM specific)
## 

# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
    StartServers          5
    MinSpareServers       5
    MaxSpareServers      10
    MaxClients           50
    MaxRequestsPerChild  8000
</IfModule>

# worker MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadLimit: ThreadsPerChild can be changed to this maximum value during a
#              graceful restart. ThreadLimit can only be changed by stopping
#              and starting Apache.
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_worker_module>
    StartServers          2
    MinSpareThreads      25
    MaxSpareThreads      75 
    ThreadLimit          64
    ThreadsPerChild      25
    MaxClients          150
    MaxRequestsPerChild   0
</IfModule>

# event MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_event_module>
    StartServers          2
    MaxClients          150
    MinSpareThreads      25
    MaxSpareThreads      75 
    ThreadLimit          64
    ThreadsPerChild      25
    MaxRequestsPerChild   0
</IfModule>

# These need to be set in /etc/apache2/envvars
User ${APACHE_RUN_USER}
Group ${APACHE_RUN_GROUP}

#
# AccessFileName: The name of the file to look for in each directory
# for additional configuration directives.  See also the AllowOverride
# directive.
#

AccessFileName .htaccess

#
# The following lines prevent .htaccess and .htpasswd files from being 
# viewed by Web clients. 
#
<Files ~ "^\.ht">
    Order allow,deny
    Deny from all
    Satisfy all
</Files>

#
# DefaultType is the default MIME type the server will use for a document
# if it cannot otherwise determine one, such as from filename extensions.
# If your server contains mostly text or HTML documents, "text/plain" is
# a good value.  If most of your content is binary, such as applications
# or images, you may want to use "application/octet-stream" instead to
# keep browsers from trying to display binary files as though they are
# text.
#
DefaultType text/plain


#
# HostnameLookups: Log the names of clients or just their IP addresses
# e.g., www.apache.org (on) or 204.62.129.132 (off).
# The default is off because it'd be overall better for the net if people
# had to knowingly turn this feature on, since enabling it means that
# each client request will result in AT LEAST one lookup request to the
# nameserver.
#
HostnameLookups Off

# ErrorLog: The location of the error log file.
# If you do not specify an ErrorLog directive within a <VirtualHost>
# container, error messages relating to that virtual host will be
# logged here.  If you *do* define an error logfile for a <VirtualHost>
# container, that host's errors will be logged there and not here.
#
ErrorLog ${APACHE_LOG_DIR}/error.log

#
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
#
LogLevel warn

# Include module configuration:
Include mods-enabled/*.load
Include mods-enabled/*.conf

# Include all the user configurations:
Include httpd.conf

# Include ports listing
Include ports.conf

#
# The following directives define some format nicknames for use with
# a CustomLog directive (see below).
# If you are behind a reverse proxy, you might want to change %h into %{X-Forwarded-For}i
#
LogFormat "%v:%p %h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" vhost_combined
LogFormat "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %O" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

# Include of directories ignores editors' and dpkg's backup files,
# see README.Debian for details.

# Include generic snippets of statements
Include conf.d/

# Include the virtual host configurations:
Include sites-enabled/

Mysql Config:

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
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user		= mysql
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
skip-external-locking
#
# 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		= 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#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.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /var/log/mysql/error.log

# 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.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * 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!
#
# * 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

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Huschi · Dec 5, 2010

Ich weiß, dieser Thread hat bereits 16 Seiten. Aber bitte bitte bitte macht Euch doch mal die Mühe und ließt ihn!
Dann käme sowas nicht:

frogfire said:
Uptime = 0 days 0 hrs 15 min 27 sec
Total Questions = 2481

Tipp 2:
Bemüht Euch die englischen Sätze in der Ausgabe zu verstehen. Damit lässt sich der Großteil einstellen.

huschi.

Real · Jan 22, 2011

Sorry, sind die Empfehlungen hier nur für Vserver oder für kleine Server mit wenig RAM und CPU, oder allgemein für alle MYSQL. ich habe hier die hälfte der Thread gelesen es sind Vorschläge nur für Vserver. Wollte mall die experten fragen ob das ganze für mein ROOT Server ein sinn macht.

# Intel® Xeon® X3440 Quadcore 64 Bit
# 12 GB DDR3-RAM
# 2x 1.500 GB SATA II-HDD
# Plesk 10.0.1
# Debian 5
# Linux 2.6.26-2-amd64
# Apache2
# ca. 40 Domains meist CMS

dann habe ich bei Google gefunden das da auch mit mysqltuner.pl MySQL-Optimierung möglich ist. Ist das auch so ähnlich wie der tuning-primer.sh oder regelt der alles alleine ohne was zu konfigurieren.

mfg

Huschi · Jan 23, 2011

Real said:
sind die Empfehlungen hier nur für Vserver oder für kleine Server mit wenig RAM und CPU, oder allgemein für alle MYSQL.

Alles drei und auch noch vier.

Dein Eindruck liegt wohl daran, dass Leute mit ausreichend Speicher bei der Umsetzung der Empfehlungen keine Probleme haben.

habe ich bei Google gefunden das da auch mit mysqltuner.pl MySQL-Optimierung möglich ist.

Ist eine Perl-Implementation des tuning-primer-Scripts.
Siehe auch huschi.net.

huschi.

Real · Jan 25, 2011

huschi.net kenne ich, Das ist die beste Howto oder Hilfe Seite den ich bis jetzt je gesehen hab. Viele Sachen habe ich von dort gelernt.
Danke für die Infos.

ich hab mir das angeguckt und installiert, das ist auch ein Scrip der Empfehlungen macht?

also so hat er mein Test Server getestet. was müsste ich hier jetzt genau machen.

---

Code:

# ./mysqltuner.pl

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny5
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 101M (Tables: 5470)
[--] Data in InnoDB tables: 3M (Tables: 178)
[!!] Total fragmented tables: 432

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 0h 55m 53s (3M q [7.496 qps], 138K conn, TX: 269M, RX: 570M)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 51.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 313.5M (15% of installed RAM)
[OK] Slow queries: 0% (11/3M)
[!!] Highest connection usage: 100%  (101/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/31.6M
[OK] Key buffer hit rate: 97.3% (5M cached / 153K reads)
[OK] Query cache efficiency: 71.4% (1M cached / 1M selects)
[!!] Query cache prunes per day: 26112
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 57K sorts)
[!!] Joins performed without indexes: 2408
[!!] Temporary tables created on disk: 34% (92K on disk / 267K total)
[OK] Thread cache hit rate: 98% (1K created / 138K connections)
[!!] Table cache hit rate: 0% (64 open / 313K opened)
[OK] Open file limit used: 12% (126/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 4.0M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 100)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 3M)

Huschi · Jan 26, 2011

Real said:
[OK] Operating on 32-bit architecture with less than 2GB RAM

Stimmt das denn? Oben redest Du von einem 64-bit Server mit 12 GB RAM.

Ansonsten bevorzuge ich hier das tuning-primer-script. Einfach weil die Ausgabe mehr Informationen über Deine Maschine enthält.

huschi.

Real · Jan 26, 2011

Guten Morgen,

ich habe eine Test Server in dem ich nur Sachen teste und übe, das oben genannte Server habe ich erst paar tagen, da trau ich mich nicht was zu probieren

Also die werte sind von eine Vserver 2 GB Maxx-RAM 512MB Garantie RAM.

MFG

Riddler · Jul 2, 2011

Hallo zusammen,

ich habe zwar die hiesigen Suchmaschinen angeschmissen, doch nirgendwo eine deutschsprachige Übersetzung gefunden. Teile verstehe ich zwar, doch bei weitem nicht alles. Kennt jemand da eine Empfehlung (ausser English-Nachhilfe)? ;-)

s24! · Jul 3, 2011

Ausgabe posten, Hilfe bekommen wäre zumindest eine der vielen Optionen.

Riddler · Jul 3, 2011

Hi,

dann will ich das mal tun:

Code:

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.0.51a-24+lenny5-log i486

Uptime = 78 days 1 hrs 56 min 40 sec
Avg. qps = 8
Total Questions = 54330637
Threads Connected = 1

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 enabled.
Current long_query_time = 2 sec.
You have 5275979 out of 54330658 that take longer than 2 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 11
The number of used connections is 11% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 15 M
Current InnoDB data space = 8 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 47 M
Configured Max Per-thread Buffers : 262 M
Configured Max Global Buffers : 19 M
Configured Max Memory Limit : 281 M
Physical Memory : 1.96 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 171 M
Current key_buffer_size = 1 M
Key cache miss rate is 1 : 29
Key buffer free ratio = 88 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 6 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 84.28 %
Current query_cache_min_res_unit = 4 K
However, 6693182 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
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 3657 queries where a join could not use an index properly
You have had 7 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 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 1736 tables
You have 64 open tables.
Current table_cache hit rate is 0%
, while 100% 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 973109 temp tables, 30% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 72 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 4120
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'.

Huschi · Jul 3, 2011

Riddler said:
Teile verstehe ich zwar, doch bei weitem nicht alles.

Und jetzt bitte noch dazu Deine Fragen spezifizieren. Denn woher sollen wir wissen, was Du davon verstanden hast und was nicht?

huschi.

Riddler · Jul 4, 2011

Hi,

vielleicht habe ich meine Frage falsch formuliert. Es ging mir darum, zu erfahren, ob es eine Internetseite gibt, die verschiedenen "Variablen" bereits ins Deutsche übersetzt hat.

Ich sitze gerade an den Übersetzungen. Jedoch kannst Du, wenn Du magst, bereits anhand meiner Auswertung ein paar Empfehlungen sprechen?

MySQL Performance Tuning mit Tuning-Primer.sh Script

Moderator

New Member

Moderator

Registered User

Registered User

New Member

New Member

Moderator

New Member

Moderator

New Member

Moderator

New Member

Moderator

New Member

New Member

Registered User

New Member

Moderator

New Member

We value your privacy