Benötige dringend Serveroptimierung vom Profi

stefan2k1
Posts: 24
Joined: 2003-09-09 21:01

Benötige dringend Serveroptimierung vom Profi

Post by stefan2k1 »

Hallo,

ich betreibe einen Multiforenhost, der phpBB Foren hostet. Also so ein Dienst wo der User sich selbst ein kostenloses Forum registrieren kann und das auch gleich verfügbar ist.

Das ganze läuft auf einem 1&1 3XXL64 mit 4GB RAM. Ich habe das Problem, dass die Kiste aktuell sehr langsam läuft, sprich teilweise dauert es 20 oder mehr Sekunden bis ein Userforum geladen ist, das ist natürlich kein Zustand und ich möchte das so schnell es geht abstellen. Ich bin mir relativ sicher, dass der Server mit seiner Ausstattung eigentlich dafür ausreichen sollte. Komischerweise lag der Load eine Zeit lang bei +- 1.00 und der Seitenaufbau war superschnell so wie es sein sollte. Wieso der Load jetzt bei 10 oder mehr liegt, kann ich mir wirklich nicht erklären. Es läuft Apache2 als Prefork auf Suse 9.3.

Code: Select all

mysql  Ver 14.7 Distrib 4.1.10a, for suse-linux (x86_64)
Hier mal die Ausgabe vom Status, nachdem die Kiste ein paar Tage gelaufen hat:

Code: Select all

mysql> show status;
+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| Aborted_clients          | 12           |
| Aborted_connects         | 88           |
| Binlog_cache_disk_use    | 0            |
| Binlog_cache_use         | 0            |
| Bytes_received           | 7350313657   |
| Bytes_sent               | 132305804630 |
| Com_admin_commands       | 0            |
| Com_alter_db             | 0            |
| Com_alter_table          | 1            |
| Com_analyze              | 0            |
| Com_backup_table         | 0            |
| Com_begin                | 20415        |
| Com_change_db            | 1952602      |
| Com_change_master        | 0            |
| Com_check                | 0            |
| Com_checksum             | 0            |
| Com_commit               | 20398        |
| Com_create_db            | 0            |
| Com_create_function      | 0            |
| Com_create_index         | 0            |
| Com_create_table         | 0            |
| Com_dealloc_sql          | 0            |
| Com_delete               | 70436        |
| Com_delete_multi         | 0            |
| Com_do                   | 0            |
| Com_drop_db              | 0            |
| Com_drop_function        | 0            |
| Com_drop_index           | 0            |
| Com_drop_table           | 4            |
| 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               | 1104735      |
| Com_insert_select        | 25347        |
| Com_kill                 | 0            |
| Com_load                 | 0            |
| Com_load_master_data     | 0            |
| Com_load_master_table    | 0            |
| Com_lock_tables          | 6            |
| 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              | 6            |
| Com_replace_select       | 1            |
| Com_reset                | 0            |
| Com_restore_table        | 0            |
| Com_revoke               | 0            |
| Com_revoke_all           | 0            |
| Com_rollback             | 7            |
| Com_savepoint            | 0            |
| Com_select               | 6201688      |
| Com_set_option           | 945          |
| Com_show_binlog_events   | 0            |
| Com_show_binlogs         | 0            |
| Com_show_charsets        | 0            |
| Com_show_collations      | 0            |
| Com_show_column_types    | 0            |
| Com_show_create_db       | 6            |
| Com_show_create_table    | 264          |
| Com_show_databases       | 0            |
| Com_show_errors          | 0            |
| Com_show_fields          | 525          |
| Com_show_grants          | 0            |
| Com_show_innodb_status   | 0            |
| Com_show_keys            | 261          |
| Com_show_logs            | 0            |
| Com_show_master_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          | 2            |
| Com_show_storage_engines | 0            |
| Com_show_tables          | 726          |
| Com_show_variables       | 223          |
| Com_show_warnings        | 0            |
| Com_slave_start          | 0            |
| Com_slave_stop           | 0            |
| Com_truncate             | 11           |
| Com_unlock_tables        | 6            |
| Com_update               | 2405887      |
| Com_update_multi         | 0            |
| Connections              | 966996       |
| Created_tmp_disk_tables  | 128057       |
| Created_tmp_files        | 399          |
| Created_tmp_tables       | 556073       |
| Delayed_errors           | 0            |
| Delayed_insert_threads   | 0            |
| Delayed_writes           | 0            |
| Flush_commands           | 1            |
| Handler_commit           | 227          |
| Handler_delete           | 883904       |
| Handler_discover         | 0            |
| Handler_read_first       | 955519       |
| Handler_read_key         | 487202959    |
| Handler_read_next        | 4826060535   |
| Handler_read_prev        | 191128       |
| Handler_read_rnd         | 30269891     |
| Handler_read_rnd_next    | 2741760005   |
| Handler_rollback         | 224          |
| Handler_update           | 139537124    |
| Handler_write            | 259079279    |
| Key_blocks_not_flushed   | 8358         |
| Key_blocks_unused        | 414487       |
| Key_blocks_used          | 49109        |
| Key_read_requests        | 956903378    |
| Key_reads                | 22719320     |
| Key_write_requests       | 9951609      |
| Key_writes               | 3504689      |
| Max_used_connections     | 251          |
| Not_flushed_delayed_rows | 0            |
| Open_files               | 923          |
| Open_streams             | 0            |
| Open_tables              | 512          |
| Opened_tables            | 4678766      |
| Qcache_free_blocks       | 4036         |
| Qcache_free_memory       | 12078848     |
| Qcache_hits              | 8135661      |
| Qcache_inserts           | 6046518      |
| Qcache_lowmem_prunes     | 2008382      |
| Qcache_not_cached        | 154722       |
| Qcache_queries_in_cache  | 7645         |
| Qcache_total_blocks      | 24398        |
| Questions                | 21002828     |
| Rpl_status               | NULL         |
| Select_full_join         | 134313       |
| Select_full_range_join   | 1            |
| Select_range             | 435204       |
| Select_range_check       | 0            |
| Select_scan              | 1991748      |
| Slave_open_temp_tables   | 0            |
| Slave_running            | OFF          |
| Slow_launch_threads      | 31           |
| Slow_queries             | 168553       |
| Sort_merge_passes        | 0            |
| Sort_range               | 674314       |
| Sort_rows                | 4283603999   |
| Sort_scan                | 778104       |
| Table_locks_immediate    | 15656768     |
| Table_locks_waited       | 95781        |
| Threads_cached           | 8            |
| Threads_connected        | 211          |
| Threads_created          | 145944       |
| Threads_running          | 208          |
| Uptime                   | 123434       |
+--------------------------+--------------+
156 rows in set (0.00 sec)
Die my.cnf sieht so aus:

Code: Select all

[client]
port                  = 3306
socket                = /var/lib/mysql/mysql.sock

[mysql]
no-auto-rehash

[mysqldump]
max_allowed_packet    = 16M
quick

[isamchk]
character-sets-dir    = /usr/share/mysql/charsets
key_buffer_size       = 256M
sort_buffer_size      = 256M
read_buffer_size      = 4M
write_buffer_size     = 4M

[myisamchk]
character-sets-dir    = /usr/share/mysql/charsets
key_buffer_size       = 256M
sort_buffer_size      = 256M
read_buffer_size      = 4M
write_buffer_size     = 4M

[myisampack]
character-sets-dir    = /usr/share/mysql/charsets

[mysqld_safe]
err-log               = /var/log/mysql/mysql.err
open-files-limit      = 4096

[mysqld]
character-sets-dir    = /usr/share/mysql/charsets
user                  = mysql
port                  = 3306
bind-address          = 127.0.0.1
socket                = /var/lib/mysql/mysql.sock
pid-file              = /var/lib/mysql/mysql.pid
log-error             = /var/log/mysql/mysql.err
basedir               = /usr
datadir               = /var/lib/mysql
tmpdir                = /tmp
language              = /usr/share/mysql/english
#log-bin               = mysql-bin
max_connections       = 250
server-id             = 1
sync_binlog           = 1
binlog_cache_size     = 1M
max_binlog_size       = 10M
slave_compressed_protocol = 1
delay-key-write       = ALL
skip-locking
skip-networking
skip-name-resolve
skip-external-locking
skip-show-database
safe-user-create      = 1
default_table_type    = MYISAM
transaction_isolation = REPEATABLE-READ
max_allowed_packet    = 16M
max_heap_table_size   = 64M
tmp_table_size        = 64M
key_buffer_size       = 512M
sort_buffer_size      = 8M
join_buffer_size      = 8M
read_buffer_size      = 8M
read_rnd_buffer_size  = 16M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_max_extra_sort_file_size = 1G
myisam_repair_threads = 1
thread_cache_size     = 8
table_cache           = 512
query_cache_type      = 1
query_cache_size      = 32M
query_cache_limit     = 2M
thread_concurrency    = 8
local-infile          = 0
long_query_time       = 3
#log_slow_queries
log_long_format
myisam_recover
#skip-innodb
#innodb_thread_concurrency = 8
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
#innodb_data_home_dir = /var/lib/mysql
#innodb_log_arch_dir = /var/lib/mysql
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_log_files_in_group = 2
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 60

[mysqlhotcopy]
interactive-timeout 
Ich kenne mich mit Mysql Tuning leider nicht so gut aus, habe schon einiges versucht, auch mit Konfigurationen aus diesem Forum, das hatte bisher leider keinen dauerhaften Erfolg.

Falls sich mal ein Profi damit auseinandersetzen könnte wäre ich wirklich sehr dankbar,

EDIT
chris76 solche Dinge sind hier nicht gerne gesehen.!

Gruss
Stefan
Top

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: Benötige dringend Serveroptimierung vom Profi

Post by isotopp »

stefan2k1 wrote:| Select_full_join | 134313 |
| Uptime | 123434 |
Du und Dein Slow Query Log, Ihr zwei solltet Euch mal kennen lernen.
| Key_read_requests | 956903378 |
| Key_reads | 22719320 |
Und Dein Key Buffer schreit auch nach Hilfe.
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
Dir ist schon klar, daß das per-thread Buffer sind und die Werte im worst-case mal max_connections gehen?

MySQL lernen, dann beweisen, daß man was verstanden hat.
Top