PDA

Archiv verlassen und diese Seite im Standarddesign anzeigen : Mysqld 99% CPU auslastung



bombaldi
30.12.07, 19:25
Hallo,

Mir ist vorhin aufgefallen das mein mysql server viel zu viel CPU last macht, ich stehe grade mit der Frage wo ich nach der Ursache suchen soll ein wenig im dunkeln...

Mein Server hat 512MB Ram 2Ghz Celeron und momentan läuft eine Seite mit ca. 6000 Visits am Tag und eine mit 2000 Visits am Tag, sowie ein Irc Server.


Ich poste euch einfach mal die Standard ausgben


Top:

top - 10:15:12 up 22:34, 1 user, load average: 1.47, 1.27, 1.28
Tasks: 86 total, 10 running, 76 sleeping, 0 stopped, 0 zombie
Cpu(s): 90.6%us, 9.4%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 500668k total, 445352k used, 55316k free, 19572k buffers
Swap: 1461872k total, 36k used, 1461836k free, 310208k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2072 mysql 14 -1 132m 41m 5544 S 86.7 8.5 164:09.46 mysqld
12745 www-data 16 0 19948 7800 4508 R 12.1 1.6 0:05.19 apache2
1 root 15 0 1944 644 552 S 0.0 0.1 0:00.82 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 R 0.0 0.0 0:00.02 ksoftirqd/0
4 root 10 -5 0 0 0 S 0.0 0.0 0:00.02 events/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper


Mysql Status


Dieser MySQL-Server läuft bereits 0 Tage, 22 Stunden, 35 Minuten und 16 Sekunden. Er wurde am 29. Dezember 2007 um 11:41 gestartet.

Traffic Tip ø pro Stunde
Empfangen 938 MiB 42 MiB
Gesendet 2.152 MiB 95 MiB
Insgesamt 3.089 MiB 137 MiB

Verbindungen ø pro Stunde %
max. gleichzeitige Verbindungen 20 --- ---
Fehlgeschlagen 85 3,76 0,08%
Abgebrochen 6 0,27 0,01%
Insgesamt 107 k 4.735,26 100,00%



my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0


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

bind-address = 127.0.0.1


key_buffer = 16M


query_cache_size = 16M
max_connections = 200
low_priority_updates = 1
long_query_time = 2





tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.32-Debian_7etch1-log i486

Uptime = 0 days 22 hrs 38 min 40 sec
Avg. qps = 134
Total Questions = 10969789
Threads Connected = 6

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:
MySQL AB :: MySQL 5.0 Reference Manual :: 5.1.3 System Variables (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html)
Visit MySQL AB :: MySQL Enterprise Advisors (http://www.mysql.com/products/enterprise/advisors.html)
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 2 sec.
You have 69 out of 10969824 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 = 0
Current threads_cached = 0
Current threads_per_sec = 4
Historic threads_per_sec = 1
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 6
Historic max_used_connections = 20
The number of used connections is 10% 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

MEMORY USAGE
Max Memory Ever Allocated : 96 M
Configured Max Per-thread Buffers : 543 M
Configured Max Global Buffers : 42 M
Configured Max Memory Limit : 585 M
Physical Memory : 488.93 M

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 233 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 26155
Key buffer fill ratio = 17.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 = 16 M
Current query_cache_used = 8 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 55.61 %
Current query_cache_min_res_unit = 4 K
Query Cache is 12 % 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.
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 14 queries where a join could not use an index properly
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 = 1024 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 = 64 tables
You have a total of 1107 tables
You have 64 open tables.
Current table_cache hit rate is 0%, 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 = 32 M
Of 28689 temp tables, 4% 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 = 243 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1966
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'.


Und die Mysql Variablen


Variable Wert für diese Sitzung / Globaler Wert
auto increment increment 1
auto increment offset 1
automatic sp privileges ON
back log 50
basedir /usr/
binlog cache size 32.768
bulk insert buffer size 8.388.608
character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_unicode_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
completion type 0
concurrent insert 1
connect timeout 5
datadir /var/lib/mysql/
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1.000
div precision increment 4
engine condition pushdown OFF
expire logs days 10
flush OFF
flush time 0
ft boolean syntax + -><()~*:""&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1.024
have archive YES
have bdb NO
have blackhole engine NO
have compress YES
have crypt YES
have csv YES
have dynamic loading YES
have example engine NO
have federated engine YES
have geometry YES
have innodb YES
have isam NO
have merge engine YES
have ndbcluster DISABLED
have openssl DISABLED
have query cache YES
have raid NO
have rtree keys YES
have symlink YES
init connect
init file
init slave
innodb additional mem pool size 1.048.576
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 8.388.608
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table OFF
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log arch dir
innodb log archive OFF
innodb log buffer size 1.048.576
innodb log file size 5.242.880
innodb log files in group 2
innodb log group home dir ./
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb rollback on timeout OFF
innodb support xa ON
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 8
innodb thread sleep delay 10.000
interactive timeout 28.800
join buffer size 131.072
key buffer size 16.777.216
key cache age threshold 300
key cache block size 1.024
key cache division limit 100
language /usr/share/mysql/english/
large files support ON
large page size 0
large pages OFF
lc time names en_US
license GPL
local infile ON
locked in memory OFF
log OFF
log bin ON
log bin trust function creators OFF
log error /var/log/mysql.err
log queries not using indexes OFF
log slave updates OFF
log slow queries ON
log warnings 1
long query time 2
low priority updates ON
lower case file system OFF
lower case table names 0
max allowed packet 1.048.576
max binlog cache size 4.294.967.295
max binlog size 104.857.600
max connect errors 10
max connections 200
max delayed threads 20
max error count 64
max heap table size 16.777.216
max insert delayed threads 20
max join size 18.446.744.073.709.552.000
max length for sort data 1.024
max prepared stmt count 16.382
max relay log size 0
max seeks for key 4.294.967.295
max sort length 1.024
max sp recursion depth 0
max tmp tables 32
max user connections 0
max write lock count 4.294.967.295
multi range count 256
myisam data pointer size 6
myisam max sort file size 2.147.483.647
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 8.388.608
myisam stats method nulls_unequal
ndb autoincrement prefetch sz 32
ndb force send ON
ndb use exact count ON
ndb use transactions ON
(Globaler Wert) OFF
ndb cache check time 0
net buffer length 16.384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old passwords OFF
open files limit 1.024
optimizer prune level 1
optimizer search depth 62
pid file /var/run/mysqld/mysqld.pid
port 3.306
preload buffer size 32.768
protocol version 10
query alloc block size 8.192
query cache limit 1.048.576
query cache min res unit 4.096
query cache size 16.777.216
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8.192
range alloc block size 2.048
read buffer size 131.072
read only OFF
read rnd buffer size 262.144
relay log purge ON
relay log space limit 0
rpl recovery rank 0
secure auth OFF
server id 1
skip external locking ON
skip networking OFF
skip show database OFF
slave compressed protocol OFF
slave load tmpdir /tmp/
slave net timeout 3.600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
socket /var/run/mysqld/mysqld.sock
sort buffer size 2.097.144
sql big selects ON
sql mode
sql notes ON
sql warnings OFF
ssl ca
ssl capath
ssl cert
ssl cipher
ssl key
storage engine MyISAM
sync binlog 0
sync frm ON
system time zone PST
table cache 64
table lock wait timeout 50
table type MyISAM
thread cache size 0
thread stack 196.608
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
tmp table size 33.554.432
tmpdir /tmp
transaction alloc block size 8.192
transaction prealloc size 4.096
tx isolation REPEATABLE-READ
updatable views with limit YES
version 5.0.32-Debian_7etch1-log
version comment Debian etch distribution
version compile machine i486
version compile os pc-linux-gnu
wait timeout 28.800






Für jedes kommentar bin ich euch wie immer dankbar ;)


Achja, tuning primer sagt mir mein Memory limit ist zu hoch ich habe gerade eine halbe Stunde gegoogled aber ich finde in der Mysql doku keinen eintrag der das memory limit festlegt...

lg,
Tom

asi_dkn
30.12.07, 19:57
Ich kenne mich mit MySQL nicht so gut aus. Ich habe aber von der Oracle Seite her vielleicht ein paar Anregungen die dir helfen können.

Ne Oracle DB verbraucht viel CPU wenn sie zu wenig Memory hat (also zu wenig Daten im Cache halten kann), wenn Indizes schlecht gesetzt sind oder gar fehlen (bei grossen Tabellen die oft abgefragt werden gibt das bald viel Disk I/O was die CPU ebenfalls beansprucht).

Weisst du ob MySQL soviel CPU verbraucht wenn der Server unter Last steht oder auch dann wenn weniger User unterwegs sind? Wie verhält sie die Performance der Webseiten? Ist der Zugriff spürbar langsam oder merkt man nix davon? Laufen irgend welche Verarbeitungen, Reports oder so was auf der DB? Sind einige Dinge die du vielleicht auch noch beachten kannst / solltest.

Ich würde dir mal empfehlen den I/O des Systems im Auge zu behalten, das kann durchaus ein Bottleneck darstellen. Es würde sich sicher auch lohnen mal zu checken welche SQL Abfragen am häufigsten gemacht werden und was die genau machen, eventuell findest du dort ein Antwort auf dein Problem.

Gruss