alternative_
New Member
Hallo,
also ich habe mal eine Frage wo ich da, was falsch eingestellt habe da mir tuning-primer folgendes ausgibt .
Server 2GB
Memory Usage irritiert mich etwas, wo habe ich da zu viel vergeben?
meine my.cnf
und apache
noch mal TOP
Habe ein CMS am laufen, mit ca. 100-150 User gleichzeitig Online ( ca. 3000/Tag )
Habe sehr oft 100% Cpu auslastung und würde gern wissen, ob man an der Servereinstellung noch was optimieren kann, oder es am CMS selbst liegt!
Danke
also ich habe mal eine Frage wo ich da, was falsch eingestellt habe da mir tuning-primer folgendes ausgibt .
Server 2GB
Code:
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 5 sec.
You have 2942 out of 114383922 that take longer than 5 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 = 32
Current threads_cached = 30
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 2
Historic max_used_connections = 126
The number of used connections is 63% of the configured maximum.
Your max_connections variable seems to be fine.
MEMORY USAGE
Max Memory Ever Allocated : 11 G
Configured Max Per-thread Buffers : 18 G
Configured Max Global Buffers : 650 M
Configured Max Memory Limit : 18 G
Physical Memory : 1.97 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 40 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 16051
Key buffer fill ratio = 4.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 = 128 M
Current query_cache_used = 16 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 12.59 %
Current query_cache_min_res_unit = 4 K
Query Cache is 22 % 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 = 16 M
Current read_rnd_buffer_size = 63 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 81102 queries where a join could not use an index properly
You have had 15 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 4306 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 331 tables
You have 531 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 512 M
Of 1096490 temp tables, 34% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
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 = 11 M
Current table scan ratio = 114 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_buffer
TABLE LOCKING
Current Lock Wait ratio = 1 : 409
You may benefit from selective use of InnoDB.
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.meine my.cnf
Code:
# * Fine Tuning
#
key_buffer = 512M
max_allowed_packet = 1M
table_cache = 2048
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 64M
record_buffer = 12M
myisam_sort_buffer_size = 128M
thread_cache_size = 32
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2
max_connections = 200
tmp_table_size = 512M
max_heap_table_size = 256M
low_priority_updates=1
#
# * Query Cache Configuration
#
query_cache_limit=2M
query_cache_size=128M
query_cache_type = 1
# * 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 = 5
#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 = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2Mund apache
Code:
#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 300
#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive On
#
# 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 100
#
# 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 150
ServerLimit 150
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
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>noch mal TOP
Code:
Tasks: 130 total, 6 running, 122 sleeping, 1 stopped, 1 zombie
Cpu(s): 95.2%us, 4.2%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st
Mem: 2075708k total, 1825156k used, 250552k free, 74024k buffers
Swap: 2650684k total, 5312k used, 2645372k free, 1222788k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29541 www-data 20 0 74236 43m 14m R 49 2.1 0:10.89 apache2
29471 www-data 21 0 74276 43m 14m R 37 2.2 0:11.78 apache2
29726 www-data 15 0 52344 21m 13m S 21 1.1 0:03.02 apache2
29598 www-data 18 0 77072 44m 13m R 21 2.2 0:02.68 apache2
29421 www-data 15 0 52648 24m 15m S 19 1.2 0:09.59 apache2
2299 mysql 15 0 709m 211m 4864 S 7 10.4 1648:54 mysqld
29591 www-data 18 0 52308 21m 13m S 3 1.1 0:02.16 apache2
29731 www-data 16 0 58264 25m 11m R 2 1.2 0:01.78 apache2
26632 www-data 18 0 53448 24m 15m S 0 1.2 0:27.16 apache2
27444 www-data 15 0 52740 23m 14m S 0 1.2 0:16.68 apache2
29536 www-data 15 0 51772 18m 10m S 0 0.9 0:01.06 apache2
29588 www-data 16 0 52356 22m 14m S 0 1.1 0:02.96 apache2
29592 www-data 18 0 51952 19m 11m S 0 1.0 0:03.01 apache2
29613 www-data 15 0 52668 23m 15m S 0 1.2 0:05.62 apache2
29709 www-data 18 0 0 0 0 Z 0 0.0 0:01.25 apache2 <defunct>
29754 www-data 16 0 51212 12m 6012 S 0 0.6 0:00.02 apache2
29755 www-data 25 0 51188 12m 5984 S 0 0.6 0:00.01 apache2
1 root 17 0 1944 636 544 S 0 0.0 0:10.46 initHabe ein CMS am laufen, mit ca. 100-150 User gleichzeitig Online ( ca. 3000/Tag )
Habe sehr oft 100% Cpu auslastung und würde gern wissen, ob man an der Servereinstellung noch was optimieren kann, oder es am CMS selbst liegt!
Danke
Last edited by a moderator:
