Hohe CPU-Last durch mysqld

[Foxgerman]

Erstmal ein freundliches Hallo in die Runde. Ich habe den Vorstellungsthread leider nicht gefunden. Zu meiner Person: ich besitze rudimentäre Linux Kenntnisse und warte damit meinen Rootserver selbst. Bin also noch am Anfang des Lernstadiums und bitte um Nachsicht wenn ich etwas nicht sofort kapiere.

Da ich nicht weiss ob mir einer ein paar Fragen zur MySQL Konfiguration beantworten kann bzw. möchte, frage ich einfach mal darauf los.

Ich habe auf meinem Server (8x 2,4 GHz, 32GB) über TOP folgende Werte:

Code: Select all

top - 22:40:51 up 19 days, 10:25,  2 users,  load average: 1.49, 1.22, 1.26
Tasks: 223 total,   1 running, 222 sleeping,   0 stopped,   0 zombie
Cpu(s): 12.2%us,  3.3%sy,  0.0%ni, 83.9%id,  0.5%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32814400k total, 19592576k used, 13221824k free,   263204k buffers
Swap:  2097136k total,        0k used,  2097136k free, 16192788k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                                                                  
20142 apache    20   0  374m  48m 5712 S 40.9  0.2   0:28.17 httpd                                                                                                                                                                                                     
 1114 mysql     20   0 4494m 311m 7836 S 30.6  1.0   2948:06 mysqld                                                                                                                                                                                                    
14353 apache    20   0  374m  47m 5864 S  9.0  0.1   1:53.54 httpd                                                                                                                                                                                                     
 7699 apache    20   0  375m  49m 6324 S  8.0  0.2   3:12.27 httpd                                                                                                                                                                                                     
18649 apache    20   0  380m  53m 6344 S  6.0  0.2   0:41.54 httpd                                                                                                                                                                                                     
16244 apache    20   0  381m  54m 6244 S  5.6  0.2   1:26.43 httpd                                                                                                                                                                                                     
15860 apache    20   0  378m  50m 6140 S  4.0  0.2   1:34.92 httpd                                                                                                                                                                                                     
16129 apache    20   0  366m  40m 6136 S  3.7  0.1   1:27.41 httpd                                                                                                                                                                                                     
21105 apache    20   0  383m  57m 4420 S  3.7  0.2   0:07.62 httpd                                                                                                                                                                                                     
19536 apache    20   0  385m  59m 4640 S  2.7  0.2   0:35.10 httpd                                                                                                                                                                                                     
15555 apache    20   0  382m  55m 5952 S  2.3  0.2   1:42.62 httpd                                                                                                                                                                                                     
20077 apache    20   0  371m  46m 4480 S  2.0  0.1   0:29.45 httpd                                                                                                                                                                                                     
20140 apache    20   0  369m  44m 4608 S  2.0  0.1   0:27.32 httpd                                                                                                                                                                                                     
12332 apache    20   0  374m  48m 4684 S  1.7  0.2   2:24.73 httpd                                                                                                                                                                                                     
19620 apache    20   0  373m  48m 4480 S  1.7  0.2   0:38.86 httpd                                                                                                                                                                                                     
21339 apache    20   0  372m  47m 4428 S  1.3  0.1   0:03.60 httpd                                                                                                                                                                                                     
    1 root      20   0 19352 1548 1244 S  0.0  0.0   0:02.63 init                                                                                                                                                                                                      
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd                                                                                                                                                                                                  
    3 root      RT   0     0    0    0 S  0.0  0.0   0:38.16 migration/0                                                                                                                                                                                               
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.06 ksoftirqd/0                                                                                                                                                                                               
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0                                                                                                                                                                                               
    6 root      RT   0     0    0    0 S  0.0  0.0   0:01.38 watchdog/0 

Die CPU Last erscheint mir etwas zu hoch. Auf dem Server ist eine Domain mit Phpfox Script installiert. Zum Zeitpunkt der Abfrage waren ca. 60 Leute aktiv. Es kommt vor das mysqld auf über 100% schiesst, kurz dort bleibt und dann wieder absackt.

Hier mal die Daten von mysqltuner:

Code: Select all

>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 80M (Tables: 626)
[--] Data in InnoDB tables: 12M (Tables: 252)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 268

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 1h 39m 30s (87M q [143.785 qps], 5M conn, TX: 209B, RX: 19B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 176.0M global + 2.9M per thread (150 max threads)
[OK] Maximum possible memory usage: 607.2M (1% of installed RAM)
[OK] Slow queries: 0% (402/87M)
[OK] Highest usage of available connections: 62% (93/150)
[OK] Key buffer size / total MyISAM indexes: 8.0M/45.7M
[OK] Key buffer hit rate: 100.0% (34B cached / 548K reads)
[OK] Query cache efficiency: 25.6% (14M cached / 55M selects)
[!!] Query cache prunes per day: 23399
[!!] Sorts requiring temporary tables: 13% (1M temp sorts / 10M sorts)
[!!] Joins performed without indexes: 198509
[OK] Temporary tables created on disk: 2% (103K on disk / 3M total)
[OK] Thread cache hit rate: 90% (545K created / 5M connections)
[!!] Table cache hit rate: 1% (400 open / 39K opened)
[OK] Open file limit used: 50% (512/1K)
[OK] Table locks acquired immediately: 98% (122M immediate / 124M locks)
[OK] InnoDB data size / buffer pool: 12.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 8M)
    sort_buffer_size (> 2M)
    read_rnd_buffer_size (> 256K)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_cache (> 400)

Und jetzt dazu noch die Daten von tuning-primer

Code: Select all

MySQL Version 5.5.29-cll x86_64

Uptime = 7 days 1 hrs 51 min 46 sec
Avg. qps = 143
Total Questions = 87965289
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 4.000000 sec.
You have 402 out of 87965310 that take longer than 4.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 4
Current threads_cached = 2
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 150
Current threads_connected = 5
Historic max_used_connections = 93
The number of used connections is 62% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 6 M
Current InnoDB data space = 12 M
Current InnoDB buffer pool free = 80 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 427 M
Configured Max Per-thread Buffers : 431 M
Configured Max Global Buffers : 160 M
Configured Max Memory Limit : 591 M
Physical Memory : 31.29 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 45 M
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 61517
Key buffer free ratio = 79 %
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 = 8 M
Current query_cache_used = 3 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 44.71 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 260.00 K
You have had 198767 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_open_cache = 400 tables
Current table_definition_cache = 400 tables
You have a total of 919 tables
You have 400 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
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 3690761 temp tables, 2% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 911 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 59
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 concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=ALWAYS'.

Jetzt noch meine my.cnf:

Code: Select all

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#set-variable = max_connections=150
log-slow-queries
max_connections=150
thread_cache_size = 4
query_cache_size = 8M
query_cache_limit = 2M
join_buffer_size = 256K
read_rnd_buffer_size = 256K
sort_buffer_size = 2M
long_query_time= 4
log-slow-queries=/var/log/mysql_slow.log

Da ich leider nur wenig Kenntnisse über Optimierungen der Datenbank habe würde ich mich über etwaige Optmierungshinweise sehr freuen. Vielleicht erbarmt sich jemand und schaut mal drüber.

Danke vorab und Gruß

Chris

[Joe User]

Das Erste um das Du Dich kümmern solltest, ist dies:

Code: Select all

You have had 198767 queries where a join could not use an index properly

Wenn das gelöst ist, sind einige Nebenwirkungen mit behoben und die Zahlen sehen ganz anders aus.
Dazu noch table_cache und table_definitions_cache auf jeweils 1024 setzen.

Danach zwei/drei Tage laufen lassen und mysqltuner/tuning-primer erneut laufen lassen.

[Foxgerman]

Wenn ich die beiden Zeilen

Code: Select all

table_cache = 1024
table_definitions_cache = 1024

einfüge startet der mysqld nicht mehr.

Kannst du mir bitte sagen wie ich mich um das hier kümmern kann?

Code: Select all

You have had 198767 queries where a join could not use an index properly

Edit: ich habe gerade

Code: Select all

log-queries-not-using-indexes

in die my.cnf eingefügt und danach mit tail -f /var/log/mysql_slow.log mal nachgesehen. Heraus kommt dabei:

Code: Select all

ORDER BY a.time_stamp DESC
LIMIT 20
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.026126  Lock_time: 0.000060 Rows_sent: 20  Rows_examined: 12899
SET timestamp=1360193326;
SELECT *, a.time_stamp AS tempo
FROM phpfox_ces_trail AS a
JOIN phpfox_user AS b
	ON(b.user_id = a.user)

ORDER BY a.time_stamp DESC
LIMIT 20
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.075086  Lock_time: 0.000089 Rows_sent: 0  Rows_examined: 37733
SET timestamp=1360193326;
SELECT *, a.time_stamp AS tempo
FROM phpfox_ces_trail AS a
JOIN phpfox_user AS b
	ON(b.user_id = a.user)

WHERE b.user_name = ""
ORDER BY a.time_stamp DESC
LIMIT 20
/* OO Query */;
# Time: 130207  0:28:47
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.000607  Lock_time: 0.000070 Rows_sent: 16  Rows_examined: 568
SET timestamp=1360193327;
SELECT *
FROM phpfox_user AS b
JOIN phpfox_ces_online AS c
	ON(b.user_id = c.user)

WHERE b.user_id != 0 and b.user_id != 1 and c.activity > 1360192427
ORDER BY c.activity DESC
LIMIT 50
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.001454  Lock_time: 0.000044 Rows_sent: 1  Rows_examined: 1095
SET timestamp=1360193327;
SELECT *
FROM phpfox_user AS a
WHERE a.profile_page_id = 0 and a.last_ip_address = "91.114.143.253"
ORDER BY a.user_id ASC
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.001398  Lock_time: 0.000037 Rows_sent: 1  Rows_examined: 1095
SET timestamp=1360193327;
SELECT *
FROM phpfox_user AS a
WHERE a.profile_page_id = 0 and a.last_ip_address = "2.202.94.93"
ORDER BY a.user_id ASC
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.001415  Lock_time: 0.000035 Rows_sent: 1  Rows_examined: 1095
SET timestamp=1360193327;
SELECT *
FROM phpfox_user AS a
WHERE a.profile_page_id = 0 and a.last_ip_address = "178.196.217.76"
ORDER BY a.user_id ASC
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.001419  Lock_time: 0.000035 Rows_sent: 1  Rows_examined: 1095
SET timestamp=1360193327;
SELECT *
FROM phpfox_user AS a
WHERE a.profile_page_id = 0 and a.last_ip_address = "80.218.73.19"
ORDER BY a.user_id ASC
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.001423  Lock_time: 0.000035 Rows_sent: 1  Rows_examined: 1095
SET timestamp=1360193327;
SELECT *
FROM phpfox_user AS a
WHERE a.profile_page_id = 0 and a.last_ip_address = "80.226.24.5"
ORDER BY a.user_id ASC
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.001372  Lock_time: 0.000036 Rows_sent: 1  Rows_examined: 1095
SET timestamp=1360193327;
SELECT *
FROM phpfox_user AS a
WHERE a.profile_page_id = 0 and a.last_ip_address = "13.157.142.98"
ORDER BY a.user_id ASC
/* OO Query */;
# User@Host: f_adm_340[f_adm_340] @ localhost []
# Query_time: 0.001386  Lock_time: 0.000037 Rows_sent: 1  Rows_examined: 1095
SET timestamp=1360193327;
SELECT *

Hier hört dann auch mein Verständnis dafür auf. Kann mich jemand heller machen?

[Joe User]

Wenn ich die beiden Zeilen

Code: Select all

table_cache = 1024
table_definitions_cache = 1024

einfüge startet der mysqld nicht mehr.

Oops mein Fehler, es heisst table_definition_table (ohne "s").

Kannst du mir bitte sagen wie ich mich um das hier kümmern kann?

Code: Select all

You have had 198767 queries where a join could not use an index properly

Dazu musst die PHP-App durchschauen und die Queries darauf überprüfen, wo der/die INDEX in der Datenbank fehlt.
Es wäre allerdings einfacher, wenn Du Dich an die Entwickler der PHP-App wendest und um einen Bugfix bittest.

Hier hört dann auch mein Verständnis dafür auf. Kann mich jemand heller machen?

Es geht auch ohne das Log. Du brauchst "tcpdump" (mit Deinem Paketmanager installieren), das Percona-Toolkit http://www.percona.com/software/percona-toolkit und Du musst in der PHP-App den Datenbank-Host auf 127.0.0.1 stellen.
Dann lässt Du folgenden Befehl zur Rushhour laufen und er wird 100000 Queries in der Datei "mysql.tcp.txt" aufzeichnen:

Code: Select all

tcpdump -i lo -s 65535 -x -n -q -tttt -c 100000 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2' > mysql.tcp.txt

Wenn das fertig ist, werten wir die Queries aus um die Tabellen mit fehlenden INDEX zu finden:

Code: Select all

pt-query-digest --type tcpdump --group-by tables --report-format profile --filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")' mysql.tcp.txt > mysql.noindex.txt

Den Inhalt der Datei "mysql.noindex.txt" postest Du dann bitte.
Beide Befehle könnten lange laufen, also bitte Geduld.

[Foxgerman]

Vielen Dank für deine Hilfe bis hierher.

Hier die Ergebnisse:

Code: Select all

100000 packets captured
200430 packets received by filter
424 packets dropped by kernel

mysql.noindex.txt:

Code: Select all

# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M   Item
# ==== ======== ============= ===== ====== ==== ===== ================
#    1 0x       56.6989 28.3%  5541 0.0102 1.00  0.22 phpfox_user
#    2 0x       55.3117 27.6%   847 0.0653 1.00  0.26 phpfox_friend
#    3 0x       36.8604 18.4%   196 0.1881 0.99  0.16 phpfox_feed
#    4 0x       29.2550 14.6%   634 0.0461 1.00  0.17 phpfox_ces_trail
#    5 0x       18.3903  9.2%   123 0.1495 1.00  0.20 phpfox_app
# MISC 0xMISC    4.1760  2.1%  2009 0.0021   NS   0.0 <36 ITEMS>

Zum Zeitpunkt der Aufzeichnung waren 60 Gäste und 45 aktive User online.

[Joe User]

Schade dass das Script nicht Open-Source ist, dann hätte man es sich mal ansehen können :-(

Die ersten vier Tabellen (phpfox_user, phpfox_friend, phpfox_feed, phpfox_ces_trail) sind auf jeden Fall schlecht bis gar nicht mit INDEXen versehen, dort sollte also zuerst angesetzt werden. Bitte reiche dazu bei den Entwicklern einen Bugreport ein, damit das dauerhaft gelöst wird.

Ohne Quelltext des Scripts ist das Bugfixen recht schwierig (nicht unmöglich, aber sehr aufwendig), so dass das lieber den Entwicklern überlassen werden sollte. Ich habe die Lizenz nicht gelesen, es ist aber möglich, dass wir hier gar nicht daran rumfummeln dürfen, das müsstest Du bitte abklären, bevor wir weitermachen.

Es tut mir leid, Dir erstmal nicht wirklich weiterhelfen zu können, aber mit den ungeklärten Randbedingungen ist mir das zu heiss.

[Foxgerman]

Verstehe. Da ich ein paar Lizenzen davon besitze und durchaus Development Seiten damit betreiben darf würde ich dir Zugriff geben können auf einen Clone der aktuellen Seite sofern du daran Interesse hast. Mir fällt es leichter den Entwicklern detailliertere Ergebnisse zu präsentieren um das Problem zu lösen. Wenn du Lust hast das zu unterstützen schreibe mir bitte eine PM damit ich dir Zugangsdaten mitteilen kann zur Dev-Seite.

Ich darf am Quellcode rumfummeln so viel ich möchte, verliere dadurch allerdings den Anspruch auf Support.

[Foxgerman]

Ich habe dem Hersteller mal ein Suport Ticket geschrieben. Dies ist die Antwort darauf:

Thank you for contacting the Product Support department.

System load ballast can depend on numerous things, firstly the interpretor or parser of the actual script in our case which would be the PHP processing engine and the MySQL R/DBMS.

Our support scope unfortunately does not cover the actual system administration or system support/setup or any type of adjustment or adaption to your server hosts configuration of your system services. We can provide you a quality insurance over that this script assuming that in which you are running always is using the most concurrent version of the latest build of which currently can be found listed in the client area will be performing without system load ballast issues that will slow down the engines or parsers behind the actual script index.

In relation to your issue, we would suggest that you would perform an actual benchmark on both your MySQL R/DMBS and PHP parsing engine with similar scripts and queries using the same fragment of load that you are experiencing with the product.
This issue can only be addressed to the support department of your server host as we do not cover this type of research or diagnostics on foreign or third-party systems.

We do however recommend you to always assure that your product (PhpFox) installed domain has the latest updates/build patches with the default and non modified source code (or many 3rd-party products added into the phpFox site, as we have had many reports of 3rd-party product causing overload in phpFox sites) that was provided at time of purchase.

Having said so, I recommend you to check your 3rd-party products first. Our recommendation is to benchmark the site with 3rd-party products enabled, and then, without the 3rd-party products.

Then, I recommend you to check with your hosting about what they can detect is overloading the server.

[Joe User]

LOL, mit anderen Worten:
Lieber Kunde, wir bedanken uns für die $99, aber verschonen sie uns bitte mit Bugreports, wir kümmern uns ohnehin nicht darum.


Entweder 08/15-Standardantwort, oder die haben schlicht nicht verstanden worum es geht.

Verwendest Du Plugins/Extensions für PHPFox? Wenn ja, dann bitte alle deaktivieren/deinstallieren und meinen Test von oben nochmal wiederholen. Wenn nein, dann schildere Dein Problem zusammen mit dem Test parallel im Supportforum des Herstellers, vielleicht haben andere Kunden bereits eine Lösung fertig.

Ich könnte frühestens (über)nächste Woche einen Blick aufs Testsystem werfen, kann aber keine Zusage für eine Lösung geben.
Vielleicht findet sich vorher ein anderer Helfer, oder eine Lösung anderer PHPFox-User.

[Foxgerman]

Ich sehe das genauso wie du. Ich habe diese Probleme allerdings nur auf diesem Server, andere Installationen laufen auf anderen Servern einwandfrei. Dennoch werde ich den Test wiederholen mit deaktivierten Add-ons. Die Ergebnisse werde ich der Vollständigkeit halber hier posten damit das nicht verloren geht. Es sind eh nicht viele drauf da die Seite noch relativ neu ist.
Ich habe bereits einen Thread dort mit den Ergebnissen hier eröffnet. Aber da es sehr spezifisch ist erwarte ich eigentlich keine brauchbare Antwort.

Du schliesst eine fehlerhafte mysql Konfiguration anscheinend aus oder? wie auch immer, wenn du mal Lust dazu hast tiefer hineinzuschauen steht dir eine Testumgebung gerne zur Verfügung. Ich würde dann einen Clone von der aktuellen Liveseite dazu erstellen.

Nochmals vielen Dank für deine Hilfe.

[Joe User]

Deine my.cnf und die Ergebnisse von mysqltuner/tuning-primer sehen (mit den angesprochenen Änderungen) nicht grundlegend falsch aus. Ein wenig Finetuning ist selbstverständlich noch möglich und könnte leichte Verbesserungen bringen, aber mehr als geschätzte 1% werden das auf Grund der PHP-App nicht sein. Da würde Aufwand<->Nutzen nicht im Verhältnis stehen. Erst wenn die App gefixt ist, lohnt sich das Finetuning der my.cnf, weil dann konkretere Zahlen vorliegen.

[Foxgerman]

Hier jetzt nochmals die Werte ohne aktivierte Add-ons

Code: Select all

# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M   Item
# ==== ======== ============= ===== ====== ==== ===== =============
#    1 0x       98.3459 33.1%   808 0.1217 1.00  0.28 phpfox_friend
#    2 0x       94.6892 31.9%   503 0.1882 1.00  0.23 phpfox_feed
#    3 0x       49.5911 16.7%   542 0.0915 1.00  0.31 phpfox_user
#    4 0x       47.1398 15.9%   274 0.1720 1.00  0.25 phpfox_app
# MISC 0xMISC    7.1506  2.4%  2957 0.0024   NS   0.0 <51 ITEMS>

[Joe User]

Sieht besser aus (für phpfox_user so gar erheblich besser), aber noch immer nicht wirklich gut.
Das lässt weiterhin vermuten, dass die App nicht sonderlich gut umgesetzt ist.
Definitiv kaputt ist aber mindestens ein Add-On, welches auf die phpfox_user-Tabelle zugreift.

[Foxgerman]

Es waren bei diesem Testlauf keine 3rd Party Add-on´s aktiv, nur die, die vom Script mitgeliefert werden und somit zum Core gehören. Mir graut schon davor wenn die Zahl der User zunimmt. Werden täglich mehr und irgendwann muss das Problem in den Griff. Ich werde das System so wie es jetzt ist auf jeden Fall noch auf einen anderen Server kopieren um es dort zu vergleichen.

[ddm3ve]

Kannst du mir bitte sagen wie ich mich um das hier kümmern kann?

Code: Select all

You have had 198767 queries where a join could not use an index properly

Hier hört dann auch mein Verständnis dafür auf. Kann mich jemand heller machen?

Du solltest mal zusammen sammeln, welche syntaktisch indetische SQLs ausgeführt werden.
Daraus auswerten Welche Treffer in der Datenbank das ausmacht.
Daran die Indexe ausrichten.

Ich lese immer wieder, dass man das slow query log einschaltet, guckt, welche SQLs keinen Index nutzen und denen einfach einen Index über bügelt.
Heraus kommt dann nicht selten, dass der Index meistens grösser ist als der Datenstamm selbst.
In vielen Fällen ist der Index so schlecht gewählt, dass er sogar mehr Probleme bereitet, als er nutzt.
Du solltests also die Daten untersuchen und überlegen, wie Du möglichst schnell die Daten auf eine kleine Menge einschränken kannst.
Ausserdem können Textfelder nicht bis zu eine beliebigen Länge indiziert werden.

In einem kleine praktischen Beispiel:

Ich habe eine Kundentabelle mit Kunden id, Eintittsdatum Mandantenid, Geschlecht und Status.

Es existieren meinetwegen 5 Mandanten, Mandant 1 hat 10 Mio Kunden, Mandant 2-5 jeweils zwischen 100 und 200.
Die Kunden sind ausgewogen männlich weiblich also rund 50/50.
Stati gibt es 3 aktiv, gebannt und time out.

Bei einer Analyse kamen wir auf folgenden Fakt:
Die Applikation stellt 100 000 Selects auf diese Tabelle / Stunde.
Abfragekriterien, Mandant, Status

rund 1 000 Abfragen
Kriterien, Eintritsdatum, Mandant

rund 100 Abfragen
Kriterien Kundenid, Status.

Die Frage wäre nun macht es sinn z.B. die Mandantenid zu indizieren?

Nein, warum nicht?
Lediglich die Abfragen auf Mandanten 2-5 würden tatsächlich davon profitieren.
Für Mandant 1 jedoch kämme immer eine Treferquote von 10 000 000 Einträge - 4* ca 200 Einträge aus dem Index heraus. entspricht quasi schon fast einem Full Table scan.
Zumal keine der Abfragen exakt auf die Mandantenid führt.

Jetzt könnte man die einzelnen zusätzlichen Felder auch noch Indizieren, auch das wäre zunächst mal unproduktiv, denn ein SQL Query (Mysql) kann nur einen Index nutzen.
Daher wäre für den oben genannten Fall eine Kombination der Indexe besser.
Also ein Index z.B. Mandant und Status.
Für das 2. Select wäre eine reine Indizierung des Eintrittsdatum geeignet. Sofern die 10 000 000 Kunden nicht am gleichen Tag hinzu geommen sind.
Also natürlich vorher prüfen, wie genau sich das ganze auf das Zeitspanne reduziert. Der Mandant wegen der 5 Unterteilungen reduziert die ziemlich schnell auf wenige Datensätze reduzierbare Datenmenge nicht mehr wirklich weiter.
Ob nun Mysql 10 oder 5 Datensätze genauer untersuchen muss, ist in der Tat kaum messbar.

Dito Kundenid Status.
Hier wäre eine Index auf Kundenid vollkommen ausreichend.
3* Status, davon profitiert keiner.

Schwieriger wird es natürlich um so "komplexer" gesucht wird. Also wenn noch Statistiken ausgewertet werden usw.
Abfragen die ggf. keinen Index nutzen können.
Es wäre nun fatal für jeden Fall einen eigenen Index an zu legen.
I.d.R ist es besser, wenn man die Indizierung für die häufigsten Abfragen optimiert und nicht versucht es allen abfragen recht zu machen.
Ein hoher Anteil an Indexe fordert seinen Tribut in der Schreibperformance und natürlich auch im Seek / read innerhalb des Index.

Übertreibt man es, hat man nicht selten sogar schlechtere Ergebnisse.

[Foxgerman]

Danke für die ausführliche Erklärung, bringt ja ein wenig Licht ins Geschehen.
Ich habe wiederholt massive Probleme mit dieser einzigen Installation und weiss keinen Rat mehr. Die CPU Last durch MySQL steht gerade wieder auf 600%. Die Seite ist momentan natürlich elendig langsam. Lasse gerade noch mal tcpdump laufen und poste die Ergebnisse für den Interessierten wenn es fertig ist.

Code: Select all

top - 13:34:33 up 17:19,  1 user,  load average: 0.68, 0.79, 0.82
Tasks: 239 total,   1 running, 238 sleeping,   0 stopped,   0 zombie
Cpu(s): 56.1%us, 20.6%sy,  0.0%ni, 23.2%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32814164k total, 20628612k used, 12185552k free,    88116k buffers
Swap:  2097136k total,        0k used,  2097136k free, 17981676k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                 
31489 mysql     20   0 2503m 167m 7684 S 600.1  0.5   1144:19 mysqld                                                                                  
19824 apache    20   0  367m  46m 5996 S  2.6  0.1   1:39.23 httpd                                                                                    
19825 apache    20   0  350m  30m 4652 S  2.3  0.1   1:44.18 httpd                                                                                    
20808 apache    20   0  367m  46m 5984 S  2.3  0.1   1:23.28 httpd                                                                                    
19827 apache    20   0  351m  32m 4976 S  2.0  0.1   1:42.64 httpd                                                                                    
24813 apache    20   0  353m  33m 4584 S  2.0  0.1   0:20.39 httpd                                                                                    
20942 apache    20   0  353m  34m 4752 S  1.7  0.1   1:10.30 httpd                                                                                    
25564 apache    20   0  346m  26m 4428 S  1.7  0.1   0:06.10 httpd                                                                                    
25566 apache    20   0  342m  21m 5560 S  1.7  0.1   0:05.64 httpd                                                                                    
19838 apache    20   0  353m  33m 5872 S  1.3  0.1   2:00.22 httpd                                                                                    
25565 apache    20   0  344m  24m 4548 S  1.0  0.1   0:08.15 httpd                                                                                    
25215 apache    20   0  346m  26m 4428 S  0.7  0.1   0:12.45 httpd                                                                                    
19823 apache    20   0  357m  37m 6080 S  0.3  0.1   1:28.39 httpd                                                                                    
19826 apache    20   0  348m  28m 4880 S  0.3  0.1   1:47.55 httpd                                                                                    
19829 apache    20   0  350m  30m 6012 S  0.3  0.1   1:44.83 httpd                                                                                    
19840 apache    20   0  354m  35m 4816 S  0.3  0.1   1:16.29 httpd                                                                                    
19869 apache    20   0  366m  42m 6404 S  0.3  0.1   1:30.12 httpd 

[ddm3ve]

Hi,

monitorst Du denn Dein System auch entsprechend?
Das würde ich mal anfangen.
Disk io z.B. wäre mind. genau so interessant wie z.B. die Zusammensetzung der CPU Last.
Ausserdem natürlich auch die Datenbank selbst.
Für mich sehen die Randbedingungen eigentlich gut aus:

top - 13:34:33 up 17:19, 1 user, load average: 0.68, 0.79, 0.82
Tasks: 239 total, 1 running, 238 sleeping, 0 stopped, 0 zombie
Cpu(s): 56.1%us, 20.6%sy, 0.0%ni, 23.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32814164k total, 20628612k used, 12185552k free, 88116k buffers
Swap: 2097136k total, 0k used, 2097136k free, 17981676k cached

Was bedeutet, dass intern entweder ein katastrophales Lock Handling statt findet oder andere limitierte Systemresourcen die Datenbank blockieren.
Bitte setze dir hierfür ein geeignetes Monitoring asap auf und ermittle und grpahe folgende Werte:

Code: Select all

Aborted_clients	1214
Aborted_connects	18989
Bytes_received	1039861663192
Bytes_sent	13707896459959
Com_delete	52980322
Com_delete_multi	62364
Com_insert	37093277
Com_insert_select	365406
Com_load	0
Com_replace	0
Com_replace_select	0
Com_select	3054632572
Com_update	433490777
Com_update_multi	1
Connections	3198570
Created_tmp_disk_tables	3007644
Created_tmp_files	23147381
Created_tmp_tables	597136777
Innodb_buffer_pool_pages_data	603272
Innodb_buffer_pool_pages_free	2633843
Innodb_buffer_pool_pages_misc	39684
Innodb_buffer_pool_pages_total	3276799
Innodb_data_fsyncs	11638007
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_reads	448012
Innodb_data_writes	491681163
Innodb_log_writes	451419404
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_pages_created	155366
Innodb_pages_read	447906
Innodb_pages_written	37939400
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	42690774
Innodb_row_lock_time_avg	32
Innodb_row_lock_time_max	51929
Innodb_row_lock_waits	1318236
Innodb_rows_deleted	34058904
Innodb_rows_inserted	37139640
Innodb_rows_read	273246484490
Innodb_rows_updated	404563257
Key_read_requests	102323219
Key_reads	469
Key_write_requests	17867136
Key_writes	3493
Max_used_connections	346
Qcache_free_blocks	0
Qcache_free_memory	0
Qcache_hits	0
Qcache_inserts	0
Qcache_lowmem_prunes	0
Qcache_not_cached	0
Qcache_queries_in_cache	0
Qcache_total_blocks	0
Questions	5266982220
Select_full_join	5230
Select_full_range_join	0
Select_range	200629041
Select_range_check	0
Select_scan	280675986
Slow_queries	16
Sort_merge_passes	11684965
Sort_range	228647094
Sort_rows	75973372987
Sort_scan	1548606
Table_locks_immediate	11546951213
Table_locks_waited	237
Threads_connected	196
max_connections	2000
query_cache_size	0
spin_waits 799304587
spin_rounds 689142638
OS_waits 2030134
History_list 399

Die Werte kannst Du aus dem Ergebniss nachfolgender Statements greppen:

Code: Select all

"show global status;
show variables;
show engine innodb status\G;

Die ausgabe aus dem letzten Statement kannst Du per Backpipe in eine Variable schreiben und wie folgt auswerten:

Code: Select all

echo "$RES" | $GREP Mutex | $TR -d ','| $AWK '{print $2"_"$3,$4"\n"$2"_"$5,$6"\n"$7"_"$8,$9}'
echo "$RES" | $GREP History | $AWK '{print $1"_"$2,$4}'

Insgesamt gibt es noch besserer und weiter Methoden die Ursache intern zu überprüfen aber ,machen wir mal Schritt für Schritt weiter und versuchen mal heraus zu finden wo die Ursache liegt.

[Foxgerman]

Hi,

monitorst Du denn Dein System auch entsprechend?

Offen gestanden nein. Ich habe überhaupt keine Ahnung wie und mit welchem Tool das am besten zu machen ist. Kannst du mir eines empfehlen damit ich die Schritte nachvollziehen und durchführen kann die benötigt werden um hier weiter zu machen?

Ich habe im übrigen noch ein Support Ticket an den Hersteller geschrieben und der antwortete wie folgt:

Hello,

System load ballast can depend on numerous things, firstly the interpretor or parser of the actual script in our case which would be the PHP processing engine and the MySQL R/DBMS.

Our support scope unfortunately does not cover the actual system administration or system support/setup or any type of adjustment or adaption to your server hosts configuration of your system services. We can provide you a quality insurance over that this script assuming that in which you are running always is using the most concurrent version of the latest build of which currently can be found listed in the client area will be performing without system load ballast issues that will slow down the engines or parsers behind the actual script index.

In relation to your issue, we would suggest that you would perform an actual benchmark on both your MySQL R/DMBS and PHP parsing engine with similar scripts and queries using the same fragment of load that you are experiencing with the product.
This issue can only be addressed to the support department of your server host as we do not cover this type of research or diagnostics on foreign or third-party systems.

[Foxgerman]

Aktuelle Ergebnisse von tcpdump:

Code: Select all

100000 packets captured
200148 packets received by filter
147 packets dropped by kernel

Code: Select all

# Profile
# Rank Query ID Response time    Calls R/Call Apdx V/M   Item
# ==== ======== ================ ===== ====== ==== ===== =================
#    1 0x       11744.5520 37.7% 30434 0.3859 0.96  0.49 phpfox_feed
#    2 0x        6885.5196 22.1%  7834 0.8789 0.88  0.09 phpfox_friend
#    3 0x        4761.0404 15.3%  9886 0.4816 0.94  0.42 phpfox_user
#    4 0x        4561.2773 14.6%  9368 0.4869 0.94  0.42 phpfox_app
#    5 0x        1310.9919  4.2%  5880 0.2230 0.98  0.54 phpfox_privacy
#    6 0x        1310.9919  4.2%  5880 0.2230 0.98  0.54 phpfox_friend_list_data
#    7 0x         377.9286  1.2%   909 0.4158 0.97  0.29 phpfox_user_blocked
#    8 0x         188.7216  0.6%   408 0.4626 0.96  0.25 cometchat_status
# MISC 0xMISC      15.7344  0.1%  4715 0.0033   NS   0.0 <30 ITEMS>

[ddm3ve]

die etwas umfangreichere Methode wäre wohl auf Nagios/Icinga zu setzen. für ein einfaches Monitoring würde auch erstmal serverstats.berlios.de genügen oder auch collectd.

Alternativ, auch cacti, zabbix xymon oder was es auch sonst so gibt.
In Deinem Fall musst Du dir dann auch gute und ausführliche Plugins für mysql checks besorgen oder ggf. selber schreiben.
Erst dann kannst Du abschätzen, was in der Datenbank passiert.

Ebenfalls als alternative wäre natürlich noch die Verwendung diverser profiling tools.
Du kannst natürlich auch selbst erstmal hand anlegen:
http://dev.mysql.com/doc/refman/5.1-olh ... files.html
http://webmonkeyuk.wordpress.com/2011/0 ... g-support/

Und zu guter letzt ist eventuell:http://www.jetprofiler.com/de/
ein gutes Tool um ad hoc die wesentlichen Dinge zu visualisieren.

[Joe User]

Laut dem tcpdump ist unter Anderem ein Plugin schuld, dass auf die Tabelle phpfox_feed zugreift.
Ansonsten hilft dauerhaft nur was ddm3ve schrieb und der Verzicht auf nicht-lebenswichtige Plugins.

Solange die Entwickler von phpfox und die Plugin-Schreiber keine Lust haben, sich mit ihren Bugs auseinander zu setzen, hast Du leider keine grossen Möglichkeiten (ausser Wechsel des Script) selbst etwas zu unternehmen. Die Antwort der Entwickler ist eine Standardantwort und bedeutet übersetzt nur:
"Wir wissen, dass wir Mist gebaut haben, aber wir wollen den Mist nicht freiwillig selbst wegschaufeln. Schicke uns einen fertigen Patch und wir schauen mal, ob wir den vielleicht irgendwann einmal einbauen. Bis dahin nerve bitte Andere aber nicht uns."

[Foxgerman]

Ich habe schon die Befürchtung das meine Konfiguration ungenügend ist. Bin jetzt seit kompletten Drei Tagen dran und habe keine Schimmer mehr.
Die Supportantwort stellt mich auch nicht zufrieden und stimme deiner Äußerung 100% zu.

Mit den Monitoring muss ich mich sicher erstmal drei Tage einlesen bevor ich das verstehe. Dennoch vielen Dank für eure Tips.

[Foxgerman]

Nur zur Info: Ich konnte das Problem nicht lösen, weder mit Support vom Hersteller und durch Lösungsvorschläge von hier.
Im Ergebnis habe ich dann eine komplette Neuinstallation samt Server gemacht. Seitdem läuft die Kiste und auch die Datenbank ohne Probleme.

Der Hersteller verspricht nun in den kommenden Update einige Tabellen mit index zu versehen und sonstige Optimierungen.

[Joe User]

Schade, dass Du neuinstallieren musstest, auch wenn es erstmal Linderung brachte. Immerhin bewegt sich nun auch der Hersteller, so dass künftig weniger Probleme mit seiner Software entstehen sollten.

Bleibt nur zu hoffen, dass Du so schnell nicht wieder vor dem gleichen oder ähnlichem Problem stehst und Dir auch ein paar unserer Tipps weiterhelfen können.

[Foxgerman]

Ja, das war echt übel da es auch noch eine Kundeninstallation war die stark frequentiert wurde. Ich hatte die Administration allerdings von einem Vorgänger übernommen und wollte keine Altlasten mit übernehmen. Sonst hätte ich ein paar Tabellen stehen lassen können. Da wir letztendlich aber nicht rausbekommen haben welche diese Last verursacht haben war das der letzte Weg.
Wie auch immer, ich wollte nur kurz darüber informieren und mich für die Unterstützung bedanken.