RootForum Community

Posted: **2010-12-18 14:35**

Irgendwas ist bei meinem Myslq-Server falsch.
Früher war dies nicht der Fall. Ich musste aber aufgrund eines
Wechsels des Servers natürlich das gesamte System neu installieren und
hab dabei grad auf Mysql5.1 geupgradet(da alles korrekt gemacht, also mit mysql_upgrade).
Seither ist mein Load auf unglaubliche durchschnittlich 11 gestiegen.
Ausgabe von top:

last pid: 63820; load averages: 8.36, 9.61, 9.81 up 2+21:29:39 14:30:45
126 processes: 15 running, 111 sleeping
CPU: 4.0% user, 0.0% nice, 74.3% system, 0.1% interrupt, 21.6% idle
Mem: 2926M Active, 11G Inact, 236M Wired, 282M Cache, 112M Buf, 1503M Free
Swap: 4096M Total, 4096M Free

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
2181 mysql 64 55 0 2524M 614M ucond 3 8:23 359.52% mysqld

Ausgabe von 'status;':

Code: Select all

mysql  Ver 14.14 Distrib 5.1.53, for portbld-freebsd7.3 (i386) using  5.2

Connection id:          15731
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.53 FreeBSD port: mysql-server-5.1.53
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 2 days 21 hours 20 min 38 sec

Threads: 23  Questions: 4070401  Slow queries: 128937  Opens: 232  Flush tables: 1  Open tables: 225  Queries per second avg: 16.305

Hardwaremässig sind 16GB Ram drin, 300GB SAS 10k im Raid 10, 2 mal L5410 als CPU.

Ich hoffe ihr könnt mir hierbei helfen.

Gruss

Posted: **2010-12-18 14:54**

Wie sieht die my.cnf aus? Welche Engine(s)? mysqltuner.pl Ausgabe?

Posted: **2010-12-18 14:58**

i386? Du solltest dringend auf FreeBSD 8.1 amd64 wechseln.

Posted: **2010-12-18 15:11**

i386 ist absichtlich so. Aber auf amd64 ändert sich nichts, hatte das ganze System auch damit schon getestet.
Ausgabe von mysqltuner.pl:

Code: Select all

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.53
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 610M (Tables: 65)
[!!] Total fragmented tables: 8

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 21h 56m 32s (4M q [16.426 qps], 15K conn, TX: 2B, RX: 617M)
[--] Reads / Writes: 9% / 91%
[--] Total buffers: 1.1G global + 300.3M per thread (100 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 30.4G (6083% of installed RAM)
[OK] Slow queries: 3% (130K/4M)
[OK] Highest usage of available connections: 62% (62/100)
[OK] Key buffer size / total MyISAM indexes: 800.0M/290.1M
[OK] Key buffer hit rate: 100.0% (25B cached / 46K reads)
[OK] Query cache efficiency: 46.6% (307K cached / 661K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[!!] Joins performed without indexes: 60248
[!!] Temporary tables created on disk: 50% (6K on disk / 13K total)
[OK] Thread cache hit rate: 99% (62 created / 15K connections)
[OK] Table cache hit rate: 96% (225 open / 232 opened)
[OK] Open file limit used: 3% (313/9K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 500M)
    max_heap_table_size (> 16M)

Ausgabe von tuning-primer.sh:

Code: Select all

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

MySQL Version 5.1.53 i386

Uptime = 2 days 21 hrs 57 min 42 sec
Avg. qps = 16
Total Questions = 4138077
Threads Connected = 21

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.1/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 NOT enabled.
Current long_query_time = 5.000000 sec.
You have 130770 out of 4138100 that take longer than 5.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.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 150
Current threads_cached = 41
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

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

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 19.25 G
Configured Max Per-thread Buffers : 29.32 G
Configured Max Global Buffers : 1.07 G
Configured Max Memory Limit : 30.40 G
Physical Memory : 512 M

nMax memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 290 M
Current key_buffer_size = 800 M
Key cache miss rate is 1 : 559817
Key buffer free ratio = 82 %
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 = 300 M
Current query_cache_used = 2 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = .75 %
Current query_cache_min_res_unit = 4 K
Query Cache is 21 % 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.
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 100 M
Current read_rnd_buffer_size = 100 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 60341 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 = 9216 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 = 3072 tables
Current table_definition_cache = 256 tables
You have a total of 88 tables
You have 227 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 500 M
Of 6745 temp tables, 50% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 100 M
Current table scan ratio = 342 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_buffer

TABLE LOCKING
Current Lock Wait ratio = 1 : 229
You may benefit from selective use of InnoDB.

Benutzt wird nur MyISAM.

Meine my.cnf:

Code: Select all

[client]
port		= 3306
socket		= /tmp/mysql.sock


[mysqld]
port = 3306
socket = /tmp/mysql.sock

skip-innodb

low_priority_updates=1
concurrent_insert=2
skip-name-resolve
skip-external-locking
key_buffer_size = 800M
max_allowed_packet = 128M
table_cache = 3072
open_files_limit = 9216
tmp_table_size=500M
sort_buffer_size = 100M
read_buffer_size = 100M
read_rnd_buffer_size = 100M
myisam_sort_buffer_size = 100M
max_length_for_sort_data=2048
max_sort_length=2048
long-query-time=5
slow_query_log=/var/db/mysql/slow-query
interactive_timeout=800
wait_timeout=800
thread_cache = 150
max_connections=100
query_cache_size = 300M
connect_timeout=800

server-id	= 1

big-tables

[mysqldump]
quick

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 256M
read_buffer = 16M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

Posted: **2010-12-18 15:33**

garrisson wrote:i386 ist absichtlich so.

Die damit verbundenen Limits sind Dir bewusst? Wozu dann 16GB RAM?

Folgende Meldungen hast Du gelesen?

garrisson wrote:

Code: Select all

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 30.4G (6083% of installed RAM)
[!!] Joins performed without indexes: 60248
[!!] Temporary tables created on disk: 50% (6K on disk / 13K total)

Code: Select all

Max memory limit exceeds 90% of physical memory

Your key_buffer_size seems to be too high.

Query Cache is 21 % 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.
Your query_cache_size seems to be too high.

You have had 60341 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.

Zudem sind die meisten Buffer und die Max_Connections viel zu hoch gesetzt.

Posted: **2010-12-18 15:47**

Sind mir bewusst ja. Aber der mysqld-Prozess hat noch nie mehr als 1.8GB Ram angesprochen. 16GB Ram können mit PAE genutzt werden. Ich weiss, amd64 wäre besser. Aber an i386 liegts bestimmt nicht, da ich wie gesagt das Ganze schon auf amd64 getestet hab.
Der Flaschenhals muss also irgendwo anders liegen...

Posted: **2010-12-18 16:18**

In Deinem top möchte MySQL bereits 2,5GB reservieren, also mehr als i386 normalerweise pro Prozess/Thread zulässt. Daher solltest Du ernsthaft auf amd64 wechseln, zumal es auch sicherheitstechnisch ratsam ist. Der Overhead von PAE ist auch nicht förderlich für die Load.

Der zweite Flaschenhals steckt aber in den Buffern und den Connections. Folgender [mysqld]-Abschnitt (bitte komplett austauschen) sollte erstmal etwas besser passen und uns weitere Anhaltspunkte zum Tuning liefern:

Code: Select all

[mysqld]
user                            = mysql
port                            = 3306
bind-address                    = 127.0.0.1
socket                          = /tmp/mysql.sock
character-sets-dir              = /usr/local/share/mysql/charsets
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/tmp
slave-load-tmpdir               = /var/tmp
language                        = /usr/local/share/mysql/english
log-bin                         = /var/db/mysql/mysql-bin
relay-log                       = /var/db/mysql/relay.log
relay-log-index                 = /var/db/mysql/relay.index
relay-log-info-file             = /var/db/mysql/relay.info
master-info-file                = /var/db/mysql/master.info
#master-host                     = <hostname>
#master-user                     = <username>
#master-password                 = <password>
#master-port                     = 3306
#auto_increment_increment        = 10
#auto_increment_offset           = 1
server-id                       = 1
back_log                        = 50
sync_binlog                     = 1
binlog_cache_size               = 1M
max_binlog_size                 = 100M
binlog-format                   = MIXED
expire_logs_days                = 7
slow-query-log                  = 1
slow-query-log-file             = /var/db/mysql/slow-query.log
slave_compressed_protocol       = 1
#lower_case_table_names          = 1
safe-user-create                = 1
delay-key-write                 = ALL
myisam-recover                  = FORCE,BACKUP
key_buffer_size                 = 320M
join_buffer_size                = 2M
sort_buffer_size                = 2M
read_buffer_size                = 2M
read_rnd_buffer_size            = 8M
myisam_sort_buffer_size         = 64M
max_allowed_packet              = 32M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
table_cache                     = 3072
table_definition_cache          = 1024
query_cache_type                = 1
query_cache_size                = 256M
query_cache_limit               = 16M
#low_priority_updates            = 1
#concurrent_insert               = 2
thread_concurrency              = 8
thread_cache_size               = 32
max_connections                 = 32
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 2
local-infile                    = 0
log-warnings                    = 2
log-slave-updates
log-queries-not-using-indexes
skip-external-locking
skip-name-resolve
skip-innodb
big-tables

Posted: **2010-12-18 16:57**

Hm, ich schau mir das mit amd64 nochmal an, eventuell muss ich halt die datenbank auslagern.
Hab jetzt mal die my.cnf geupdatet. Alles von dir übernommen bis auf bin-adress 127.0.0.1. DB muss von ausserhalb zugreifbar sein.
Ausgabe von tuning-primer.sh:

./tuning-primer.sh

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

MySQL Version 5.1.53-log i386

Uptime = 0 days 0 hrs 3 min 37 sec
Avg. qps = 25
Total Questions = 5462
Threads Connected = 21

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:
http://dev.mysql.com/doc/refman/5.1/en/ ... ables.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 = 2.000000 sec.
You have 230 out of 5484 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled

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

MAX CONNECTIONS
Current max_connections = 32
Current threads_connected = 21
Historic max_used_connections = 21
The number of used connections is 65% of the configured maximum.
Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 894 M
Configured Max Per-thread Buffers : 486 M
Configured Max Global Buffers : 576 M
Configured Max Memory Limit : 1.03 G
Physical Memory : 512 M

nMax memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 290 M
Current key_buffer_size = 320 M
Key cache miss rate is 1 : 9717
Key buffer free ratio = 86 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 256 M
Current query_cache_used = 85 K
Current query_cache_limit = 16 M
Current Query cache Memory fill ratio = .03 %
Current query_cache_min_res_unit = 4 K
Query Cache is 21 % 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.
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
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 = 8 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 76 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 = 11095 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 = 3072 tables
Current table_definition_cache = 1024 tables
You have a total of 88 tables
You have 123 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 280 temp tables, 50% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 28398 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.

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

mysqltuner.pl:

./mysqltuner.pl

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.53-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 610M (Tables: 65)
[!!] Total fragmented tables: 6

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 20s (6K q [26.600 qps], 232 conn, TX: 679K, RX: 1M)
[--] Reads / Writes: 12% / 88%
[--] Total buffers: 640.0M global + 14.2M per thread (32 max threads)
[!!] Maximum possible memory usage: 1.1G (213% of installed RAM)
[OK] Slow queries: 4% (290/6K)
[OK] Highest usage of available connections: 68% (22/32)
[OK] Key buffer size / total MyISAM indexes: 320.0M/290.2M
[OK] Key buffer hit rate: 100.0% (43M cached / 4K reads)
[OK] Query cache efficiency: 25.0% (254 cached / 1K selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 100% (3 temp sorts / 3 sorts)
[!!] Joins performed without indexes: 97
[!!] Temporary tables created on disk: 50% (294 on disk / 588 total)
[OK] Thread cache hit rate: 90% (22 created / 232 connections)
[OK] Table cache hit rate: 40% (123 open / 306 opened)
[OK] Open file limit used: 1% (216/11K)
[OK] Table locks acquired immediately: 98% (6K immediate / 6K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 8M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)

Am load des Servers hat sich nichts verändert.
Danke schonmal für die Hilfe...

gruss

Posted: **2010-12-18 17:42**

Ich glaub dem primer aber nicht so richtig. Denn er gibt auch an, dass der Physical Memory 512M betrage. Obwohls 16G sind.

Posted: **2010-12-18 17:58**

garrisson wrote:Ich glaub dem primer aber nicht so richtig. Denn er gibt auch an, dass der Physical Memory 512M betrage. Obwohls 16G sind.

Das liegt unter Anderem am i386 und dem PAE. Die exakten Details sind für diesen Thread allerdings zu umfangreich, da schreiben andere ganze Bücher drüber.

Die beiden Scripts werfen erst nach circa 48 Stunden verwertbare Ergebnisse, bis dahin musst die leider erstmal etwaige Schwankungen im Betrieb ertragen.

Posted: **2010-12-18 18:01**

matzewe01 wrote:Was sagt free zu Deinem Speicher?

free und /proc kennt ein Standard-FreeBSD nicht ;)

Posted: **2010-12-18 18:43**

Es ist ein dedizierter Server.
CPU:

Code: Select all

 dmesg | grep CPU
CPU: Intel(R) Xeon(R) CPU           L5410  @ 2.33GHz (2346.13-MHz 686-class CPU)
FreeBSD/SMP: Multiprocessor System Detected: 8 CPUs
cpu0: <ACPI CPU> on acpi0
p4tcc0: <CPU Frequency Thermal Control> on cpu0
cpu1: <ACPI CPU> on acpi0
p4tcc1: <CPU Frequency Thermal Control> on cpu1
cpu2: <ACPI CPU> on acpi0
p4tcc2: <CPU Frequency Thermal Control> on cpu2
cpu3: <ACPI CPU> on acpi0
p4tcc3: <CPU Frequency Thermal Control> on cpu3
cpu4: <ACPI CPU> on acpi0
p4tcc4: <CPU Frequency Thermal Control> on cpu4
cpu5: <ACPI CPU> on acpi0
p4tcc5: <CPU Frequency Thermal Control> on cpu5
cpu6: <ACPI CPU> on acpi0
p4tcc6: <CPU Frequency Thermal Control> on cpu6
cpu7: <ACPI CPU> on acpi0
p4tcc7: <CPU Frequency Thermal Control> on cpu7
SMP: AP CPU #3 Launched!
SMP: AP CPU #1 Launched!
SMP: AP CPU #4 Launched!
SMP: AP CPU #2 Launched!
SMP: AP CPU #6 Launched!
SMP: AP CPU #5 Launched!
SMP: AP CPU #7 Launched!

RAM:

Code: Select all

 dmesg | grep memory
real memory  = 17716740096 (16896 MB)
avail memory = 16832745472 (16052 MB)

Show global status gibt aus:

Code: Select all

Aborted_clients	23
Aborted_connects	2
Binlog_cache_disk_use	0
Binlog_cache_use	26
Bytes_received	17661136
Bytes_sent	11219318
Com_admin_commands	3
Com_assign_to_keycache	0
Com_alter_db	0
Com_alter_db_upgrade	0
Com_alter_event	0
Com_alter_function	0
Com_alter_procedure	0
Com_alter_server	0
Com_alter_table	0
Com_alter_tablespace	0
Com_analyze	0
Com_backup_table	0
Com_begin	0
Com_binlog	0
Com_call_procedure	0
Com_change_db	6
Com_change_master	0
Com_check	0
Com_checksum	0
Com_commit	0
Com_create_db	0
Com_create_event	0
Com_create_function	0
Com_create_index	0
Com_create_procedure	0
Com_create_server	0
Com_create_table	0
Com_create_trigger	0
Com_create_udf	0
Com_create_user	0
Com_create_view	0
Com_dealloc_sql	0
Com_delete	37843
Com_delete_multi	0
Com_do	0
Com_drop_db	0
Com_drop_event	0
Com_drop_function	0
Com_drop_index	0
Com_drop_procedure	0
Com_drop_server	0
Com_drop_table	0
Com_drop_trigger	0
Com_drop_user	0
Com_drop_view	0
Com_empty_query	0
Com_execute_sql	0
Com_flush	0
Com_grant	0
Com_ha_close	0
Com_ha_open	0
Com_ha_read	0
Com_help	0
Com_insert	147
Com_insert_select	6
Com_install_plugin	0
Com_kill	1
Com_load	0
Com_load_master_data	0
Com_load_master_table	0
Com_lock_tables	0
Com_optimize	0
Com_preload_keys	0
Com_prepare_sql	0
Com_purge	0
Com_purge_before_date	0
Com_release_savepoint	0
Com_rename_table	0
Com_rename_user	0
Com_repair	0
Com_replace	53552
Com_replace_select	0
Com_reset	0
Com_restore_table	0
Com_revoke	0
Com_revoke_all	0
Com_rollback	0
Com_rollback_to_savepoint	0
Com_savepoint	0
Com_select	10999
Com_set_option	327
Com_show_authors	0
Com_show_binlog_events	0
Com_show_binlogs	0
Com_show_charsets	1
Com_show_collations	1
Com_show_column_types	0
Com_show_contributors	0
Com_show_create_db	0
Com_show_create_event	0
Com_show_create_func	0
Com_show_create_proc	0
Com_show_create_table	0
Com_show_create_trigger	0
Com_show_databases	2
Com_show_engine_logs	0
Com_show_engine_mutex	0
Com_show_engine_status	0
Com_show_events	0
Com_show_errors	0
Com_show_fields	0
Com_show_function_status	1
Com_show_grants	0
Com_show_keys	0
Com_show_master_status	0
Com_show_new_master	0
Com_show_open_tables	0
Com_show_plugins	0
Com_show_privileges	0
Com_show_procedure_status	1
Com_show_processlist	0
Com_show_profile	0
Com_show_profiles	0
Com_show_slave_hosts	0
Com_show_slave_status	0
Com_show_status	122
Com_show_storage_engines	0
Com_show_table_status	0
Com_show_tables	2
Com_show_triggers	0
Com_show_variables	97
Com_show_warnings	0
Com_slave_start	0
Com_slave_stop	0
Com_stmt_close	0
Com_stmt_execute	0
Com_stmt_fetch	0
Com_stmt_prepare	0
Com_stmt_reprepare	0
Com_stmt_reset	0
Com_stmt_send_long_data	0
Com_truncate	0
Com_uninstall_plugin	0
Com_unlock_tables	0
Com_update	5642
Com_update_multi	0
Com_xa_commit	0
Com_xa_end	0
Com_xa_prepare	0
Com_xa_recover	0
Com_xa_rollback	0
Com_xa_start	0
Compression	OFF
Connections	578
Created_tmp_disk_tables	458
Created_tmp_files	416
Created_tmp_tables	458
Delayed_errors	0
Delayed_insert_threads	0
Delayed_writes	0
Flush_commands	1
Handler_commit	26
Handler_delete	13112
Handler_discover	0
Handler_prepare	0
Handler_read_first	460
Handler_read_key	291529379
Handler_read_next	24425231
Handler_read_prev	24
Handler_read_rnd	40498
Handler_read_rnd_next	479735797
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	42445
Handler_write	774668
Key_blocks_not_flushed	3442
Key_blocks_unused	276939
Key_blocks_used	11571
Key_read_requests	878467607
Key_reads	11506
Key_write_requests	104019
Key_writes	0
Last_query_cost	0.000000
Max_used_connections	28
Not_flushed_delayed_rows	0
Open_files	236
Open_streams	0
Open_table_definitions	88
Open_tables	143
Opened_files	2598
Opened_table_definitions	88
Opened_tables	326
Prepared_stmt_count	0
Qcache_free_blocks	157
Qcache_free_memory	267987792
Qcache_hits	4270
Qcache_inserts	5550
Qcache_lowmem_prunes	0
Qcache_not_cached	5417
Qcache_queries_in_cache	404
Qcache_total_blocks	973
Queries	113547
Questions	113547
Rpl_status	NULL
Select_full_join	2743
Select_full_range_join	0
Select_range	17
Select_range_check	0
Select_scan	4357
Slave_open_temp_tables	0
Slave_retried_transactions	0
Slave_running	OFF
Slow_launch_threads	0
Slow_queries	6857
Sort_merge_passes	208
Sort_range	12
Sort_rows	16665158
Sort_scan	211
Table_locks_immediate	108766
Table_locks_waited	6113
Tc_log_max_pages_used	0
Tc_log_page_size	0
Tc_log_page_waits	0
Threads_cached	0
Threads_connected	28
Threads_created	28
Threads_running	16
Uptime	4771
Uptime_since_flush_status	4771

Posted: **2010-12-18 18:53**

tuning-primer.sh:

Code: Select all

MySQL Version 5.1.53-log i386

Uptime = 0 days 1 hrs 57 min 51 sec
Avg. qps = 23
Total Questions = 164321
Threads Connected = 27

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:
http://dev.mysql.com/doc/refman/5.1/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 = 2.000000 sec.
You have 10153 out of 164344 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled

WORKER THREADS
Current thread_cache_size = 32
Current threads_cached = 6
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 32
Current threads_connected = 27
Historic max_used_connections = 33
The number of used connections is 103% of the configured maximum.
You should raise max_connections

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 1.05 G
Configured Max Per-thread Buffers : 486 M
Configured Max Global Buffers : 576 M
Configured Max Memory Limit : 1.03 G
Physical Memory : 512 M

nMax memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 290 M
Current key_buffer_size = 320 M
Key cache miss rate is 1 : 100692
Key buffer free ratio = 84 %
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 = 256 M
Current query_cache_used = 689 K
Current query_cache_limit = 16 M
Current Query cache Memory fill ratio = .26 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
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 = 8 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 3904 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 = 11095 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 = 3072 tables
Current table_definition_cache = 1024 tables
You have a total of 88 tables
You have 157 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 675 temp tables, 50% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 42952 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.

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

Der Server ist, wie du richtig vermutet hast, eine Kombia us Web, Mail und DB Server. Jedoch brauchen die restlichen Prozesse praktisch keine Ressourcen. (Alles zusammen max. 10% zu Spitzenzeiten). Speziell ist vllt. noch, dass ein anderer Server auf die Daten des Webservers zugreift.(nein keine Replication).

Posted: **2010-12-18 19:47**

Er kann die max_connections noch auf 50 erhöhen, darüber wirds vermutlich langsam eng. Mehr Sorgen machen mir die fehlenden Indexe, die vielen TEXT/BLOB-Spalten in den Tabellen und die langsamen SELECTS. Mit anderen Worten: Die Anwendung(en) sind zum Teil sehr schlecht programmiert.

Posted: **2010-12-20 08:14**

Die Applikation kann ich leider nicht verändern. Wäre es sinnvoll einen Mysql-Cluster aufzubauen, bei dem read/write getrennt sind? Z.B. einen Master wo alle Writes drauf laufen(mit SSD Platten plus SATA2 für Backup) und dann einige Slaves umd die Reads zu regeln? Dies mit mysql-proxy? Sollte das Mysql-Proxy auf dem Master sein, oder kann das auch auf nem anderen Server sein(zwecks mehr Performance)?

Ich hab mittlerweile die ganze DB ausgelagert, auf einen Quadcore-Server mit SSD-Festplatte. Hat leider nicht so viel gebracht, sind immer noch ca. 10% Slow-querries vorhanden.

Posted: **2010-12-20 10:25**

Die Slow-Queries kannst Du auch nicht durch Hardware erschlagen, sondern durch eine geeignetere Datenbankstruktur. Als Erstes solltest Du benötigten Indexe setzen, das geht oft ohne Eingriff in die Applikation. Danach solltest Du schauen ob Du ein paar BLOB/TEXT-Spalten zum Beispiel auf VARCHAR umdefinieren kannst, das spart on-Disk Temptables.

Ein MySQL-Cluster bringt vermutlich nicht viel, da das Datenbankdesign der Applikation kaputt ist und diese Fehler mitgeclustert werden.

Posted: **2010-12-20 19:34**

Ok, dann vergess ich das mit dem Cluster wieder. Werd jetzt mal neue Indexe anlegen, bzw. zusätzliche, und schauen, ob das was bringt. BLOB's kann ich leider nicht entfernen.

RootForum Community

Mysqld verursacht Load zwischen 9 und 14

Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14

Re: Mysqld verursacht Load zwischen 9 und 14