mysqld erzeugt kurzzeitig hohe CPU Last

BlackPixel · Aug 2, 2007

Hallo Gemeinde,

einer meiner Server hat anscheinend ab und an irgendein Problem mit mysqld.

Spontan geht die Last auf dem Server hoch ohne Ende, gleichzeitig nimmt mysqld Speicher ohne Ende ein.

Ich hab mal top über einen löngeren Zeitraum überwachen lassen. Dass ergebniss findet Ihr am Ende des Posts.

die mysqld.log hat um diesen Zeitraum keine Einträge. Meine mysql_slow_log hat zu dieser Zeit zwar Einträge, aber in der vergangenheit keine Deckungsgleichen, bzw keine die mir aufgefallen sind.

Was kann ich noch überprüfen? um dem Mysterium auf die Schliche zu kommen? Auf dem Server selbst laufen 3 Typo3 Instanzen sowie ein phpbb2. Ansonsten geht es Lastmässig auf dem Server eher gemütlich zu. Braucht Ihr weitere Infos?

Gruß Manfred

Code:

top - 19:30:02 up 1 day, 10:12,  1 user,  load average: 0.27, 0.45, 0.45
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.9% id,  4.4% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   290688k used,   718552k free,     2212k buffers
Swap:  3919840k total,   352596k used,  3567244k free,   135300k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  258m  42m 2548 S    0  4.3   9:35.98 mysqld

top - 19:31:01 up 1 day, 10:13,  1 user,  load average: 0.32, 0.43, 0.44
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.9% id,  4.4% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   323872k used,   685368k free,     2292k buffers
Swap:  3919840k total,   352596k used,  3567244k free,   151992k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  258m  56m 3204 S    0  5.8   9:36.59 mysqld

top - 19:32:02 up 1 day, 10:14,  1 user,  load average: 0.15, 0.36, 0.41
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.9% id,  4.4% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   324976k used,   684264k free,     2348k buffers
Swap:  3919840k total,   352596k used,  3567244k free,   152044k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  258m  57m 3204 S    0  5.9   9:36.94 mysqld

top - 19:33:01 up 1 day, 10:15,  1 user,  load average: 0.11, 0.31, 0.39
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.9% id,  4.4% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   329112k used,   680128k free,     2424k buffers
Swap:  3919840k total,   352596k used,  3567244k free,   152344k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  258m  58m 3204 S    0  5.9   9:37.59 mysqld

top - 19:34:02 up 1 day, 10:16,  1 user,  load average: 0.24, 0.31, 0.38
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.9% id,  4.4% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   341616k used,   667624k free,     2480k buffers
Swap:  3919840k total,   352596k used,  3567244k free,   157640k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  258m  58m 3204 S    2  5.9   9:37.96 mysqld

top - 19:35:10 up 1 day, 10:17,  1 user,  load average: 3.72, 1.12, 0.64
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.9% id,  4.5% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   999288k used,     9952k free,      184k buffers
Swap:  3919840k total,   739208k used,  3180632k free,    12520k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0 1424m 892m 2536 S   88 90.6   9:56.81 mysqld

top - 19:36:09 up 1 day, 10:18,  1 user,  load average: 11.24, 3.68, 1.54
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.8% id,  4.5% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   999216k used,    10024k free,      224k buffers
Swap:  3919840k total,  1428940k used,  2490900k free,    62976k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     18   0 1972m 796m 2236 S    5 80.8  10:06.51 mysqld

top - 19:37:12 up 1 day, 10:19,  1 user,  load average: 10.93, 5.00, 2.14
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.8% id,  4.5% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   999116k used,    10124k free,      160k buffers
Swap:  3919840k total,  1907132k used,  2012708k free,    11392k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     17   0 2363m 883m 2340 S   12 89.6  10:14.82 mysqld

top - 19:38:13 up 1 day, 10:20,  1 user,  load average: 12.48, 6.40, 2.79
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.8% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,  1000600k used,     8640k free,      164k buffers
Swap:  3919840k total,  2232152k used,  1687688k free,    13204k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     17   0 2690m 886m 2224 S    9 90.0  10:21.75 mysqld

top - 19:39:11 up 1 day, 10:21,  1 user,  load average: 17.10, 8.85, 3.86
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   636008k used,   373232k free,      228k buffers
Swap:  3919840k total,  2187332k used,  1732508k free,    37772k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     17   0 2209m 398m 2192 S    0 40.4  10:27.13 mysqld

top - 19:40:01 up 1 day, 10:22,  1 user,  load average: 14.66, 9.47, 4.33
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   674088k used,   335152k free,      364k buffers
Swap:  3919840k total,  1644800k used,  2275040k free,   132412k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     17   0 1639m 197m 2384 S    0 20.1  10:27.84 mysqld

top - 19:41:02 up 1 day, 10:23,  1 user,  load average: 7.85, 8.59, 4.35
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   280404k used,   728836k free,      436k buffers
Swap:  3919840k total,   338428k used,  3581412k free,    93768k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     15   0  259m  51m 2440 S    0  5.3  10:29.60 mysqld

top - 19:42:01 up 1 day, 10:24,  1 user,  load average: 3.27, 7.13, 4.12
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   297680k used,   711560k free,      540k buffers
Swap:  3919840k total,   338088k used,  3581752k free,   109752k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     15   0  259m  52m 2444 S    0  5.3  10:30.98 mysqld

top - 19:43:02 up 1 day, 10:25,  1 user,  load average: 1.73, 5.99, 3.91
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   298356k used,   710884k free,      604k buffers
Swap:  3919840k total,   338088k used,  3581752k free,   110908k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  259m  52m 2444 S    0  5.4  10:32.24 mysqld

top - 19:44:01 up 1 day, 10:26,  1 user,  load average: 0.82, 4.95, 3.68
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.5% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   326068k used,   683172k free,      684k buffers
Swap:  3919840k total,   338088k used,  3581752k free,   121984k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     15   0  259m  53m 2444 S   16  5.5  10:33.63 mysqld

top - 19:45:02 up 1 day, 10:27,  1 user,  load average: 0.41, 4.07, 3.46
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.5% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   306240k used,   703000k free,      780k buffers
Swap:  3919840k total,   338088k used,  3581752k free,   116192k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  259m  53m 2444 S    0  5.5  10:34.62 mysqld

top - 19:46:01 up 1 day, 10:28,  1 user,  load average: 0.28, 3.36, 3.25
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   305844k used,   703396k free,      836k buffers
Swap:  3919840k total,   338088k used,  3581752k free,   117036k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  259m  53m 2444 S    0  5.5  10:34.98 mysqld

top - 19:47:02 up 1 day, 10:29,  1 user,  load average: 0.17, 2.77, 3.05
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   305592k used,   703648k free,      884k buffers
Swap:  3919840k total,   338088k used,  3581752k free,   117508k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25818 mysql     16   0  259m  54m 2444 S    0  5.5  10:35.96 mysqld

top - 19:48:01 up 1 day, 10:30,  1 user,  load average: 0.39, 2.36, 2.89
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.4% us,  1.9% sy,  0.0% ni, 83.7% id,  4.6% wa,  0.1% hi,  0.3% si
Mem:   1009240k total,   320716k used,   688524k free,      940k buffers

Huschi · Aug 2, 2007

Wie man sieht wird in der selben Zeit eine gehörige Menge Swap-Speicher aufgebaut und nach dem Höhepunkt wieder frei gegeben.
Ich tippe darauf, daß Deine Buffer, Connections, etc. zu hoch angesiedelt sind und MySQL zuviel Speicher braucht. (Oder ein anderes Programm wie z.B. der Apache.)
Das Swappen und gleichzeitige Lesen aus den Tabellen lastet alles den Server entsprechend aus. Zieh Deine Parameter soweit runter, daß Du mit Deinem physischen Speicher auskommst.

huschi.

BlackPixel · Aug 3, 2007

Hallo Huschi, danke.

Da ich mir das ganze ansehen konnte, als es zugeschlagen hat, war der mysqld prozess der einizige der den speicher sowie die prozessorlast in anspruch nahm. php-cgi dümpelt ab und an auch da oben vor sich hin, httpd ist so gut wie gar nicht zu sehen.

Was mich stutzig macht ist die Tatsache, dass es diese Peaks 3 bis 4 mal am Tag gibt. Enormen zuwachs an Besuchern zu dieser Zeit ist allerdings nicht zu verzeichnen. Andere Scripte oder backups oder sonstiges habe ich geprüft, da gibt es nichts was um diese Zeit das auslösen könnte. Zumal die Zeiten für die Peaks nicht immer identisch sind.

Aus dem unteren Auszug macht mir bis auf die max_connections nicht zu denken. Vielleicht kann da ein mysql Profi mal einen Blick drauf werfen.

Vielen Dank, Manfred

Code:

SLOW QUERIES
Current long_query_time = 2 sec.
You have 842 out of 755349 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 = 5
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 = 15
Current threads_connected = 1
Historic max_used_connections = 16
The number of used connections is 106% of the configured maximum.
You should raise max_connections

MEMORY USAGE
Max Memory Ever Allocated : 228 M
Configured Max Per-thread Buffers : 133 M
Configured Max Global Buffers : 87 M
Configured Max Memory Limit : 220 M
Total System Memory : 4.70 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 65 M
Current key_buffer_size = 65 M
Key cache miss rate is 1 : 818
Key buffer fill ratio = 75.00 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 12 M
Current query_cache_used = 9 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 80.19 %
However, 76807 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 512 K
Current record/read_rnd_buffer_size = 3 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 1 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 = 6027 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 = 3001 tables
You have a total of 608 tables
You have 1286 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 19754 temp tables, 13% 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 = 3 M
Current table scan ratio = 5144 : 1
read_buffer_size seems to be fine

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

BlackPixel · Aug 3, 2007

Uupp... gerade entdeckt:

Configured Max Per-thread Buffers : 133 M

Historic max_used_connections = 16

16 *133 = 2128M

Stimmt das so?

Sollte Max Per-thread buffer ao angelegt sein, dass es mit den max_used_connections im RAM passt?

daseddy · Aug 3, 2007

Kommen die Peaks regelmäßig?
Ist vielleicht eine "sql injection"?

BlackPixel · Aug 3, 2007

Nein. Die kommen nicht zu gewissen Zeiten, wie oben geschrieben

BlackPixel · Aug 13, 2007

Code:

Tasks: 156 total,   1 running, 154 sleeping,   0 stopped,   1 zombie
Cpu0  : 16.0% us,  4.6% sy,  0.0% ni, 69.8% id,  9.6% wa,  0.0% hi,  0.0% si
Cpu1  : 12.7% us,  5.5% sy,  0.0% ni, 67.7% id, 13.5% wa,  0.1% hi,  0.4% si
Mem:   1009080k total,  1001204k used,     7876k free,       68k buffers
Swap:  3919840k total,  3919840k used,        0k free,     4212k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14664 mysql     16   0 4548m 909m  216 S    8 92.3   1:50.37 mysqld
 2780 root      17   0 13752  164   56 D    7  0.0   0:06.00 hald
16683 dslrseit  18   0 49852  748  352 D    7  0.1   0:01.46 php-cgi
  270 root      15   0     0    0    0 D    6  0.0   1:06.68 kswapd0
16677 dslrseit  18   0  181m  552  156 D    5  0.1   0:01.52 php-cgi
16691 root      18   0  7184  268  192 D    5  0.0   0:00.87 suphp
16417 wwwrun    17   0  162m  328  148 D    4  0.0   0:00.38 httpd2-prefork
16678 dslrseit  18   0  181m 1280  136 D    4  0.1   0:01.40 php-cgi
16615 dslrseit  18   0  189m  740  140 D    4  0.1   0:02.45 php-cgi
16681 u4561126  17   0  5588  536  276 R    4  0.1   0:01.16 top
16685 dslrseit  17   0 49848  800  408 D    4  0.1   0:01.31 php-cgi
16536 dslrseit  18   0  190m  500  184 D    4  0.0   0:01.79 php-cgi
16611 dslrseit  18   0  190m  660  156 D    4  0.1   0:02.20 php-cgi
16687 dslrseit  18   0 49848  648  236 D    4  0.1   0:01.06 php-cgi
16690 root      18   0  7180  272  192 D    4  0.0   0:00.68 suphp

Ich komme irgendwie nicht weiter. Hab schon so einiges probiert was die werte für prefork und mysqld angeht. Heute gehts schon den ganzen abend so. Ich hab keine Idee mehr

Code:

<IfModule prefork.c>
  StartServers         1
   MinSpareServers      1
   MaxSpareServers      2
   ServerLimit        150
   MaxClients         20
   MaxRequestsPerChild  100
</IfModule>

KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 2
HostNameLookups off

Code:

set-variable=local-infile=0
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
skip_networking

Ne Idee, bin echt Ratlos...

noto · Aug 16, 2007

1.
wieviel RAM hat dein Server?

2.

Code:

<IfModule prefork.c>
  StartServers         1
   MinSpareServers      1
   MaxSpareServers      2
   ServerLimit        150
   MaxClients         20
[COLOR="Red"]   MaxRequestsPerChild  100[/COLOR]
</IfModule>

auf 8000 machen!

3.

Code:

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-bdb


key_buffer = 64M
net_buffer_length = 8K

sort_buffer_size = 32K
myisam_sort_buffer_size = 32K
read_buffer_size = 16K
read_rnd_buffer_size = 512K
join_buffer_size = 512K

query_cache_size = 1M
thread_cache = 16
table_cache = 1024
max_allowed_packet = 1M

max_connections = 20
low_priority_updates = 1
long_query_time = 2

innodb_thread_concurrency=2

probier das mal aus und lass Tuning-Primer laufen.

BlackPixel · Sep 10, 2007

Neue erkenntnisse von der Front:

5.0.18 ist Mist

Egal was ich an der config geändert habe, das ding läuft über bis zum Knall.

Lösung: Updaten

Habe den Server nun doch auf die 5.1.21 upgedatet, läuft schon mehrere Stunden stabil bei 53 MB im Ram...

mysqld erzeugt kurzzeitig hohe CPU Last

BlackPixel

New Member

Huschi

Moderator

BlackPixel

New Member

BlackPixel

New Member

daseddy

Registered User

BlackPixel

New Member

BlackPixel

New Member

noto

New Member

BlackPixel

New Member

We value your privacy