Aborted connection & slow_queries

[t-eddie]

Hallo RootForum-Gemeinde,

mir ist im mysqld.err Log folgendes ins Auge gefallen:

Code: Select all

081211 11:50:19 [Warning] Aborted connection 1257 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)
081211 11:52:39 [Warning] Aborted connection 1312 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)
081211 11:52:39 [Warning] Aborted connection 1313 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)
081211 11:56:04 [Warning] Aborted connection 1389 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)
081211 12:00:05 [Warning] Aborted connection 1432 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)
081211 12:00:05 [Warning] Aborted connection 1433 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)
081211 12:00:05 [Warning] Aborted connection 1434 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)
081211 12:00:19 [Warning] Aborted connection 1431 to db: 'syscp' user: 'syscp' host: 'localhost' (Got timeout reading communication packets)

Phpmyadmin sagt mir ausserdem, dass bei einer Laufzeit von 1h 45min des MySql-Servers:

Verbindungen:

Code: Select all

Fehlgeschlagen 	1 	0,58 	0,06%
Abgebrochen 	146 	84,88 	9,41%
Insgesamt 	1.552 	902,33 	100,00%

Code: Select all

Slow_queries  	9.655 
Select_full_join  	706
Select_range_check  	808

Lt. Mysql FAQ soll das auf einen fehlconfiguration des Servers hinweisen, bzw. fehlerhafte timeouts.
Hier mal meine my.cnf

Code: Select all

[client]
port                    = 3306
socket                  = /var/run/mysqld/mysqld.sock
character-sets-dir      = /usr/share/mysql/charsets
default-character-set   = latin1

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

[mysqldump]
quick
quote-names
max_allowed_packet      = 32M

[isamchk]
key_buffer              = 4M

[myisamchk]
key_buffer              = 12M

[mysqld_safe]
err_log         = /var/log/mysql/mysql.err

[mysqld]
character_sets_dir              = /usr/share/mysql/charsets
character_set_server            = utf8
default_character_set           = utf8
user                            = mysql
port                            = 3306
bind_address                    = 127.0.0.1
bind_address                    = *MYIPADRESS*
socket                          = /var/run/mysqld/mysqld.sock
pid_file                        = /var/run/mysqld/mysqld.pid
log_error                       = /var/log/mysql/mysqld.err
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /var/tmp
slave_load_tmpdir               = /var/tmp
language                        = /usr/share/mysql/english
log_bin                         = /var/lib/mysql/mysql-bin
relay-log                       = /var/lib/mysql/relay.log
relay-log-index                 = /var/lib/mysql/relay.index
master-info-file                = /var/lib/mysql/master.info
relay-log-info-file             = /var/lib/mysql/relay.info
#master_host                     = <hostname>
#master_user                     = <username>
#master_password                 = <password>
#master_port                     = 3306
#auto_increment_increment        = 10
#auto_increment_offset           = 1
server_id                       = 1
back_log                        = 50
sync_binlog                     = 0
binlog_cache_size               = 1M
max_binlog_size                 = 100M
expire_logs_days                = 7
slave_compressed_protocol       = 1
lower_case_table_names          = 1
delay_key_write                 = ALL
safe_user_create                = 1
myisam_repair_threads           = 1
myisam_recover                  = FORCE,BACKUP
skip_locking
skip_name_resolve
skip_external_locking
#skip_show_database
key_buffer_size                 = 64M
join_buffer_size                = 3M
sort_buffer_size                = 2M
read_buffer_size                = 2M
read_rnd_buffer_size            = 4M
myisam_sort_buffer_size         = 16M
max_allowed_packet              = 40M
max_heap_table_size             = 196M
tmp_table_size                  = 196M
table_cache                     = 15000
query_cache_type                = 4800
query_cache_size                = 24M
query_cache_limit               = 4M
query_cache_min_res_unit        = 512
thread_concurrency              = 16
thread_cache_size               = 16
max_connections                 = 60
wait_timeout                    = 45
connect_timeout                 = 25
open-files-limit                = 8240
ft_max_word_len                 = 20
ft_min_word_len                 = 3
local_infile                    = 0
log_warnings                    = 2
long_query_time                 = 3
log_slow_queries                = /var/log/mysql/slow-queries.log
log_queries_not_using_indexes
#log_slave_updates
#log_long_format
skip_innodb
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 32M
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir            = /var/lib/mysql
innodb_log_arch_dir             = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_data_file_path           = ibdata1:2000M;ibdata2:10M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 100M
innodb_log_buffer_size          = 8M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 90
innodb_file_per_table

[mysqlhotcopy]
interactive_timeout

Kann mir wer einen Tipp geben, wie ich die connection timeouts beheben kann? Und was ich gegen die SLOW-Queries machen kann?

Vielen Dank im voraus

t-eddie

[t-eddie]

Danke erstmal für die Antwort,

ich habe die entsprechende Frage schon im SysCp Forum platziert - vielleicht gibt es da Probleme mit dem Code selbst. Ein anpassen der connection-timeout in der my.cnf hat auf jeden Fall keine veränderung gebracht.

richtige langläufer habe ich eigentlich nicht. Ich habe die DB vohin neugestartet, und nach 1h habe ich bei 46.000 Anfragen nur 2 die länger als 3Sekunden gelaufen sind. Oder ist 3Sek. zu viel?

Was mir auch noch sorgen macht, ist die temp tables extrem hoch sind:

Code: Select all

von 2006 temp tables, 46% were created on disk

Das ist die aktuelle Einstellung in der my.cnf

Code: Select all

Current max_heap_table_size = 96 M
Current tmp_table_size = 96 M

Ich hatte den Wert auch schon bei 332MB - ohne das die tmp tables significant gesunken wäre - eher umgedreht...dann waren es 54%.

Aktuell bin ich dabei die Querys, welche mir das slow-query-log ausgespuckt hat zu überarbeiten. Obwohl es sich ja nicht um slow-querys handelt, sondern eher um querys die keinen Index benutzen bzw. joins without keys that check for key usage after each row.

Kann mir vielleicht noch wer nen Tipp geben? Vor allem in bezug auf die temp tables??

Danke

[t-eddie]

Neustart des MySQL Servers hab ich natürlich durchgeführt und kontrolliert ob die Änderungen übernommen worden sind. Ich habe mir jetzt mal das mysqltuner.pl Script noch mit zur Analyse herangezogen. Hier der Auszug:

Code: Select all

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.32-Debian_7etch8-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: 72M (Tables: 896)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 105

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 43m 0s (281K q [28.821 qps], 3K conn, TX: 1B, RX: 90M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 176.0M global + 11.2M per thread (80 max threads)
[OK] Maximum possible memory usage: 1.0G (53% of installed RAM)
[!!] Slow queries: 11% (32K/281K)
[OK] Highest usage of available connections: 51% (41/80)
[OK] Key buffer size / total MyISAM indexes: 16.0M/39.3M
[OK] Key buffer hit rate: 99.8% (30M cached / 47K reads)
[!!] Query cache efficiency: 0.0% (0 cached / 192K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23K sorts)
[!!] Joins performed without indexes: 4093
[!!] Temporary tables created on disk: 30% (3K on disk / 12K total)
[OK] Thread cache hit rate: 98% (41 created / 3K connections)
[OK] Table cache hit rate: 86% (955 open / 1K opened)
[OK] Open file limit used: 6% (1K/30K)
[OK] Table locks acquired immediately: 99% (275K immediate / 275K locks)

-------- 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
Variables to adjust:
    query_cache_limit (> 2M, or use smaller result sets)
    join_buffer_size (> 3.0M, or always use indexes with joins)
    tmp_table_size (> 96M)
    max_heap_table_size (> 96M)

Ich habe mir dann erstmal die fragmented Tables vorgenommen, aber ich finde keine 105 Fragmentierten Tabellen. Sondern nur die hier....und einige von denen lassen sich nicht reparieren, oder muss für die Reperatur die Datenbank zwingend gestoppt werden???

Code: Select all

/var/lib/mysql/vdb_backup# myisamchk --fast --silent /var/lib/mysql/*/*.MYI
myisamchk: MyISAM file /var/lib/mysql/eddiesql5/vtponygallery.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 623540            Should be: 625588
MyISAM-table '/var/lib/mysql/eddiesql5/vtponygallery.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/eddiesql5/vtstats_agents.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/eddiesql5/vtstats_agents.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/kthsql3/jos_banner.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/kthsql3/jos_banner.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/kthsql3/jos_content.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/kthsql3/jos_content.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/kthsql3/jos_joomlawatch_info.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found 4704 keys of 4706
MyISAM-table '/var/lib/mysql/kthsql3/jos_joomlawatch_info.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql3/jos_joomlawatch.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found 43 keys of 44
MyISAM-table '/var/lib/mysql/kthsql3/jos_joomlawatch.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql3/jos_joomlawatch_uri.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found 169 keys of 172
MyISAM-table '/var/lib/mysql/kthsql3/jos_joomlawatch_uri.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql3/jos_session.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found key at page 1024 that points to record outside datafile
MyISAM-table '/var/lib/mysql/kthsql3/jos_session.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql4/ebay_transaktionen.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/kthsql4/ebay_transaktionen.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/kthsql5/ek_joomlawatch_info.MYI
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of indexfile is: 3323904         Should be: 3633152
myisamchk: error: Size of datafile is: 6540548           Should be: 7270980
MyISAM-table '/var/lib/mysql/kthsql5/ek_joomlawatch_info.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql5/ek_joomlawatch.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found 46 keys of 50
MyISAM-table '/var/lib/mysql/kthsql5/ek_joomlawatch.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql5/ek_joomlawatch_uri.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of indexfile is: 10240           Should be: 14336
MyISAM-table '/var/lib/mysql/kthsql5/ek_joomlawatch_uri.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql5/ek_redirection.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of indexfile is: 5801984         Should be: 5806080
MyISAM-table '/var/lib/mysql/kthsql5/ek_redirection.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql5/ek_session.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of indexfile is: 8192            Should be: 10240
MyISAM-table '/var/lib/mysql/kthsql5/ek_session.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql6/gk_banner.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 2812              Should be: 2820
MyISAM-table '/var/lib/mysql/kthsql6/gk_banner.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql6/gk_joomlawatch_info.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found 14784 keys of 14788
MyISAM-table '/var/lib/mysql/kthsql6/gk_joomlawatch_info.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql6/gk_joomlawatch.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found 42 keys of 43
MyISAM-table '/var/lib/mysql/kthsql6/gk_joomlawatch.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql6/gk_joomlawatch_uri.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Found 638 keys of 686
MyISAM-table '/var/lib/mysql/kthsql6/gk_joomlawatch_uri.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql6/gk_session.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of indexfile is: 5120            Should be: 66560
myisamchk: error: Size of datafile is: 19644             Should be: 1573444
MyISAM-table '/var/lib/mysql/kthsql6/gk_session.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/kthsql6/gk_vm_product.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/kthsql6/gk_vm_product.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/kthsql6/gk_vm_product_price.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/kthsql6/gk_vm_product_price.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/syscp/panel_settings.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 4988              Should be: 5036
MyISAM-table '/var/lib/mysql/syscp/panel_settings.MYI' is corrupted
Fix it using switch "-r" or "-o"

Und wenn ich die Tabellen alle gefixt bekommen, dann muss ich da auch noch einen OPTIMIZE TABLE machen, oder wie? kann man auch mehrere Tabellen in einem rutsch (z.B. via script) optimieren?