MySQL Performance Tuning mit Tuning-Primer.sh Script

[LinuxAdmin]

Zitat von Huschi

Schau halt mal mit fdisk nach, ob er korrekt markiert und eingerichtet ist.

In der Partitionstabelle sollte zwar schon Typ 82 stehen, überprüft wird das letztendlich jedoch nirgends. Entscheidend ist die richtige Signatur in den letzten 10 Bytes der ersten Page. Bei einer Pagesize von 4096 Bytes (x86-Systeme) sollte

dd if=/dev/hda5 bs=1 skip=4086 count=10 |od -c

SWAPSPACE2 liefern.

Auf die Device-Namen der SATA-Platten würde ich auch nicht in jedem Fall wetten -- ich habe auch schon einen SATA-Controller erlebt, bei dem Platten als /dev/sd? erscheinen (Lenovo Thinkpad T61, OpenSuSE 10.2). Vielleicht ist die erste Platte tatsächlich eine PATA(IDE)-Platte??

HALT: Wenn man die abgebildete fstab nochmal genau anschaut, sieht man, dass dort gar keine wirklichen Partitionen vorhanden sind! Es wird jeweils die ganze Platte verwendet (was auch ohne Probleme möglich ist, wenn auch recht unüblich: Niemand verlangt, dass man Partitionen hat...). Dann kann die Swap-Partition natürlich auch nicht wirklich existieren (auch falls sie in der Partitionstabelle stehen sollte und von fdisk angezeigt wird...).

Falls FireTurbo das nicht bei einer Neuinstallation ändern will, kann er ja eine Swap-Datei anlegen und diese stattdessen verwenden, die Performance ist nur geringfügig schlechter als bei einer Swap-Partition.

LinuxAdmin

[druckgott]

Zitat von noto

[/code] mit tuning-primer.sh beobachten wir nun das Verhalten von MySQL und fügen bei Bedarf in der my.cnf neue Variables hinzu oder ändern deren Werte. Eine Datenbank ist dynamisch d.h. Tabellen werden mit der Zeit grösser. Daher sollte man tuning-primer.sh regelmässig laufen lassen z.B. jede Woche.

Hallo, hier steht ja man soll das Script immer wieder mal laufen lassen. Gibt es eine Möglichkeit das Script z.B. jede Woche per Cron laufen zu lassen, und die Daten per E-Mail zugeschickt zu bekommen?

Wenn ja wie?

mfg
druckgott

[Huschi]

An sich ist das mit "wöchentlich" ein ziemlicher Quatsch. Natürlich ist die Pflege und Überwachung ein dynamischer Prozess, aber so schnell und in dem Ausmaß ändert sich an einer DB auch nichts. Bzw. jeder sollte selber wissen, ob und wie sehr sich seine DB ändert. Hierbei geht es vorallem um den Umfang einer DB. Z.B. ein gut besuchtes Forum wie dieses hier oder ein Buchhändler mit ständig neuer Ware, etc.

Aber wer es wirklich will:
Einfach das Script als Cronjob eintragen. Die Ausgabe mailt Dir crond selber zu. (Vorausgesetzt, Emails an "root" erreichen Dich.)

huschi.

[druckgott]

Ah ok vielleicht mach ich es dann auch nur Monatsweise oder zwei Monatsweise

Danke für den Tip.

mfg
druckgott

[aymanghost]

Was könnt ihr mir für settings vorschlagen um die performance zu steigern ?

Derzeit siehts so aus

skip-external-locking
skip-innodb
key_buffer_size = 128M
sort_buffer_size = 4M
read_buffer_size = 4M
net_buffer_length = 8K
myisam_sort_buffer_size = 32K
read_rnd_buffer_size = 64K
join_buffer_size = 512K
thread_cache_size = 256
table_cache = 3000
max_allowed_packet = 256K
max_tmp_tables = 512
tmp_table_size         = 512M
max_heap_table_size    = 512M
open_files_limit       = 5000
max_connections = 350
max_user_connections = 320
low_priority_updates = 1
long_query_time = 2
wait_timeout = 120
thread_concurrency     = 10
concurrent_insert = 4

Timeout 20
KeepAlive Off
MaxKeepAliveRequests 0
KeepAliveTimeout 1
<IfModule mpm_prefork_module>
    ServerLimit	   400
    StartServers          3
    MinSpareServers       2
    MaxSpareServers      2
    MaxClients          395
    MaxRequestsPerChild   100000
</IfModule>

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

BINARY UPDATE LOG
The binary update log is enabled

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

MAX CONNECTIONS
Current max_connections = 350
Current threads_connected = 81
Historic max_used_connections = 324
The number of used connections is 92% of the configured maximum.
You should raise max_connections

MEMORY USAGE
Max Memory Ever Allocated : 2 G
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 139 M
Configured Max Memory Limit : 3 G
Physical Memory : 1.94 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 128 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 498
Key buffer fill ratio = 11.00 %
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 = 1 M
Current query_cache_used = 427 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 41.79 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 60 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 512.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 = 6360 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 = 3000 tables
You have a total of 523 tables
You have 1235 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 512 M
Current tmp_table_size = 512 M
Of 48368 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 3 M
Current table scan ratio = 105 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 5
You may benefit from selective use of InnoDB.

[Huschi]

Sag uns mehr zu Deiner Maschine.

huschi.

[aymanghost]

Es ist ein strato Dedicated server
mit:

2GB RAM 
AMD Opteron™ 1212 HE
RAID1 2 x 250 GB
Ubuntu 6.06 LTS als OS
Plesk Controlpanel 8.4

laut Webalizer fallen bis zu 250000 visits am tag an.
davon sind ungefähr bis zu 3/5 zugriffe von usern die Ihre Daten via Client http://:80 aktualiesieren.

Meine Frage wäre auch was ich ungefähr an maxclients und connections by mysql und apache2 erwarten kann und ob ich evtl. besser bedient wäre meinen zweiten server der identisch ist nur für mysql einzusetzen und diesen nur für das hosting oder umgekehrt ?

[Huschi]

Zitat von aymanghost

Max Memory Ever Allocated : 2 G
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 139 M
Configured Max Memory Limit : 3 G
Physical Memory : 1.94 G

Dir ist dann hoffentlich klar, daß diese Wert z.B. eine absolute Leistungsbremse sind.

Auch Dien MaxClients von 395 sind bereits überdimensioniert. Nach alter Faustregel (12 MB pro Prefork-Thread) braucht der Apache bei voller Auslastung 4 GB. Wenn man davon ausgeht, daß dieser Zustand auch eine hohe Belastung des MySQL mitführt, dann ist Dein Rechner komplett in den Knien. Und statt wenigstens die Requests mit entsprechender Zeitverzögerung abzuarbeiten, ist er dann 80% der Zeit mit swappen beschäftigt.

Tipps:
Zieh Deine MaxClients entsprechend runter. Ob es reicht oder nicht, mußt Du mit mod_status ermitteln.
Dementsprechend kannst Du Deine max-connections runter ziehen. Je nach Seitenart sind nur 1/3 bis 1/2 der HTTP-Request auch mit DB-Abfragen behaftet.

huschi.

[aymanghost]

@hushi das mit den max_clients im apache2 leuchtet mir ein.

Nur verstehe ich das verhältnis zu den max_connections in mysql nicht,
wie hoch sollte ich das einstellen wenn z.b. im Apache2 :
KeepAlive ON
KeepAlive requests 150
max_clients 240

[Huschi]

Kannst Du Deine Rückfrage nochmal in sinnvollen Sätzen formulieren?

huschi.

[aymanghost]

upps was war da passiert , habe woll einige wörter und Buchstaben verschluckt

Hab es oben korrigiert

Gruß

[Huschi]

Zitat von aymanghost

Nur verstehe ich das verhältnis zu den max_connections in mysql nicht,

Versuch es mal mit max_connections = max_clients / 2

KeepAlive ON
KeepAlive requests 150

Ist die Seite so Grafiklastig, daß bis zu 150 Bilder, CSS, JS, etc. (evtl. auch 4mal so viel?) geladen werden

max_clients 240

240 * 12MB = 2880 GB
Soviel RAM hast Du nicht. Du mußt verhindern, daß der Server überhaupt ins Swappen kommt. Denn in dem Fall versinkt Dein Server im Performance-Tief.

huschi.

[aymanghost]

danke @huschi für deine Hilfe und Erklärung.

Ich komme mit dem mir verfügbarem Speicher auf jeden Fall nicht weiter.
Die Seite öffnet in den meisten Fällen nicht oder benötigt 30sec zum öffnen.

Daher bestellte ich einen neuen Server mit mehr RAM und schnellerer Anbingung ans Netz. Melde mich wieder zu diesem Thema wenn der Server eingerichtet ist.

[aymanghost]

melde mich wieder zum thema

Was kann verbessert werden

Serverinfo:

CPU

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Core(TM)2 Duo CPU     E6750  @ 2.66GHz
stepping        : 11
cpu MHz         : 2666.613
cache size      : 4096 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 2
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr lahf_lm
bogomips        : 5336.66
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Core(TM)2 Duo CPU     E6750  @ 2.66GHz
stepping        : 11
cpu MHz         : 2666.613
cache size      : 4096 KB
physical id     : 0
siblings        : 2
core id         : 1
cpu cores       : 2
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr lahf_lm
bogomips        : 5333.23
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

RAM

MemTotal:      4033452 kB
MemFree:        194844 kB
Buffers:        112944 kB
Cached:        2895120 kB
SwapCached:          0 kB
Active:        1501880 kB
Inactive:      2048848 kB
SwapTotal:     2096472 kB
SwapFree:      2096444 kB
Dirty:            7676 kB
Writeback:           0 kB
AnonPages:      542472 kB
Mapped:          23808 kB
Slab:           216848 kB
SReclaimable:   159540 kB
SUnreclaim:      57308 kB
PageTables:      50712 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   4113196 kB
Committed_AS:  2208816 kB
VmallocTotal: 34359738367 kB
VmallocUsed:      1220 kB
VmallocChunk: 34359737131 kB

tuning-primer output

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

MySQL Version 5.0.32-Debian_7etch5-log x86_64

Uptime = 0 days 3 hrs 30 min 36 sec
Avg. qps = 1252
Total Questions = 15826670
Threads Connected = 57

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 113142 out of 15826888 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

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

MAX CONNECTIONS
Current max_connections = 250
Current threads_connected = 58
Historic max_used_connections = 172
The number of used connections is 68% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 1 G
Configured Max Per-thread Buffers : 2 G
Configured Max Global Buffers : 98 M
Configured Max Memory Limit : 2 G
Physical Memory : 3.84 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 138 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 4661
Key buffer fill ratio = 30.00 %
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 = 24 M
Current query_cache_used = 8 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 33.71 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 3 M
Current read_rnd_buffer_size = 252 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 3.00 M
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 = 8192 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 = 1000 tables
You have a total of 275 tables
You have 704 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 256 M
Of 541519 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 145 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 24
You may benefit from selective use of InnoDB.

my.cnf

#
# 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
#
back_log = 50
skip-innodb
concurrent_insert = 2
connect_timeout = 60
interactive_timeout = 120
join_buffer_size = 3M
key_buffer		= 64M
low_priority_updates = 1
myisam_sort_buffer_size = 3M
max_allowed_packet	= 32M
max_connections     = 250
max_user_connections     = 250
max_heap_table_size = 256M
max_tmp_tables = 10000
net_buffer_length = 8K
open_files_limit = 8192
read_buffer_size = 3M
read_rnd_buffer_size = 256K
table_cache            = 3072
tmp_table_size = 256M
thread_stack		= 384K
thread_cache_size	= 5000
thread_concurrency     = 2
sort_buffer_size = 3M
query_cache_limit       = 4M
query_cache_size        = 24M
wait_timeout = 120
#
# * Query Cache Configuration
#
#
# * 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
set-variable = table_cache=1000
#
# * 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		= 64M

#
# * 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/

apache.conf

Timeout 30
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 2
<IfModule mpm_prefork_module>
ServerLimit 250
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 250
MaxRequestsPerChild 0
</IfModule>

[Huschi]

Zitat von aymanghost

Was kann verbessert werden

Gegenfrage: Warum sollte etwas verbessert werden?

huschi.