Problema Carico Alto e my.cnf

[lordkibop]

Ciao a tutti, ho un problema con il mio server dedicato in cui ci gira il mio sito internet.
Ora monta un processore Quad core Xeon x3360 con 4Gb di Ram.
Ho un database di circa 1'000'000 righe su una decina di tabelle.
Le operazioni piu' effettuate sono gli "UPDATE" e poi i "JOIN".
Oltre i 550 utenti connessi, il server inizia ad aumentare il suo carico, arrivando tranquillamente oltre gli 80 continuativi.
Vi allego una copia del mio "my.cnf", di una schermata "TOP" e di una schermata "SHOW VARIABLE;" in mysql.
Se potete aiutarmi dicendo che ottimizzazioni potrei fare oppure se e' necessario aumentare la poteza dell hardware...

my.cnf :
Nelle impostazioni del cnf, ho sforato il quantitativo di ram fino al 170% cosi da poter utilizzare piu' ram per i temp, NON HO MAI SWAP SU HD IN ONGI CASO:

Spoiler

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_password=1

query_cache_type=1
query_cache_limit=4M
query_cache_size=512M

thread_concurrency=8
thread_cache_size=16M
set-variable = max_connections=220
max_connection=220
read_buffer_size=4M
join_buffer_size=8M
low_priority_updates=1
max_allowed_packet=64M
max_write_lock_count = 4294967295

interactive_timeout=100
wait_timeout=10

sort_buffer_size=8M
table_cache=512M
tmp_table_size=512M
key_buffer_size=384M
read_rnd-buffer_size=16M

myisam_sort_buffer_size=128M

innodb_buffer_pool_size=96M

log_slow_queries=var/log/mysqld.slow.log
long_query_time=3

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=mysqld.pid

[myisamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=4M
write_buffer=4M

[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=4M
write_buffer=4M

Schermata SHOW VARIABLES in mysql:

Spoiler

Variable_name Value auto_increment_increment 1 auto_increment_offset 1 automatic_sp_privileges ON back_log 50 basedir / binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type 0 concurrent_insert 1 connect_timeout 10 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 div_precision_increment 4 keep_files_on_create OFF engine_condition_pushdown OFF 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 YES have_compress YES have_community_features NO have_profiling NO have_crypt YES have_csv YES have_dynamic_loading YES have_example_engine YES have_federated_engine YES have_geometry YES have_innodb YES have_isam NO have_merge_engine YES have_ndbcluster NO have_openssl NO have_ssl NO have_query_cache YES have_raid NO have_rtree_keys YES have_symlink YES hostname server.ddlhits.com 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 100663296 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_adaptive_hash_index ON innodb_doublewrite ON innodb_fast_shutdown 1 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_rollback_on_timeout OFF innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON Variable_name Value innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 innodb_use_legacy_cardinality_algorithm ON interactive_timeout 100 join_buffer_size 8388608 key_buffer_size 402653184 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 language /usr/share/mysql/english/ large_files_support ON large_page_size 0 large_pages OFF lc_time_names en_US license GPL local_infile OFF locked_in_memory OFF log OFF log_bin OFF log_bin_trust_function_creators OFF log_error log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries ON log_warnings 1 long_query_time 3 low_priority_updates ON lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 67108864 max_binlog_cache_size 4294963200 max_binlog_size 1073741824 max_connect_errors 10 max_connections 220 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_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 4294967295 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 2146435072 myisam_mmap_size 4294967295 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 134217728 myisam_stats_method nulls_unequal net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old_passwords ON open_files_limit 65535 optimizer_prune_level 1 optimizer_search_depth 62 pid_file /var/lib/mysql/server.ddlhits.com.pid plugin_dir port 3306 preload_buffer_size 32768 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 4194304 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 4096 read_buffer_size 4194304 read_only OFF read_rnd_buffer_size 16777216 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_space_limit 0 rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 0 skip_external_locking ON skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_load_tmpdir /tmp/ slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slow_launch_time 2 socket /var/lib/mysql/mysql.sock Variable_name Value sort_buffer_size 8388608 sql_big_selects ON sql_mode sql_notes ON sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAM sync_binlog 0 sync_frm ON system_time_zone CEST table_cache 32652 table_lock_wait_timeout 50 table_type MyISAM thread_cache_size 16384 thread_stack 196608 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF tmp_table_size 536870912 tmpdir /tmp/ transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ updatable_views_with_limit YES version 5.0.91-community-log version_comment MySQL Community Edition (GPL) version_compile_machine i686 version_compile_os pc-linux-gnu wait_timeout 10

Scermata TOP con 500 utenti connessi:

Spoiler

top - 17:41:18 up 86 days, 16:29, 2 users, load average: 46.56, 45.57, 44.26
Tasks: 509 total, 15 running, 486 sleeping, 0 stopped, 8 zombie
Cpu(s): 71.3%us, 27.7%sy, 0.0%ni, 0.7%id, 0.1%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 4148468k total, 3222776k used, 925692k free, 224356k buffers
Swap: 2104504k total, 96k used, 2104408k free, 1933180k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17844 mysql 15 0 1401m 245m 3584 D 340.8 6.1 1:42.28 mysqld
18630 admin 15 0 39000 10m 6736 S 1.3 0.3 0:00.04 php
18656 admin 17 0 38976 9m 6640 R 1.3 0.2 0:00.04 php
18621 admin 15 0 39000 10m 6696 S 1.0 0.3 0:00.03 php
18665 admin 15 0 0 0 0 Z 1.0 0.0 0:00.03 php <defunct>
1180 nobody 15 0 10912 3376 1676 S 0.7 0.1 0:00.38 httpd

GRAZIE A TUTTI!

[Antonio]

Dai una occhiata a questo script: Script PHP per l'ottimizzazione di MySQL

Non fa il lavoro al posto tuo, però a grandi linee puoi capire dove sta il problema.

[WizOfOz]

Puoi provare anche questo
Sundry MySQL Scripts and Docs

[cxcs]

Ciao lordkibop puoi aiutarti con mysqtuner e mysqlreport per capire quali valori possono essere migliorati nel tuo my.cnf
Considera comunque che i join sono una brutta bestia.Gioca con la direttiva join_buffer_size per ottenere un incremento di prestazioni per le query di tipo JOIN.

[lordkibop]

Allora, dopo varie prove con mysqltuner, tuning primer ecc sono riuscito a configurare il io my.cnf cosi:

Spoiler

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_password=1

query_cache_type=1
query_cache_limit=32M
query_cache_size=1024M
query_prealloc_size=256M
query_alloc_block_size=32M

thread_concurrency=32
thread_cache_size=1024M
set-variable = max_connections=450
max_connection=500
read_buffer_size=4M
join_buffer_size=1M
low_priority_updates=1
max_allowed_packet=64M
bulk_insert_buffer_size=64M
concurrent_insert=2

interactive_timeout=35
wait_timeout=10
max_connect_errors=10

sort_buffer_size=6M
max_heap_table_size=1024M
table_cache=1024M
tmp_table_size=1024M
key_buffer_size=512M
read_rnd-buffer_size=8M

myisam_sort_buffer_size=512M

innodb_buffer_pool_size=2048M
innodb_thread_concurrency=16

log_slow_queries=/var/log/mysqld.slow.log
long_query_time=10

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=mysqld.pid

[myisamchk]
key_buffer=512M
sort_buffer_size=512M
read_buffer=128M
write_buffer=128M

[isamchk]
key_buffer=512M
sort_buffer_size=512M
read_buffer=128M
write_buffer=128M

Il problema e' che il carico, e' aumentato ancora! Ora passo tranquillamente i 200 di load...

Ricordo che la macchina e' un Doppio Xeon 5620 (HT) quindi 16 core totali, e con 12Gb di Ram.

Se potete aiutarmi, ve ne sarei grato poiche' non so' piu' dove andare a sbattere la testa....

[andrea.paiola]

la macchina è cambiata?

[EvolutionCrazy]

hai analizzato il log delle long_query?

se il database è stato concepito male puoi avere tutto l'hardware che vuoi... ma non ti basterà mai.

molto probabilmente sono i join (sopratutto se DB progettato male) a segare le gambe al server... memcached lo stai usando vero?

hai scritto:
max_connection=220
ma parli di 550 utenti connessi.... sono connessioni al server sql o altro???

tmp_table_size=1024M

se stai davvero lavorando con tabelle temp da 1gb la cosa è veramente tosta (o veramente fatta male)......

posta un po' di "explain" delle query che risultano tra le più lente........ tanto per capire che tipo di carico gli stai buttando addosso...