Otimizacao de banco de dados

[llsanches]

ola pesssoal, o servidor esta muito lento.

utilizo servidor dedicado.

intel Pentium dual core 2.2 ghz / 2gb ram/ hd 160gb / 2mbps

empresa homehost

o site está lento a maior parte do lempo....

veja detalhes abaixo

SHOW STATUS

Textos completos

Variable_name Value

Aborted_clients 73

Aborted_connects 188

Binlog_cache_disk_use 0

Binlog_cache_use 0

Bytes_received 633

Bytes_sent 8302

Com_admin_commands 0

Com_alter_db 0

Com_alter_table 0

Com_analyze 0

Com_backup_table 0

Com_begin 0

Com_call_procedure 0

Com_change_db 4

Com_change_master 0

Com_check 0

Com_checksum 0

Com_commit 0

Com_create_db 0

Com_create_function 0

Com_create_index 0

Com_create_table 0

Com_create_user 0

Com_dealloc_sql 0

Com_delete 0

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 0

Com_insert_select 0

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 0

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 2

Com_set_option 4

Com_show_binlog_events 0

Com_show_binlogs 0

Com_show_charsets 1

Com_show_collations 1

Com_show_column_types 0

Com_show_create_db 0

Com_show_create_table 0

Com_show_databases 1

Com_show_errors 0

Com_show_fields 0

Com_show_grants 1

Com_show_innodb_status 0

Com_show_keys 0

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 1

Com_show_storage_engines 0

Com_show_tables 0

Com_show_triggers 0

Com_show_variables 3

Com_show_warnings 0

Com_slave_start 0

Com_slave_stop 0

Com_stmt_close 0

Com_stmt_execute 0

Com_stmt_fetch 0

Com_stmt_prepare 0

Com_stmt_reset 0

Com_stmt_send_long_data 0

Com_truncate 0

Variable_name Value

Com_unlock_tables 0

Com_update 0

Com_update_multi 0

Com_xa_commit 0

Com_xa_end 0

Com_xa_prepare 0

Com_xa_recover 0

Com_xa_rollback 0

Com_xa_start 0

Compression OFF

Connections 254349

Created_tmp_disk_tables 0

Created_tmp_files 17

Created_tmp_tables 7

Delayed_errors 0

Delayed_insert_threads 0

Delayed_writes 0

Flush_commands 1

Handler_commit 0

Handler_delete 0

Handler_discover 0

Handler_prepare 0

Handler_read_first 0

Handler_read_key 0

Handler_read_next 0

Handler_read_prev 0

Handler_read_rnd 0

Handler_read_rnd_next 176

Handler_rollback 0

Handler_savepoint 0

Handler_savepoint_rollback 0

Handler_update 0

Handler_write 302

Innodb_buffer_pool_pages_data 61

Innodb_buffer_pool_pages_dirty 0

Innodb_buffer_pool_pages_flushed 1963

Innodb_buffer_pool_pages_free 450

Innodb_buffer_pool_pages_latched 0

Innodb_buffer_pool_pages_misc 1

Innodb_buffer_pool_pages_total 512

Innodb_buffer_pool_read_ahead_rnd 3

Innodb_buffer_pool_read_ahead_seq 0

Innodb_buffer_pool_read_requests 25071621

Innodb_buffer_pool_reads 36

Innodb_buffer_pool_wait_free 0

Innodb_buffer_pool_write_requests 2055

Innodb_data_fsyncs 7574

Innodb_data_pending_fsyncs 0

Innodb_data_pending_reads 0

Innodb_data_pending_writes 0

Innodb_data_read 3182592

Innodb_data_reads 58

Innodb_data_writes 7642

Innodb_data_written 66312192

Innodb_dblwr_pages_written 1963

Innodb_dblwr_writes 1889

Innodb_log_waits 0

Innodb_log_write_requests 116

Innodb_log_writes 1901

Innodb_os_log_fsyncs 3790

Innodb_os_log_pending_fsyncs 0

Innodb_os_log_pending_writes 0

Innodb_os_log_written 1021440

Innodb_page_size 16384

Innodb_pages_created 0

Innodb_pages_read 61

Innodb_pages_written 1963

Innodb_row_lock_current_waits 0

Innodb_row_lock_time 0

Innodb_row_lock_time_avg 0

Innodb_row_lock_time_max 0

Innodb_row_lock_waits 0

Innodb_rows_deleted 0

Innodb_rows_inserted 1

Innodb_rows_read 38297763

Innodb_rows_updated 34

Key_blocks_not_flushed 0

Key_blocks_unused 6664

Key_blocks_used 987

Key_read_requests 17419510

Key_reads 278714

Key_write_requests 44369

Key_writes 43371

Last_query_cost 10.499000

Max_used_connections 501

Ndb_cluster_node_id 0

Ndb_config_from_host

Ndb_config_from_port 0

Ndb_number_of_data_nodes 0

Not_flushed_delayed_rows 0

Open_files 72

Open_streams 0

Open_tables 64

Opened_tables 0

Prepared_stmt_count 0

Qcache_free_blocks 0

Qcache_free_memory 0

Qcache_hits 0

Qcache_inserts 0

Qcache_lowmem_prunes 0

Variable_name Value

Qcache_not_cached 0

Qcache_queries_in_cache 0

Qcache_total_blocks 0

Questions 2734144

Rpl_status NULL

Select_full_join 0

Select_full_range_join 0

Select_range 0

Select_range_check 0

Select_scan 7

Slave_open_temp_tables 0

Slave_retried_transactions 0

Slave_running OFF

Slow_launch_threads 266

Slow_queries 0

Sort_merge_passes 0

Sort_range 0

Sort_rows 0

Sort_scan 0

Table_locks_immediate 3308751

Table_locks_waited 76381

Tc_log_max_pages_used 0

Tc_log_page_size 0

Tc_log_page_waits 0

Threads_cached 0

Threads_connected 95

Threads_created 254348

Threads_running 1

Uptime 352662

Uptime_since_flush_status 352662

variaveis

Textos completos

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 utf8

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_unicode_ci

collation_database utf8_unicode_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_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 DISABLED

have_openssl NO

have_ssl NO

have_query_cache YES

have_raid NO

have_rtree_keys YES

have_symlink YES

hostname server.facildownloads.com.br

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_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

innodb_thread_concurrency 8

innodb_thread_sleep_delay 10000

Variable_name Value

interactive_timeout 28800

join_buffer_size 131072

key_buffer_size 8384512

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 ON

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 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 1048576

max_binlog_cache_size 4294963200

max_binlog_size 1073741824

max_connect_errors 10

max_connections 500

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_recover_options OFF

myisam_repair_threads 1

myisam_sort_buffer_size 8388608

myisam_stats_method nulls_unequal

ndb_autoincrement_prefetch_sz 1

ndb_force_send ON

ndb_use_exact_count ON

ndb_use_transactions ON

ndb_cache_check_time 0

ndb_connectstring

net_buffer_length 16384

net_read_timeout 30

net_retry_count 10

net_write_timeout 60

new OFF

old_passwords OFF

open_files_limit 2500

optimizer_prune_level 1

optimizer_search_depth 62

pid_file /var/lib/mysql/server.facildownloads.com.br.pid

plugin_dir

port 3306

preload_buffer_size 32768

profiling OFF

profiling_history_size 15

protocol_version 10

query_alloc_block_size 8192

query_cache_limit 1048576

query_cache_min_res_unit 4096

query_cache_size 0

query_cache_type ON

query_cache_wlock_invalidate OFF

query_prealloc_size 8192

range_alloc_block_size 4096

read_buffer_size 131072

read_only OFF

read_rnd_buffer_size 262144

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

Variable_name Value

slave_skip_errors OFF

slave_transaction_retries 10

slow_launch_time 2

socket /var/lib/mysql/mysql.sock

sort_buffer_size 2097144

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 BRT

table_cache 64

table_lock_wait_timeout 50

table_type MyISAM

thread_cache_size 0

thread_stack 196608

time_format %H:%i:%s

time_zone SYSTEM

timed_mutexes OFF

tmp_table_size 33554432

tmpdir /tmp/

transaction_alloc_block_size 8192

transaction_prealloc_size 4096

tx_isolation REPEATABLE-READ

updatable_views_with_limit YES

version 5.0.67-community

version_comment MySQL Community Edition (GPL)

version_compile_machine i686

version_compile_os redhat-linux-gnu

wait_timeout 28800

[Denis Courcy]

Oi, 'llsanches'!

Verifique, em primeiro lugar, como você organizou os índices e suas consultas.

90% dos problemas de lentidão serão resolvidos analisando estes dois elementos.

Quanto aos dados enviados, analisarei amanhã. Não há tempo hábil para fazê-lo hoje.

[llsanches]

que legal, 90% e muita coisa. seria de muita ajuda mesmo... mas como sou meio leigo no assunto.. como posso te demonstrar como os indices foram organizaods juntamente com a configuracao da consulta?

[llsanches]

Versão do Apache 2.2.11 (Unix)

Versão do PHP 5.2.8

Versão do MySQL 5.0.67-community

Arquitetura i686

Sistema Operacional Linux

Endereço de IP Compartida 200.169.232.147

path para o sendmail /usr/sbin/sendmail

Path para PERL /usr/bin/perl

Versão do Kernel 2.6.18-92.el5

[llsanches]

verificando o valor da variável max_conections

usando o commando show variables like "max%"

Variable_name Value

max_allowed_packet 1048576

max_binlog_cache_size 4294963200

max_binlog_size 1073741824

max_connect_errors 10

max_connections 500

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

será que teremos que alterar por exemplo o max_connections?

[llsanches]

todas as variaveis..... ou que será que devemos alterar para o site receber alta demanda de acessos?

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 utf8

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_unicode_ci

collation_database utf8_unicode_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_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 DISABLED

have_openssl NO

have_ssl NO

have_query_cache YES

have_raid NO

have_rtree_keys YES

have_symlink YES

hostname server.facildownloads.com.br

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_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

innodb_thread_concurrency 8

innodb_thread_sleep_delay 10000

Variable_name Value

interactive_timeout 28800

join_buffer_size 131072

key_buffer_size 8384512

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 ON

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 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 1048576

max_binlog_cache_size 4294963200

max_binlog_size 1073741824

max_connect_errors 10

max_connections 500

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_recover_options OFF

myisam_repair_threads 1

myisam_sort_buffer_size 8388608

myisam_stats_method nulls_unequal

ndb_autoincrement_prefetch_sz 1

ndb_force_send ON

ndb_use_exact_count ON

ndb_use_transactions ON

ndb_cache_check_time 0

ndb_connectstring

net_buffer_length 16384

net_read_timeout 30

net_retry_count 10

net_write_timeout 60

new OFF

old_passwords OFF

open_files_limit 2500

optimizer_prune_level 1

optimizer_search_depth 62

pid_file /var/lib/mysql/server.facildownloads.com.br.pid

plugin_dir

port 3306

preload_buffer_size 32768

profiling OFF

profiling_history_size 15

protocol_version 10

query_alloc_block_size 8192

query_cache_limit 1048576

query_cache_min_res_unit 4096

query_cache_size 0

query_cache_type ON

query_cache_wlock_invalidate OFF

query_prealloc_size 8192

range_alloc_block_size 4096

read_buffer_size 131072

read_only OFF

read_rnd_buffer_size 262144

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

Variable_name Value

slave_skip_errors OFF

slave_transaction_retries 10

slow_launch_time 2

socket /var/lib/mysql/mysql.sock

sort_buffer_size 2097144

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 BRT

table_cache 64

table_lock_wait_timeout 50

table_type MyISAM

thread_cache_size 0

thread_stack 196608

time_format %H:%i:%s

time_zone SYSTEM

timed_mutexes OFF

tmp_table_size 33554432

tmpdir /tmp/

transaction_alloc_block_size 8192

transaction_prealloc_size 4096

tx_isolation REPEATABLE-READ

updatable_views_with_limit YES

version 5.0.67-community

version_comment MySQL Community Edition (GPL)

version_compile_machine i686

version_compile_os redhat-linux-gnu

wait_timeout 28800

[Denis Courcy]

Já achei alguns erros na configuração de seu servidor.

As variáveis abaixo devem ser regonfiguradas no arquivo my.cnf.

table_cache está com valor de 64 e deve passar para 1000

key_buffer está com valor de 8MB deve passar para 64 MB

join_buffer_size deve passar de 131072 para 1MB

Wait_timeout está com o valor 28800. está muito alto. Muitas conexões estão em sleep. passar para 15.

Você vai ter que verificar pois, talvez, haverá a necessidade de alterar max_conections novamente por causa desta última variável. Se alterar max_conections deverá alterar table_cache, também.