MySQL performance & variables tweaking
Solution 1
Optimising mysql is very well commented on the net, and you’ll find huge information on how to do this. There is never “best parameters”, the best parameters is those fits your needs, box hardware, mysql usage… So, I’ll not give the best parameters but rather how to define these ones. Make some tests, and you’ll quickly find your own parameters.
There a lot of available parameters but only few one are very important to tweak your mysql box.
The most important variables are (for me, and it is not exhaustive):
- max_connections
- wait_timeout
- thread_cache_size
- table_cache
- key_buffer_size
- query_cache_size
- tmp_table_size
To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.
1 - The two most important variables : Table_cache and Key_buffer_size
- If Opened_tables is big, then your table_cache variable is probably too small. table_cache 64 Open_tables 64 Opened_tables 544468
This is the first serious problem. "The table_cache is the number of open tables for all threads. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table." Therefore, even though we only have a few tables, we will need many more open_tables.
The Opened_tables value is high and shows the number of cache misses. Getting the table_cache size correct is one of the two best things you can do to improve performance.
- If Key_reads is big, then your key_buffer_size variable is probably too small. The cache hit rate can be calculated with Key_reads/Key_read_requests. key_buffer_size 16M Key_read_requests 2973620399 Key_reads 8490571 (cache hit rate = 0.0028)
“The key_buffer_size affects the size of the index buffers and the speed of index handling, particularly reading." The MySQL manual (and other sources) say that "Key_reads/Key_read_request ratio should normally be < 0.01." This is the other most important thing to get correct. Here the value seems to be correct (< 0.01)
Also check key_write_requests and key_writes. The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)
Here is a very interesting web pointer : table_cache and key_buffer_size
2 - Others important settings are : Wait_timeout, max_connexion, thread_cache
A little explanation : Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.
The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done. The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.
So the solution is to use the Thread_cache (from mysql doc) : “How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.”
- If Threads_created is big, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections. thread_cache_size 0 Threads_created 150022 Connections 150023
This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.
you may try this formula : table_cache = opened table / max_used_connection
3 - Finally, you may also have a look at : tmp_table_size and Handler_read_rnd / Handler_read_rnd_next
- If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size variable to get the temporary tables memory-based instead of disk based.
tmp_table_size 32M Created_tmp_disk_tables 3227 Created_tmp_tables 159832 Created_tmp_files 4444
Created_tmp_disk_tables are the "number of implicit temporary tables on disk created while executing statements" and Created_tmp_tables are memory-based. Obviously it is bad if you have to go to disk instead of memory. About 2% of temp tables go to disk, which doesn't seem too bad but increasing the tmp_table_size probably couldn't hurt either.
- If Handler_read_rnd is big, then you probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly. Handler_read_rnd 27712353 Handler_read_rnd_next 283536234
These values are high, that we could probably stand to improve the indexes and queries.
I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.
Solution 2
For those who'll read this response later :
ONLY FOR MYSQL SERVER LESS THAN 6.0 If you change tmp_table_size
, don't forget to carry about max_heap_table_size
value too.
Explanation here
Another topic about this VARS : here
Martin
Updated on September 15, 2020Comments
-
Martin over 3 years
I have a problem with a MySql server causing it to freeze all the time. In phpMyAdmin when this happens I can see in "Show Processlist" are lots of processes for User "unauthenticated user" with Command "connect" and State "reading from net".
The database is using InnoDB but I also use one table running MyISAM to be able to perform full text search. The server has about 4GB memory, less than 500MB in use.
I have been using the slow Query Log from MySql to find the queries not using indexes. I also think I need to do some changes / tweaking on the server variables.
I really could use some help here and therefore I'm posting both SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES here and maybe you can give me some ideas how I should set the variables?
Current SHOW GLOBAL STATUS:
Variable_name Value Aborted_clients 730 Aborted_connects 35 Binlog_cache_disk_use 0 Binlog_cache_use 0 Binlog_stmt_cache_disk_use 0 Binlog_stmt_cache_use 0 Bytes_received 31558148370 Bytes_sent 556410688609 Com_admin_commands 4565 Com_assign_to_keycache 0 Com_alter_db 0 Com_alter_db_upgrade 0 Com_alter_event 0 Com_alter_function 0 Com_alter_procedure 0 Com_alter_server 0 Com_alter_table 3 Com_alter_tablespace 0 Com_analyze 0 Com_begin 168 Com_binlog 0 Com_call_procedure 0 Com_change_db 26791502 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 0 Com_create_db 0 Com_create_event 0 Com_create_function 0 Com_create_index 0 Com_create_procedure 0 Com_create_server 0 Com_create_table 0 Com_create_trigger 0 Com_create_udf 0 Com_create_user 0 Com_create_view 0 Com_dealloc_sql 0 Com_delete 584152 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_event 0 Com_drop_function 0 Com_drop_index 0 Com_drop_procedure 0 Com_drop_server 0 Com_drop_table 0 Com_drop_trigger 0 Com_drop_user 0 Com_drop_view 0 Com_empty_query 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 5871056 Com_insert_select 2833 Com_install_plugin 0 Com_kill 160 Com_load 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_release_savepoint 0 Com_rename_table 0 Com_rename_user 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_resignal 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 0 Com_rollback_to_savepoint 0 Com_savepoint 0 Com_select 21739455 Com_set_option 53600251 Com_signal 0 Com_show_authors 0 Com_show_binlog_events 0 Com_show_binlogs 217 Com_show_charsets 3 Com_show_collations 26786645 Com_show_contributors 0 Com_show_create_db 0 Com_show_create_event 0 Com_show_create_func 0 Com_show_create_proc 0 Com_show_create_table 6412 Com_show_create_trigger 84 Com_show_databases 213 Variable_name Value Com_show_engine_logs 0 Com_show_engine_mutex 0 Com_show_engine_status 0 Com_show_events 0 Com_show_errors 0 Com_show_fields 4389 Com_show_function_status 14 Com_show_grants 2 Com_show_keys 25 Com_show_master_status 5 Com_show_open_tables 0 Com_show_plugins 560 Com_show_privileges 0 Com_show_procedure_status 14 Com_show_processlist 189 Com_show_profile 0 Com_show_profiles 0 Com_show_relaylog_events 0 Com_show_slave_hosts 0 Com_show_slave_status 5 Com_show_status 156 Com_show_storage_engines 16 Com_show_table_status 4154 Com_show_tables 280 Com_show_triggers 4144 Com_show_variables 26786703 Com_show_warnings 2363 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_reprepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_uninstall_plugin 0 Com_unlock_tables 168 Com_update 2572452 Com_update_multi 35171 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 26787752 Created_tmp_disk_tables 119645 Created_tmp_files 7069 Created_tmp_tables 58427880 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 30698922 Handler_delete 177424 Handler_discover 0 Handler_prepare 0 Handler_read_first 1239039 Handler_read_key 2226723080 Handler_read_last 9200 Handler_read_next 4186985860 Handler_read_prev 6811735740 Handler_read_rnd 8942473 Handler_read_rnd_next 16431972655 Handler_rollback 495 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 9720709 Handler_write 15014651212 Innodb_buffer_pool_pages_data 8173 Innodb_buffer_pool_pages_dirty 138 Innodb_buffer_pool_pages_flushed 34780758 Innodb_buffer_pool_pages_free 0 Innodb_buffer_pool_pages_misc 18 Innodb_buffer_pool_pages_total 8191 Innodb_buffer_pool_read_ahead_rnd 0 Innodb_buffer_pool_read_ahead 26163447 Innodb_buffer_pool_read_ahead_evicted 669245354 Innodb_buffer_pool_read_requests 19793139859 Innodb_buffer_pool_reads 1156881183 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 81288931 Innodb_data_fsyncs 8482576 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 19409628139520 Innodb_data_reads 1184668281 Innodb_data_writes 25747879 Innodb_data_written 576917547008 Innodb_dblwr_pages_written 17390379 Innodb_dblwr_writes 255445 Innodb_have_atomic_builtins ON Innodb_log_waits 0 Innodb_log_write_requests 6930648 Innodb_log_writes 7850155 Innodb_os_log_fsyncs 7976564 Variable_name Value Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 7007020032 Innodb_page_size 16384 Innodb_pages_created 78647 Innodb_pages_read 1184669628 Innodb_pages_written 17390379 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 1116 Innodb_row_lock_time_avg 39 Innodb_row_lock_time_max 963 Innodb_row_lock_waits 28 Innodb_rows_deleted 177424 Innodb_rows_inserted 5863854 Innodb_rows_read 14138431339 Innodb_rows_updated 4886889 Innodb_truncated_status_writes 0 Key_blocks_not_flushed 0 Key_blocks_unused 13 Key_blocks_used 13 Key_read_requests 3458723878 Key_reads 49092342 Key_write_requests 462162 Key_writes 436874 Last_query_cost 0.000000 Max_used_connections 137 Not_flushed_delayed_rows 0 Open_files 0 Open_streams 0 Open_table_definitions 113 Open_tables 4 Opened_files 6485943 Opened_table_definitions 116 Opened_tables 31771706 Performance_schema_cond_classes_lost 0 Performance_schema_cond_instances_lost 0 Performance_schema_file_classes_lost 0 Performance_schema_file_handles_lost 0 Performance_schema_file_instances_lost 0 Performance_schema_locker_lost 0 Performance_schema_mutex_classes_lost 0 Performance_schema_mutex_instances_lost 0 Performance_schema_rwlock_classes_lost 0 Performance_schema_rwlock_instances_lost 0 Performance_schema_table_handles_lost 0 Performance_schema_table_instances_lost 0 Performance_schema_thread_classes_lost 0 Performance_schema_thread_instances_lost 0 Prepared_stmt_count 0 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 Queries 191630861 Questions 191562751 Rpl_status AUTH_MASTER Select_full_join 7193 Select_full_range_join 0 Select_range 957755 Select_range_check 0 Select_scan 57577013 Slave_heartbeat_period 0.000 Slave_open_temp_tables 0 Slave_received_heartbeats 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 445 Sort_merge_passes 3531 Sort_range 5655320 Sort_rows 43857009 Sort_scan 757681 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 51310065 Variable_name Value Table_locks_waited 14101 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 0 Threads_connected 4 Threads_created 26787751 Threads_running 1 Uptime 1208447 Uptime_since_flush_status 1208447
Current SHOW GLOBAL VARIABLES:
Variable_name Value auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON back_log 50 basedir /usr 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 latin1 character_set_connection latin1 character_set_database latin1 character_set_filesystem binary character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type NO_CHAIN concurrent_insert AUTO connect_timeout 10 datadir /var/lib/mysql/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_storage_engine InnoDB 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 event_scheduler OFF expire_logs_days 0 flush OFF flush_time 0 foreign_key_checks ON ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 3 ft_query_expansion_limit 20 ft_stopword_file (built-in) general_log OFF general_log_file /var/lib/mysql/db.log group_concat_max_len 1024 have_compress YES have_crypt YES have_csv YES have_dynamic_loading YES have_geometry YES have_innodb YES 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 silldb5 ignore_builtin_innodb OFF init_connect init_file init_slave innodb_adaptive_flushing ON innodb_adaptive_hash_index ON innodb_additional_mem_pool_size 8388608 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_instances 1 innodb_buffer_pool_size 134217728 innodb_change_buffering all innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_format Antelope innodb_file_format_check ON innodb_file_format_max Antelope innodb_file_per_table OFF innodb_flush_log_at_trx_commit 1 innodb_flush_method innodb_force_load_corrupted OFF innodb_force_recovery 0 innodb_io_capacity 200 innodb_large_prefix OFF innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_buffer_size 8388608 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ Variable_name Value innodb_max_dirty_pages_pct 75 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_old_blocks_pct 37 innodb_old_blocks_time 0 innodb_open_files 300 innodb_purge_batch_size 20 innodb_purge_threads 0 innodb_random_read_ahead OFF innodb_read_ahead_threshold 56 innodb_read_io_threads 4 innodb_replication_delay 0 innodb_rollback_on_timeout OFF innodb_rollback_segments 128 innodb_spin_wait_delay 6 innodb_stats_method nulls_equal innodb_stats_on_metadata ON innodb_stats_sample_pages 8 innodb_strict_mode OFF innodb_support_xa ON innodb_sync_spin_loops 30 innodb_table_locks ON innodb_thread_concurrency 0 innodb_thread_sleep_delay 10000 innodb_use_native_aio ON innodb_use_sys_malloc ON innodb_version 1.1.8 innodb_write_io_threads 4 interactive_timeout 28800 join_buffer_size 131072 keep_files_on_create OFF key_buffer_size 16384 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 lc_messages en_US lc_messages_dir /usr/share/mysql/ lc_time_names en_US license GPL local_infile ON lock_wait_timeout 31536000 locked_in_memory OFF log OFF log_bin OFF log_bin_trust_function_creators OFF log_error /var/lib/mysql/db.err log_output FILE log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries OFF log_warnings 1 long_query_time 10.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 1048576 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_binlog_stmt_cache_size 18446744073709547520 max_connect_errors 10 max_connections 2000 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_long_data_size 1048576 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 metadata_locks_cache_size 1024 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_mmap_size 18446744073709551615 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal myisam_use_mmap OFF net_buffer_length 2048 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old OFF old_alter_table OFF old_passwords OFF open_files_limit 10000 optimizer_prune_level 1 Variable_name Value optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_so... 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 /var/run/mysqld/mysqld.pid plugin_dir /usr/lib64/mysql/plugin 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 8 388 608 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 range_alloc_block_size 4096 read_buffer_size 262144 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_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 1 skip_external_locking ON skip_name_resolve ON skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_exec_mode STRICT slave_load_tmpdir /tmp 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 /var/lib/mysql/slow.log socket /var/lib/mysql/mysql.sock sort_buffer_size 65536 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 18446744073709551615 sql_mode sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 18446744073709551615 sql_slave_skip_counter 0 sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine InnoDB sync_binlog 0 sync_frm ON sync_master_info 0 sync_relay_log 0 sync_relay_log_info 0 system_time_zone CET table_definition_cache 400 table_open_cache 4 thread_cache_size 100 thread_concurrency 8 thread_handling one-thread-per-connection thread_stack 524288 time_format %H:%i:%s Variable_name Value time_zone SYSTEM timed_mutexes OFF tmp_table_size 16777216 tmpdir /tmp transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ unique_checks ON updatable_views_with_limit YES version 5.5.20 version_comment MySQL Community Server (GPL) by Remi version_compile_machine x86_64 version_compile_os Linux wait_timeout 28800
-
DRapp about 12 yearsCould you show some of the queries that are slow... it might be as simple as a poorly designed query, missing indexes, etc... And sometimes, querying from "Views" are not able to be as optimized as one might think without going to the raw tables originating the views.
-
Martin about 12 yearsYes it might be problems with the queries as well, I'm looking into that and I understand that this might be the problem. I think it will be quite hard to post all that information here, is it possible to you give some advice by looking at the info I already provided?
-
DRapp about 12 yearsSorry, I'm not an optimizer by configuration settings, just by writing queries... I would leave that to someone more hardware/settings level than myself.
-
Laurynas Biveinis about 12 yearsThis is offtopic for SO, if you asked on dba.stackexchange.com, you'd probably have it solved by now. I am not sure this has anything to do with slow queries, since you have many connections at "reading from net" state.
-
Matt Dodge about 12 yearsIt will be very difficult to optimize your server's configuration without knowing what types of queries are slowing it down. Maybe post the slowest query you have found in the query log
-
Martin about 12 yearsOk, maybe I should ask the question there instead. Thanks!
-
-
Martin about 12 yearsThis was a great help! I'm looking into this now. One question though, is table_cache same as table_open_cache? I can't find the table_cache variable, I only have the table_open_cache.
-
Somnath Muluk about 12 yearsYes, both are same.table_open_cache was known as table_cache in MySQL 5.1.2 and earlier.
-
Martin about 12 yearsFor some reason the query_cache_size and thread_cache_size values was wrong in my question. They should not be zero but: 8 388 608 and 100. Sorry about that!
-
Martin about 12 yearsFirst I thought you'd found the problem when table_open_cache was set to 4. But that wasn't really true as it was already set to 1024. It was just SHOW GLOBAL VARIABLES that was showing the value of table_cache instead of table_open_cache, MySql was however using table_open_cache with the right value of 1024. It seems like neither of the changes I made did solve the problem at all. :(
-
Martin about 12 yearsIt didn't solve the issue, but you did what you could to help me with the information I gave you and pointed me in the right direction. I will continue investigating the problem. Thanks!
-
Martin about 12 yearsI realized I was using pooling=false in my connection-string to the database. I changed this to default pooling=true and now the performance seems much better. Could that have been the problem?
-
Somnath Muluk about 12 yearsI don't know about false. But if set true, SQLConnection object is drawn from the appropriate pool. And it's max limit is 100.
-
ursitesion over 9 years@SomnathMuluk: You should avoid the usage of words used for chatting purposes. Its a forum for explanation of your answer. For example you have used
max_connexion
,pbm
etc. -
John over 8 years@SomnathMuluk What is the title of that article that you linked above? It's 404'ing because of the "...". (+1 by the way)