MySQLs CPU-Verbrauch steigt auf 99%

[dohǃ]

Hi!

Seit einiger Zeit steigt der MySQL CPU-Verbrauch auf 96-99%. Erst nach einem restart verhälts sich wieder einige Zeit normal...

Code: Select all

uranus: top
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14647 mysql     15   0  335m  91m 4104 S 99.0 18.1  18:46.15 mysqld
18128 root      16   0  2188 1088  752 R  0.7  0.2   0:00.29 top

99.0%?

Code: Select all

uranus:/var/lib/mysql # ps aux | grep mysql
root     14613  0.0  0.2   2704  1244 ?        S    11:31   0:00 /bin/sh /usr/bin/mysqld_safe --user=mysql --pid-file=/var/lib/mysql/mysqld.pid --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql
mysql    14647 11.8 18.1 343396 93616 ?        Sl   11:31  18:07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql 
--user=mysql --pid-file=/var/lib/mysql/mysqld.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
root     18126  0.0  0.1   1828   660 pts/0    S+   14:04   0:00 grep mysql

Hier nur 11.8%?

Code: Select all

uranus:/var/lib/mysql # free -m
             total       used       free     shared    buffers     cached
Mem:           503        497          6          0          5         91
-/+ buffers/cache:        400        103
Swap:         1027          0       1027

Swap passt.

Code: Select all

uranus: mytop
MySQL on localhost (4.1.10a-log)                                                                                                   up 0+02:36:10 [14:07:55]
 Queries: 194.8k  qps:   21 Slow:    10.0         Se/In/Up/De(%):    79/00/08/01
             qps now:    4 Slow qps: 0.0  Threads:   62 (  52/   8) 00/00/00/00
 Cache Hits: 77.8k Hits/s:  8.5 Hits now:   0.0  Ratio: 50.8% Ratio now:  0.0%
 Key Efficiency: 100.0%  Bps in/out:  2.4k/11.1k   Now in/out:  67.2/22.9k

Der Fehler liegt vermutlich bei der hohen Thread-Anzahl? Wieso wird diese nicht schnell genug geringer?

log-slow-queries
long_query_time = 3
max_connections = 500
max_user_connections = 100
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 512
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 60
connect_timeout = 2
max_allowed_packet = 1M
read_rnd_buffer_size = 4M
thread_concurrency = 2
query_cache_limit = 2M
query_cache_size= 32M
query_cache_type = 1
tmp_table_size = 16M

my.cnf-Auszug, Intel Celeron 2400 mit 512 MB RAM

[aubergine]

Wenn top 99% anzeigt, wie hoch ist zu diesem Zeitpunkt der Wert für (wa%) ?

[dohǃ]

Der wa%-Wert ist mir leider entgangen... Das Problem lag an untenstehende MySQL Abfrage! Diese wurde mehrmals von unterschiedlichen Benutzern aufgerufen und sperrte die MyISAM-Tabelle, worauf hin ein Deadlock entstand :( Sehr hilfreich für die Fehlersuche war mtop.

Die Abfrage dauerte trotz Indizes ca. 6 Sekunden. Jetzt stellt sich mir die Frage, wie kann ich diese trotzdem verwenden? Kann (automatisches) Lock/Unlock deaktiviert werden? Könnte ich mit Stored procedures mehr Erfolg haben? Query beschleunigen, aber wie?

Code: Select all

# Query_time: 499  Lock_time: 2  Rows_sent: 0  Rows_examined: 17747764
select a.persona, a.personb as d1,  b.personb as d2, c.personb as d3, d.personb as d4, e.personb as d5 from kontaktpersonen as a 
left join kontaktpersonen as b on a.personb = b.persona and b.istaktiv = 1
left join kontaktpersonen as c on b.personb = c.persona and c.istaktiv = 1
left join kontaktpersonen as d on c.personb = d.persona and d.istaktiv = 1
left join kontaktpersonen as e on d.personb = e.persona and e.istaktiv = 1
where a.persona = 1 and a.istaktiv = 1 and e.personb = 20
and b.personb not in (1, a.personb) 
and c.personb not in (1, a.personb, b.personb) 
and d.personb not in (1, a.personb, b.personb, c.personb) 
and e.personb not in (1, a.personb, b.personb, c.personb, d.personb) limit 1;

[edit]istaktiv in der Abfrage hinzugefügt.[/edit]

[juergen]

was sagt:

Code: Select all

select a.persona, a.personb as d1, ..."?

[Joe User]

Teile den Query in mehrere Queries auf und verarbeite die Ergebnisse anschliessend innerhalb der Software. Manchmal sind mehrere Queries schneller und ressourcenschonender, als ein Query ;)

[juergen]

was sagt:

Code: Select all

select a.persona, a.personb as d1, ..."?

ich meinte natürlich:

Code: Select all

explain select a.persona, a.personb as d1, ..."?

;-)

[dohǃ]

Code: Select all

id select_type table type   possible_keys    key      key_len ref             rows Extra
1  SIMPLE      a     ref    PRIMARY,istaktiv istaktiv 3       const           86   Using where; Using index
1  SIMPLE      b     ref    PRIMARY,istaktiv istaktiv 3       a.personb       28   Using where; Using index
1  SIMPLE      c     ref    PRIMARY,istaktiv istaktiv 3       b.personb       28   Using where; Using index
1  SIMPLE      d     ref    PRIMARY,istaktiv istaktiv 3       c.personb       28   Using where; Using index
1  SIMPLE      e     eq_ref PRIMARY,istaktiv PRIMARY  6       d.personb,const  1   Using where

Lass ich b.istaktiv = 1, c.istaktiv = 1, d.istaktiv = 1, e.istaktiv = 1 und a.istaktiv = 1 in der Abfrage weg, dauert diese nur 0.0028 Sekunden und es kommt zu keinem Tabellen-Lock. Der Explain-Output ist mit oder ohne istaktiv bei beiden "der selbe". Kann in dieser Abfrage das Sperren deaktiviert werden? Sollte man die Tabelle dublizieren, dort dann nur noch die aktiven Personen beibehalten?

[juergen]

"istaktiv" ist ein sehr schlechter schlüssel (ich denke er hat nur die Werte 0 und 1).

Warum hast du keinen Index auf kontaktpersonen.person{a,b} (darüber machst du ja den Join), das sollte die Selektivität verbessern.

[dohǃ]

Jup, istaktiv ist 0 oder 1 ;)

Code: Select all

PRIMARY(persona, personb)
istaktiv(persona, personb, istaktiv)

Die Kardinalität liegt bei beiden bei 103876... Der Index istaktiv sollte doch (persona, personb) abdecken?

[juergen]

Ich hatte vermutet, "isaktiv" bezieht sich nur auf die entsprechende Spalte. Du kannst den "isaktiv"-Index eigentlich wegwerfen, verlangsamt nur die Schreib-Performance und bringt gegenüber dem PRIMARY KEY nichts.

Dein Problem liegt wohl bei parallelen Schreibzugriffen. MyISAM beherscht nur Table-Locking. In diesem Fall wäre InnoDB besser.

Viel Erfolg.

[dohǃ]

Okay, habe den Index gedroppt. Werds dann mal mit InnoDB versuchen.