Mysqld verursacht Load zwischen 9 und 14

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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
Top

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

Wie sieht die my.cnf aus? Welche Engine(s)? mysqltuner.pl Ausgabe?
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

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

i386? Du solltest dringend auf FreeBSD 8.1 amd64 wechseln.
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

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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

Top

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

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.
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

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

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
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

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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
Top

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

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.
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

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

matzewe01 wrote:Was sagt free zu Deinem Speicher?

free und /proc kennt ein Standard-FreeBSD nicht ;)
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

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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
Top

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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).
Top

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

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.
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

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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

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

Re: Mysqld verursacht Load zwischen 9 und 14

Post by Joe User »

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.
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

garrisson
Posts: 10
Joined: 2010-12-18 14:26

Re: Mysqld verursacht Load zwischen 9 und 14

Post by garrisson »

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