Pesquisar na Comunidade

Olá a todos, Estou com um problema no servidor MySQL e preciso de ajuda. De uns dias pra cá ele tem apresentado muita lentidão, abaixo seguem os dados do show variables, se alguém puder ajudar eu agradeço. O servidor em questão tem 96GB de memória ram. Variable_name Value auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON back_log 50 basedir C:/Program Files/MySQL/MySQL Server 5.5/ big_tables OFF binlog_cache_size 32768 binlog_direct_non_transactional_updates OFF binlog_format STATEMENT binlog_stmt_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 C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type NO_CHAIN concurrent_insert AUTO connect_timeout 30 datadir D:\MySQL\Data\ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_storage_engine MyISAM default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 div_precision_increment 4 engine_condition_pushdown ON error_count 0 event_scheduler ON expire_logs_days 0 external_user flush OFF flush_time 1800 foreign_key_checks ON ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) general_log OFF general_log_file D:\MySQL\Data\Neo.log group_concat_max_len 1024 have_compress YES have_crypt NO have_csv YES have_dynamic_loading YES have_geometry YES have_innodb DISABLED have_ndbcluster NO have_openssl DISABLED have_partitioning YES have_profiling YES have_query_cache YES have_rtree_keys YES have_ssl DISABLED have_symlink YES hostname Neo identity 0 ignore_builtin_innodb OFF init_connect init_file init_slave insert_id 0 interactive_timeout 1 join_buffer_size 131072 keep_files_on_create OFF key_buffer_size 15204352000 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 large_files_support ON large_page_size 0 large_pages OFF last_insert_id 0 lc_messages en_US lc_messages_dir C:\Program Files\MySQL\MySQL Server 5.5\share\ lc_time_names en_US license GPL local_infile ON lock_wait_timeout 31536000 log OFF log_bin OFF log_bin_trust_function_creators OFF log_error D:\MySQL\Data\Neo.err log_output FILE log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries OFF log_warnings 2 long_query_time 20.000.000 low_priority_updates OFF lower_case_file_system ON lower_case_table_names 1 max_allowed_packet 268435456 max_binlog_cache_size 1,84467E+19 max_binlog_size 1073741824 max_binlog_stmt_cache_size 1,84467E+19 max_connect_errors 1000000 max_connections 5290 max_delayed_threads 20 max_error_count 64 max_heap_table_size 10737418240 max_insert_delayed_threads 20 max_join_size 1,84467E+19 max_length_for_sort_data 1024 max_long_data_size 268435456 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 metadata_locks_cache_size 1024 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 2146435072 myisam_mmap_size 1,84467E+19 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 2147483648 myisam_stats_method nulls_unequal myisam_use_mmap OFF named_pipe OFF net_buffer_length 16384 net_read_timeout 65 net_retry_count 10 net_write_timeout 65 new OFF old OFF old_alter_table OFF old_passwords OFF open_files_limit 71048 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on performance_schema OFF performance_schema_events_waits_history_long_size 10000 performance_schema_events_waits_history_size 10 performance_schema_max_cond_classes 80 performance_schema_max_cond_instances 1000 performance_schema_max_file_classes 50 performance_schema_max_file_handles 32768 performance_schema_max_file_instances 10000 performance_schema_max_mutex_classes 200 performance_schema_max_mutex_instances 1000000 performance_schema_max_rwlock_classes 30 performance_schema_max_rwlock_instances 1000000 performance_schema_max_table_handles 100000 performance_schema_max_table_instances 50000 performance_schema_max_thread_classes 50 performance_schema_max_thread_instances 1000 pid_file D:\MySQL\Data\Neo.pid plugin_dir C:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\ port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 proxy_user pseudo_slave_mode OFF pseudo_thread_id 46121734 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 67108864 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 rand_seed1 0 rand_seed2 0 range_alloc_block_size 4096 read_buffer_size 524288 read_only OFF read_rnd_buffer_size 32768 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_recovery OFF relay_log_space_limit 0 report_host report_password report_port 3306 report_user rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 0 shared_memory OFF shared_memory_base_name MYSQL skip_external_locking ON skip_name_resolve OFF skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_exec_mode STRICT slave_load_tmpdir C:\Windows\TEMP slave_max_allowed_packet 1073741824 slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slave_type_conversions slow_launch_time 2 slow_query_log OFF slow_query_log_file D:\MySQL\Data\Neo-slow.log socket MySQL sort_buffer_size 2097152 sql_auto_is_null OFF sql_big_selects ON sql_big_tables OFF sql_buffer_result OFF sql_log_bin ON sql_log_off OFF sql_low_priority_updates OFF sql_max_join_size 1,84467E+19 sql_mode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 1,84467E+19 sql_slave_skip_counter 0 sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAM stored_program_cache 256 sync_binlog 0 sync_frm ON sync_master_info 0 sync_relay_log 0 sync_relay_log_info 0 system_time_zone E. South America Standard Tim table_definition_cache 110000 table_open_cache 17900 thread_cache_size 2400 thread_concurrency 20 thread_handling one-thread-per-connection thread_stack 262144 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF timestamp 1471875661 tmp_table_size 10737418240 tmpdir C:\Windows\TEMP transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ unique_checks ON updatable_views_with_limit YES version 5.5.46 version_comment MySQL Community Server (GPL) version_compile_machine x86 version_compile_os Win64 wait_timeout 1 warning_count 0

Estou com uma consulta fazendo três inner join, fiz o select no pgadmin beleza rápido e informações certas 21ms, quando executou em VB ou ASP demora 15seg cravados. porque a diferença? Me ajudem !!!

Ola pessoal estou trabalhando num portal para a visualização de obras do governo. O site contem muitas fotos, antigamente utilizávamos a biblioteca do flickr para armazenar e exibir as fotos das obras no portal. Só que o plugin do flickr deu problema, então resolvemos armazenar estas fotos no banco de dados Mysql, armazenando apenas o caminho destas fotos sendo que as fotos estão armazenadas numa pasta do projeto. O problema é que chegou a quase mil fotos, o portal está muito lento e inoperante. Já avisaram aqui que terão mais fotos, totalizando quase umas 5 mil. Acredito que apenas compactando as fotos não resolveria o problema, então queria saber se tem alguma forma de chamar essas fotos do banco sem comprometer a velocidade dela, tipo chamar via ajax ou outra coisa do tipo.

Boa Tarde Pessoal, Sou iniciante ainda em SQL e estou tendo problemas de lentidão na consulta deste script que montei: DECLARE @TIMEZONE VARCHAR(50), @BIAS INT, @FROMDATE DATETIME, @TODATE DATETIME SET @FROMDATE = NULL SET @TODATE = NULL IF @FROMDATE IS NULL OR @TODATE IS NULL BEGIN SET @FROMDATE = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(DAY,-7,GETDATE()),112),120) SET @TODATE = DATEADD(SECOND,-1,DATEADD(DAY,7,@FROMDATE)) END SELECT DISTINCT DATEADD(MINUTE, TZ1.BIAS, ACT.STARTTIME), DATEADD(MINUTE, TZ2.BIAS, ACT.ENDTIME), ACT.EMPLOYEEID, ACT.ACTIVITYID, DATEDIFF(SECOND, ACT.STARTTIME, ACT.ENDTIME) QTD_SEGUNDOS FROM AAAAAAAAAAAAAAAAAAA ACT (NOLOCK) INNER JOIN BBBBBBBBBBBBBBBBBBBBBBBB WO1 (NOLOCK) ON ACT.EMPLOYEEID = WO1.WORKRESOURCEID AND ACT.STARTTIME BETWEEN WO1.STARTTIME AND ISNULL(WO1.ENDTIME, CAST('99991231' AS DATETIME)) INNER JOIN BBBBBBBBBBBBBBBBBBBBBBBB WO2 (NOLOCK) ON ACT.EMPLOYEEID = WO2.WORKRESOURCEID AND ACT.ENDTIME BETWEEN WO2.STARTTIME AND ISNULL(WO2.ENDTIME, CAST('99991231' AS DATETIME)) INNER JOIN CCCCCCCCCCCC O1 (NOLOCK) ON WO1.ORGANIZATIONID = O1.ID INNER JOIN CCCCCCCCCCCC O2 (NOLOCK) ON WO2.ORGANIZATIONID = O2.ID INNER JOIN DDDDDDDDDD TZ1 (NOLOCK) ON O1.TIMEZONE = TZ1.TIMEZONE AND NOT((TZ1.ENDTIME <= ACT.STARTTIME) OR (TZ1.STARTTIME > ACT.STARTTIME)) INNER JOIN DDDDDDDDDD TZ2 (NOLOCK) ON O2.TIMEZONE = TZ2.TIMEZONE AND NOT((TZ2.ENDTIME <= ACT.ENDTIME) OR (TZ2.STARTTIME > ACT.ENDTIME)) WHERE DATEADD(MINUTE, TZ1.BIAS, ACT.STARTTIME) BETWEEN @FROMDATE AND @TODATE AND ACT.ACTIVITYID <> -4001 O resultado deste script é exatamente o que preciso (ajustar o fuso-horário de cada dia/atividade/funcionário, baseado no timzezone da organização de cada funcionário), porém esta demorando mais de 20 horas para processar. Detalhes: Tabela: AAAAAAAAAAAAAAAAAAA Tem mais de 10.000.000 de registros, separado por dia/hora inicio e dia/hora fim, por funcionário e atividade. (Esses registros são apenas de 7 dias). Tabela: BBBBBBBBBBBBBBBBBBBBBBBB Tem mais de 4.000.000 de registros, separado por dia/hora inicio e dia/hora fim, por funcionário organização. (Esses registros são apenas de 7 dias). Tabela: CCCCCCCCCCCC Tem mais de 32.000 de registros, separado por dia/hora inicio e dia/hora fim, e organização. (Esses registros são apenas de 7 dias). Tabela: DDDDDDDDDD Tem mais de 80.000 de registros, separado por dia/hora inicio e dia/hora fim, e timezone. (Esses registros são apenas de 7 dias). Já criei chaves primárias nos campos de cruzamento e não ajudou. Poderiam me auxiliar? Muito Obrigado!!!!