MySQL Performance Tuning mit Tuning-Primer.sh Script

aymanghost · May 26, 2008

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

Hab es oben korrigiert

Gruß

Huschi · May 26, 2008

aymanghost said:
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 · May 30, 2008

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 · Jun 8, 2008

melde mich wieder zum thema

Was kann verbessert werden

Serverinfo:

CPU

Code:

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

Code:

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

Code:

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

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
#
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 · Jun 8, 2008

aymanghost said:
Was kann verbessert werden

Gegenfrage: Warum sollte etwas verbessert werden?

huschi.

Michael83 · Aug 6, 2008

Hallo zusammen,

ich hänge mich hier einfach mal dran. Bisher habe ich mich nie wirklich um die Optimierung der MySQL-Konfiguration gekümmert, da es "einfach lief". In letzter Zeit allerdings kommt es mir vor, als würde MySQL unverhältnismäßig viel Ressourcen der CPUs verbrauchen. Wenn ich mir die Ausgabe von top ansehe, verbraucht mysqld zeitweise über 100%.

Ich habe mich dann mal in das Thema eingelesen und auch schon einiges angepasst. Jetzt läuft der Server wieder eine Weile, aber wirklich gebessert hat sich die Situation nicht. Ich habe sowohl die tuning-primer.sh, als auch die mysqltuner.pl verwendet, aber inzwischen widersprechen sie sich (query_cache & table_cache). Hat von euch vielleicht noch jemand eine Idee für Optimierungsmöglichkeiten?

Im Folgenden Angaben zum Server, der Inhalt der my.cnf und die Ausgaben von tuning-primer.sh und mysqltuner.pl.

Server:

AMD Athlon 64 X2 5600+ Dual Core
2 GB DDR2 RAM
OS: Debian Etch 32bit

apache2.conf:

Code:

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

my.cnf:

Code:

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

# The MySQL server
[mysqld]
port        = 3306
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
language    = /usr/share/mysql/english
skip-locking
skip-external-locking
key_buffer = 64M
max_allowed_packet = 1M
table_cache = 768
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 3M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 32M
set-variable = max_connections=100
long_query_time = 3
log_slow_queries                = /var/log/mysql/slow-queries.log
back_log                        = 50
sync_binlog                     = 0
binlog_cache_size               = 1M
max_binlog_size                 = 100M
expire_logs_days                = 7
tmp_table_size                  = 64M
max_heap_table_size             = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

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

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-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

# Uncomment the following if you are using InnoDB tables
skip-innodb
#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 = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[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 = 64M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 64M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

tuning-primer.sh:

Code:

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

MySQL Version 5.0.51a-7.dotdeb.1-log i486

Uptime = 8 days 5 hrs 58 min 42 sec
Avg. qps = 11
Total Questions = 7963545
Threads Connected = 10

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 3 sec.
You have 7 out of 7963567 that take longer than 3 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 = 8
Current threads_cached = 3
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 = 10
Historic max_used_connections = 23
The number of used connections is 23% of the configured maximum.
Your max_connections variable seems to be fine.

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

KEY BUFFER
Current MyISAM index space = 23 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 1344
Key buffer fill ratio = 41.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 = 32 M
Current query_cache_used = 10 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 33.52 %
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 = 2 M
Current read_rnd_buffer_size = 3 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 3.00 M
You have had 9409 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 = 1646 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 = 768 tables
You have a total of 210 tables
You have 430 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 65740 temp tables, 19% were created on disk
Created disk tmp tables ratio seems fine

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

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

mysqltuner.pl:

Code:

 >>  MySQLTuner 0.9.8 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-7.dotdeb.1-log
[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: 37M (Tables: 191)
[--] Data in MEMORY tables: 1M (Tables: 2)
[!!] Total fragmented tables: 17

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 6h 0m 36s (7M q [11.175 qps], 844K conn, TX: 3B, RX: 1B)
[--] Reads / Writes: 73% / 27%
[--] Total buffers: 11.2M per thread and 170.0M global
[OK] Maximum possible memory usage: 1.3G (64% of installed RAM)
[OK] Slow queries: 0% (7/7M)
[OK] Highest usage of available connections: 23% (23/100)
[OK] Key buffer size / total MyISAM indexes: 64.0M/23.7M
[OK] Key buffer hit rate: 99.9%
[OK] Query cache efficiency: 68.1%
[!!] Query cache prunes per day: 244
[OK] Sorts requiring temporary tables: 10%
[!!] Joins performed without indexes: 9409
[OK] Temporary tables created on disk: 16%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 8%
[OK] Open file limit used: 38%
[OK] Table locks acquired immediately: 99%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 3.0M, or always use indexes with joins)
    table_cache (> 768)

Über Tipps wäre ich dankbar!

Gruß,
Michael

Huschi · Aug 6, 2008

Michael83 said:
Wenn ich mir die Ausgabe von top ansehe, verbraucht mysqld zeitweise über 100%.

Mit welchen Aufgaben? (show processlist

huschi.

daseddy · Aug 6, 2008

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

Der Tipp wird meist nie beachtet. Du hast scheinbar eine Tabelle in die viel geschrieben wird und aus der auch viel gelesen wird. MyISAM blockiert bei Schreibzugriffen immer die ganze Tabelle, dass heißt auch lesende Zugriffe müssen warten. Vielleicht ist InnoDB für diese bestimmte Tabelle die bessere Wahl.

Michael83 · Aug 6, 2008

Hallo,

danke für die schnellen Antworten.

@Huschi: Da ist nichts ungewöhnliches zu sehen. Auf dem Server läuft ein größeres Forum (MyBB 1.4) und dessen Abfragen scheinen die Last beim MySQL-Server auszulösen.

Code:

mysql> show processlist;
+---------+--------------+-----------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id      | User         | Host            | db           | Command | Time | State          | Info                                                                                                 |
+---------+--------------+-----------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
|  957182 | openfire     | localhost:53767 | openfire     | Sleep   |    5 |                | NULL                                                                                                 |
|  957183 | openfire     | localhost:53768 | openfire     | Sleep   |    5 |                | NULL                                                                                                 |
|  957184 | openfire     | localhost:53769 | openfire     | Sleep   |    5 |                | NULL                                                                                                 |
|  957185 | openfire     | localhost:53770 | openfire     | Sleep   |    5 |                | NULL                                                                                                 |
|  957186 | openfire     | localhost:53771 | openfire     | Sleep   |    5 |                | NULL                                                                                                 |
| 1064045 | root         | localhost       | NULL         | Query   |    0 | NULL           | show processlist                                                                                     |
| 1064206 | syscp        | localhost:53204 | syscp        | Sleep   |  105 |                | NULL                                                                                                 |
| 1064359 | mybboardsql2 | localhost       | mybboardsql2 | Query   |    0 | Sorting result | SELECT pid FROM mybb_forum_posts WHERE tid='9684' AND dateline > '1218029331' ORDER BY dateline ASC  |
| 1064360 | mybboardsql2 | localhost       | NULL         | Sleep   |    0 |                | NULL                                                                                                 |
+---------+--------------+-----------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

@daseddy: Das wird die Tabelle des Forums sein, in der sich die Beiträge befinden. Soweit ich weiß, funktioniert in InnoDB-Tabellen die MySQL Volltextsuche nicht, mit der das Forum aber arbeitet. Daher kommt ein Wechsel zur Zeit eher nicht in Betracht.

Gruß,
Michael

Huschi · Aug 6, 2008

Michael83 said:
Soweit ich weiß, funktioniert in InnoDB-Tabellen die MySQL Volltextsuche nicht

Keine Ahnung was myBB nutzt, daher hier lediglich die Richtigstellung bzgl. "Volltextsuche":
Die Volltextsuche und Indizierung für Matching (ála MySQL) funktioniert auf InnoDB nicht.
Die Textsuche mit LIKE hingegen schon.

huschi.

Michael83 · Aug 7, 2008

Das MyBB unterstützt beides, wobei die richtige Volltextsuche gerade in einem größeren Forum natürlich komfortabler ist. Ich werde mir das Ganze nochmal durch den Kopf gehen lassen.

Wenn sonst aber nichts direkt an der Konfiguration auszusetzen ist, bin ich schonmal zufrieden.

noto · Aug 29, 2008

Code:

MaxKeepAliveRequests 10

jedes Objekt wie .gif Bild auf deiner Seite erzeugt einen Request! Der Wert 10 scheint mir zu wenig zu sein.

Apache und MySQL müssen sich den RAM teilen. Daher sollte man Apache und MySQL auf den vorhandenen RAM abstimmen (Anleitung "Beispielkonfiguration" ist am Anfang des Threads).

Zudem solltest du den join_buffer erhöhen z.B. auf 5MB

Noch was zum Tuning Primer Script. Bei dir nimmt sich MySQL derzeit bis zu 1 GB RAM (Max Memory Limit) bei max_connections = 100
Bisher hattest du zum Glück nur max 23 Connections, bei 100 Connections würde dir der RAM ausgehen, da Apache pro Client (entspricht einem Child bzw. httpd2 Prozess) mindestens 12 MB benötigt. Bei MaxClients = 100 wären das 1,2 GB RAM.

1 GB MySQL + 1,2 GB Apache (ohne Linux, Mail Server ect !) wären 2,2 GB RAM. Du hast aber nur 2 GB RAM.

Wa soll ich tun?

1. schauen wieviele httpd Prozesse (MaxClients) bei Apache tatsächlich benötigt werden (auf sleepings + waitings achten!, diese werden nicht benötigt!). Daraus ergibt sich der Wert für MaxClients und der benötigte RAM für Apache. (MaxClients x ~12 MB = RAM). Tatsächlicher RAM Wert für die httpd Prozesse kann man ermitteln, s.a. Thread Anfang.

2. Linux, Mail Server, Plesk ect. benötigen auch nochmal RAM, daher ~ 250 MB pauschal vom gesamten RAM abziehen.

3. restlicher RAM kann man nun MySQL zur Verfügung stellen und die Caches/Buffers ect. ausreichend dimensionieren. Das Verhältnis MaxClients (Apache) zu max_connections (MySQL) kann laut Erfahrungswerte von Huschi 3:1 betragen. MaxClients = 150 entspricht max_connections = 50 (gleichzeitige Verbindungen zum Web bzw. MySQL Server)

in der my.cnf

Code:

[mysqld]

low_priority_updates = 1
concurrent_insert = 2
join_buffer_size = 5MB

hinzufügen bzw. ändern

REINI_:o) · Sep 1, 2008

Hallo,
Was soll mir dieses Sagen? ROT = Schlecht?

Code:

TEMP TABLES
Current max_heap_table_size = 63 M
Current tmp_table_size = 64 M
Of 19 temp tables, 94% 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.

Wenn ich die werte erhöre kommt z.b

Code:

Of 2 temp tables, 100% were created on disk

aber auch erst bei

Code:

Current max_heap_table_size = 2 G
Current tmp_table_size = 2 G

Das Grundproblem ist, dass der v-server SEHR langsam geworden ist... kann dieses Problem überhaupt was damit zu tun haben?

Jeder der sich nun Fragt, ob ich weiß was ich tuhe...
leider NEIN

kann mir da jemand weiterhelfen? Wäre supi

Huschi · Sep 1, 2008

REINI_:o) said:
Current max_heap_table_size = 2 G

Du hast einen vServer mit mehr als 2 GByte? Cool!

Ich denke Du hast den wichtigsten Satz überlesen:
Nicht alle Tipps sind umsetzbar oder müssen umgesetzt werden!
Quelle: MySQL-Tuning - huschi.net

huschi.

REINI_:o) · Sep 1, 2008

MOD: Full-Quote entfernt!
warscheinlich nicht wenn du dich soo ausdrückst...
habe die beiden Werte nun wieder ganz raus genommen, oder ist das noch schlechter?
Also sie waren in der my.cnf vorher garnicht drin. Ist das dann noch schlechter?
Und kann mein Problem das alles ewig zum laden brauch überhaupt daran liegen? War der "Tipp" von Strato

Code:

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 3 sec.
You have 0 out of 24658 that take longer than 3 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.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 3
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 = 4
The number of used connections is 4% 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

MEMORY USAGE
Max Memory Ever Allocated : 32 M
Configured Max Per-thread Buffers : 155 M
Configured Max Global Buffers : 26 M
Configured Max Memory Limit : 181 M
Physical Memory : 1.97 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 9 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 555
Key buffer fill ratio = 3.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 = 500 K
Current query_cache_used = 301 K
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 60.28 %
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 = 512 K
Current read_rnd_buffer_size = 508 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 14 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 = 4206 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 = 2048 tables
You have a total of 616 tables
You have 123 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 63 M
Current tmp_table_size = 64 M
Of 408 temp tables, 94% 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 = 252 K
Current table scan ratio = 124 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 7797
Your table locking seems to be fine

h1356625:~ # /etc/init.d/mysql stop
Shutting down service mysql^[[A^[[A^[[A                               done
h1356625:~ # /etc/init.d/mysql stop
Shutting down service mysql                                           done
h1356625:~ # /etc/init.d/mysql start
Starting service MySQL                                                done
h1356625:~ # tuning-primer.sh
mysqld is alive

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

MySQL Version 4.1.10a i686

Uptime = 0 days 0 hrs 0 min 3 sec
Avg. qps = 30
Total Questions = 91
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/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 = 3 sec.
You have 0 out of 134 that take longer than 3 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.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 1
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 = 2
The number of used connections is 2% 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

MEMORY USAGE
Max Memory Ever Allocated : 29 M
Configured Max Per-thread Buffers : 155 M
Configured Max Global Buffers : 26 M
Configured Max Memory Limit : 181 M
Physical Memory : 1.97 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 9 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 25
Key buffer fill ratio = 0 %
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 = 500 K
Current query_cache_used = 220 K
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 44.05 %
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 = 512 K
Current read_rnd_buffer_size = 508 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 = 4206 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 = 2048 tables
You have a total of 616 tables
You have 26 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 7 temp tables, 100% 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 = 252 K
Current table scan ratio = 38 : 1
read_buffer_size seems to be fine

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

Huschi · Sep 1, 2008

Meine Kristallkugel benötigt noch ein paar Fakten über Dein System.

huschi.

REINI_:o) · Sep 1, 2008

Da ich nicht genau weiß was Dir und damit mir hilft.

GenuineIntel, Intel(R) Pentium(R) 4 CPU 3.06GHz
psa v8.6.0_build86080722.00 os_SuSE 9.3
Linux 2.6.9-023stab046.2-enterprise

Tasks: 54 total, 1 running, 53 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.7% us, 1.8% sy, 0.0% ni, 98.5% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 2068364k total, 1981904k used, 86460k free, 172192k buffers
Swap: 3068392k total, 2536k used, 3065856k free, 1008208k cached

ist das Rote schlimm??

gruß reini

Huschi · Sep 1, 2008

Aha, Dein vServer hat einen eigenen Quad-Core?
Glaube ich genauso wenig wie die 2 GigaByte Speicher.

Du solltest die Fakten wirklich mal vernünftig zusammensammeln.
Und mit "Fakten" meine ich nicht die Info's über Dein Hostsystem, sondern Deine "zugesicherten Daten" des vServers.

huschi.

REINI_:o) · Sep 1, 2008

hm, min. 512Mb RAM

Huschi · Sep 1, 2008

Mit so wenig RAM sollte man eher den MySQL auf geringeren Speicherverbrauch tunen.
Größeren Erfolg verspricht dazu auch erstmal der Apache.

huschi.

MySQL Performance Tuning mit Tuning-Primer.sh Script

New Member

Moderator

New Member

New Member

Moderator

New Member

Moderator

Registered User

New Member

Moderator

New Member

New Member

New Member

Moderator

New Member

Moderator

New Member

Moderator

New Member

Moderator

We value your privacy