Hallo,
dies ist mein erster Beitrag hier, also bitte nicht schlagen, wenn meine Frage(n) blöd klingen.
Also ich habe folgendes Problem, ich habe einen Server, auf dem ich ein Board am Laufen habe mit einer mittelgroßen Community (ca. 5000 User).
Zur Zeit haben wir sehr große Probleme mit unserer Serverauslastung und es kommt sehr oft die Meldung "Serverlast zu hoch, versuchen Sie es später noch einmal" und ich weiß nicht wirklich wie ich dem entgegen wirken kann, da ich noch nicht solange im Geschäft bin (Server und Community habe ich nur übernommen, da unser Admin einfach keinen Bock mehr hatte und mir das Ganze aufs Auge gedrückt hat...)
Nunja lange Rede kurzer Sinn ich hab mich etwas umgeschaut was man denn optimieren könnte und bin dabei u.a. auf primer gestoßen und bei intensiverer Suche eben auf dieses Forum.
Die Anleitung hier ist wirklich spitze und ich konnte schon ein paar Sachen verbesseren, so kommt z.B. das "Serverlast zu hoch" gar nicht mehr, jedoch ist der Seitenaufbau immer noch sehr langsam.
Die Daten des Servers sind ja eigentlich sehr gut und ich verstehe nicht wieso er so lahm ist, insbesondere da wir max. 500 Leute gleichzeitig online haben:
Intel Dual Xeon 3.0Ghz
2048MB RAM
2x 250GB in Hardware RAID-0
2000GB Traffic
100mbit/s Anbindung
primer hat mir folgende Sachen ausgegeben und ich kann wie gesagt wenig damit anfangen:
Code:
Uptime = 1 days 0 hrs 37 min 7 sec
Avg. qps = 569
Total Questions = 50489171
Threads Connected = 7
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
SLOW QUERIES
Current long_query_time = 2 sec.
You have 2070 out of 50489196 that take longer than 2 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine
WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 61
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 6
Historic max_used_connections = 159
The number of used connections is 31% of the configured maximum.
Your max_connections variable seems to be fine.
MEMORY USAGE
Max Memory Ever Allocated : 1003 M
Configured Max Per-thread Buffers : 1 G
Configured Max Global Buffers : 586 M
Configured Max Memory Limit : 1 G
Total System Memory : 1.97 G
Max memory limit exceeds 85% of total system memory
KEY BUFFER
Current MyISAM index space = 18 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 111063
Key buffer fill ratio = 2.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 = 64 M
Current query_cache_used = 21 M
Current query_cach_limit = 2 M
Current Query cache fill ratio = 32.89 %
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 = 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 = 2558 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 = 1024 tables
You have a total of 371 tables
You have 1024 open tables.
Current table_cache hit rate is 36%, while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 64 M
Of 843424 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 231 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 10
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 concurrentcy of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
Ich weiß mysql läuft noch keine 48 h, aber ich hab es schon mal durchlaufen lassen und da waren die Werte fast identisch (mysql lief zu dem Zeitpunkt ca. 6 Tage)...
hier auch noch meine my.conf:
Code:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 512M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
max_connections = 500
#max_connections = 100
tmp_table_size = 64M
table_cache = 512
thread_concurrency = 8
#
# * Query Cache Configuration
#
query_cache_limit = 4M
query_cache_size = 128M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
#expire_logs_days = 10
#max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 32M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 256M
#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1
#
# * IMPORTANT: Additional settings that can override those from this file!
#
!includedir /etc/mysql/conf.d/
Ich hoffe, dass ihr mir vllt helfen könnt und euch nicht schon wegen des veilen Zeugs, dass ich gepostet habe, abwendet von meinem Beitrag
EDIT: Da ich nicht weiß, ob es relevant ist oder nicht, habe ich sicherheitshalber noch die apache.conf (in der httpd.conf ist gähnende Leere) und die php.ini als Anhang hinzugefügt