Problem Z Serwerem Mysql

[Kamil01 0]

Witam wszystkich serdecznie, od przeszlo miesiąca może dłużej mam problem z serwerem mysql, a mianowicie jego obciązeniem.... Codziennie bynajmiej raz serwer mysql wywala na kazdym moim serwisie max users connections(parametr ustawiony dużo na zapas) wywali taki bład i serwer zaczyna jechac po swapie.... ramu mam dośc dużo wolnego więc nie wiem w czym może być problem... Bardzo proszę o pomoc, będe wdzięczny. Jak coś potrzebne to pisac

 

 

 

 

 

Pozdrawiam

muchakk

[r.s 0]

Optymalizacja... http://forum.ovh.pl/showthread.php?t=614 - przeczytaj sobie conieco :-) Może również xcache?

[Kamil01 0]

mam zainstalowany eaccelerator mysql-zoptymalizowany, bynajmiej tak mi się wydaje...

[beliq 442]

mysql-zoptymalizowany, bynajmiej tak mi się wydaje...

mysql -u root -p --batch -e "show variables"

 

?

[Kamil01 0]

Proszę bardzo:

 

host6:~# mysql -u root -p --batch -e "show variables"

Enter password:

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 latin2

character_set_results latin1

character_set_server latin2

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

collation_connection latin1_swedish_ci

collation_database latin2_general_ci

collation_server latin2_general_ci

concurrent_insert ON

connect_timeout 5

datadir /hallo/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_ndbcluster DISABLED

have_openssl NO

have_query_cache YES

have_raid YES

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 131072

key_buffer_size 16777216

key_cache_age_threshold 300

key_cache_block_size 1024

key_cache_division_limit 100

language /usr/share/mysql/english/

large_files_support ON

license GPL

local_infile ON

locked_in_memory OFF

log OFF

log_bin OFF

log_error

log_slave_updates OFF

log_slow_queries OFF

log_update OFF

log_warnings 1

long_query_time 10

low_priority_updates OFF

lower_case_file_system OFF

lower_case_table_names 0

max_allowed_packet 1047552

max_binlog_cache_size 18446744073709551615

max_binlog_size 104857600

max_connect_errors 9999

max_connections 400

max_delayed_threads 20

max_error_count 64

max_heap_table_size 16777216

max_insert_delayed_threads 20

max_join_size 18446744073709551615

max_length_for_sort_data 1024

max_relay_log_size 0

max_seeks_for_key 18446744073709551615

max_sort_length 1024

max_tmp_tables 32

max_user_connections 20

max_write_lock_count 18446744073709551615

myisam_data_pointer_size 4

myisam_max_extra_sort_file_size 2147483648

myisam_max_sort_file_size 9223372036854775807

myisam_recover_options OFF

myisam_repair_threads 1

myisam_sort_buffer_size 8388608

ndb_autoincrement_prefetch_sz 32

ndb_force_send ON

ndb_use_exact_count ON

ndb_use_transactions ON

net_buffer_length 16384

net_read_timeout 30

net_retry_count 10

net_write_timeout 60

new OFF

old_passwords ON

open_files_limit 2010

pid_file /var/run/mysqld/mysqld.pid

port 3306

preload_buffer_size 32768

protocol_version 10

query_alloc_block_size 8192

query_cache_limit 1048576

query_cache_min_res_unit 4096

query_cache_size 16777216

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 262144

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 2097144

sql_mode

storage_engine MyISAM

sql_notes ON

sql_warnings ON

sync_binlog 0

sync_replication 0

sync_replication_slave_id 0

sync_replication_timeout 0

sync_frm ON

system_time_zone CEST

table_cache 64

table_type MyISAM

thread_cache_size 0

thread_stack 131072

time_format %H:%i:%s

time_zone SYSTEM

tmp_table_size 33554432

tmpdir /tmp

transaction_alloc_block_size 8192

transaction_prealloc_size 4096

tx_isolation REPEATABLE-READ

version 4.1.11-Debian_4sarge7

version_comment Source distribution

version_compile_machine x86_64

version_compile_os pc-linux-gnu

wait_timeout 28800

host6:~#

[beliq 442]

1. Niezły mix of collations

2. Max user connections na 20? ( Masz tam same strony firmowe? )

3. 64 dla table cache? ( z pewnością używasz większej ilości tabel w jednej jednostce czasu )

4. Masz dużo wolnego RAMu, więc dlaczego tak mało pamięci przeznaczyłeś na cache kluczy i selectow?

5. open_files_limit na 2010? ( dziwna wartość jak na paczkę Debiana )

6. Loguj slow queries, to nie boli, a pomaga.

n. Coś się jeszcze na pewno znajdzie. Googluj dalej...

[Kamil01 0]

sow queries logowalem-wylaczylem....

max users connections mam na 40

 

jak zwiekszyc te rzeczy co podalwales? w configu mysql-a? nie znam sie dobrze na serwerze mysql...

[r.s 0]

my.cnf albo via set variable w konsoli mysqla.

[zrio 6]

To normalnie rządzi :

 

character_set_results latin1
character_set_server latin2
character_set_system utf8

[Kamil01 0]

w my.cnf to zahaszowalem a nie wiem dlaczego tutaj pokazuje...

[r.s 0]

Ponieważ jest to default :-)