MySQL probleme bei optimierung

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

MySQL probleme bei optimierung

Post by fulltilt »

mysql erzeugt massive load, ich versuche die config zu optimieren es brachte aber so weit keinen erfolg ...
was würdet ihr empfehlen?

Code: Select all

TABLE CACHE
Current table_open_cache = 13000 tables
Current table_definition_cache = 1024 tables
You have a total of 12366 tables
You have 12413 open tables.
Current table_cache hit rate is 25%, while 95% 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 = 64 M
Current tmp_table_size = 64 M
Of 2104 temp tables, 49% 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 LOCKING
Current Lock Wait ratio = 1 : 552
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'.


config

Code: Select all

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = -5
open_files_limit = 30000

back_log = 75
skip-external-locking
skip_name_resolve
skip_locking

#
key_buffer              = 384M
max_allowed_packet      = 16M
thread_stack        = 128K
thread_cache_size       = 384
#thread_cache=32
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam-recover         = BACKUP
max_connections        = 400
interactive_timeout=100
wait_timeout=7200
connect_timeout=10
table_open_cache        = 13000
table_definition_cache = 1024
max_heap_table_size       = 64M
max_allowed_packet = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
join_buffer_size     = 1M
#max_connect_errors = 10
#low_priority_updates = 1
#concurrent_insert = 2
thread_concurrency     = 2

query_cache_type        = 1
#query_cache_limit      = 1M
query_cache_limit       = 4M
#query_cache_size        = 32M
query_cache_size        = 128M

query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

[mysqldump]
quick
#quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout



top

Code: Select all

top - 14:36:31 up 23:20,  1 user,  load average: 0.17, 0.19, 0.18
Tasks: 471 total,   2 running, 469 sleeping,   0 stopped,   0 zombie
Cpu(s):  2.6%us,  0.4%sy,  0.0%ni, 95.9%id,  1.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  16406500k total, 10293312k used,  6113188k free,  1121120k buffers
Swap:  1998840k total,        0k used,  1998840k free,  4485120k cached



Code: Select all

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 429M (Tables: 11032)
[--] Data in InnoDB tables: 112M (Tables: 1263)
[--] Data in MEMORY tables: 0B (Tables: 48)
[!!] Total fragmented tables: 1341

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23m 28s (283K q [201.112 qps], 13K conn, TX: 1B, RX: 43M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 650.0M global + 14.1M per thread (100 max threads)
[OK] Maximum possible memory usage: 2.0G (12% of installed RAM)
[OK] Slow queries: 2% (7K/283K)
[OK] Highest usage of available connections: 6% (6/100)
[OK] Key buffer size / total MyISAM indexes: 256.0M/126.4M
[OK] Key buffer hit rate: 99.8% (3M cached / 7K reads)
[OK] Query cache efficiency: 77.8% (197K cached / 253K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (104 temp sorts / 6K sorts)
[!!] Joins performed without indexes: 345
[!!] Temporary tables created on disk: 28% (1K on disk / 4K total)
[OK] Thread cache hit rate: 99% (6 created / 13K connections)
[!!] Table cache hit rate: 4% (4K open / 98K opened)
[OK] Open file limit used: 20% (6K/30K)
[OK] Table locks acquired immediately: 99% (69K immediate / 69K locks)
[!!] InnoDB data size / buffer pool: 112.5M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_cache (> 4096)
    innodb_buffer_pool_size (>= 112M)

Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

so wird es etwas ruhiger:

table_open_cache = 4096
table_definition_cache = 4096

ist irgendwie seltsam, wenn ich den table_cache erhöhe dann geht die load hoch, mysql läuft dann mit 20-60% cpu usage
könnte das auf ein problem mit dem RAM hindeuten?
Last edited by fulltilt on 2013-03-06 17:25, edited 1 time in total.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Backup Deiner my.cnf und Datenbanken anlegen, dann viewtopic.php?f=103&t=36343 befolgen allerdings mit dieser my.cnf testen:

Code: Select all

[client]
port                            = 3306
max_allowed_packet              = 64M

[mysql]
prompt                          = \u@\h [\d]>\_
max_allowed_packet              = 64M
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
bind-address                    = 127.0.0.1
socket                          = /var/run/mysqld/mysqld.sock
server-id                       = 1
back_log                        = 500
sync_binlog                     = 1
binlog_cache_size               = 4M
binlog_stmt_cache_size          = 4M
max_binlog_size                 = 500M
binlog-format                   = MIXED
expire_logs_days                = 30
slow-query-log                  = 1
slow-query-log-file             = /var/log/mysql/mysql-slow.log
delay-key-write                 = ALL
myisam-recover                  = FORCE,BACKUP
key_buffer_size                 = 256M
max_allowed_packet              = 64M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
thread_stack                    = 192K
table_cache                     = 32768
table_open_cache                = 32768
table_definition_cache          = 16384
query_cache_type                = 1
query_cache_size                = 64M
thread_cache_size               = 150
max_connections                 = 200
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 2
local-infile                    = 0
log-warnings                    = 2
log-queries-not-using-indexes
skip-external-locking
skip-symbolic-links
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 2G
innodb_buffer_pool_instances    = 4
innodb_additional_mem_pool_size = 16M
innodb_data_file_path           = ibdata1:500M;ibdata2:50M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_file_per_table           = 1
innodb_purge_threads            = 1
innodb_strict_mode              = 1
innodb_old_blocks_time          = 1000
innodb_stats_on_metadata        = 0
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_io_capacity              = 200
innodb_open_files               = 8192

[mysqldump]
max_allowed_packet              = 64M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqlhotcopy]
interactive_timeout

Nach zwei bis drei Tagen lässt Du dann nochmal mysqltuner.pl und tuning-primer.sh laufen und postest die Ausgaben.
Sollte meine my.cnf den MySQLd am Starten hindern, dann bitte die Fehlermeldung(en) aus dem mysqld.err-Log posten.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

ddm3ve
Moderator
Moderator
Posts: 1115
Joined: 2011-07-04 10:56

Re: MySQL probleme bei optimierung

Post by ddm3ve »

fulltilt wrote:ist irgendwie seltsam, wenn ich den table_cache erhöhe dann geht die load hoch, mysql läuft dann mit 20-60% cpu usage
könnte das auf ein problem mit dem RAM hindeuten?


Nicht zwangsläufig verwunderlich. Darf er mehr auf Platte schreiben (z.B. bei Blob der Fall) in Form von temporären Tabellen, dann tut es die Datenbank auch. Hier ist eventuell die Platte das Nadelöhr. Vorher hast Du es halt nicht bemerkt, weil die Datenbankrequests nicht mehr abgearbeitet wurden oder eben lange warten mussten, bis wieder temporäre Tabellen angelegt werden konnten.
02:32:12 21.12.2012 und dann sind Deine Probleme alle unwichtig.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

also es gab tatsächlich ein Problem mit einer Festplatte, diese wurde nun ersetzt ...
Ich würde trotzdem gerne die Optimierung durchführen wie oben beschrieben, ist diese nur für MySQL 5.5 gedacht weil hier noch MySQL 5.1 läuft
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Ja, ist für 5.5, sollte auf 5.1 aber auch funktionieren, genaueres verrät dann das Errorlog von MySQL.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

danke, ich teste es gerade in einer VM wobei mysql nicht starten will:

Code: Select all

Apr 13 12:35:37 host2 /etc/init.d/mysql[28277]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Apr 13 12:35:37 host2 /etc/init.d/mysql[28277]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed
Apr 13 12:35:37 host2 /etc/init.d/mysql[28277]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Apr 13 12:35:37 host2 /etc/init.d/mysql[28277]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!


ich habe auch mal wie in der cnf pid und standard pfade hinzugefügt, bringt aber keine Änderung ...
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Existiert /var/run/mysqld/mysqld.sock denn nach dem Start?
Ansonsten die socket-Option mal deaktivieren, dann wird standardmässig /tmp/mysqld.sock genommen.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

hmm, daran scheint es zu hängen, ich hatte in der VM nicht genug speicher zugeteilt ;-)
innodb_buffer_pool_size = 2G
Last edited by fulltilt on 2013-04-13 13:22, edited 1 time in total.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

kann ich diesen Part so mit deiner cnf verwenden?

Code: Select all

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
#tmpdir          = /tmp


bzw. auch diesen:

Code: Select all

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice      = -5
Last edited by fulltilt on 2013-04-13 13:59, edited 1 time in total.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Den ersten Part ja, vom zweiten Part das nice allerdings unter [mysqld] statt [mysqld_safe] da sich [mysqld_safe] auf das mysql-safe Startup-Script bezieht und nicht auf den MySQLd und ein Socket oder nice für ein Startup-Script wenig Sinn machen.

Kurz: [mysqld_safe] sollte generell durch [mysqld] ersetzt werden.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

bis auf folgende Optionen klappt alles:

Code: Select all

innodb_buffer_pool_instances    = 4
innodb_purge_threads            = 1
innodb_strict_mode              = 1
innodb_old_blocks_time          = 1000
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_io_capacity              = 200


kann ich alles andere so einsetzen?

Code: Select all

[client]
port      = 3306
max_allowed_packet              = 64M
socket      = /var/run/mysqld/mysqld.sock

[mysql]
prompt                          = \u@\h [\d]>\_
max_allowed_packet              = 64M
no_auto_rehash

[mysqld]
user      = mysql
pid-file   = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port      = 3306
#nice           = -5
basedir      = /usr
datadir      = /var/lib/mysql
tmpdir      = /var/tmp/mysql
secure-file-priv     = /var/tmp/mysql
language   = /usr/share/mysql/english
skip-external-locking
skip_name_resolve
skip_locking
skip-symbolic-links
bind-address      = 127.0.0.1
symbolic-links          = 0

server-id                       = 1
log_bin         = /var/log/mysql/mysql-bin.log
back_log                        = 500
sync_binlog                     = 1
#binlog_cache_size               = 4M
#binlog_stmt_cache_size          = 4M
max_binlog_size                 = 500M
binlog-format                   = MIXED
expire_logs_days                = 30
slow-query-log                  = 1
slow-query-log-file             = /var/log/mysql/mysql-slow.log

delay-key-write                 = ALL
myisam-recover                  = FORCE,BACKUP

key_buffer_size                 = 256M
max_allowed_packet              = 64M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
thread_stack                    = 192K
table_cache                     = 32768
table_open_cache                = 32768
table_definition_cache          = 16384
query_cache_type                = 1
query_cache_size                = 64M
thread_cache_size               = 150
max_connections                 = 200
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 2
local-infile                    = 0

log-warnings                    = 2
log-queries-not-using-indexes

innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 2G
#innodb_buffer_pool_instances    = 4
innodb_additional_mem_pool_size = 16M
innodb_data_file_path           = ibdata1:500M;ibdata2:50M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_file_per_table           = 1
#innodb_purge_threads            = 1
#innodb_strict_mode              = 1
#innodb_old_blocks_time          = 1000
innodb_stats_on_metadata        = 0
#innodb_write_io_threads         = 8
#innodb_read_io_threads          = 8
#innodb_io_capacity              = 200
innodb_open_files               = 8192

[mysqldump]
max_allowed_packet              = 64M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqlhotcopy]
interactive_timeout

!includedir /etc/mysql/conf.d/


wie ist es damit?
myisam_sort_buffer_size = 32M

Gruss
Last edited by fulltilt on 2013-04-13 16:18, edited 2 times in total.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Bis auf das !includedir (ist ein überflüssiges Sicherheitsrisiko) sieht es OK aus.

myisam_sort_buffer_size sollte man nur ändern, wenn man exakt weiss man tut, also besser gar nicht.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

Danke, das hat mir wirklich weitergeholfen ;-)
wegen myisam_sort_buffer_size frage ich weil während mysql repair und optimize Crons cpu load und iowait etwas hoch ist ...
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

MySQL-Tabellen ständig per cron zu optimieren ist nicht empfehlenswert und meist auch nicht nötig.
Das myisam-repair iowait und load ansteigen lässt ist völlig normal, da dies vollständig über die HDD läuft.

Wichtig ist, dass myisam-repair in Ruhe, d.h. ohne andere DB-Zugriffe, arbeiten kann. andernfalls kann es zu Problemen kommen.
Desweiteren solltest Du herausfinden, warum Deine Tabellen (so oft) kaputt gehen, denn normal ist das nicht.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

OK, dann werde ich das optimieren erst mal ganz weglassen, ich habe bisher dieses Script dazu verwendet:
http://wozia.com/blog/tips/bash-script-to-repair-all-mysql-tables-for-all-databases/

gilt das auch für einen täglichen mysqlcheck?

Code: Select all

#!/bin/sh
mysqlcheck --all-databases --auto-repair --user=root --password="xxxxxxx"
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Gegen ein regelmässiges mysqlcheck ist wenig einzuwenden.
OPTIMIZE und REPAIR hingegen ausschliesslich wenn es nötig ist (steht auch in dem Blog "Use it wisely.").

mysqltuner.pl schlägt das OPTIMIZE nahezu immer vor und nahezu immer ist es falsch/unnötig, nicht irritieren lassen.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Mal zum Vergleich, so sieht es auf einer meiner Installationen aus, ganz ohne OPTIMZE/REPAIR/mysqlcheck:

Code: Select all

[root@devnull:~] # ./scripts/mysqltuner.pl

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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.30-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1M (Tables: 1)
[--] Data in InnoDB tables: 646M (Tables: 232)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 26

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 2h 56m 0s (1M q [2.923 qps], 68K conn, TX: 2B, RX: 331M)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 4.2G global + 2.7M per thread (200 max threads)
[OK] Maximum possible memory usage: 4.7G (55% of installed RAM)
[OK] Slow queries: 1% (16K/1M)
[OK] Highest usage of available connections: 49% (99/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.4M
[OK] Key buffer hit rate: 99.5% (253K cached / 1K reads)
[OK] Query cache efficiency: 26.6% (170K cached / 640K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 27K sorts)
[OK] Temporary tables created on disk: 3% (2K on disk / 61K total)
[OK] Thread cache hit rate: 99% (99 created / 68K connections)
[OK] Table cache hit rate: 43% (400 open / 924 opened)
[OK] Open file limit used: 0% (53/32K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 646.7M/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance

[root@devnull:~] # ./scripts/tuning-primer.sh

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

MySQL Version 5.5.30-log amd64

Uptime = 5 days 2 hrs 56 min 6 sec
Avg. qps = 2
Total Questions = 1293845
Threads Connected = 6

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.5/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 = 1.000000 sec.
You have 16261 out of 1293866 that take longer than 1.000000 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 = 150
Current threads_cached = 93
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 6
Historic max_used_connections = 99
The number of used connections is 49% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 393 M
Current InnoDB data space = 646 M
Current InnoDB buffer pool free = 88 %
Current innodb_buffer_pool_size = 4.00 G
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 : 4.74 G
Configured Max Per-thread Buffers : 1.30 G
Configured Max Global Buffers : 4.10 G
Configured Max Memory Limit : 5.40 G
Physical Memory : 8.50 G
Max memory limit seem to be within acceptable norms

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

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 5 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 9.26 %
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 = 32768 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 2048 tables
Current table_definition_cache = 2048 tables
You have a total of 275 tables
You have 400 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 59501 temp tables, 3% were created on disk
Created disk tmp tables ratio seems fine

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

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

Auch dort ist noch genug Optimierungspotential, mir reicht es aber erstmal so.

EDIT: Die my.cnf dazu:

Code: Select all

[client]
port                            = 3306
max_allowed_packet              = 64M

[mysql]
prompt                          = \u@\h [\d]>\_
max_allowed_packet              = 64M
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /data/db/mysql
tmpdir                          = /data/tmp/mysql
slave-load-tmpdir               = /data/tmp/mysql
secure-file-priv                = /data/tmp/mysql
log-bin                         = /data/db/mysql/mysql-bin
relay-log                       = /data/db/mysql/relay.log
relay-log-index                 = /data/db/mysql/relay.index
relay-log-info-file             = /data/db/mysql/relay.info
master-info-file                = /data/db/mysql/master.info
server-id                       = 1
back_log                        = 500
sync_binlog                     = 1
binlog_cache_size               = 4M
binlog_stmt_cache_size          = 4M
max_binlog_size                 = 500M
binlog-format                   = MIXED
expire_logs_days                = 30
slow-query-log                  = 1
slow-query-log-file             = /data/db/mysql/slow-query.log
slave_compressed_protocol       = 1
lower_case_table_names          = 1
safe-user-create                = 1
delay-key-write                 = ALL
myisam-recover                  = FORCE,BACKUP
key_buffer_size                 = 8M
max_allowed_packet              = 64M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
thread_stack                    = 192K
table_cache                     = 2048
table_open_cache                = 2048
table_definition_cache          = 2048
net_retry_count                 = 16384
query_cache_type                = 1
query_cache_size                = 64M
thread_cache_size               = 150
max_connections                 = 200
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 1
local-infile                    = 0
log-warnings                    = 2
log-slave-updates
log-queries-not-using-indexes
skip-external-locking
skip-symbolic-links
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 4G
innodb_buffer_pool_instances    = 4
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir            = /data/db/mysql
innodb_log_group_home_dir       = /data/db/mysql
innodb_data_file_path           = ibdata1:2000M;ibdata2:2000M;ibdata3:10M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_file_per_table           = 1
innodb_purge_threads            = 1
innodb_strict_mode              = 1
innodb_old_blocks_time          = 1000
innodb_stats_on_metadata        = 0
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_io_capacity              = 200
innodb_open_files               = 8192

[mysqldump]
max_allowed_packet              = 64M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqlhotcopy]
interactive_timeout
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

Joe User wrote:Gegen ein regelmässiges mysqlcheck ist wenig einzuwenden.
OPTIMIZE und REPAIR hingegen ausschliesslich wenn es nötig ist (steht auch in dem Blog "Use it wisely.").

mysqltuner.pl schlägt das OPTIMIZE nahezu immer vor und nahezu immer ist es falsch/unnötig, nicht irritieren lassen.


ich habe wohl bisher immer "viel zu viel" gemacht und dass wesentliche vernachlässigt ;-)
die neue config läuft sogar in der VM sehr performant und ich werde es heute noch einbauen ...
ich habe das System vor 10 Tagen migriert und dabei auch vor dem Import des SQL Dumps alles gelöscht um ibdata neu generieren zu lassen ...
Muss ich dass jetzt unbedingt noch einmal machen bevor ich die neue my.cnf verwende?
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

Ja, da die InnoDB-Config entscheident geändert ist und MySQL entweder nicht starten würde, oder die InnoDB-Tabellen kaputt gehen könnten. Immer das Risiko so gering halten wie möglich und den Komfort hinten anstellen.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

also auch alle Datenbanken löschen und re-import oder reicht:

Code: Select all

rm /var/lib/mysql/ibdata[0-9] /var/lib/mysql/ib_logfile[0-9]

(vorher natürlich Backup angelegt)
Last edited by fulltilt on 2013-04-14 16:19, edited 1 time in total.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL probleme bei optimierung

Post by Joe User »

DBs dumpen, MySQL stoppen, ib* löschen, MySQL starten, DBs importieren, fertig.
Also das volle Programm.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

OK, dann werde ich das genau so machen ;-)
Danke
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

Hallo Joe User,
habe das ganze vorhin auf einem neuen Server so eingerichtet, beim starten von tuning-primer bekomme ich nun:
Unable to log into socket: /var/run/mysqld/mysqld.sock
woran könnte das liegen?

OS: debian squeeze

hier die my.cnf

Code: Select all

[client]
port      = 3306
max_allowed_packet              = 64M
socket      = /var/run/mysqld/mysqld.sock

[mysql]
prompt                          = \u@\h [\d]>\_
max_allowed_packet              = 64M
no_auto_rehash

[mysqld]
user      = mysql
pid-file   = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port      = 3306
#nice           = -5
basedir      = /usr
datadir      = /var/lib/mysql
tmpdir      = /var/tmp/mysql
secure-file-priv     = /var/tmp/mysql
language   = /usr/share/mysql/english
skip-external-locking
skip_name_resolve
skip_locking
skip-symbolic-links
bind-address      = 127.0.0.1
symbolic-links          = 0

server-id                       = 1
log_bin         = /var/log/mysql/mysql-bin.log
back_log                        = 500
sync_binlog                     = 1
#binlog_cache_size               = 4M
#binlog_stmt_cache_size          = 4M
max_binlog_size                 = 500M
binlog-format                   = MIXED
expire_logs_days                = 30
slow-query-log                  = 1
slow-query-log-file             = /var/log/mysql/mysql-slow.log

delay-key-write                 = ALL
myisam-recover                  = FORCE,BACKUP

key_buffer_size                 = 256M
max_allowed_packet              = 64M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
thread_stack                    = 192K
table_cache                     = 32768
table_open_cache                = 32768
table_definition_cache          = 16384
query_cache_type                = 1
query_cache_size                = 64M
thread_cache_size               = 150
max_connections                 = 200
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 2
local-infile                    = 0

log-warnings                    = 2
log-queries-not-using-indexes

innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 2G
#innodb_buffer_pool_instances    = 4
innodb_additional_mem_pool_size = 16M
innodb_data_file_path           = ibdata1:500M;ibdata2:50M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90

innodb_file_per_table           = 1
#innodb_purge_threads            = 1
#innodb_strict_mode              = 1
#innodb_old_blocks_time          = 1000

innodb_stats_on_metadata        = 0
#innodb_write_io_threads         = 8
#innodb_read_io_threads          = 8
#innodb_io_capacity              = 200
innodb_open_files               = 8192

#myisam_sort_buffer_size         = 32M

[mysqldump]
max_allowed_packet              = 64M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqlhotcopy]
interactive_timeout


mysql läuft auch nach restart aber irgendwo ist etwas faul:

Code: Select all

mysql -S /var/run/mysqld/mysqld.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


Code: Select all

130608 14:13:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130608 14:14:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 14:14:11 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking$
130608 14:14:11 [Note] Plugin 'FEDERATED' is disabled.
130608 14:14:11  InnoDB: Initializing buffer pool, size = 2.0G
130608 14:14:12  InnoDB: Completed initialization of buffer pool
130608 14:14:12  InnoDB: Started; log sequence number 0 49092012
130608 14:14:12 [Note] Event Scheduler: Loaded 0 events
130608 14:14:12 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.66-0+squeeze1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

Last edited by fulltilt on 2013-06-08 14:38, edited 2 times in total.
Top

fulltilt
Posts: 356
Joined: 2006-08-27 02:06

Re: MySQL probleme bei optimierung

Post by fulltilt »

noch zur Info, ich verwende ein + Zeichen im Passwort, könnte es daran liegen?
oder an dem
prompt = \u@\h [\d]>\_
Top