MySQL Auslastung

MySQL, PostgreSQL, SQLite
Post Reply
landor caeyran
Posts: 5
Joined: 2006-02-12 13:48
 

MySQL Auslastung

Post by landor caeyran »

Vorab sei gesagt.
Ich habe die Suche benutzt und ich habe auch einige Lösungsansätze gefunden.
Ich habe nun auch leider keine riesen Erfahrung mit Linux :-((

Der Server hat Suse 9.3
MySQL 4.1.10a
PHP Version 4.3.10

Darauf läuft ein phpbb mit ca 270 MB derzeit (Suchindex wurde gelöscht) und 1700 User.
Wenn sich so 9 User auf dem Server befinden, liegt die Auslastung des MySQL Dienstes bei 30% (mit Suchindex 60%).

Ich habe bereits PHP optimiert (versucht) ebenso MySQL (config siehe unten).
Auch habe ich etwas darüber gelesen, das man den Characteset richtig einstellen sollte, naja leider war das Ergebnis öhm verherrend :-(
Alle Umlaute waren weg und Tabellen vom Typ Char wurden mal eben einfach gekürzt Oo

Es ist ein 2GHZ
Mit 512 MB Arbeitsspeicher.

Welche Angaben bräuchtet ihr noch?
Ich bitte zu verzeihen, dass ich deswegen einen neuen Thread aufmache, aber ich habe nun Tagelang Foren gelesen und Google benutzt und ich kapiere die Dinge teilweise nicht oder aber sie brachten nichts.

Code: Select all

my.cnf             [B---] 21 L:[  1+14  15/ 68] *(264 /1158b)= .  10 0x0A
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
default-character-set=latin1

[mysqld]
user = mysql
socket          = /var/lib/mysql/mysql.sock
bind-address =127.0.0.1
port = 3306
server-id = 1
skip-locking
skip-networking
skip-name-resolve
skip-external-locking
local-infile = 0
key_buffer_size = 64M
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_allowed_packet = 16M
net_buffer_length = 8K
thread_stack = 128K
thread_cache = 8
table_cache = 256
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 1M
thread_concurrency = 2
skip-innodb
old-passwords
default-character-set=latin1
collation-server=latin1_german2_ci

connect_timeout = 60
connect_timeout = 15
max_connections = 200


[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
log=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
#no-auto-rehash
#safe-updates
default-character-set=latin1

[isamchk]
key_buffer = 64M
sort_buffer_size = 4M
read_buffer = 1M
write_buffer = 1M

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 4M
read_buffer = 1M
write_buffer = 1M

[mysqlhotcopy]
interactive-timeout



##########################
php.ini

php.ini            [B---] 25 L:[  1+57  58/175] *(1401/3763b)= .  10 0x0A
[PHP]
engine = On
;zend.ze1_compatibility_mode = On
short_open_tag = On
asp_tags = Off
precision = 14
y2k_compliance = On
output_buffering = 4096
;output_handler =
zlib.output_compression = Off
;zlib.output_handler =
implicit_flush = Off
unserialize_callback_func =
serialize_precision = 100
allow_call_time_pass_reference = Off
safe_mode = Off
safe_mode_gid = Off
safe_mode_include_dir =
safe_mode_exec_dir =
safe_mode_allowed_env_vars = PHP_
safe_mode_protected_env_vars = LD_LIBRARY_PATH
;open_basedir =
disable_functions =
disable_classes =
highlight.string = #DD0000
highlight.comment = #FF9900
highlight.keyword = #007700
highlight.bg = #FFFFFF
highlight.default = #0000BB
highlight.html = #000000
;ignore_user_abort = On
expose_php = Off
max_execution_time = 30
max_input_time = 60
memory_limit = 16M
error_reporting = E_ALL & ~E_NOTICE & ~E_STRICT
display_errors = On
display_startup_errors = Off
log_errors = Off
log_errors_max_len = 1024
ignore_repeated_errors = Off
ignore_repeated_source = Off
report_memleaks = On
track_errors = Off
html_errors = Off
;error_log = "/var/log/apache2/error_log"
;arg_separator.output = "&"
;arg_separator.input = ";&"
variables_order = "GPCS"
register_globals = Off
register_long_arrays = Off
register_argc_argv = On
;auto_globals_jit = Off
post_max_size = 8M
gpc_order  = "GPC"
magic_quotes_gpc = On
magic_quotes_runtime = Off
magic_quotes_sybase = Off
php.ini            [B---] 22 L:[ 58+57 115/175] *(2462/3763b)= .  10 0x0A
magic_quotes_sybase = Off
auto_prepend_file =
auto_append_file =
default_mimetype = "text/html"
;default_charset = "iso-8859-1"
;always_populate_raw_post_data = Off
include_path = ".:/usr/share/php"
extension_dir = "./"
extension_dir = /usr/lib/php/extensions
doc_root =
user_dir =
enable_dl = On
file_uploads = On
upload_tmp_dir =
upload_max_filesize = 6M
allow_url_fopen = Off
from = "anonymous@localhost"
user_agent = ""
default_socket_timeout = 60
auto_detect_line_endings = Off


[extension section]
extension=ftp.so
extension=gd.so
extension=zlib.so
extension=mbstring.so
extension=session.so
extension=mysql.so


[Date]
;date.timezone =

[Syslog]
define_syslog_variables  = Off

[mail function]
SMTP = localhost
smtp_port = 25
;sendmail_from = me@example.com
;sendmail_path =
;mail.force_extra_parameters =

[SQL]
sql.safe_mode = Off

[MySQL]
mysql.allow_persistent = Off
mysql.max_persistent = -1
mysql.max_links = -1
mysql.default_port =
mysql.default_socket =
mysql.default_host =
mysql.default_user =
mysql.default_password =
mysql.connect_timeout = 60
mysql.trace_mode = Off
php.ini            [B---] 27 L:[114+56 170/175] *(3720/3763b)= .  10 0x0A
mysql.connect_timeout = 60
mysql.trace_mode = Off

[MySQLi]
mysqli.max_links = -1
mysqli.default_port = 3306
mysqli.default_socket =
mysqli.default_host =
mysqli.default_user =
mysqli.default_pw =
mysqli.reconnect = Off

[PostgresSQL]
pgsql.allow_persistent = Off
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = -1
pgsql.max_links = -1
pgsql.ignore_notice = 0
pgsql.log_notice = 0

[bcmath]
bcmath.scale = 0

[Session]
session.save_handler = files
session.save_path = "/tmp"
session.use_cookies = 1
session.use_only_cookies = 0
session.name = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.serialize_handler = php
session.gc_probability = 1
session.gc_divisor = 1000
session.gc_maxlifetime = 1440
session.bug_compat_42 = 0
session.bug_compat_warn = 1
session.referer_check =
session.entropy_length = 16
session.entropy_file = /dev/urandom
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
session.hash_function = 0
session.hash_bits_per_character = 5
url_rewriter.tags = "a=href,area=href,frame=src,input=src,fieldset="

[Tidy]
;tidy.default_config = /usr/lib/php/default.tcfg
tidy.clean_output = Off

[soap]
soap.wsdl_cache_enabled = 1
soap.wsdl_cache_dir = "/tmp"
soap.wsdl_cache_ttl = 86400
php.ini            [B---]  0 L:[117+58 175/175] *(3763/3763b)= <EOF>
[MySQLi]
mysqli.max_links = -1
mysqli.default_port = 3306
mysqli.default_socket =
mysqli.default_host =
mysqli.default_user =
mysqli.default_pw =
mysqli.reconnect = Off

[PostgresSQL]
pgsql.allow_persistent = Off
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = -1
pgsql.max_links = -1
pgsql.ignore_notice = 0
pgsql.log_notice = 0

[bcmath]
bcmath.scale = 0

[Session]
session.save_handler = files
session.save_path = "/tmp"
session.use_cookies = 1
session.use_only_cookies = 0
session.name = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.serialize_handler = php
session.gc_probability = 1
session.gc_divisor = 1000
session.gc_maxlifetime = 1440
session.bug_compat_42 = 0
session.bug_compat_warn = 1
session.referer_check =
session.entropy_length = 16
session.entropy_file = /dev/urandom
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
session.hash_function = 0
session.hash_bits_per_character = 5
url_rewriter.tags = "a=href,area=href,frame=src,input=src,fieldset="

[Tidy]
;tidy.default_config = /usr/lib/php/default.tcfg
tidy.clean_output = Off

[soap]
soap.wsdl_cache_enabled = 1
soap.wsdl_cache_dir = "/tmp"
soap.wsdl_cache_ttl = 86400
; Local Variables:
; tab-width: 4
; End:
User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin
Contact:
 

Re: MySQL Auslastung

Post by isotopp »

Landor Caeyran wrote:Welche Angaben bräuchtet ihr noch?
Die my.cnf ist ein guter Ansatz. Die nächste Frage wäre nach dem Output von SHOW STATUS, wenn "SHOW STATUS LIKE 'uptime'" mehr als 80000 (ca. ein Tag) ist.
[gca].dfritz
Posts: 28
Joined: 2004-11-30 08:34
Location: Alpen
Contact:
 

Re: MySQL Auslastung

Post by [gca].dfritz »

Hallo,

Wieviel Einträge hat die Datenbank ?

Ich vermute einfach, die Datenbank ist am Limit weil Sie pro User ca. 300.000 - 400.000 Einträge durchsuchen muss. Irgendwie ist das das übliche Leiden von Boards.

Ansonsten wäre mehr RAM und ggf. CPU auch empfehlenswert.

Gruß Daniel
User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin
Contact:
 

Re: MySQL Auslastung

Post by isotopp »

[GCA].dfritz wrote:Wieviel Einträge hat die Datenbank ?

Ich vermute einfach, die Datenbank ist am Limit weil Sie pro User ca. 300.000 - 400.000 Einträge durchsuchen muss.
Das ist möglich, aber ohne eine Ansicht der SHOW STATUS Variablen und des Slow Query Log kaum sicher zu diagostizieren. Dazu kommt, daß viele Boards einfach schlechtes SQL oder schlechte Schemadefinitionen haben.

Datenbanken sind eigentlich recht gut darin, aus vielen Daten wenige Daten zu extrahieren, wenn man die Strukturen passend definiert. Das ist genau ihr Zweck.
landor caeyran
Posts: 5
Joined: 2006-02-12 13:48
 

Re: MySQL Auslastung

Post by landor caeyran »

So, erstmal habe ich ein Update von der 4.1.10a auf die 4.1.18 gemacht.
Brachte so um die 10% Verbesserung.

Derzeit habe ich den Suchindex gelöscht, das hilft.
So habe ich nun eine Auslastung von ca 10 bis 20% bei 5 bis 10 Benutzern :-(((


Das Board selber hat ca. 329.000 Einträge und derzeit 1.700 User.

Vielen Dank für euer Bemühen.
Naja, mehr Ram und bessere CPU ist ja immer so eine Sache.
Ein Ferrarie mit Platten ist auch langsam, da kann man zwar mehr Gas geben.. aber ob das dann die optimale Lösung ist?!



  • Code: Select all

    SHOW STATUS LIKE 'uptime'
    
    Uptime 150455
  • [quote]
    SHOW STATUS
    [/quote]
    borted_clients 0
    Aborted_connects 4
    Binlog_cache_disk_use 0
    Binlog_cache_use 0
    Bytes_received 388440177
    Bytes_sent 2579587748
    Com_admin_commands 0
    Com_alter_db 0
    Com_alter_table 1
    Com_analyze 0
    Com_backup_table 0
    Com_begin 1771
    Com_change_db 35117
    Com_change_master 0
    Com_check 0
    Com_checksum 0
    Com_commit 1457
    Com_create_db 0
    Com_create_function 0
    Com_create_index 0
    Com_create_table 0
    Com_dealloc_sql 0
    Com_delete 10080
    Com_delete_multi 0
    Com_do 0
    Com_drop_db 0
    Com_drop_function 0
    Com_drop_index 0
    Com_drop_table 0
    Com_drop_user 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 459251
    Com_insert_select 706
    Com_kill 0
    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_rename_table 0
    Com_repair 0
    Com_replace 3
    Com_replace_select 0
    Com_reset 0
    Com_restore_table 0
    Com_revoke 0
    Com_revoke_all 0
    Com_rollback 0
    Com_savepoint 0
    Com_select 381376
    Com_set_option 441
    Com_show_binlog_events 0
    Com_show_binlogs 11
    Com_show_charsets 221
    Com_show_collations 221
    Com_show_column_types 0
    Com_show_create_db 10
    Com_show_create_table 33
    Com_show_databases 85
    Com_show_errors 0
    Com_show_fields 1128
    Com_show_grants 10
    Com_show_innodb_status 0
    Com_show_keys 78
    Com_show_logs 0
    Com_show_master_status 0
    Com_show_ndb_status 0
    Com_show_new_master 0
    Com_show_open_tables 0
    Com_show_privileges 0
    Com_show_processlist 0
    Com_show_slave_hosts 0
    Com_show_slave_status 0
    Com_show_status 6
    Com_show_storage_engines 0
    Com_show_tables 769
    Com_show_variables 443
    Com_show_warnings 0
    Com_slave_start 0
    Com_slave_stop 0
    Com_stmt_close 0
    Com_stmt_execute 0
    Com_stmt_prepare 0
    Com_stmt_reset 0
    Com_stmt_send_long_data 0
    Com_truncate 1995
    Com_unlock_tables 0
    Com_update 626695
    Com_update_multi 0
    Connections 35642
    Variable_name Value
    Created_tmp_disk_tables 13394
    Created_tmp_files 2
    Created_tmp_tables 40160
    Delayed_errors 0
    Delayed_insert_threads 0
    Delayed_writes 0
    Flush_commands 1
    Handler_commit 0
    Handler_delete 16039
    Handler_discover 0
    Handler_read_first 6901
    Handler_read_key 324532736
    Handler_read_next 43993571
    Handler_read_prev 29229
    Handler_read_rnd 7043491
    Handler_read_rnd_next 349281428
    Handler_rollback 0
    Handler_update 2746782
    Handler_write 16928888
    Key_blocks_not_flushed 0
    Key_blocks_unused 44812
    Key_blocks_used 13185
    Key_read_requests 893622429
    Key_reads 45956
    Key_write_requests 3310452
    Key_writes 1038535
    Max_used_connections 59
    Not_flushed_delayed_rows 0
    Open_files 318
    Open_streams 0
    Open_tables 232
    Opened_tables 3891
    Qcache_free_blocks 1034
    Qcache_free_memory 13620984
    Qcache_hits 488551
    Qcache_inserts 379285
    Qcache_lowmem_prunes 1677
    Qcache_not_cached 2024
    Qcache_queries_in_cache 1584
    Qcache_total_blocks 4353
    Questions 2238266
    Rpl_status NULL
    Select_full_join 9537
    Select_full_range_join 583
    Select_range 9161
    Select_range_check 0
    Select_scan 60810
    Slave_open_temp_tables 0
    Slave_retried_transactions 0
    Slave_running OFF
    Slow_launch_threads 18
    Slow_queries 183
    Sort_merge_passes 1
    Sort_range 15321
    Sort_rows 19202561
    Sort_scan 57640
    Table_locks_immediate 1680928
    Table_locks_waited 2006
    Threads_cached 6
    Threads_connected 2
    Threads_created 181
    Threads_running 1
    Uptime 150543
landor caeyran
Posts: 5
Joined: 2006-02-12 13:48
 

Re: MySQL Auslastung

Post by landor caeyran »

  • [quote]Habe was von Slow Querys gelesen, hier mal die Daten
    [/quote]

    Rpl status NULL
    Select full join 9565
    Select full range join 583
    Select range 9186
    Select range check 0
    Select scan 61065
    Slave open temp tables 0
    Slave retried transactions 0
    Slave running OFF
    Slow launch threads 18
    Slow queries 183
    Sort merge passes 1
    Sort range 15388
    Sort rows 19271497
    Sort scan 57847
    Table locks immediate 1690827
    Table locks waited 2006
    Threads cached 7
    Threads connected 1
    Threads created 181
    Threads running 1
flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin
 

Re: MySQL Auslastung

Post by flo »

Laß die Slow Queries mal loggen und gehe die entsprechenden Queries dann mal per EXPLAIN durch - eventuell kannst Du die Queries dann optimieren und/oder einen Index hinzufügen wo er gebraucht wird.

flo.
landor caeyran
Posts: 5
Joined: 2006-02-12 13:48
 

Re: MySQL Auslastung

Post by landor caeyran »

Ich habe es nicht geschafft, die entsprechende Option richtig einzustellen.
Ich weiß somit nicht, wie man die slow querys mitloggen kann.
flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin
 

Re: MySQL Auslastung

Post by flo »

Landor Caeyran wrote:Ich habe es nicht geschafft, die entsprechende Option richtig einzustellen.
Ich weiß somit nicht, wie man die slow querys mitloggen kann.

Code: Select all

log-slow-queries        = /var/log/mysql/mysql-slow.log
die Rechte müssen natürlich stimmen, aber mit dem Parameter loggt er hoch- und runterfahren der Datenbank, also sollte das passen.

flo.
User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin
Contact:
 

Re: MySQL Auslastung

Post by isotopp »

Generelle Serverlast bestimmen:

Dein Server sieht Questions/Uptime = 2238266/150543 = 14.8679 Queries per Second (qps). Das ist sehr wenig. Die Last verteilt sich auf die Statements wie folgt:

DML = com_delete + com_insert + com_update + com_replace = 1096029, dem steht QL von com_select + qcache_hits von 869927 gegenüber. Dein Server sieht also 1.2599 mal mehr DML als QL, was sehr ungewöhnlich ist, da er so mehr Updates als Selects sieht.

Solche Server bekommen bei Einsatz von MyISAM oft ein Problem mit den Table Locks, aber Deine QPS sind zu niedrig, als das ich das erwarten würde. Entsprechend ist auch table_locks_waiting/table_locks_immediate nur 2006/1680928 = 0.11%, also vollkommen unkritisch.

Deine Query Cache Effizienz ist qcache_hits/qcache_inserts = 488551/379285 = 1.2880. Das ist sehr wenig, und eine Folge der hohen Ã?nderungsrate. Dein Query Cache besteht aus 1034 Qcache_free_blocks, also aus sehr vielen Fragmenten. Er könnte mit PURGE QUERY CACHE geputzt werden, bei der geringen QCache-Effizienz macht das auch nix mehr aus. qcache_free_memory = 13620984, also sind 13M QCache derzeit unbenutzt.

Deine Key Buffer Effizienz ist key_read_requests/key_reads = 893622429/45956 = 19445.1742. Das ist exzellent, oder genau genommen sogar fast verdächtig. Dein key_buffer hat demnach auch Key_blocks_unused 44812, aber nur key_blocks_used 13185. Dein Server hat entweder einen zu großen key_buffer_size oder zu wenige Indices.

Deine Table Cache Effizienz ist tables_opened/tables_open = 3891/232 = 16.7715. Das ist nicht zu schlecht, aber offenbar ist Dein Table_Cache nicht groß genug.

Deine Thread Cache Effizienz ist Connections/Threads_created = 35642/181 = 196.9171. Das ist exzellent.

Die Qualität Deines SQL ist slow_queries 183 (Ist das Slow Query Log angeschaltet?), und Select_full_join = 9537, also saumäßig. Dein Server hat, wie an den key_buffer Statistiken schon vermutet, zu wenige Indices. Das Slow Query Log wird Dir eine wertvolle Hilfe sein, insbesondere wenn Du auch log-queries-not-using-indexes mit anschaltest.

Created_tmp_tables/Created_tmp_disk_tables = 40160/13394 = 2.9983 ist nicht besonders gut. Nicht nur ist Deine tmp_table_size zu klein, sondern wahrscheinlich sehen wir auch sehr viele Queries mit sehr großen Result-Sets.

Maßnahmen:
1. Slow Query Log mit log-queries-not-using-indices anschalten.
2. Feststellen, wieso die DML-Rate so hoch ist. Das würde auch helfen, die Qcache-Effizienz zu verbessern.
landor caeyran
Posts: 5
Joined: 2006-02-12 13:48
 

Re: MySQL Auslastung

Post by landor caeyran »

bescheidene Frage.

Ich habe diese Dinge für die slow queries nun in die my.cnf eingegeben
und zwar unter der Zeile die das bisherige Log erstelllt

Code: Select all

[save_mysqld]
log-slow-queries=/var/lib/mysql/mysql-slow.log
log-slow-queries-not-using-indexes=/var/lib/mysql/mysql-slow-no-indexes.log
irgendwie passiert da aber nichts, ich sehe keine neuen Logs.
User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin
Contact:
 

Re: MySQL Auslastung

Post by isotopp »

Landor Caeyran wrote:irgendwie passiert da aber nichts, ich sehe keine neuen Logs.

http://dev.mysql.com/doc/refman/5.0/en/ ... y-log.html
Post Reply