MySQL Auslastung

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

MySQL Auslastung

Post by landor caeyran » 2006-02-12 20:53

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

Re: MySQL Auslastung

Post by isotopp » 2006-02-14 09:02

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

Re: MySQL Auslastung

Post by [gca].dfritz » 2006-02-14 16:17

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

Re: MySQL Auslastung

Post by isotopp » 2006-02-14 17:12

[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 » 2006-02-14 18:34

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]SHOW STATUS LIKE 'uptime' [/code]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 » 2006-02-14 18:36

    [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 » 2006-02-14 19:12

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 » 2006-02-14 19:31

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 » 2006-02-14 20:04

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

Re: MySQL Auslastung

Post by isotopp » 2006-02-16 09:32

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 » 2006-02-16 22:49

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

Re: MySQL Auslastung

Post by isotopp » 2006-02-17 08:16

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