(Resolvido) Acesso ao mysql muito lento

[Guest --Pedro --]

Olá a todos,

Estou mesmo a precisar de ajuda. Não sei nada sobre como configurar o Mysql. Recentemente trasnferi o meu site de um servidor partilhado para um servidor dedicado. Mas o resultao foi muito mau. O site está muito mais lento que no partilhado. Os usuário do site simplesmente não conseguem navegar no site, ele demora muitos minutos. Acho que o problema é das configurações do mysql. O que fazer

Estas são as especificações do servidor:

CPU AuthenticAMD, AMD Athlon XP 2600+

psa v8.0.1_build80060713.16 os_FedoraCore 4

SO Linux 2.6.17-1.2142_FC4smp

ram: 471.21 MB

free ram: 18Mb

buffer: 3.40Mb

Cached: 221Mb

A base de dados é de 200Mb

Terei que colocar alguns parâmetro no my.cnf? Quais? Ele está assim:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with

mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

[mysql.server]

user=mysql

basedir=/var/lib

Obrigado,

Pedro

[Denis Courcy]

Oi, Pedro!

Elenquei alguns fatores para iniciarmos nossa pesquisa.

1 - Sua RAM está com 512Mb e compartilhando?. Experimente aumentar. Para o MySQL quanto mais memória melhor.

2 - Quantos acessos você tem por dia?

3 - Que tipo de engine você está usando? MyISAM, InnoDB, ...?

4 - Você indexou corretamente suas consultas? Há indices nas ligações de JOIN e nos campos de cláusula WHERE?

5 - Você poderia me passar o arquivo my.cnf para que eu possa estudá-lo?

Gostaria de ver os valores dos seguintes parâmetros:

max_connections

wait_timeout

thread_cache_size

table_cache

key_buffer_size

query_cache_size

tmp_table_size

Open_tables

Opened_tables

Key_read_requests

Key_reads

Threads_created

Handler_read_rnd

Handler_read_rnd_next

//Estes aqui abaixo são somente se você usa muitas tabelas em memória

tmp_table_size

Created_tmp_disk_tables

Created_tmp_tables

Created_tmp_files

Você pode usar o comando show variables para me informar o valor destes parâmetros.

[Guest --Pedro --]

Olá Denis,

Eu agradeço mesmo a ajuda, pois já estou a direcionar os DNS para o servidor partilhado já que ninguém consegue operar no dedicado.

Eu sou totalmente novato nestas coisas. Abri conta num servidor dedicado, porque o partilhado não dá mais conta. No partilhado eu tenho um limite 50 acessos. Geralmente tenho uns 1000 user únicos ao site e cerca de 500.000 pages view por mês. A base de dados é de 200Mb.

1 - Sua RAM está com 512Mb e compartilhando?. Experimente aumentar. Para o MySQL quanto mais memória melhor.

O pack que comprei vem apenas com 512Mb, mas a maior parte dela está ocupada. Tenho pouco de sobra.

2 - Quantos acessos você tem por dia?

Cerca de 1000 acessos por dia e umas 15000 páginas vistas.

3 - Que tipo de engine você está usando? MyISAM, InnoDB, ...?

Aqui você me pegou... não sei? Onde poderia verificar isso?

4 - Você indexou corretamente suas consultas? Há indices nas ligações de JOIN e nos campos de cláusula WHERE?

O site dentro dos limites de 50 acessos permitidos tem funcionado bem no servidor partilhado. Aqui no dedicado nem aguenta 15 ligações.

5 - Você poderia me passar o arquivo my.cnf para que eu possa estudá-lo?

Pois, o arquivo /etc/my.cnf é aquele que eu coloquei no primeiro post. Não tem nada desses parametro que você refere. Assim me foi passado para as mãos o servidor e agora não sei o que fazer com ele.

Se tiver que editar o my-cnf tenho que parar primeiro o mysql, não é? Como fazê-lo? E quais os parametro mais adquados para as carecteristicas do servidor?

Obrigado Denis,

Pedro

[Guest --Pedro --]

Denis,

aqui vai o resultado do show variables:

|

| join_buffer_size | 131072 |

| key_buffer_size | 8388600 |

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

| max_binlog_cache_size | 4294967295 |

| max_binlog_size | 1073741824 |

| max_connect_errors | 10 |

| max_connections | 100 |

| max_delayed_threads | 20 |

| max_error_count | 64 |

| max_heap_table_size | 16777216 |

| max_insert_delayed_threads | 20 |

| max_join_size | 4294967295 |

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

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

| pid_file | /var/run/mysqld/mysqld.pid |

| port | 3306 |

| preload_buffer_size | 32768 |

| prepared_stmt_count | 0 |

| 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 | 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/lib/mysql/mysql.sock |

| sort_buffer_size | 2097144 |

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

| table_cache | 64 |

| table_type | MyISAM |

| thread_cache_size | 0 |

| thread_stack | 196608 |

| time_format | %H:%i:%s |

| time_zone | SYSTEM |

| tmp_table_size | 33554432 |

| tmpdir | |

| transaction_alloc_block_size | 8192 |

| transaction_prealloc_size | 4096 |

| tx_isolation | REPEATABLE-READ |

| version | 4.1.20 |

| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |

| version_comment | Source distribution |

| version_compile_machine | i386 |

| version_compile_os | redhat-linux-gnu |

| wait_timeout | 28800 |

+---------------------------------+--------------------------------------------------------+

195 rows in set (0.00 sec)

Obrigado,

Pedro

[Denis Courcy]

1 - Sua RAM está com 512Mb e compartilhando?. Experimente aumentar. Para o MySQL quanto mais memória melhor.

O pack que comprei vem apenas com 512Mb, mas a maior parte dela está ocupada. Tenho pouco de sobra.

Este pode ser o seu grande problema. Pois a velocidade de acesso está diretamente ligado a processador e disponibilidade de memória RAM

3 - Que tipo de engine você está usando? MyISAM, InnoDB, ...?

Aqui você me pegou... não sei? Onde poderia verificar isso?

As tabelas do Mysql trabalham normalmente em dois tipos de engine. Um rápido, porém não permite o controle de constraints (integridade referencial) MyISAM. Outro, um pouco mais lento, e dependendo fica muito lento, que permite o controle de integridade InnoDB. Há outros menos usados e que também influenciam na velocidade de acesso dos dados no servidor.

4 - Você indexou corretamente suas consultas? Há indices nas ligações de JOIN e nos campos de cláusula WHERE?

O site dentro dos limites de 50 acessos permitidos tem funcionado bem no servidor partilhado. Aqui no dedicado nem aguenta 15 ligações.

Não foi isso que perguntei. Tabelas sem indices operam por table scan e demoram muito a retornar os valores desejados. Principalmente se a concorrencia de memória for grande e houver muitos GROUP BY, ORDER BY e JOINS.

5 - Você poderia me passar o arquivo my.cnf para que eu possa estudá-lo?

Pois, o arquivo /etc/my.cnf é aquele que eu coloquei no primeiro post. Não tem nada desses parametro que você refere. Assim me foi passado para as mãos o servidor e agora não sei o que fazer com ele.

O que eu preciso são as variáveis que te passei. use o utilitário de linha de comando do mysql chamado mysql.

digite

shell> mysql -r root -p

o mysql vai pedir para você colocar a senha de acesso.

se tudo ocorreu dentro do esperado um prompt como abaixo vai aparecer:

mysql >

digite

mysql >show variables;

uma lista com as variaveis e seu conteudo vais aparecer no monitor.

não lembro se você pode redirecionar a saida para um arquivo. tente.

para encerrar o mysql digite

mysql >quit;

Envie-me o arquivo dcourcy@gmail.com

Se tiver que editar o my-cnf tenho que parar primeiro o mysql, não é? Como fazê-lo? E quais os parametro mais adquados para as carecteristicas do servidor?

1 não pare o servidor. você pode copiar o arquivo. ele é usado somente na carga do servidor. Ou seja, quando este inicializa o serviço.

2 não vou ensiná-lo agora a desativar o servidor. mais tarde, se houver necessidade, sim.

3 Isto é o que eu quero medir. as variáveis que passei funcionam como parâmetros e nos auxiliarão na configuração do servidor MySQL.

Estou encerrando às 17:00 (GMT -3) amanhã continuamos.

[Guest --Pedro --]

Obrigado Denis,

Já tinha colocado num posto anterior essas variaveis... estou enviando também para o seu email.

Até amanhã,

Pedro

[Denis Courcy]

Oi, Pedro!

1. Variáveis faltando impossibilitaram uma melhor análise da performance.

Quando você me informou o valor das variáveis você o fez completo, ou seja, não esqueceu de nenhuma?

O servidor estava sendo acessado por outras pessoas no momento em que você comandou o show variables?

A pergunta se dá em razão de algumas variáveis importantes não terem aparecido. Tais como:

CREATED_TMP_DISK_TABLES

CREATED_TMP_TABLES

CREATED_TMP_FILES

HANDLER_READ_RND

HANDLER_READ_RND_NEXT

KEY_READS

KEY_RADREQUEST

OPEN_TABLES

OPENED_TABLES

2. A variável WAIT_TIMEOUT está com um valor muito alto. 28800 (segundos) no seu caso. Esta variável é a responsável por informar o tempo que os processos podem ficar em sleeping. Um tempo grande nesta variável mantém clientes segurando conexões e bloqueando o acesso de outros que queiram conectar para realizar realmente seus trabalhos. Um tempo bom para esta variável é de 15 (segundos).

Ao reduzir o valor de WAIT_TIMEOUT você deverá aumentar o valor da variável MAX_CONNECTIONS de 100 (seu caso) para 300.

Deve aumentar, também, o valor de THREAD_CACHE_SIZE (no seu caso está 0) para 5 (porque você tem pouca memória). O ideal é THREAD_CACHE_SIZE = MAX_CONNECTIONS + 1. THREAD_CACHE_SIZE é responsável por manter em memória as trheads para reutilização.

Por favor me envie os valores das variáveis acima citadas para que eu possa completar a análise.

[Guest --Pedro --]

Olá Denis,

Obrigado pela resposta.

Nste monento o servidor dedicado está sem ninguém acessando, já que eu trasnferi os DNS do domínio de volta para o partilhado. Fiz novamente o show variables e aqui vai:

| join_buffer_size | 131072 |

| key_buffer_size | 8388600 |

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

| max_binlog_cache_size | 4294967295 |

| max_binlog_size | 1073741824 |

| max_connect_errors | 10 |

| max_connections | 100 |

| max_delayed_threads | 20 |

| max_error_count | 64 |

| max_heap_table_size | 16777216 |

| max_insert_delayed_threads | 20 |

| max_join_size | 4294967295 |

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

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

| pid_file | /var/run/mysqld/mysqld.pid |

| port | 3306 |

| preload_buffer_size | 32768 |

| prepared_stmt_count | 0 |

| 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 | 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/lib/mysql/mysql.sock |

| sort_buffer_size | 2097144 |

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

| table_cache | 64 |

| table_type | MyISAM |

| thread_cache_size | 0 |

| thread_stack | 196608 |

| time_format | %H:%i:%s |

| time_zone | SYSTEM |

| tmp_table_size | 33554432 |

| tmpdir | |

| transaction_alloc_block_size | 8192 |

| transaction_prealloc_size | 4096 |

| tx_isolation | REPEATABLE-READ |

| version | 4.1.20 |

| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |

| version_comment | Source distribution |

| version_compile_machine | i386 |

| version_compile_os | redhat-linux-gnu |

| wait_timeout | 28800 |

+---------------------------------+--------------------------------------------- -----------+

195 rows in set (0.00 sec)

Se você não encontrar essas variaveis que referiu é porque elas não estão mesmo configuradas.

Um abraço e obrigado,

Pedro

[Denis Courcy]

Pedro,

O comando show variables que você passou não contempla as variáveis abaixo porque seu BD não está em "movimento", ou seja ninguém está acessando.

Voce poderia, por favor enviar-me o valor de SHOW STATUS

Tal como SHOW VARIABLES este comando me auxiliará a te ajudar.

CREATED_TMP_DISK_TABLES

CREATED_TMP_TABLES

CREATED_TMP_FILES

HANDLER_READ_RND

HANDLER_READ_RND_NEXT

KEY_READS

KEY_RADREQUEST

OPEN_TABLES

OPENED_TABLES

[Guest --Pedro --]

Denis,

aqui vai o Show Status:

| Variable_name | Value |

+--------------------------------+----------+

| Aborted_clients | 4 |

| Aborted_connects | 5 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Bytes_received | 1348074 |

| Bytes_sent | 6663930 |

| Com_admin_commands | 0 |

| Com_alter_db | 0 |

| Com_alter_table | 0 |

| Com_analyze | 0 |

| Com_backup_table | 0 |

| Com_begin | 0 |

| Com_change_db | 91 |

| 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_dealloc_sql | 0 |

| Com_delete | 3 |

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

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

| Com_set_option | 954 |

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

| Com_show_create_table | 0 |

| Com_show_databases | 0 |

| Com_show_errors | 0 |

| Com_show_fields | 0 |

| Com_show_grants | 0 |

| 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_variables | 478 |

| Com_show_warnings | 0 |

| Com_slave_start | 0 |

| Com_slave_stop | 0 |

| Com_stmt_close | 0 |

| Com_stmt_execute | 0 |

| Com_stmt_prepare | 0 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

| Com_truncate | 0 |

| Com_unlock_tables | 0 |

| Com_update | 142 |

| Com_update_multi | 0 |

| Connections | 578 |

| Created_tmp_disk_tables | 116 |

| Created_tmp_files | 4 |

| Created_tmp_tables | 169 |

| Delayed_errors | 0 |

| Delayed_insert_threads | 0 |

| Delayed_writes | 0 |

| Flush_commands | 1 |

| Handler_commit | 0 |

| Handler_delete | 2 |

| Handler_discover | 0 |

| Handler_read_first | 610 |

| Handler_read_key | 5188 |

| Handler_read_next | 11703 |

| Handler_read_prev | 433560 |

| Handler_read_rnd | 54213 |

| Handler_read_rnd_next | 19424448 |

| Handler_rollback | 479 |

| Handler_update | 103 |

| Handler_write | 66183 |

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 7102 |

| Key_blocks_used | 146 |

| Key_read_requests | 22422 |

| Key_reads | 146 |

| Key_write_requests | 88 |

| Key_writes | 88 |

| Max_used_connections | 2 |

| Not_flushed_delayed_rows | 0 |

| Open_files | 52 |

| Open_streams | 0 |

| Open_tables | 33 |

| Opened_tables | 39 |

| Qcache_free_blocks | 0 |

| Qcache_free_memory | 0 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 0 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 0 |

| Questions | 16696 |

| Rpl_status | NULL |

| Select_full_join | 6 |

| Select_full_range_join | 0 |

| Select_range | 1 |

| Select_range_check | 0 |

| Select_scan | 12087 |

| Slave_open_temp_tables | 0 |

| Slave_retried_transactions | 0 |

| Slave_running | OFF |

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 54479 |

| Sort_scan | 543 |

| Ssl_accept_renegotiates | 0 |

| Ssl_accepts | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_cipher | |

| Ssl_cipher_list | |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_ctx_verify_depth | 0 |

| Ssl_ctx_verify_mode | 0 |

| Ssl_default_timeout | 0 |

| Ssl_finished_accepts | 0 |

| Ssl_finished_connects | 0 |

| Ssl_session_cache_hits | 0 |

| Ssl_session_cache_misses | 0 |

| Ssl_session_cache_mode | NONE |

| Ssl_session_cache_overflows | 0 |

| Ssl_session_cache_size | 0 |

| Ssl_session_cache_timeouts | 0 |

| Ssl_sessions_reused | 0 |

| Ssl_used_session_cache_entries | 0 |

| Ssl_verify_depth | 0 |

| Ssl_verify_mode | 0 |

| Ssl_version | |

| Table_locks_immediate | 14620 |

| Table_locks_waited | 0 |

| Threads_cached | 0 |

| Threads_connected | 1 |

| Threads_created | 577 |

| Threads_running | 1 |

| Uptime | 27928 |

+--------------------------------+----------+

186 rows in set (0.00 sec)

[Denis Courcy]

Oi, Pedro!

Continuando a avaliação do post #7

3. As variaveis Handler_read_rnd (com 54213) e Handler_read_rnd_next (com 19424448) confirmam o que perguntei sobre índices no post #2 item 4. Valores altos nestas váriáveis indicam que as consultas estão sendo executadas com table scan (sem uso de índices, ou seja está fazendo varredura em toda a tabela para encontrar o desejado)

Você precisa indexar várias de suas tabelas para melhorar as queries executadas. Se você desejar, pode passar as queries que eu te informo onde criar os índices.

4. O mais importante o cálculo abaixo mostra a quantidade de memória disponível necessária para operar o mysql com sua configuração atual:

MySQL_MEMORIA_USADA = key_buffer_size + max_connections * (join_buffer_size + read_buffer_size + sort_buffer_size + thread_stack + tmp_table_size)

8388600 + 100 * (131072 + 131072 + 2097144 + 196608 + 33554432) =

8388700 * 36110328 = 280MB aproximadamente.

No post #5 item 1 falei que seu problema é falta de memória RAM. Aqui está a explicação. Você necessita ter, no mínimo, 280MB e possui apenas 18MB. Mesmo se contasse com o cache atual 220MB + o buffer 3,4 MB daria 241MB. Mas este buffer e este cache serão usados também pelo SO Linux. O que mostra que você precisa, com urgência, aumentar a memória desta máquina.

5. Não adianta fazer mais otimizações que estas já descritas se você não aumentar a quantidade de memória RAM.

[Guest --Pedro --]

Denis,

Questionei as pessoas que me alugaram o servidor sobre o uso da memória. Acho estranho que um servidor com 512Mb de RAM tenho 98% ocupado, tendo em conta que eles têm um pack de 256Mb de RAM onde consegue fazer correr o servidor, por isso deveria ter pelo menos 256Mb livres.

A resposta deles foi a seguinte, e queria que você me ajudasse a perceber o que é que eles querem dizer com isto:

"quanto à questão da memória é relativamente normal, desde que a memória Swap não esteja elevada, seja como for poderá optimizar um pouco o uso de memória desinstalando serviços superfulos que possam estar activos no servidor."

Como é que eu vejo quais os serviços que estão instalados e quais o que podem ser superfluos?

Um abraço e obrigado

[Denis Courcy]

Denis,

Questionei as pessoas que me alugaram o servidor sobre o uso da memória. Acho estranho que um servidor com 512Mb de RAM tenho 98% ocupado, tendo em conta que eles têm um pack de 256Mb de RAM onde consegue fazer correr o servidor, por isso deveria ter pelo menos 256Mb livres.

A resposta deles foi a seguinte, e queria que você me ajudasse a perceber o que é que eles querem dizer com isto:

"quanto à questão da memória é relativamente normal, desde que a memória Swap não esteja elevada, seja como for poderá optimizar um pouco o uso de memória desinstalando serviços superfulos que possam estar activos no servidor."

Como é que eu vejo quais os serviços que estão instalados e quais o que podem ser superfluos?

Um abraço e obrigado

Oi, Pedro!

Esta sua pergunta foge do meu escopo. Você pode abrir um tópico sobre este questionamento, na área de linux aqui do fórum e eles poderão te auxiliar melhor.

[Guest --Pedro --]

Obrigado Denis,

Irei tentar resolver a questão da memória e depois voltarei aqui...

Uma vez mais obrigado,

Pedro

[Guest --Pedro --]

Denis,

Recebi uma nova resposta do pessoal técnico do servidor sobre o facto de ter quase toda a memória ocupada e foi a seguinte:

-------------

"Vimos por este meio informar que o motivo desta questão é mais intrínseco à área de arquitectura de sistemas, mas o facto é que os endereços de memória quando são usados muitas vezes contêm lixo que o computador nunca mais irá usar, mas não quer dizer que quando precise de usar esse endereço não substitua essa informação "lixo" que lá está, isto é particularmente visível em sistemas até 768 MB, o que não quer dizer que na realidade o servidor não tenha capacidade de resposta.

O indicador mais fiável é de facto a memória swap, sendo que enquanto a utilização da Swap estiver baixa significa que o Processador não precisa de recorrer a esta memória para fazer o seu processamento, quando começa a ter valores elevados o significado é justamente que a memória RAM está ocupada com processos que não podem ser substituídos e então o computador passa a usar a Swap.

Todos este conceito é algo complexo de entender em pormenor mas deve reter que o indicador mais importante é a memória Swap que é a memória virtual alocada no Disco Rígido que serve de extensão à RAM e só usada em último caso pelo sistema, logo enquanto não estiver a ser usada é sinónimo que o circuito normal de memória é suficiente."

-------------

Vou tentar configurar com a memória actual e ver o que dá.

Os dados que você me passou para configurar foram os seguinte, e pedia que você me explicasse como devo proceder para configurá-los.

WAIT_TIMEOUT = 15

MAX_CONNECTIONS = 300

THREAD_CACHE_SIZE = 5 (ou deverei usar esta formula THREAD_CACHE_SIZE = MAX_CONNECTIONS + 1.)

Seria necessário algum outro?

Uma vez mais obrigado,

Pedro

[Guest --Pedro --]

Denis,

Acabei de desinstalar algumas aplicações superfluas do servidor e agora estou com 60Mb livres.

Como devo optmizar o mysql agora? Mantenho apenas aquelas referência ou seria necessário configurar outros parâmetros?

Irei também tentar optimizar o código para não consumir muita memória.

Obrigado,

Pedro

[Denis Courcy]

Olá todos que leem este post

Este alerta é para informar que as modificações aqui citadas valem para este usuário

Modificar o arquivo my.cnf sem o devido conhecimento do que estão fazendo pode provocar danos não intencionais.

Oi, Pedro!

Para realizar as otimizações você deverá seguir estes passos:

1. parar o servidor mysql

o comando para isso é: (através de linha de comando)

shell> mysql.server stop

mysql.server pode ser encontrado no diretório share/mysql sob o diretório de instalação do MySQL ou no diretório support-files da árvore fonte do MySQL.” Fonte: Manual do Mysql Versão 4.1 tradução em português (2.4.3. Inicializando e parando o MySQL automaticamente.)

2. fazer cópia do my.cnf 3. editar arquivo my.cnf para que o conteúdo fique como o abaixo:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with
mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Nossas modificações inicializam aqui
query_cache_type=1
# Ativa o query cache no MySQL 4.x.
# Armazena todos os resultados, exceto SELECT no cache.

query_cache_limit=1M
query_cache_size=32M
max_connections=100
# É o valor default. Vamos permanecer com este valor, por enquanto, porque você não tem memória suficiente para mais conexões.
# memory = key_buffer + (sort_buffer_size + read_buffer_size)* max_connections
# Que é agora: 8 + (20 + 0,25) * 100 = 233 MB
# max_connections = aprox. MaxClients setting in httpd.conf file

interactive_timeout=100

#wait_timeout=28800
# Valor de wait_timeout reduzido para prevenir que idle de clientes segurem conexões.
wait_timeout=15

connect_timeout=10

# table_cache=64
# Verifique sempre a variavel opened_tables e ajuste table_cache após executar por algum tempo.
table_cache=1024

#tmp_table_size=32M by default
#thread_cache=128
# Valor de thread_cache reduzido para prevenir consumo indevido de memória (memory hogging).
thread_cache=32

# key_buffer=258M
# Reduzido key_buffer para verificar o tamanho dos arquivos de índice *.MYI files.
key_buffer=8M

# Comentados os buffers para manter o default.
# sort_buffer_size=2M by default.
# read_buf  fer_size=128K by default.
# read_rnd_buffer_size=256K by default.
# myisam_sort_buffer_size=8M by default.

# thread_concurrency O valor deve ser 2 * (no. de CPUs)
thread_concurrency=2

# Habitei o log de slow queries. Para verificar quais queries estão tomando mais de 2 segundos. 
# As tabelas que aparecerem no arquivo mysqld.slow.log necessitarão de melhorias.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2

[mysql.server]
user=mysql
basedir=/var/lib

[client]
socket=/path/to/mysql.sock

4. reinicializar o server mysql o comando para isso é: (através de linha de comando)

shell> mysql.server stop

5. Coloque em uso para teste e informe qualquer variação positiva ou negativa, pois só tenho .

[Guest --Pedro --]

Denis,

Muito obrigado.

Estou a pedir aos membros do site para testar o novo e até agora parece realmente tudo muito mais rápido. Terei que esperar um pouco mais para que mais membros possam testar, mas parece que está tudo bem.

Há algum tipo de ficheiro que eu possa consultar que tenha algum relatório do desempenho?

Um abraço e obrigado,

Pedro

[Denis Courcy]

Denis,

Muito obrigado.

Estou a pedir aos membros do site para testar o novo e até agora parece realmente tudo muito mais rápido. Terei que esperar um pouco mais para que mais membros possam testar, mas parece que está tudo bem.

Há algum tipo de ficheiro que eu possa consultar que tenha algum relatório do desempenho?

Um abraço e obrigado,

Pedro

Pedro, respondendo sua pergunta, ativei um log de consultas lentas log_slow_queries=/var/log/mysqld.slow.log. de vez em quando dê uma olhada no conteúdo do log para avaliar que tabelas/consultas estão ficando críticas.

Além disso,

As variáveis do MySQL mostradas em SHOW VARIABLES e SHOW STATUS te darão uma boa visão.

Abaixo segue uns links que utilizo para me lembrar do que fazer quando um problema desse aparece.

Os conteúdos estão em inglês, mas logo disponibilizarei, aqui no forum, os artigos traduzidos para o português.

[HowTo] Optimising MYSQL

Optimizing MySQL: Hardware and the Mysqld Variables

MySQL 3.23, 4.0, 4.1 Reference Manual :: 7 Optimization

MySQL Query Optimization

[Guest --Pedro --]

Obrigado Denis,

Estes links vão ser de muita utilidade para afinar as coisas à medida que o site for crescendo...

Só uma nota: Eu coloquei o wait_timeout para 15, como você sugeriu, mas no show variables ele aparece como 100.

Um abraço,

Pedro