Page 1 of 1
MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 16:27
by schüri
Hiho,
nach mehreren Stunden Internet-Recherche und einer etwas längeren Nacht (bis 5:30 Uhr) muss ich mich wieder mal an euch wenden :)
Ich betreibe eine Webseite auf Basis des CMS Clansphere.
Seit ca. 5 Tagen liegt die durchschnittliche Besucherzahl, die gleichzeitig auf der Seite surfen, bei ca. 400-500 - Tendenz steigend.
Nun läuft der mysql-Dienst seit gestern Amok... er benötigt bis zu 200% von der CPU (laut "top"). und die CPU ist auch durchgehend ausgelastet :(
Der Server hat 2 GB RAM.
Hier nun meine Config-Dateien, vllt findet ihr den "Killer"
my.cnf
Code: Select all
[client]
character-sets-dir = /usr/share/mysql/charsets
#character-set-server = utf8
port = 3306
[mysql]
character-sets-dir = /usr/share/mysql/charsets
#character-set-server = utf8
prompt = \u@\h [\d]>\_
no_auto_rehash
[mysqladmin]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
[mysqlcheck]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
[mysqldump]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
max_allowed_packet = 32M
quote_names
quick
[mysqlimport]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
[mysqlshow]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
[isamchk]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
key_buffer_size = 256M
[myisamchk]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
key_buffer_size = 256M
[myisampack]
character-sets-dir = /usr/share/mysql/charsets
character-set-server = utf8
[mysqld_safe]
err_log = /var/log/mysql/mysql.err
[mysqld]
user = mysql
port = 3306
bind-address = 127.0.0.1
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
character-sets-dir = /usr/share/mysql/charsets
#character-set-server = utf8
#collation-server = utf8_bin
default-storage-engine = InnoDB
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp # /var/tmp
slave-load-tmpdir = /var/tmp
language = /usr/share/mysql/english
log_error = /var/log/mysql/mysqld.err
log-bin = /var/lib/mysql/mysql-bin
#relay-log = /var/lib/mysql/relay.log
#relay-log-index = /var/lib/mysql/relay.index
#relay-log-info-file = /var/lib/mysql/relay.info
master-info-file = /var/lib/mysql/master.info
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = 3306
#auto_increment_increment = 10
#auto_increment_offset = 1
#server-id = 1 # nicht auskommentiert
back_log = 50
sync_binlog = 1
binlog_cache_size = 1M
max_binlog_size = 100M
binlog-format = MIXED
expire_logs_days = 7
#slow-query-log = 1
#slow-query-log-file = /var/lib/mysql/slow-query.log
slave_compressed_protocol = 1
lower_case_table_names = 1
safe-user-create = 1
delay-key-write = ALL
myisam-recover = BACKUP #FORCE,BACKUP
key_buffer_size = 16M
join_buffer_size = 4M
sort_buffer_size = 5M
read_buffer_size = 4M
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 64M
max_allowed_packet = 64M
max_heap_table_size = 256M
tmp_table_size = 256M
table_cache = 5000
table_definition_cache = 5000
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 3M
thread_concurrency = 16
thread_cache_size = 256
max_connections = 300
ft_max_word_len = 20
ft_min_word_len = 3
long_query_time = 4
local-infile = 1
log-warnings = 2
log-slave-updates
log-queries-not-using-indexes
skip-external-locking
skip-character-set-client-handshake
[mysqlhotcopy]
interactive_timeout
apache2.conf
Code: Select all
#
# Based upon the NCSA server configuration files originally by Rob McCool.
#
# This is the main Apache server configuration file. It contains the
# configuration directives that give the server its instructions.
# See http://httpd.apache.org/docs/2.2/ for detailed information about
# the directives.
#
# Do NOT simply read the instructions in here without understanding
# what they do. They're here only as hints or reminders. If you are unsure
# consult the online docs. You have been warned.
#
# The configuration directives are grouped into three basic sections:
# 1. Directives that control the operation of the Apache server process as a
# whole (the 'global environment').
# 2. Directives that define the parameters of the 'main' or 'default' server,
# which responds to requests that aren't handled by a virtual host.
# These directives also provide default values for the settings
# of all virtual hosts.
# 3. Settings for virtual hosts, which allow Web requests to be sent to
# different IP addresses or hostnames and have them handled by the
# same Apache server process.
#
# Configuration and logfile names: If the filenames you specify for many
# of the server's control files begin with "/" (or "drive:/" for Win32), the
# server will use that explicit path. If the filenames do *not* begin
# with "/", the value of ServerRoot is prepended -- so "/var/log/apache2/foo.log"
# with ServerRoot set to "" will be interpreted by the
# server as "//var/log/apache2/foo.log".
#
### Section 1: Global Environment
#
# The directives in this section affect the overall operation of Apache,
# such as the number of concurrent requests it can handle or where it
# can find its configuration files.
#
#
# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
#
# NOTE! If you intend to place this on an NFS (or otherwise network)
# mounted filesystem then please read the LockFile documentation (available
# at <URL:http://httpd.apache.org/docs-2.1/mod/mpm_common.html#lockfile>);
# you will save yourself a lot of trouble.
#
# Do NOT add a slash at the end of the directory path.
#
ServerRoot "/etc/apache2"
#
# The accept serialization lock file MUST BE STORED ON A LOCAL DISK.
#
#<IfModule !mpm_winnt.c>
#<IfModule !mpm_netware.c>
LockFile /var/lock/apache2/accept.lock
#</IfModule>
#</IfModule>
#
# PidFile: The file in which the server should record its process
# identification number when it starts.
# This needs to be set in /etc/apache2/envvars
#
PidFile ${APACHE_PID_FILE}
#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 30
#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive Off
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 50
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 2
##
## Server-Pool Size Regulation (MPM specific)
##
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 250
MaxRequestsPerChild 0
</IfModule>
# worker MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_worker_module>
StartServers 2
MinSpareThreads 25
MaxSpareThreads 75
ThreadLimit 64
ThreadsPerChild 25
MaxClients 150
MaxRequestsPerChild 0
</IfModule>
# event MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_event_module>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadLimit 64
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>
# These need to be set in /etc/apache2/envvars
User ${APACHE_RUN_USER}
Group ${APACHE_RUN_GROUP}
#
# AccessFileName: The name of the file to look for in each directory
# for additional configuration directives. See also the AllowOverride
# directive.
#
AccessFileName .htaccess
#
# The following lines prevent .htaccess and .htpasswd files from being
# viewed by Web clients.
#
<Files ~ "^\.ht">
Order allow,deny
Deny from all
Satisfy all
</Files>
#
# DefaultType is the default MIME type the server will use for a document
# if it cannot otherwise determine one, such as from filename extensions.
# If your server contains mostly text or HTML documents, "text/plain" is
# a good value. If most of your content is binary, such as applications
# or images, you may want to use "application/octet-stream" instead to
# keep browsers from trying to display binary files as though they are
# text.
#
DefaultType text/plain
#
# HostnameLookups: Log the names of clients or just their IP addresses
# e.g., www.apache.org (on) or 204.62.129.132 (off).
# The default is off because it'd be overall better for the net if people
# had to knowingly turn this feature on, since enabling it means that
# each client request will result in AT LEAST one lookup request to the
# nameserver.
#
HostnameLookups On
# ErrorLog: The location of the error log file.
# If you do not specify an ErrorLog directive within a <VirtualHost>
# container, error messages relating to that virtual host will be
# logged here. If you *do* define an error logfile for a <VirtualHost>
# container, that host's errors will be logged there and not here.
#
ErrorLog /var/log/apache2/error.log
#
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
#
LogLevel warn
# Include module configuration:
Include /etc/apache2/mods-enabled/*.load
Include /etc/apache2/mods-enabled/*.conf
# Include all the user configurations:
Include /etc/apache2/httpd.conf
# Include ports listing
Include /etc/apache2/ports.conf
#
# The following directives define some format nicknames for use with
# a CustomLog directive (see below).
# If you are behind a reverse proxy, you might want to change %h into %{X-Forwarded-For}i
#
LogFormat "%v:%p %h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" vhost_combined
LogFormat "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %O" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent
#
# Define an access log for VirtualHosts that don't define their own logfile
CustomLog /var/log/apache2/other_vhosts_access.log vhost_combined
# Include of directories ignores editors' and dpkg's backup files,
# see README.Debian for details.
# Include generic snippets of statements
Include /etc/apache2/conf.d/
# Include the virtual host configurations:
Include /etc/apache2/sites-enabled/
Was mir noch aufgefallen ist:
Es laufen ziemlich viele apache-Prozesse.. bei "top" werden teilweise bis zu 50+ apache2-Prozesse angezeigt.
Ich bedanke mich für eure Mühen - bisher seid ihr einfach die besten :-BD
@Joe User: Super neues Design :-D
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 16:33
by Joe User
Danke ;)
Poste bitte mal die Ausgaben von mysqltuner.pl und tuning-primer.sh, danke.
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 16:47
by schüri
Code: Select all
$ ./tuning-primer.sh
Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found Sockets: /var/run/mysqld/mysqld.sock
Using: /var/run/mysqld/mysqld.sock
Would you like to provide a different socket?: [y/N] N
Do you have your login handy ? [y/N] : y
User: root
Password: xxxxxxxxxxxxxxxx
Would you like me to create a ~/.my.cnf file for you? [y/N] : N
- FINAL LOGIN ATTEMPT FAILED -
Unable to log into socket: /var/run/mysqld/mysqld.sock
Das ist mein nächstes Problem, weshalb ich auch so lang wach war... ich habe versuscht das Tool zum Laufen zu kriegen.
Auch beim anderen Tool komme ich auch nur bis zur Eingabe der Logindaten:
Code: Select all
$ ./mysqltuner.pl
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[!!] Attempted to use login credentials, but they were invalid.
und die Logindaten sind korrekt:
Code: Select all
~$ mysql -uroot -pxxxxxxxxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1165
Server version: 5.1.41-3ubuntu12.6-log (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)]>
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 17:03
by schüri
show status:
Code: Select all
Variable_name Value
Aborted_clients 48
Aborted_connects 1
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 158
Bytes_sent 105
Com_admin_commands 0
Com_assign_to_keycache 0
Com_alter_db 0
Com_alter_db_upgrade 0
Com_alter_event 0
Com_alter_function 0
Com_alter_procedure 0
Com_alter_server 0
Com_alter_table 0
Com_alter_tablespace 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_binlog 0
Com_call_procedure 0
Com_change_db 0
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_event 0
Com_create_function 0
Com_create_index 0
Com_create_procedure 0
Com_create_server 0
Com_create_table 0
Com_create_trigger 0
Com_create_udf 0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_event 0
Com_drop_function 0
Com_drop_index 0
Com_drop_procedure 0
Com_drop_server 0
Com_drop_table 0
Com_drop_trigger 0
Com_drop_user 0
Com_drop_view 0
Com_empty_query 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 0
Com_insert_select 0
Com_install_plugin 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_release_savepoint 0
Com_rename_table 0
Com_rename_user 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_rollback_to_savepoint 0
Com_savepoint 0
Com_select 0
Com_set_option 2
Com_show_authors 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_contributors 0
Com_show_create_db 0
Com_show_create_event 0
Com_show_create_func 0
Com_show_create_proc 0
Com_show_create_table 0
Com_show_create_trigger 0
Variable_name Value
Com_show_databases 0
Com_show_engine_logs 0
Com_show_engine_mutex 0
Com_show_engine_status 0
Com_show_events 0
Com_show_errors 0
Com_show_fields 0
Com_show_function_status 0
Com_show_grants 0
Com_show_keys 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_plugins 0
Com_show_privileges 0
Com_show_procedure_status 0
Com_show_processlist 0
Com_show_profile 0
Com_show_profiles 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_table_status 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reprepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_uninstall_plugin 0
Com_unlock_tables 0
Com_update 0
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 7507
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 0
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 0
Innodb_buffer_pool_pages_data 174
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 338
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 512
Innodb_buffer_pool_read_ahead_rnd 3
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 6269
Innodb_buffer_pool_reads 149
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 0
Innodb_data_fsyncs 3
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 5033984
Innodb_data_reads 168
Innodb_data_writes 3
Innodb_data_written 1536
Innodb_dblwr_pages_written 0
Innodb_dblwr_writes 0
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 1
Innodb_os_log_fsyncs 3
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Variable_name Value
Innodb_os_log_written 512
Innodb_page_size 16384
Innodb_pages_created 0
Innodb_pages_read 174
Innodb_pages_written 0
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 0
Innodb_rows_inserted 0
Innodb_rows_read 0
Innodb_rows_updated 0
Key_blocks_not_flushed 764
Key_blocks_unused 6214
Key_blocks_used 7182
Key_read_requests 518946678
Key_reads 8477
Key_write_requests 23382
Key_writes 0
Last_query_cost 0.000000
Max_used_connections 247
Not_flushed_delayed_rows 0
Open_files 757
Open_streams 0
Open_table_definitions 194
Open_tables 649
Opened_files 23449
Opened_table_definitions 0
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 1154
Qcache_free_memory 62801288
Qcache_hits 272623
Qcache_inserts 65792
Qcache_lowmem_prunes 0
Qcache_not_cached 74850
Qcache_queries_in_cache 3829
Qcache_total_blocks 8852
Queries 461836
Questions 3
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 0
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_ctx_verify_depth 0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_timeouts 0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries 0
Ssl_verify_depth 0
Ssl_verify_mode 0
Ssl_version
Table_locks_immediate 91442
Table_locks_waited 88731
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 49
Threads_connected 198
Threads_created 247
Threads_running 140
Uptime 1317
Uptime_since_flush_status 1317
show innodb status:
Code: Select all
Type Name Status
InnoDB
=====================================
101019 17:0...
Die Prozessliste muss ich nachliefern...
Code: Select all
Id User Host db Command Time State Info
Beenden 4969 webseite localhost webseite Sleep 800 NULL
Beenden 6207 webseite localhost webseite Sleep 601 NULL
Beenden 6718 webseite localhost webseite Sleep 429 NULL
Beenden 6722 webseite localhost webseite Sleep 504 NULL
Beenden 6937 webseite localhost webseite Sleep 395 NULL
Beenden 7209 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 7267 webseite localhost webseite Sleep 334 NULL
Beenden 7414 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 7574 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 7628 webseite localhost webseite Sleep 256 NULL
Beenden 7643 webseite localhost webseite Sleep 208 NULL
Beenden 7680 webseite localhost webseite Sleep 236 NULL
Beenden 7698 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 7737 webseite localhost webseite Sleep 230 NULL
Beenden 7742 webseite localhost webseite Sleep 229 NULL
Beenden 7746 webseite localhost webseite Sleep 227 NULL
Beenden 7879 webseite localhost webseite Sleep 175 NULL
Beenden 7905 webseite localhost webseite Sleep 173 NULL
Beenden 7941 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 7995 webseite localhost webseite Sleep 163 NULL
Beenden 8206 webseite localhost webseite Sleep 122 NULL
Beenden 8247 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8269 webseite localhost webseite Query 3 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8314 webseite localhost webseite Sleep 98 NULL
Beenden 8326 webseite localhost webseite Sleep 97 NULL
Beenden 8327 webseite localhost webseite Sleep 97 NULL
Beenden 8351 webseite localhost webseite Sleep 94 NULL
Beenden 8425 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8426 webseite localhost webseite Sleep 77 NULL
Beenden 8436 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8440 webseite localhost webseite Sleep 76 NULL
Beenden 8447 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8483 webseite localhost webseite Sleep 71 NULL
Beenden 8486 webseite localhost webseite Sleep 70 NULL
Beenden 8499 webseite localhost webseite Sleep 69 NULL
Beenden 8509 webseite localhost webseite Sleep 67 NULL
Beenden 8510 webseite localhost webseite Sleep 67 NULL
Beenden 8512 webseite localhost webseite Sleep 66 NULL
Beenden 8526 webseite localhost webseite Sleep 62 NULL
Beenden 8531 webseite localhost webseite Query 0 Locked DELETE FROM cs_count WHERE count_time < '128726640...
Beenden 8541 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8549 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8567 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8568 webseite localhost webseite Sleep 55 NULL
Beenden 8571 webseite localhost webseite Sleep 54 NULL
Beenden 8572 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8576 webseite localhost webseite Sleep 53 NULL
Beenden 8590 webseite localhost webseite Query 1 Locked DELETE FROM cs_count WHERE count_time < '128726640...
Beenden 8596 webseite localhost webseite Sleep 50 NULL
Beenden 8602 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8603 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8608 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8615 webseite localhost webseite Sleep 48 NULL
Beenden 8618 webseite localhost webseite Sleep 48 NULL
Beenden 8640 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8641 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8646 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8655 webseite localhost webseite Sleep 40 NULL
Beenden 8661 webseite localhost webseite Sleep 39 NULL
Beenden 8663 webseite localhost webseite Sleep 39 NULL
Beenden 8662 webseite localhost webseite Sleep 39 NULL
Beenden 8666 webseite localhost webseite Sleep 38 NULL
Beenden 8667 webseite localhost webseite Sleep 38 NULL
Beenden 8669 webseite localhost webseite Sleep 37 NULL
Beenden 8673 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8675 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8682 webseite localhost webseite Sleep 34 NULL
Beenden 8685 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8688 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8692 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8695 webseite localhost webseite Sleep 31 NULL
Beenden 8701 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8708 webseite localhost webseite Sleep 30 NULL
Beenden 8710 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8714 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8720 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8724 webseite localhost webseite Sleep 25 NULL
Beenden 8725 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8726 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8727 webseite localhost webseite Sleep 25 NULL
Beenden 8728 webseite localhost webseite Sleep 25 NULL
Beenden 8730 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8731 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8734 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8735 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8736 webseite localhost webseite Query 3 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8737 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8739 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8740 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8741 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8743 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8742 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8744 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8746 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8748 webseite localhost webseite Sleep 23 NULL
Beenden 8749 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8752 webseite localhost webseite Sleep 23 NULL
Beenden 8758 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8759 webseite localhost webseite Sleep 22 NULL
Beenden 8760 webseite localhost webseite Sleep 22 NULL
Id User Host db Command Time State Info
Beenden 8761 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8762 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8763 webseite localhost webseite Sleep 22 NULL
Beenden 8764 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8765 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8767 webseite localhost webseite Sleep 21 NULL
Beenden 8768 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8769 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8770 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8771 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8774 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8775 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8776 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8778 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8779 webseite localhost webseite Sleep 19 NULL
Beenden 8780 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8781 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8782 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8783 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8785 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8786 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8787 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8788 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8789 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8790 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8791 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8792 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8793 webseite localhost webseite Sleep 17 NULL
Beenden 8794 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8795 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8796 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8797 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8798 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8799 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8800 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8801 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8802 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8803 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8804 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8805 webseite localhost webseite Sleep 15 NULL
Beenden 8806 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8807 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8808 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8809 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8812 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8813 webseite localhost webseite Sleep 14 NULL
Beenden 8814 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8815 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8816 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8817 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8818 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8819 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8820 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8821 webseite localhost webseite Sleep 12 NULL
Beenden 8822 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8823 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8826 webseite localhost webseite Sleep 11 NULL
Beenden 8827 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8828 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8829 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8830 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8831 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8832 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8833 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8834 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8835 webseite localhost webseite Sleep 10 NULL
Beenden 8836 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8837 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8838 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8839 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8840 webseite localhost webseite Sleep 9 NULL
Beenden 8841 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8842 webseite localhost webseite Sleep 8 NULL
Beenden 8843 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8844 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8845 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8846 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8847 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8848 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8849 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8850 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8851 webseite localhost webseite Sleep 5 NULL
Beenden 8852 webseite localhost webseite Sleep 5 NULL
Beenden 8853 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8854 webseite localhost webseite Sleep 5 NULL
Beenden 8855 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8856 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8857 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8858 webseite localhost webseite Query 2 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8859 webseite localhost webseite Sleep 4 NULL
Beenden 8860 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8861 webseite localhost webseite Sleep 4 NULL
Beenden 8862 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8863 webseite localhost webseite Query 0 Locked SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8864 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8865 webseite localhost webseite Sleep 4 NULL
Beenden 8866 webseite localhost webseite Sleep 4 NULL
Beenden 8867 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8868 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8869 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Id User Host db Command Time State Info
Beenden 8870 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493863', count...
Beenden 8871 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8872 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8873 webseite localhost webseite Query 1 Sending data SELECT COUNT(*) FROM cs_count WHERE count_time > '...
Beenden 8874 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8875 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8876 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8877 webseite localhost webseite Sleep 1 NULL
Beenden 8878 webseite localhost webseite Sleep 1 NULL
Beenden 8879 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8880 webseite localhost webseite Query 1 Locked SELECT count_id, count_time FROM cs_count WHERE co...
Beenden 8881 webseite localhost webseite Query 1 Locked UPDATE cs_count SET count_time='1287493864', count...
Beenden 8882 webseite localhost webseite Query 0 Locked UPDATE cs_count SET count_time='1287493865', count...
Beenden 8883 webseite localhost webseite Query 0 Locked SELECT count_id, count_time FROM cs_count WHERE co...
Beenden 8884 webseite localhost webseite Sleep 0 NULL
Beenden 8885 webseite localhost webseite Query 0 Locked UPDATE cs_count SET count_time='1287493865', count...
Beenden 8886 webseite localhost webseite Query 0 Locked SELECT count_id, count_time FROM cs_count WHERE co...
Beenden 8887 phpmyadmin localhost NULL Sleep 0 NULL
Beenden 8888 root localhost NULL Query 0 NULL show processlist
Beenden 8889 webseite localhost webseite Sleep 0 NULL
Beenden 8890 webseite localhost webseite Query 0 Locked SELECT count_id, count_time FROM cs_count WHERE co...
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 17:07
by Joe User
Wird der MySQL-Socket unter Ubuntu wirklich als /var/run/mysqld/mysqld.sock angelegt? Gegebenfalls bitte den Pfad (und die anderen Pfade) in der my.cnf an Ubuntu anpassen.
Für die Scripte bitte /root/
.my.cnf (Punkt beachten) mit folgendem Inhalt anlegen:
Code: Select all
[client]
user = root
password = PaSsWoRd
Die httpd.conf schaut erstmal OK aus.
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 17:24
by schüri
Joe User wrote:Wird der MySQL-Socket unter Ubuntu wirklich als /var/run/mysqld/mysqld.sock angelegt? Gegebenfalls bitte den Pfad (und die anderen Pfade) in der my.cnf an Ubuntu anpassen.
Für die Scripte bitte /root/
.my.cnf (Punkt beachten) mit folgendem Inhalt anlegen:
Code: Select all
[client]
user = root
password = PaSsWoRd
Die httpd.conf schaut erstmal OK aus.
ich habe die Konfiguration aus diesem Thread übernommen:
http://www.rootforum.org/forum/viewtopi ... 04&t=36343
ich habe gerade aber in der Ursprungs-Konfig nachgeschaut, dort steht derselbe Pfad drin
.my.ncf-Datei ist angelegt - hmm scheint aber immer noch nicht zu funktionieren:
Code: Select all
$ ./tuning-primer.sh
Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found Sockets: /var/run/mysqld/mysqld.sock
Using: /var/run/mysqld/mysqld.sock
Would you like to provide a different socket?: [y/N] N
Do you have your login handy ? [y/N] : y
User: root
Password: xxxxxxx
Would you like me to create a ~/.my.cnf file for you? [y/N] : N
- FINAL LOGIN ATTEMPT FAILED -
Unable to log into socket: /var/run/mysqld/mysqld.sock
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 17:32
by schüri
wie mach ich das?
also beide Tools funktionieren nicht
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 17:39
by Joe User
Sind user und pw definitiv richtig? Debian und dessen Derivate wie Ubuntu nutzen auch gerne mal statt "root" andere absurde Usernamen für root-User.
Die von mir im anderen Thread gepostete my.cnf muss natürlich immer an die Eigenheiten, insbesondere der Pfade, der jeweiligen Distro angepasst werden. Da ich Debian und dessen Derivate grundsätzlich nicht einsetze, kann ich die dortigen Pfade nicht selbst prüfen.
PS:
Aktueller mysqltuner.pl
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 18:02
by schüri
@mattze:
ich habe jetzt den Counter ausgebaut - nun sollten diese Abfragen der Geschichte angehören...
@Joe:
sind es definitiv - ich kann mich darüber ins phpmyadmin und auch über die Konsole in mysql einloggen
siehe meinen Post letzter Code-Tag:
http://www.rootforum.org/forum/viewtopi ... 47#p319547
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 18:16
by Joe User
OK, dann stimmt der Pfad zum Socket nicht.
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 18:30
by schüri
und wie bekomme ich den heraus? :)
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 18:55
by Joe User
Eventuell im Init-Script, oder auf gut Glück in der my.cnf beide socket kommentieren (ich mag Debian nicht ;) )
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 19:54
by Joe User
Hilft ihm mit der falschen Config nicht viel ;)
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 20:05
by Joe User
http://ubuntuforums.org/archive/index.php/t-312460.html bitte vollständig lesen, zweimal nachdenken, beide Möglichkeiten prüfen, Ergebnis(se) hier posten.
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 20:07
by Joe User
matzewe01 wrote:Dann eben mysql> show global variables like 'socket';
Wobei er doch in seiner Config oben in der mysqld Sektion den socket angegeben hat... Habe ich was übersehen?
Ja: Client und Server wollen momentan unterschiedliche Sockets, mal wieder ein typischer Debian/Ubuntu-Bug.
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-19 20:51
by Joe User
Zumindest mysqltuner prüft den initialen Login per mysqladmin und ob mysqladmin nun den (falschen) Socketpath hardcoded bekommt oder die my.cnf ausliest, habe ich nicht mehr im Kopf :/
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 03:32
by schüri
Joe User wrote:Eventuell im Init-Script, oder auf gut Glück in der my.cnf beide socket kommentieren (ich mag Debian nicht ;) )
"auf gut Glück" hat wohl funktioniert ;)
Code: Select all
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.41-3ubuntu12.6-log x86_64
Uptime = 0 days 0 hrs 2 min 54 sec
Avg. qps = 72
Total Questions = 12551
Threads Connected = 2
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
You have 3498 out of 12586 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is enabled
WORKER THREADS
Current thread_cache_size = 24
Current threads_cached = 4
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 1
Historic max_used_connections = 5
The number of used connections is 1% 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
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 460 M
Configured Max Per-thread Buffers : 4.46 G
Configured Max Global Buffers : 384 M
Configured Max Memory Limit : 4.84 G
Physical Memory : 3.87 G
nMax memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 16 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 3374
Key buffer free ratio = 79 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 1 M
Current query_cache_limit = 16 M
Current Query cache Memory fill ratio = 1.13 %
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 = 8 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 2.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 = 2358 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 1024 tables
Current table_definition_cache = 1024 tables
You have a total of 151 tables
You have 159 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 486 temp tables, 31% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 1962 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 26
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'.
ich werde nun aber erstmal den Server 24-48 Stunden laufen lassen - dann schauen wir mal :)
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 03:35
by schüri
hab mal nach mysqld.sock gesucht:
$ whereis mysqld.sock
mysqld: /usr/sbin/mysqld /usr/share/man/man8/mysqld.8.gz
vllt sollte ich mal es mit dieser mysqld.sock probieren... aber schauen wir mal was der morgige Tag bringt
in diesem Sinnne gute Nacht und großes Dankeschön für eure Hilfe! :-BD
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 10:29
by Joe User
Teste bitte mal folgende my.cnf unverändert (bitte wegen den Ubuntu-Bugs als /etc/my.cnf und /etc/mysql/my.cnf anlegen):
Code: Select all
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysql]
prompt = \u@\h [\d]>\_
no_auto_rehash
[mysqldump]
max_allowed_packet = 32M
quote_names
quick
[isamchk]
key_buffer_size = 256M
[myisamchk]
key_buffer_size = 256M
[mysqld_safe]
err_log = /var/log/mysql/mysql.err
[mysqld]
user = mysql
port = 3306
bind-address = 127.0.0.1
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmp
slave-load-tmpdir = /var/tmp
language = /usr/share/mysql/english
log_error = /var/log/mysql/mysqld.err
log-bin = /var/lib/mysql/mysql-bin
relay-log = /var/lib/mysql/relay.log
relay-log-index = /var/lib/mysql/relay.index
relay-log-info-file = /var/lib/mysql/relay.info
master-info-file = /var/lib/mysql/master.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 = 1
binlog_cache_size = 1M
max_binlog_size = 100M
binlog-format = MIXED
expire_logs_days = 7
slow-query-log = 1
slow-query-log-file = /var/lib/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 = 32M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
max_allowed_packet = 32M
max_heap_table_size = 64M
tmp_table_size = 64M
table_cache = 1024
table_definition_cache = 1024
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 16M
thread_concurrency = 8
thread_cache_size = 24
max_connections = 24
ft_max_word_len = 20
ft_min_word_len = 3
long_query_time = 2
local-infile = 0
log-warnings = 2
log-slave-updates
log-queries-not-using-indexes
skip-external-locking
skip-character-set-client-handshake
skip-innodb
[mysqlhotcopy]
interactive_timeout
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 11:02
by schüri
Tuning-Primer von heute Morgen:
Code: Select all
$ ./tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.41-3ubuntu12.6-log x86_64
Uptime = 0 days 7 hrs 19 min 40 sec
Avg. qps = 180
Total Questions = 4760664
Threads Connected = 47
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
You have 1192365 out of 4760790 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is enabled
WORKER THREADS
Current thread_cache_size = 24
Current threads_cached = 5
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 46
Historic max_used_connections = 219
The number of used connections is 73% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 0 bytes
Current InnoDB data space = 0 bytes
Current InnoDB buffer pool free = 99 %
Current innodb_buffer_pool_size = 2.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 : 5.71 G
Configured Max Per-thread Buffers : 4.46 G
Configured Max Global Buffers : 2.45 G
Configured Max Memory Limit : 6.92 G
Physical Memory : 3.87 G
nMax memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 16 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 684005
Key buffer free ratio = 77 %
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 = 64 M
Current query_cache_used = 9 M
Current query_cache_limit = 16 M
Current Query cache Memory fill ratio = 15.10 %
Current query_cache_min_res_unit = 4 K
Query Cache is 28 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
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 = 8 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 2.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 = 2358 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 1024 tables
Current table_definition_cache = 1024 tables
You have a total of 194 tables
You have 456 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 58614 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 SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 2616 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 3
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'.
max_used_connections liegt bei 219 und ich schätze, die Zahl wird noch steigen ;)
query_cache_min_res_unit... gibts hier für einen guten Wert? Ich habe ihn mal auf 512 gesetzt und die Seite läuft seitdem viel flüssiger ;)
@Joe: max_connections 24 ? bissl wenig oder?
@Matze: ich habe erstmal key_buffer runtergesetzt, dann sollte der RAM net mehr so ausgelastet werden
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 11:13
by Joe User
24 Connection sind viel, glaube mir ;)
Dein System verkraftet nichtmal die 24 Connections, da jede zusätzliche Connection (viel) RAM braucht und so Dein System lahmlegt.
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 15:59
by schüri
aber wenn ich da jetzt 24 Eintrage, kommt keiner mehr auf die Seite wegen "Too many connections"...
und richtig, es ist ein vServer - aber mir gehört auch der Root-Server, auf dem der vServer läuft
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 16:29
by schüri
ok, werde ich machen
habt ihr noch was an meiner apache-Konfiguration auszusetzen?
manchmal habe ich das Gefühl, dass da auch was schief läuft :(
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-20 16:40
by Joe User
schüri wrote:aber wenn ich da jetzt 24 Eintrage, kommt keiner mehr auf die Seite wegen "Too many connections"...
Wenn das auch bei 50 noch passiert, dann ist definitiv die WebApp kaputt.
Zum Vergleich: Das RootForum läuft aktuell mit phpBB 3.0, WordPress 3.1, MediaWiki 1.16 und Piwik 1.0 bei max_connections 24 und benötigt davon zu Spitzenzeiten nichtmal 20. Und diese WebApps sind weitestgehend unoptimiert und gehen daher nicht wirklich sparsam mit den Connections und anderen Resourcen um.
Die Apache Config schaut für mich OK aus.
Re: MySQL läuft Amok -> CPU 200%
Posted: 2010-10-23 19:56
by schüri
um euch nochmal in Kenntnis zu setzen, wie es nun ausgegangen ist:
Ich habe das Clansphere von 2009 auf die aktuelle 2010-Version geupdatet. Daraufhin war dieser Counter nicht mehr drin bzw. hat nicht mehr gezählt.
Seitdem läuft die Seite tadellos - 700+ Besucher sind online und die Seite ist flott :)
Btw. die max_connection liegt bisher bei 250 - denke, mehr wird es auch nicht ;)
Vielen Dank nochmal für eure Hilfe - ich schaue bei allen Fragen eh nur noch hier vorbei :-BD
Grüße
schüri