RootForum Community

Hallo,
ich habe hier einen Datenbankserver der folgendermassen bestückt ist:

4x P3 800 Mhz mit jeweils 2 MB Cache.
4 GB SD-RAM 100 Mhz
4x 18 GB SCSI RAID5

Problem ist das der Load letzte zeit gestiegen ist, es ist ja auch nicht wenig los auf dem Server, aber trotzdem bin ich der Meinung man könnte noch das letzte aus dem System herausholen. Welche einstellungen würdet ihr noch verbessern bzw. ändern oder habe ich vieleicht was übersehen?

mysql> SHOW VARIABLES;
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_german1_ci |
| collation_server | latin1_german1_ci |
| concurrent_insert | OFF |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | YES |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 520192 |
| key_buffer_size | 268435456 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |

| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 2 |
| low_priority_updates | ON |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 33553408 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 104857600 |
| max_connect_errors | 10 |
| max_connections | 600 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16776192 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 262144 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 65535 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 67108864 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 536870912 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 126976 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 262136 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | CET |
| table_cache | 30000 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.24-20070902-Debian_0.aixit.2-log |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+--------------------------------------+

Hier noch ein Auszug aus dem top:

top - 15:46:52 up 3 days, 4:03, 1 user, load average: 2.04, 2.18, 2.09
Tasks: 85 total, 1 running, 84 sleeping, 0 stopped, 0 zombie
Cpu0 : 55.1% us, 13.2% sy, 0.0% ni, 30.4% id, 0.3% wa, 0.3% hi, 0.7% si
Cpu1 : 31.9% us, 3.7% sy, 0.0% ni, 64.5% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu2 : 43.7% us, 5.6% sy, 0.0% ni, 50.0% id, 0.3% wa, 0.0% hi, 0.3% si
Cpu3 : 30.2% us, 3.7% sy, 0.0% ni, 66.1% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 3634164k total, 3510860k used, 123304k free, 49788k buffers
Swap: 1052248k total, 4k used, 1052244k free, 2270916k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23456 mysql 15 0 1225m 1.0g 5244 S 99.9 29.2 685:03.17 mysqld

Ich würde auf einen aktuellen (>=5.0.44) nicht-Debian (vgl. http://www.rootforum.de/forum/viewtopic ... 232#240232) MySQLd wechseeln (MySQL-Handbuch beachten!) und die my.cnf auf Basis von http://www.rootforum.org/forum/viewtopic.php?t=36343 anpassen. Das Ganze bedarf allerdings einer sorgfältigen Vorbereitung (Testsystem) und je nach notwendiger Konvertierung des Datenbestandes (latin1->utf8) wenige Minuten bis einige Stunden Downtime. Auch die Client-Applikationen müssen gegebenenfalls auf UTF-8 umgestellt werden. Dieser Aufwand sollte mit mindestens 10% mehr Performance belohnt werden und künftige Upgrades (MySQL 5.1/6.0) deutlich vereinfachen.