MySQL Performance Tuning mit Tuning-Primer.sh Script

@vampsm
Hast Du meine Antwort auf Deinen Request von oben:
1. eigentlich gelesen?
2. und verstanden?

Wenn 1. Nein: dann nachholen!
Wenn 1. Ja: dann bitte 2. nachholen.
Wenn 2. dauerhaft Nein: Konkrete Nachfrage stellen.

huschi.
 
Hallo,

Die Optimierung meines Sql-servers bereitet mir momentan Probleme, zumindest seit der Root gewechselt wurde. Nach vielen schlaflosen Nächten poste Ich hier mal meine .conf.

Zum Root:

Intel Atom mit Hyper Threating
2 x 1,6 Ghz
2 GB Ram
OS: Debian lenny mit Plesk

Apache conf

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 "/var/log/apache2/foo.log"
# with ServerRoot set to "" will be interpreted by the
# server as "//var/log/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.1/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.
#
#<IfModule !mpm_winnt.c>
#<IfModule !mpm_netware.c>
LockFile /var/lock/apache2/accept.lock
#</IfModule>
#</IfModule>

#
# 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 30

#
# 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 150

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

##
## 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          100
    MaxRequestsPerChild   0
</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
# 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
    MaxClients          100
    MinSpareThreads      25
    MaxSpareThreads      75 
    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
</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 /var/log/apache2/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 /etc/apache2/mods-enabled/*.load
Include /etc/apache2/mods-enabled/*.conf

# Include all the user configurations:
Include /etc/apache2/httpd.conf

# Include ports listing
Include /etc/apache2/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 %b \"%{Referer}i\" \"%{User-Agent}i\"" vhost_combined
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

#
# Define an access log for VirtualHosts that don't define their own logfile
CustomLog /var/log/apache2/other_vhosts_access.log vhost_combined

#
# Customizable error responses come in three flavors:
# 1) plain text 2) local redirects 3) external redirects
#
# Some examples:
#ErrorDocument 500 "The server made a boo boo."
#ErrorDocument 404 /missing.html
#ErrorDocument 404 "/cgi-bin/missing_handler.pl"
#ErrorDocument 402 http://www.example.com/subscription_info.html
#

#
# Putting this all together, we can internationalize error responses.
#
# We use Alias to redirect any /error/HTTP_<error>.html.var response to
# our collection of by-error message multi-language collections.  We use 
# includes to substitute the appropriate text.
#
# You can modify the messages' appearance without changing any of the
# default HTTP_<error>.html.var files by adding the line:
#
#   Alias /error/include/ "/your/include/path/"
#
# which allows you to create your own set of files by starting with the
# /usr/share/apache2/error/include/ files and copying them to /your/include/path/, 
# even on a per-VirtualHost basis.  The default include files will display
# your Apache version number and your ServerAdmin email address regardless
# of the setting of ServerSignature.
#
# The internationalized error documents require mod_alias, mod_include
# and mod_negotiation.  To activate them, uncomment the following 30 lines.

#    Alias /error/ "/usr/share/apache2/error/"
#
#    <Directory "/usr/share/apache2/error">
#        AllowOverride None
#        Options IncludesNoExec
#        AddOutputFilter Includes html
#        AddHandler type-map var
#        Order allow,deny
#        Allow from all
#        LanguagePriority en cs de es fr it nl sv pt-br ro
#        ForceLanguagePriority Prefer Fallback
#    </Directory>
#
#    ErrorDocument 400 /error/HTTP_BAD_REQUEST.html.var
#    ErrorDocument 401 /error/HTTP_UNAUTHORIZED.html.var
#    ErrorDocument 403 /error/HTTP_FORBIDDEN.html.var
#    ErrorDocument 404 /error/HTTP_NOT_FOUND.html.var
#    ErrorDocument 405 /error/HTTP_METHOD_NOT_ALLOWED.html.var
#    ErrorDocument 408 /error/HTTP_REQUEST_TIME_OUT.html.var
#    ErrorDocument 410 /error/HTTP_GONE.html.var
#    ErrorDocument 411 /error/HTTP_LENGTH_REQUIRED.html.var
#    ErrorDocument 412 /error/HTTP_PRECONDITION_FAILED.html.var
#    ErrorDocument 413 /error/HTTP_REQUEST_ENTITY_TOO_LARGE.html.var
#    ErrorDocument 414 /error/HTTP_REQUEST_URI_TOO_LARGE.html.var
#    ErrorDocument 415 /error/HTTP_UNSUPPORTED_MEDIA_TYPE.html.var
#    ErrorDocument 500 /error/HTTP_INTERNAL_SERVER_ERROR.html.var
#    ErrorDocument 501 /error/HTTP_NOT_IMPLEMENTED.html.var
#    ErrorDocument 502 /error/HTTP_BAD_GATEWAY.html.var
#    ErrorDocument 503 /error/HTTP_SERVICE_UNAVAILABLE.html.var
#    ErrorDocument 506 /error/HTTP_VARIANT_ALSO_VARIES.html.var



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

# Include generic snippets of statements
Include /etc/apache2/conf.d/

# Include the virtual host configurations:
Include /etc/apache2/sites-enabled/
AddOutputFilter INCLUDES .shtml
AddType text/html .shtml
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

my.conf

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
#
# 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
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# 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!
#
# * InnoDB plugin
# As of MySQL 5.1.38, the InnoDB plugin from Oracle is included in the MySQL source code.
# It has many improvements and better performances than the built-in InnoDB storage engine.
# Please read http://www.innodb.com/products/innodb_plugin/ for more information.
# Uncommenting the two following lines to use the InnoDB plugin.
#ignore_builtin_innodb
#plugin-load=innodb=ha_innodb_plugin.so
#
# * 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/

tuning-primer.sh

Code: 
n112h143:/usr/bin# tuning-primer.sh

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

MySQL Version 5.1.51-0.dotdeb.1 i486

Uptime = 1 days 22 hrs 57 min 23 sec
Avg. qps = 0
Total Questions = 134530
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 2 out of 134551 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 = 7
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 = 20
The number of used connections is 13% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 3 M
Current InnoDB data space = 3 M
Current InnoDB buffer pool free = 13 %
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 : 95 M
Configured Max Per-thread Buffers : 405 M
Configured Max Global Buffers : 42 M
Configured Max Memory Limit : 447 M
Physical Memory : 1.97 G
Max memory limit seem to be within acceptable norms

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

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 15 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 99.34 %
Current query_cache_min_res_unit = 4 K
However, 6112 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 37 queries where a join could not use an index properly
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_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 864 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
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 10174 temp tables, 29% were created on disk
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 = 406 : 1
read_buffer_size seems to be fine

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

Lg Andi
 
Hallo,

Ich hab jetz einiges aus den tuning-primer entnommen, wo bei Ich einiges wieder rausnehmen konnte da es zu Datenbankfehlern in meiner vbulletin Installation kam. Obwohl der Mysql ohne Fehler sich stoppen und starten liess.

my.conf

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
#
# 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            = 1024
thread_concurrency     = 8
#
# * 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
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# 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!
#
# * InnoDB plugin
# As of MySQL 5.1.38, the InnoDB plugin from Oracle is included in the MySQL source code.
# It has many improvements and better performances than the built-in InnoDB storage engine.
# Please read http://www.innodb.com/products/innodb_plugin/ for more information.
# Uncommenting the two following lines to use the InnoDB plugin.
#ignore_builtin_innodb
#plugin-load=innodb=ha_innodb_plugin.so
#
# * 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/

tuning-primer.sh

Code: 
n112h143:/usr/bin# tuning-primer.sh

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

MySQL Version 5.1.51-0.dotdeb.1-log i486

Uptime = 0 days 0 hrs 6 min 21 sec
Avg. qps = 761
Total Questions = 290056
Threads Connected = 3

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

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 = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 3
Historic max_used_connections = 3
The number of used connections is 3% 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 = 3 M
Current InnoDB data space = 3 M
Current InnoDB buffer pool free = 19 %
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 : 50 M
Configured Max Per-thread Buffers : 271 M
Configured Max Global Buffers : 42 M
Configured Max Memory Limit : 313 M
Physical Memory : 1.97 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 44 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 15766
Key buffer free ratio = 87 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 19 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = .12 %
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 = 2158 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 = 1024 tables
Current table_definition_cache = 256 tables
You have a total of 896 tables
You have 897 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 580 temp tables, 12% were created on disk
Created disk tmp tables ratio seems fine

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

TABLE LOCKING
Current Lock Wait ratio = 0 : 293384
Your table locking seems to be fine
 
andianden said:
da es zu Datenbankfehlern in meiner vbulletin Installation kam.
Click to expand...
Jetzt frag ich schon zum zweiten Mal:
Was heißt das Konkret? Gibt es dazu Fakten?

Uptime = 0 days 0 hrs 6 min 21 sec
Click to expand...
Nach 6 Minuten liefert das Script noch keine brauchbaren statistischen Werte.
Mit dem Output ist also noch nichts anzufangen.


@Voodoo:
Kommt drauf an, wo Du die Fehlermeldung bekommst.

huschi.
 
Mit ein bisschen Verständnis fürs Programmieren würde man die Meldung direkt im Source finden und erkennen, dass es ausgegeben wird sobald pct_temp_disk > 25 und max_tmp_table_size >= 256 ist. Vorher wird das Selbe nur mit einem anderen Wert für max_tmp_table_size < 256*1024*1024 abgefragt.
Nach Adam Riese und allgemeiner Logik folgt: Dein max_tmp_table_size übersteigt die 256 MiB.
Da dennoch über 25% der Temp-Tables auf Platte geschrieben werden, empfiehlt mysqltuner die Queries zu überarbeiten. (Du hast uns nämlich die zweite Zeile der Meldung unterschlagen: "Reduce your SELECT DISTINCT queries without LIMIT clauses")

Nochmal auf Deutsch und für Leute ohne Programmierkenntnisse:
Um diese Meldung weg zu bekommen, müssen die SQL-Queries verbessert werden.
Falls man das nicht kann, muss man eben damit leben.

huschi.
 
Hallo Huschi,

Nachdem Ich laut tuning-primer Änderungen in der my.conf editiert hatte und danach den Mysql reaktiviert hatte.
Kam es in meiner vb Installation zu dauernden Datenbankfehler egal ob Ich mich durch das Forum oder durch das Admincp geklickt hatte. Aber komischerweise waren die fehler weg nachdem ich das Backup was Ich vor den Einstellungen gemacht hatte , wiedereingespielt hatte. Aber mir kommt gerade der Gedanke da Ich n meinen schicken Pleskpanel auf Mysql-client 5.0 komme und Mysql-server-5.1 sich befindet . Das darin womöglich der Fehler liegt.

Gebe Ich aptitude show mysql-client über shell ein kommt mysql-client-5.1 und gekennzeichnet ist er als manuell installiert.

MFG Andi
 
Huschi said:
Um diese Meldung weg zu bekommen, müssen die SQL-Queries verbessert werden.
Falls man das nicht kann, muss man eben damit leben.
Click to expand...
Dann muss ich wohl damit leben. In meinem vB und wBB schraube ich nichts drin rum.
Dachte ich's mir doch, dass es so ist und die Meldung an den Queries liegt.
 
andianden said:
Kam es in meiner vb Installation zu dauernden Datenbankfehler
Click to expand...
Ich hatte nach Fakten gefragt, nicht nach Erzählungen. Für Geschichten kann ich auch ein Buch lesen.

Pleskpanel auf Mysql-client 5.0 komme und Mysql-server-5.1 sich befindet
Click to expand...
Das Selbe auch hier. Wo im Plesk? Oder meinst Du eher phpMyAdmin? Meinst Du vielleicht den den php-mysql-Client?
Wir wissen es nicht!

huschi.
 
voodoo44 said:
Dann muss ich wohl damit leben. In meinem vB und wBB schraube ich nichts drin rum.
Dachte ich's mir doch, dass es so ist und die Meldung an den Queries liegt.
Click to expand...

Auch wenn ich von vB und wbb nicht viel halte, so wage ich doch zu bezweifeln, dass in deren Cores derart kaputte Queries stecken. Ich behaupte daher jetzt einfach mal, das Du blind irgendwelche als Alpha/Dev/Beta/Untested gekennzeichnete Plugins/Mods/Hacks installiert hast und diese SQLtechnisch als Müll bezeichnet werden dürfen.
 
Hallo,

vieleicht kann mir jemand empfehlungen geben, weil zur zeit mein mysql und dann auch der apache bei last abschmiert. Und ja ich weiß es ist ein altes system,w as ich aber erst im nächsten jahr erneuern kann.

Strato Server
1 GB Ram
Suse Linux

mysqltuner.pl
Code: 
 >>  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
[!!] Your MySQL version 4.0.15 is EOL software!  Upgrade soon!
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB +ISAM -NDBCluster
[--] Data in HEAP tables: 252K (Tables: 3)
[--] Data in MyISAM tables: 490M (Tables: 554)
[!!] ISAM is enabled but isn't being used
[!!] Total fragmented tables: 5

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11h 24m 28s (553K q [13.473 qps], 22K conn, TX: 869M, RX: 77M)
[--] Reads / Writes: 66% / 34%
[--] Total buffers: 48.0M global + 1.7M per thread (60 max threads)
[OK] Maximum possible memory usage: 149.2M (14% of installed RAM)
[OK] Slow queries: 0% (25/553K)
[OK] Highest usage of available connections: 16% (10/60)
[OK] Key buffer size / total MyISAM indexes: 16.0M/67.1M
[OK] Key buffer hit rate: 98.1% (30M cached / 576K reads)
[OK] Query cache efficiency: 61.2% (261K cached / 428K selects)
[!!] Query cache prunes per day: 29720
[OK] Sorts requiring temporary tables: 0% (128 temp sorts / 34K sorts)
[!!] Joins performed without indexes: 216
[!!] Temporary tables created on disk: 36% (8K on disk / 22K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 14% (101 open / 697 opened)
[OK] Open file limit used: 0% (198/32K)
[OK] Table locks acquired immediately: 99% (310K immediate / 310K locks)
[!!] Connections aborted: 38%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-isam to MySQL configuration to disable ISAM (MySQL > 4.1.0)
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    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
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Upgrade to MySQL 4.1+ to use concurrent MyISAM inserts
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 16384)

tuning-primer.sh
Code: 
        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 4.0.15  is EOL please upgrade to MySQL 4.1 or later

Uptime = 0 days 11 hrs 22 min 51 sec
Avg. qps = 13
Total Questions = 551737
Threads Connected = 1

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/4.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 sec.
You have 25 out of 551756 that take longer than 10 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/4.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 0
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 = 60
Current threads_connected = 1
Historic max_used_connections = 10
The number of used connections is 16% of the configured maximum.
Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 48 M
Configured Max Per-thread Buffers : 101 M
Configured Max Global Buffers : 32 M
Configured Max Memory Limit : 133 M
Physical Memory : 1000 M
Max memory limit seem to be within acceptable norms

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

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 6 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 43.32 %
No query_cache_min_res_unit is defined.  Using MySQL < 4.1 cache fragmentation can be inpredictable
Query Cache is 21 % 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.
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 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 216 queries where a join could not use an index properly
You should enable "log-long-format"
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 = 32838 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 = 16384 tables
You have a total of 557 tables
You have 97 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 14543 temp tables, 36% 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 = 1212 : 1
read_buffer_size seems to be fine

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

xerver-tuning.conf
Code: 
##
## Server-Pool Size Regulation (MPM specific)
## 

# the MPM (multiprocessing module) is not a dynamically loadable module in the
# sense of other modules. It is a compile time decision which one is used. We
# provide different apache2 MPM packages, containing different httpd2 binaries
# compiled with the available MPMs. See APACHE_MPM in /etc/sysconfig/apache2.

# prefork MPM
<IfModule prefork.c>
	# number of server processes to start
	StartServers         2
	# minimum number of server processes which are kept spare
	MinSpareServers      3
	# maximum number of server processes which are kept spare
	MaxSpareServers     15
	# highest possible MaxClients setting for the lifetime of the Apache process.
	ServerLimit        150
	# maximum number of server processes allowed to start
	MaxClients         150
	# maximum number of requests a server process serves
	MaxRequestsPerChild  0
</IfModule>

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

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

# perchild MPM
<IfModule perchild.c>
	# constant number of server processes
	NumServers           5
	# initial number of worker threads in each server process
	StartThreads         5
	# minimum number of worker threads which are kept spare
	MinSpareThreads      5
	# maximum number of worker threads which are kept spare
	MaxSpareThreads     10
	# maximum number of worker threads in each server process
	MaxThreadsPerChild  20
	# maximum number of connections per server process
	MaxRequestsPerChild  0

	AcceptMutex fcntl
</IfModule>

# metux MPM
<IfModule metuxmpm.c>
	# initial number of worker threads in each server process
	StartThreads          5
	# minimum number of worker threads which are kept spare
	MinSpareThreads       5
	# maximum number of worker threads which are kept spare
	MaxSpareThreads      10
	# maximum number of connections per server process
	MaxRequestsPerChild   0

	Multiplexer	"wwwrun"	"www"

</IfModule>


#
# 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 2

#
# EnableMMAP: Control whether memory-mapping is used to deliver
# files (assuming that the underlying OS supports it).
# The default is on; turn this off if you serve from NFS-mounted
# filesystems.  On some systems, turning it off (regardless of
# filesystem) can improve performance; for details, please see
# http://httpd.apache.org/docs-2.0/mod/core.html#enablemmap
#
#EnableMMAP off

#
# EnableSendfile: Control whether the sendfile kernel support is
# used  to deliver files (assuming that the OS supports it).
# The default is on; turn this off if you serve from NFS-mounted
# filesystems.  Please see
# http://httpd.apache.org/docs-2.0/mod/core.html#enablesendfile
#
#EnableSendfile off


#
# The following directives modify normal HTTP response behavior to
# handle known problems with browser implementations.
#
BrowserMatch "Mozilla/2" nokeepalive
BrowserMatch "MSIE 4\.0b2;" nokeepalive downgrade-1.0 force-response-1.0
BrowserMatch "RealPlayer 4\.0" force-response-1.0
BrowserMatch "Java/1\.0" force-response-1.0
BrowserMatch "JDK/1\.0" force-response-1.0

#
# The following directive disables redirects on non-GET requests for
# a directory that does not include the trailing slash.  This fixes a 
# problem with Microsoft WebFolders which does not appropriately handle 
# redirects for folders with DAV methods.
# Same deal with Apple's DAV filesystem and Gnome VFS support for DAV.
#
BrowserMatch "Microsoft Data Access Internet Publishing Provider" redirect-carefully
BrowserMatch "^WebDrive" redirect-carefully
BrowserMatch "^WebDAVFS/1.[012]" redirect-carefully
BrowserMatch "^gnome-vfs" redirect-carefully

my.cnf
Code: 
[client]
#password	= your_password
port		= 3306
socket		= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64K
sort_buffer_size = 1M
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
set-variable = max_connections=60
# Maximale Groesse eines Abfrage Ergebnisses, damit es gecached wird
query_cache_limit       = 1M
# Gesamtgroesse des Cache
query_cache_size        = 16M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
# log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir		= /tmp/		
#log-update 	= /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Es wäre schön wenn mir jemand sagen könnte was ich wo ändern kann, soll damit es nicht mehr zu abstürzen kommt. Mal von einem neuen System abgesehen, das ist mir klar.

lg Dirk
 
vielleicht als denkansatz.

warum kommt es zu einer "Last" zu bestimmten zeiten?

ich habe mich auch viel und oft mit dem thema beschäftigt aber folgendes nie gefunden, obwohl es ja als webmaster auf der hand liegen könnte :)


nutzt ihr google tools?
die webmaster-tools hier speziell?
sprich sitemaps?

wieviele einträge gibt es in der sitemap?

ich hatte auch oft probleme mit last zu bestimmten zeiten (meist nachts).

und die ursache?

die sitemaps und google :)
(obwohl es ja eigentlich positiv ist)

ich hatte vor graumer zeit (< 3 monate) die geschwindigkeit manuell in den webmaster-tools von google nach oben geschraubt (sprich google soll schneller suchen :)) und siehe da, zu last-zeiten und bei ausfällen, war der google bot "schuld".

also auch immer bei nicht-erkennbaren "last" und "serverausfällen" die webmaster-tools am besten komplett abschalten oder extrem minimieren und schauen, ob es besser wird.
 
Definiere bitte genauer:
weil zur zeit mein mysql und dann auch der apache bei last abschmiert.
Click to expand...
Geben die Dienste auf? Funktioniert nichts mehr am Server? Musst/Tust Du rebooten? Stehen Kernel-Meldungen in den Logfiles?

Kurze Analyse:
Falls Du den Prefork-Apache installiert hast, dann könnten die MaxClients dran schuld sein.

huschi.
 
Meist gibt der Server komplett auf. Manchmal wird er nur mega langsam.

prefork MPM ist richtig.

Max Clients runtersetzen ?
Was sollte man noch ändern ?

lg Dirk
 
Kommt Dir diese Lösung zu simpel vor?
Denn sie erklärt bereits die Symptome:
Bei vielen gleichzeitigen Anfragen forkt sich der Apache entsprechend oft und belegt immer mehr Speicher. Irgendwann läuft er über den Hauptspeicher raus und der Server fängt an zu swappen. Dadurch wird er bereits deutlich langsamer.
Noch ein paar Anfragen mehr und der Kernel macht nichts anderes mehr, weil er nur noch mit dem Swap beschäftigt ist.

Lösung: Halte den Apache im Rahmen des Hauptspeichers.
Und genau das regulierst Du mit MaxClients.

huschi.
 
was wäre denn hier für die beste einstellung

Schönen Guten Abend ,
ich wollte mal nachfragen, was hier für, die beste Konfig wäre, damit die sql auch richtig resourcen hat.
ich hoffe man kann mir hier weiter helfen, den ab und zu geht die sql cpu schon ziemlig hoch .


vielen dank schon mal .


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

MySQL Version 5.0.77 x86_64

Uptime = 0 days 12 hrs 12 min 26 sec
Avg. qps = 146
Total Questions = 6422276
Threads Connected = 8

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.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 0 out of 6422340 that take longer than 10 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 = 0
Current threads_cached = 0
Current threads_per_sec = 39
Historic threads_per_sec = 10
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

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

INNODB STATUS
Current InnoDB index space = 2 M
Current InnoDB data space = 2 M
Current InnoDB buffer pool free = 35 %
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 : 111 M
Configured Max Per-thread Buffers : 274 M
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 292 M
Physical Memory : 7.80 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 341 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 201
Key buffer free ratio = 76 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_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 7546 queries where a join could not use an index properly
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 1522 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 50362 temp tables, 1% 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 = 1074 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 55
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'.
 
Hallo Kobold,

wie wäre es mit dem Umsetzen der diversen Vorschlägen die Dir das Script an die Hand gibt?

huschi.
 
nun, ich bin nicht besonders geübt damit.
ich weiss zwar wo ich die my.cnf finde, aber weiss leider nicht was ich da eintragen müsste richtig.
deswegen erhoffte ich mir etwas beistand dies bezüglich , um nix abzuschiessen :rolleyes:.

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

ich hatte das mal gemacht, aber bestimmt total falsch
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top