zunächsteinmal möchte ich sagen, dass ich vorher alle relevanten Themen gelesen habe und nicht einfach nur ein Problem poste ohne mich vorher im Forum zu informieren.
Leider habe ich trotz vieler Posts hier keine Lösung zu meinem Problem gefunden, deswegen hoffe ich dass mir der ein oder andere hier etwas helfen kann.
Hier mein Problem...
Ich habe eine extrem hohe Server Load von ca 5-6 den ganzen tag Obwohl nur ca 20-40 Apache Prozesse am laufen sind. Ich habe eine Seite die in PHP geschrieben ist und die Load ist ständig bei 5-6. Ich habe schon alle möglichen Apache Module rausgeworfen und weiss nun einfach nicht mehr weiter.
Kein Swap des Arbeitsspeichers = ausreichend
Auf einem nicht so Leistungsfähigen Server hatte ich eine Load nie mehr als 1,5 und dabei waren noch mehr Besucher online.
Ich denke mit mysql ist alles soweit okay, denn mysql braucht relativ wenig von der CPU und die Speicherauslastung ist auch nicht mein Problem.
Hauptsächlich Apache verursacht dieses Problem...
Was bedeutet dies... Bei vielen anderen usern laß ich das TABLE LOCKING 1:5000 ist bei mir relativ niedrig... liegt das Problem daran ?
TABLE LOCKING
Current Lock Wait ratio = 1 : 17
You may benefit from selective use of InnoDB.
Hoffe jemand von euch kann mir helfen...
Zuerst zu meinem Rootserver:
Apache 2.2.0
Mysql 5.X
PHP 5.1.2
AMD 3000+
1024 MB Ram
Plesk 8.2
SuSELinux
Auszug aus meiner My.cnf
Code: Select all
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
set-variable=local-infile=0
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
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
skip_locking
skip_name_resolve
skip_external_locking
skip_show_database
key_buffer_size = 16M
max_allowed_packet = 32M
max_heap_table_size = 64M
tmp_table_size = 64M
table_cache = 2048
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 8M
thread_concurrency = 8
thread_cache_size = 32
max_connections = 60
max_connect_errors = 20
ft_max_word_len = 30
ft_min_word_len = 3
local_infile = 0
log_warnings = 2
long_query_time = 3
log_slow_queries = slow-queries.log
#log_queries_not_using_indexes
#log_slave_updates
log_long_format
myisam_recover
#skip_bdb
#skip_innodb
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 384M
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 = 256M
innodb_log_buffer_size = 256M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
max_join_size= 2M
join_buffer_size = 512K
low_priority_updates=1
concurrent_insert=2
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
# The safe_mysqld script
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Code: Select all
# prefork MPM
<IfModule prefork.c>
# number of server processes to start
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#startservers
StartServers 5
# minimum number of server processes which are kept spare
# http://httpd.apache.org/docs/2.2/mod/prefork.html#minspareservers
MinSpareServers 5
# maximum number of server processes which are kept spare
# http://httpd.apache.org/docs/2.2/mod/prefork.html#maxspareservers
MaxSpareServers 12
# highest possible MaxClients setting for the lifetime of the Apache process.
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#serverlimit
ServerLimit 100
# maximum number of server processes allowed to start
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#maxclients
MaxClients 100
# maximum number of requests a server process serves
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#maxrequestsperchild
MaxRequestsPerChild 8000
</IfModule>
# worker MPM
<IfModule worker.c>
# initial number of server processes to start
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#startservers
StartServers 3
# minimum number of worker threads which are kept spare
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#minsparethreads
MinSpareThreads 25
# maximum number of worker threads which are kept spare
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#maxsparethreads
MaxSpareThreads 75
# upper limit on the configurable number of threads per child process
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#threadlimit
ThreadLimit 64
# maximum number of simultaneous client connections
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#maxclients
MaxClients 150
# number of worker threads created by each child process
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#threadsperchild
ThreadsPerChild 25
# maximum number of requests a server process serves
# http://httpd.apache.org/docs/2.2/mod/mpm_common.html#maxrequestsperchild
MaxRequestsPerChild 8000
</IfModule>
KeepAlive On
Timeout 30
HostNameLookups off
MaxKeepAliveRequests 100
KeepAliveTimeout 1
BrowserMatch "Mozilla/2" nokeepalive
BrowserMatch "MSIE 4.0b2;" nokeepalive downgrade-1.0 force-response-1.0
BrowserMatch "RealPlayer 4.0" force-response-1.0
BrowserMatch "Java/1.0" force-response-1.0
BrowserMatch "JDK/1.0" force-response-1.0
BrowserMatch "Microsoft Data Access Internet Publishing Provider" redirect-carefully
BrowserMatch "^WebDrive" redirect-carefully
BrowserMatch "^WebDAVFS/1.[012]" redirect-carefully
BrowserMatch "^gnome-vfs" redirect-carefully
Code: Select all
85-31-186-181:/etc/apache2 # ps aux --sort:command
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 66 0.0 0.0 0 0 ? S< 16:33 0:00 [aio/0]
root 677 0.0 0.0 0 0 ? S< 16:33 0:00 [ata/0]
root 4357 0.0 0.2 9876 2396 pts/1 Ss 18:10 0:00 -bash
root 6119 0.0 0.1 7400 1160 ? S 18:21 0:00 /usr/sbin/courierlogger imapd
root 6131 0.0 0.1 7396 1156 ? S 18:21 0:00 /usr/sbin/courierlogger imapd-ssl
root 6142 0.0 0.1 7400 1160 ? S 18:21 0:00 /usr/sbin/courierlogger pop3d
root 6151 0.0 0.1 7396 1156 ? S 18:21 0:00 /usr/sbin/courierlogger pop3d-ssl
root 6117 0.0 0.0 9544 724 ? S 18:21 0:00 /usr/lib/courier-imap/couriertcpd -address=0 -stderrlogger=/usr/sbin/courierlogger -stderrlo
root 6129 0.0 0.0 9544 720 ? S 18:21 0:00 /usr/lib/courier-imap/couriertcpd -address=0 -stderrlogger=/usr/sbin/courierlogger -stderrlo
root 6138 0.0 0.0 9544 720 ? S 18:21 0:00 /usr/lib/courier-imap/couriertcpd -address=0 -stderrlogger=/usr/sbin/courierlogger -stderrlo
root 6148 0.0 0.0 9544 720 ? S 18:21 0:00 /usr/lib/courier-imap/couriertcpd -address=0 -stderrlogger=/usr/sbin/courierlogger -stderrlo
root 274 0.0 0.0 0 0 ? S< 16:33 0:00 [cqueue/0]
root 2739 0.0 0.0 5996 556 ? Ss 16:34 0:00 /usr/sbin/cron
100 1894 0.0 0.0 13032 908 ? Ss 16:34 0:00 /usr/bin/dbus-daemon --system
root 3 0.0 0.0 0 0 ? S< 16:33 0:00 [events/0]
root 2075 0.0 0.2 12972 2756 ? Ss 16:34 0:00 /usr/sbin/hald --daemon=yes --retain-privileges
root 6487 0.0 0.9 169556 9908 ? Ss 18:22 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 6488 0.0 0.4 169528 4404 ? S 18:22 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8465 1.1 1.7 178356 17272 ? S 19:05 0:02 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8467 1.0 1.7 178248 17172 ? S 19:05 0:02 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8544 1.0 1.7 178796 17700 ? S 19:07 0:01 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8556 1.7 1.7 178040 17092 ? S 19:07 0:02 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8557 1.3 1.7 178248 17136 ? S 19:07 0:02 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8558 1.3 1.7 178832 17580 ? S 19:07 0:01 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8560 1.9 1.7 178576 17600 ? S 19:07 0:02 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8566 1.5 1.7 178100 17148 ? S 19:08 0:01 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8567 1.7 0.0 0 0 ? Z 19:08 0:01 [httpd2-prefork] <defunct>
wwwrun 8570 1.1 1.7 178032 17044 ? S 19:08 0:01 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8578 1.5 1.7 178036 17084 ? S 19:08 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8579 0.6 1.7 178068 17108 ? S 19:08 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8581 1.6 1.7 178012 17060 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8583 2.2 1.7 178228 17108 ? S 19:09 0:01 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8584 1.9 1.7 178032 17076 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8585 1.3 1.7 178016 17056 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8586 1.7 1.7 178020 17028 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8588 1.4 1.6 178016 16872 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8589 1.4 1.6 178012 16880 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8590 2.5 1.7 178580 17536 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8591 3.3 1.7 178580 17492 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8592 1.3 1.0 171984 10620 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8593 0.6 1.0 171544 10104 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8594 0.0 0.5 169556 5432 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
wwwrun 8595 0.8 1.2 173604 12340 ? S 19:09 0:00 /usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf
root 6217 0.0 0.6 71552 6196 ? Ss 18:21 0:00 /usr/local/psa/admin/bin/httpsd
psaadm 7629 0.0 0.3 71552 3324 ? S 18:43 0:00 /usr/local/psa/admin/bin/httpsd
root 1 0.0 0.0 776 192 ? S 16:33 0:00 init [3]
root 7 0.0 0.0 0 0 ? S< 16:33 0:00 [kblockd/0]
root 4 0.0 0.0 0 0 ? S< 16:33 0:00 [khelper]
root 1364 0.0 0.0 0 0 ? S< 16:34 0:00 [khubd]
root 796 0.0 0.0 0 0 ? S 16:34 0:00 [kjournald]
root 2188 0.0 0.0 2880 668 ? Ss 16:34 0:00 /sbin/klogd -c 1 -x -x
root 306 0.0 0.0 0 0 ? S< 16:33 0:00 [kpsmoused]
root 275 0.0 0.0 0 0 ? S< 16:33 0:00 [kseriod]
root 2 0.0 0.0 0 0 ? SN 16:33 0:00 [ksoftirqd/0]
root 65 0.0 0.0 0 0 ? S 16:33 0:02 [kswapd0]
root 5 0.0 0.0 0 0 ? S< 16:33 0:00 [kthread]
root 2906 0.0 0.0 3060 708 tty1 Ss+ 16:34 0:00 /sbin/mingetty --noclear tty1
root 2907 0.0 0.0 3056 700 tty2 Ss+ 16:34 0:00 /sbin/mingetty tty2
root 2908 0.0 0.0 3060 692 tty3 Ss+ 16:34 0:00 /sbin/mingetty tty3
root 2909 0.0 0.0 3056 696 tty4 Ss+ 16:34 0:00 /sbin/mingetty tty4
root 2910 0.0 0.0 3060 692 tty5 Ss+ 16:34 0:00 /sbin/mingetty tty5
root 2911 0.0 0.0 3060 696 tty6 Ss+ 16:34 0:00 /sbin/mingetty tty6
root 2913 0.0 0.2 29368 2516 ? Ssl 16:34 0:00 /usr/local/psa/admin/bin/modules/watchdog/monit -Ic /usr/local/psa/etc/modules/watchdog/moni
mysql 7559 10.5 10.7 780256 106832 ? Sl 18:43 2:48 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql
root 7522 0.0 0.1 7976 1140 ? S 18:43 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/85-31-186-18
named 5867 0.0 0.2 48096 2756 ? Ssl 18:20 0:00 /usr/sbin/named -t /var/lib/named -u named
Code: Select all
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
SLOW QUERIES
Current long_query_time = 3 sec.
You have 2 out of 97195 that take longer than 3 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine
WORKER THREADS
Current thread_cache_size = 32
Current threads_cached = 29
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 60
Current threads_connected = 7
Historic max_used_connections = 42
The number of used connections is 70% of the configured maximum.
Your max_connections variable seems to be fine.
MEMORY USAGE
Max Memory Ever Allocated : 808 M
Configured Max Per-thread Buffers : 183 M
Configured Max Global Buffers : 680 M
Configured Max Memory Limit : 863 M
Total System Memory : 2.81 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 34 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 299
Key buffer fill ratio = 36.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 1 M
Current query_cach_limit = 8 M
Current Query cache fill ratio = 13.89 %
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 record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 512.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
OPEN FILES LIMIT
Current open_files_limit = 4166 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 2048 tables
You have a total of 481 tables
You have 526 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 431 temp tables, 12% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 2733 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 17
You may benefit from selective use of InnoDB.
TOP
Code: Select all
top - 19:25:58 up 2:52, 1 user, load average: 5.79, 3.65, 2.54
Tasks: 123 total, 2 running, 121 sleeping, 0 stopped, 0 zombie
Cpu(s): 69.4% us, 11.1% sy, 0.0% ni, 18.5% id, 0.0% wa, 0.0% hi, 0.9% si
Mem: 995576k total, 828704k used, 166872k free, 3404k buffers
Swap: 1951856k total, 312748k used, 1639108k free, 149520k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9554 wwwrun 15 0 174m 15m 4428 S 11.7 1.6 0:01.87 httpd2-prefork
9556 wwwrun 15 0 174m 15m 4484 S 11.7 1.6 0:01.87 httpd2-prefork
9540 wwwrun 15 0 174m 15m 4440 S 9.9 1.6 0:02.09 httpd2-prefork
9587 wwwrun 15 0 173m 15m 4360 S 9.9 1.6 0:01.87 httpd2-prefork
9529 wwwrun 16 0 174m 16m 4464 S 9.0 1.6 0:02.88 httpd2-prefork
9637 wwwrun 15 0 174m 15m 4400 S 9.0 1.6 0:00.86 httpd2-prefork
9702 wwwrun 15 0 174m 15m 4404 S 6.3 1.6 0:00.22 httpd2-prefork
9617 wwwrun 16 0 174m 15m 4404 S 2.7 1.6 0:01.13 httpd2-prefork
9611 wwwrun 15 0 174m 15m 4452 S 1.8 1.6 0:01.12 httpd2-prefork
9635 wwwrun 16 0 173m 15m 4356 S 1.8 1.6 0:00.88 httpd2-prefork
7559 mysql 15 0 771m 32m 2868 S 0.9 3.3 4:15.35 mysqld
9588 wwwrun 15 0 174m 15m 4428 S 0.9 1.6 0:01.53 httpd2-prefork
9622 wwwrun 16 0 173m 15m 4360 S 0.9 1.6 0:00.61 httpd2-prefork
9634 wwwrun 15 0 174m 15m 4356 S 0.9 1.6 0:00.59 httpd2-prefork
9674 wwwrun 16 0 173m 15m 4380 S 0.9 1.6 0:00.60 httpd2-prefork
9704 wwwrun 15 0 174m 15m 4392 S 0.9 1.6 0:00.30 httpd2-prefork
9711 root 16 0 5592 1240 880 R 0.9 0.1 0:00.04 top
1 root 16 0 776 76 40 S 0.0 0.0 0:00.24 init
2 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
3 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/0
4 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
7 root 10 -5 0 0 0 S 0.0 0.0 0:00.04 kblockd/0
63 root 15 0 0 0 0 S 0.0 0.0 0:00.04 pdflush
64 root 15 0 0 0 0 S 0.0 0.0 0:00.03 pdflush
66 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0
65 root 15 0 0 0 0 S 0.0 0.0 0:02.43 kswapd0
274 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
275 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod
306 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused
677 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 ata/0
710 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_0
711 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_1
796 root 15 0 0 0 0 S 0.0 0.0 0:00.79 kjournald
855 root 18 -4 7096 312 308 S 0.0 0.0 0:00.18 udevd
1364 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd
1377 root 20 0 0 0 0 S 0.0 0.0 0:00.00 shpchpd_event
1883 root 16 0 10088 504 500 S 0.0 0.1 0:00.00 resmgrd
Vielen Dank
Uwe