MySQLs CPU-Verbrauch steigt auf 99%

dohǃ
Posts: 23
Joined: 2005-09-15 23:52

MySQLs CPU-Verbrauch steigt auf 99%

Post by 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
Top

aubergine
RSAC
Posts: 475
Joined: 2005-09-10 17:52
Location: Frankfurt am Main

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by aubergine »

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

dohǃ
Posts: 23
Joined: 2005-09-15 23:52

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by 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]
Last edited by dohǃ on 2007-05-05 12:57, edited 1 time in total.
Top

juergen
Posts: 133
Joined: 2004-03-30 14:44

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by juergen »

was sagt:

Code: Select all

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

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by 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 ;)
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

juergen
Posts: 133
Joined: 2004-03-30 14:44

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by juergen »

juergen wrote: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, ..."?
;-)
Top

dohǃ
Posts: 23
Joined: 2005-09-15 23:52

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by 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?
Top

juergen
Posts: 133
Joined: 2004-03-30 14:44

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by 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.
Top

dohǃ
Posts: 23
Joined: 2005-09-15 23:52

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by 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?
Top

juergen
Posts: 133
Joined: 2004-03-30 14:44

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by 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.
Top

dohǃ
Posts: 23
Joined: 2005-09-15 23:52

Re: MySQLs CPU-Verbrauch steigt auf 99%

Post by dohǃ »

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