MySQL Performance
Forum for MySQL Performance and Benchmarks.
Updated: 2 hours 11 min ago
DECIMAL(65, 0) data size (1 reply)
Hey guys!
DECIMAL(65, 0) should take 29 bytes to store each value
http://dev.mysql.com/doc/refman/5.5/en/precision-math-decimal-changes.html.
My math is: 65 = 7 times * 9 digits + 2 digits left; 7 times * 4 bytes + 1 byte = 29 bytes.
Well, I wanted to test that :) and made a simple test - the test follows. My question however is - what is wrong with my test? Because it seems to prove that DECIMAL(65, 0) takes more than 29 bytes actually, which I do not believe in.
Here is my test:
create table decimal_size_test (
decimal_data decimal(65,0)
); -- as simple as it could be...
delimiter //
create procedure generate_test_data(sample_data decimal(65,0), number_of_records int)
begin
declare i int default 0;
delete from decimal_size_test;
while i < number_of_records do
insert into decimal_size_test (decimal_data) values (sample_data);
set i = i + 1;
end while;
end //
delimiter ;
call generate_test_data(1, 100000);
select table_name, data_length from information_schema.tables where table_name = 'decimal_size_test';
Speaking English, I try to add exactly 100 000 records to a simple table where only one column exists, and this column is DECIMAL(65, 0). No PK, no index, nothing except this simple column. Value being added = 1. Now, I select the data_length from information_schema.tables for this table - it is about 6 832 128, instead of 29 * 100 000 = 2 900 000 as I originally expected.
But that's not it. If I add other values, not 1, but, let's say 78695886597596 - data_length will be 6 307 840. And if I make this column a PK, data_length is much less in this case - 5 783 552.
To make this test more representative, I decided to use a table like this:
create table decimal_size_test2 (
id int,
decimal_data decimal(65, 0),
primary key (id)
);
and populate it with 100 000 rows with decimal_data set to NULL for every row. This table size (data_length) is 2 637 824 bytes. Okay. Now:
update decimal_size_test2 set decimal_data = 1 where decimal_data is null;
And the new table size is 8 929 280. I.e. putting 1 into 100 000 rows added 6 291 456 bytes. I.e. around the same as in the very first test.
So, the questions are: why the data_length is more than expected, and why the values are different each time?
DECIMAL(65, 0) should take 29 bytes to store each value
http://dev.mysql.com/doc/refman/5.5/en/precision-math-decimal-changes.html.
My math is: 65 = 7 times * 9 digits + 2 digits left; 7 times * 4 bytes + 1 byte = 29 bytes.
Well, I wanted to test that :) and made a simple test - the test follows. My question however is - what is wrong with my test? Because it seems to prove that DECIMAL(65, 0) takes more than 29 bytes actually, which I do not believe in.
Here is my test:
create table decimal_size_test (
decimal_data decimal(65,0)
); -- as simple as it could be...
delimiter //
create procedure generate_test_data(sample_data decimal(65,0), number_of_records int)
begin
declare i int default 0;
delete from decimal_size_test;
while i < number_of_records do
insert into decimal_size_test (decimal_data) values (sample_data);
set i = i + 1;
end while;
end //
delimiter ;
call generate_test_data(1, 100000);
select table_name, data_length from information_schema.tables where table_name = 'decimal_size_test';
Speaking English, I try to add exactly 100 000 records to a simple table where only one column exists, and this column is DECIMAL(65, 0). No PK, no index, nothing except this simple column. Value being added = 1. Now, I select the data_length from information_schema.tables for this table - it is about 6 832 128, instead of 29 * 100 000 = 2 900 000 as I originally expected.
But that's not it. If I add other values, not 1, but, let's say 78695886597596 - data_length will be 6 307 840. And if I make this column a PK, data_length is much less in this case - 5 783 552.
To make this test more representative, I decided to use a table like this:
create table decimal_size_test2 (
id int,
decimal_data decimal(65, 0),
primary key (id)
);
and populate it with 100 000 rows with decimal_data set to NULL for every row. This table size (data_length) is 2 637 824 bytes. Okay. Now:
update decimal_size_test2 set decimal_data = 1 where decimal_data is null;
And the new table size is 8 929 280. I.e. putting 1 into 100 000 rows added 6 291 456 bytes. I.e. around the same as in the very first test.
So, the questions are: why the data_length is more than expected, and why the values are different each time?
Problem with server "freezing" (1 reply)
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 0
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 0
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
Thanks a lot for your help!
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 0
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 0
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
Thanks a lot for your help!
How to improve the join performance on 15 tables (4 replies)
HI,
I have the nearly 15 master tables, all the tables stores the employee related master data like department, designation, grade, location, shift, bank, blood group etc..
I created view called employee details using these tables with JOIN(INNER and LEFT JOINS) this view selects the 95 columns value from these tables.
When i access the view it will take the more time to return the result set.
SHOW VARIABLES;
-----------------
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 MIXED
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 /usr/share/mysql/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 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
error_count 0
event_scheduler OFF
expire_logs_days 10
external_user
flush OFF
flush_time 0
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
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 chat
identity 0
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 ./
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
insert_id 0
interactive_timeout 28800
join_buffer_size 131072
keep_files_on_create OFF
key_buffer_size 402653184
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 /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 ON
log_bin_trust_function_creators OFF
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 104857600
max_binlog_stmt_cache_size 18446744073709547520
max_connect_errors 10
max_connections 151
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 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
min_examined_row_limit 0
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 67108864
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length 8192
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 1185
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 /var/lib/mysql/chat.pid
plugin_dir /usr/lib/mysql/plugin
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
proxy_user
pseudo_thread_id 19
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 33554432
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 2097152
read_only OFF
read_rnd_buffer_size 8388608
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 OFF
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
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 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 IST
table_definition_cache 400
table_open_cache 512
thread_cache_size 8
thread_concurrency 10
thread_handling one-thread-per-connection
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
timestamp 1352784119
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.18-log
version_comment MySQL Community Server (GPL)
version_compile_machine i686
version_compile_os Linux
wait_timeout 28800
warning_count 0
Is there any optimization ?
I have the nearly 15 master tables, all the tables stores the employee related master data like department, designation, grade, location, shift, bank, blood group etc..
I created view called employee details using these tables with JOIN(INNER and LEFT JOINS) this view selects the 95 columns value from these tables.
When i access the view it will take the more time to return the result set.
SHOW VARIABLES;
-----------------
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 MIXED
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 /usr/share/mysql/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 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
error_count 0
event_scheduler OFF
expire_logs_days 10
external_user
flush OFF
flush_time 0
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
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 chat
identity 0
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 ./
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
insert_id 0
interactive_timeout 28800
join_buffer_size 131072
keep_files_on_create OFF
key_buffer_size 402653184
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 /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 ON
log_bin_trust_function_creators OFF
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 104857600
max_binlog_stmt_cache_size 18446744073709547520
max_connect_errors 10
max_connections 151
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 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
min_examined_row_limit 0
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 67108864
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length 8192
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 1185
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 /var/lib/mysql/chat.pid
plugin_dir /usr/lib/mysql/plugin
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
proxy_user
pseudo_thread_id 19
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 33554432
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 2097152
read_only OFF
read_rnd_buffer_size 8388608
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 OFF
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
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 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 IST
table_definition_cache 400
table_open_cache 512
thread_cache_size 8
thread_concurrency 10
thread_handling one-thread-per-connection
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
timestamp 1352784119
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.18-log
version_comment MySQL Community Server (GPL)
version_compile_machine i686
version_compile_os Linux
wait_timeout 28800
warning_count 0
Is there any optimization ?
join on same table (3 replies)
Hello,
I have a table with 3 million records. The DDL is :
==========================================================
CREATE TABLE IF NOT EXISTS `TimeRegistrations` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`RegistreeId` int(11) NOT NULL,
`StartDateTime` datetime NOT NULL,
`EndDateTime` datetime NOT NULL,
PRIMARY KEY (`Id`),
KEY `FK_TR_RegistreeId` (`RegistreeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
==========================================================
I want to check if there is an overlap in timeregistrations with the following sql ===============================
select a.*
from TimeRegistrations as a join TimeRegistrations as b on a.Id <> b.Id and b.StartDateTime < a.EndDateTime and b.EndDateTime > a.StartDateTime and a.RegistreeId = b.RegistreeId
order by a.RegistreeId, a.StartDateTime
================================
This query takes about 1 hour and if I also put indices on StartDateTime and EndDateTime the query still takes 15 minutes.
Is this normal, how can I make a significant boost? Is this even posiible?
Thanks beforehand and greetz
Coen Dunnink
I have a table with 3 million records. The DDL is :
==========================================================
CREATE TABLE IF NOT EXISTS `TimeRegistrations` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`RegistreeId` int(11) NOT NULL,
`StartDateTime` datetime NOT NULL,
`EndDateTime` datetime NOT NULL,
PRIMARY KEY (`Id`),
KEY `FK_TR_RegistreeId` (`RegistreeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
==========================================================
I want to check if there is an overlap in timeregistrations with the following sql ===============================
select a.*
from TimeRegistrations as a join TimeRegistrations as b on a.Id <> b.Id and b.StartDateTime < a.EndDateTime and b.EndDateTime > a.StartDateTime and a.RegistreeId = b.RegistreeId
order by a.RegistreeId, a.StartDateTime
================================
This query takes about 1 hour and if I also put indices on StartDateTime and EndDateTime the query still takes 15 minutes.
Is this normal, how can I make a significant boost? Is this even posiible?
Thanks beforehand and greetz
Coen Dunnink
Inspecting queries currently being executed in MySQL? (2 replies)
Hi Guys,
Is there a way to check what queries are currently being executed in MySQL?
I am interested in finding out simultaneous execution of queries. This includes the queries that are partially executed and waiting for I/O, or almost finished but waiting for a commit. However, this does not include queries that have not started yet.
I look forward to your reply.
Is there a way to check what queries are currently being executed in MySQL?
I am interested in finding out simultaneous execution of queries. This includes the queries that are partially executed and waiting for I/O, or almost finished but waiting for a commit. However, this does not include queries that have not started yet.
I look forward to your reply.
Worker-per-connection vs. Worker-per-request? (3 replies)
Hi Guys,
Does MySQL follow a Worker-per-connection architecture or Worker-per-request architecture?
1. Worker-per-connection architecture: In this architecture there is a one-to-one mapping between database server connections and database server workers. A worker is created once a connection is established and is responsible for servicing all requests from that connection.
2. Worker-per-request architecture: In this architecture there is one queue that is used to queue all database server requests from all connections. A worker dequeues a request from the request queue, processes the request and then goes back to process the next available request in the queue or block on an idle queue.
Many thanks.
Does MySQL follow a Worker-per-connection architecture or Worker-per-request architecture?
1. Worker-per-connection architecture: In this architecture there is a one-to-one mapping between database server connections and database server workers. A worker is created once a connection is established and is responsible for servicing all requests from that connection.
2. Worker-per-request architecture: In this architecture there is one queue that is used to queue all database server requests from all connections. A worker dequeues a request from the request queue, processes the request and then goes back to process the next available request in the queue or block on an idle queue.
Many thanks.
ENUM vs TINYINT for mutually exclusive values (2 replies)
I am considering using ENUM over TINYINT(1) as a datatype for a column that stores mutually exclusive values.
The column can have integer values like 1,2,3,4,5,6,etc., up to about 20. Each of these values stand for something (non-numerical) that is interpreted elsewhere in my application. Both datatypes, according to the manual, take only 1 byte per character with no overhead unless there are more than 255 members to the ENUM. ENUM seems store a value like "Mystring" as 1, but allow it to be presented as "Mystring" in the database fields.
Are there any costs here that I am not considering in using ENUM over TINYINT(1)? Please let me know if I might encounter problems later on taking this approach. I plan to perform many calculations on these fields, so cpu overhead matters as well.
The column can have integer values like 1,2,3,4,5,6,etc., up to about 20. Each of these values stand for something (non-numerical) that is interpreted elsewhere in my application. Both datatypes, according to the manual, take only 1 byte per character with no overhead unless there are more than 255 members to the ENUM. ENUM seems store a value like "Mystring" as 1, but allow it to be presented as "Mystring" in the database fields.
Are there any costs here that I am not considering in using ENUM over TINYINT(1)? Please let me know if I might encounter problems later on taking this approach. I plan to perform many calculations on these fields, so cpu overhead matters as well.
Union in sub query and Left join vs. for each union left join (1 reply)
Hello all,
which statement has a better performance
SELECT a.*, b.c_text
(
SELECT * FROM t_type_1 WHERE ....
UNION
SELECT * FROM t_type_2 WHERE ....
) a LEFT JOIN t_catalog b ON a.n_catalog_id = b.n_id
vs.
SELECT *, b.c_text
FROM t_type_1 a, LEFT JOIN t_catalog b ON a.n_catalog_id = b.n_id
WHERE ....
UNION
SELECT *, b.c_text
FROM t_type_2 a, LEFT JOIN t_catalog b ON a.n_catalog_id = b.n_id
WHERE ....
Thanks
Thomas
which statement has a better performance
SELECT a.*, b.c_text
(
SELECT * FROM t_type_1 WHERE ....
UNION
SELECT * FROM t_type_2 WHERE ....
) a LEFT JOIN t_catalog b ON a.n_catalog_id = b.n_id
vs.
SELECT *, b.c_text
FROM t_type_1 a, LEFT JOIN t_catalog b ON a.n_catalog_id = b.n_id
WHERE ....
UNION
SELECT *, b.c_text
FROM t_type_2 a, LEFT JOIN t_catalog b ON a.n_catalog_id = b.n_id
WHERE ....
Thanks
Thomas
union distinct vs or query (with order by and range) (1 reply)
Hi,
I have a table with a ANNOID, LOGINID, TYPE_NUM, and varchar TITLE.
I want to do a query that returns a range of annoids sorted by TITLE. That is, I might like the range from 1000 to 2000 (when sorting by TITLE) for a given query. However, the effective search is where LOGINID = :loginid OR TYPE_NUM = 1 OR TYPE_NUM = 2 typically, with ORDER BY TITLE.
I've been told that ORs are typically hard for the optimizer, even with best indices, and to therefore split into 3 queries and do a union distinct on the annoids.
That worked before, when I didn't have a huge result set because there was another limitation (by PAGENUM), and then I sorted the postings in the client. However, now the result set can be very big, (the entire forum), and I use paging, so I make a query with a range and and order by on TITLE.
What is my best strategy?
Andy
I have a table with a ANNOID, LOGINID, TYPE_NUM, and varchar TITLE.
I want to do a query that returns a range of annoids sorted by TITLE. That is, I might like the range from 1000 to 2000 (when sorting by TITLE) for a given query. However, the effective search is where LOGINID = :loginid OR TYPE_NUM = 1 OR TYPE_NUM = 2 typically, with ORDER BY TITLE.
I've been told that ORs are typically hard for the optimizer, even with best indices, and to therefore split into 3 queries and do a union distinct on the annoids.
That worked before, when I didn't have a huge result set because there was another limitation (by PAGENUM), and then I sorted the postings in the client. However, now the result set can be very big, (the entire forum), and I use paging, so I make a query with a range and and order by on TITLE.
What is my best strategy?
Andy
perfoemance difference in structured select vs long orred select (1 reply)
Hi All,
I have a question regarding a data mining application I'm developing.
I have a table of objects. Each object has multiple routes (or trajectories). Each route has multiple points (e.g. x. y. t). In short, my tables look like:
objects -> routes -> data points.
Here's the problem. I'd like to retrieve all the points for a single object. The simple SQL command will have the form of structured select:
select * from points_talble where
points_talble.route_id in (select route_id from routes_table where object_id = n));
This code works fine, but it's slow.
The other way of retrieving the points is in two steps:
1. retrive all route IDs from routes table to a vector.
2. retrive all points from points_table adding a long orred select:
select * from points_talble where route_id=1 ot route_id=2 or... (you get the point)
While the first (structured select) is simpler to implement, it's much slower then the 2nd, long orred select, by a factor of 100 times, give or take.
Note that both methods are yeilding the same results.
My only question is the huge performance difference.
Can anyone please provide an explanation?
Thanks a lot in advance,
Omer.
I have a question regarding a data mining application I'm developing.
I have a table of objects. Each object has multiple routes (or trajectories). Each route has multiple points (e.g. x. y. t). In short, my tables look like:
objects -> routes -> data points.
Here's the problem. I'd like to retrieve all the points for a single object. The simple SQL command will have the form of structured select:
select * from points_talble where
points_talble.route_id in (select route_id from routes_table where object_id = n));
This code works fine, but it's slow.
The other way of retrieving the points is in two steps:
1. retrive all route IDs from routes table to a vector.
2. retrive all points from points_table adding a long orred select:
select * from points_talble where route_id=1 ot route_id=2 or... (you get the point)
While the first (structured select) is simpler to implement, it's much slower then the 2nd, long orred select, by a factor of 100 times, give or take.
Note that both methods are yeilding the same results.
My only question is the huge performance difference.
Can anyone please provide an explanation?
Thanks a lot in advance,
Omer.
Mysql Table structure and storage engine for millions of records (1 reply)
Hello guys, are you ok?
I have to treat some tables, which will be stored millions of records (72mi approximated). The table structure is:
field ID (int - primary key)
field DID (int - index)
field CID (int)
This records will be queried always by the primary key, and i'm afraid if this can be slow. i know that the primary key queries are always the better option, but my queries will make some restrictions (like "WHERE id=x && did=y").
I've choose the InnoDB for my storage engine, because the table is highly dynamic, so record will be inserted and deleted (never updated) about 1 seconds. I imagine that the mysql processor will be very high in this operation, because de table access will very higly to.
The big question is: is this the best alternative for this structure of data? Can i use some alternative of storage engine for data?
I have to treat some tables, which will be stored millions of records (72mi approximated). The table structure is:
field ID (int - primary key)
field DID (int - index)
field CID (int)
This records will be queried always by the primary key, and i'm afraid if this can be slow. i know that the primary key queries are always the better option, but my queries will make some restrictions (like "WHERE id=x && did=y").
I've choose the InnoDB for my storage engine, because the table is highly dynamic, so record will be inserted and deleted (never updated) about 1 seconds. I imagine that the mysql processor will be very high in this operation, because de table access will very higly to.
The big question is: is this the best alternative for this structure of data? Can i use some alternative of storage engine for data?
mysqld.exe consumption extremely high (1 reply)
Hi Guys
Not sure if this is in the right place but I hope so. Recently I had a client get in some external developers to re code the front of their website however since this has happened I have seen a HUGE increase in the memory consumption for mysqld.exe on their server from 190,000K (when they still used the old code) to 1,082,000K with the new code.
The site doesn't have much new functionality but they have stopped using the PHP classes for DB Connections we have written and are using the standard php mysql connection functions instead on every page.
They have also started using TRIGGERS in the database which is something that wasn't done in the previous version of the site.
What is the best way to track down the cause of this sudden increase in memory usage? It is a windows 2003 server running IIS6, mysql 5.1 and PHP
The memory usage is now so high I am having to manually restart the mysql service to bring it back to an acceptable level as it is making the server unresponsive.
Thanks
Not sure if this is in the right place but I hope so. Recently I had a client get in some external developers to re code the front of their website however since this has happened I have seen a HUGE increase in the memory consumption for mysqld.exe on their server from 190,000K (when they still used the old code) to 1,082,000K with the new code.
The site doesn't have much new functionality but they have stopped using the PHP classes for DB Connections we have written and are using the standard php mysql connection functions instead on every page.
They have also started using TRIGGERS in the database which is something that wasn't done in the previous version of the site.
What is the best way to track down the cause of this sudden increase in memory usage? It is a windows 2003 server running IIS6, mysql 5.1 and PHP
The memory usage is now so high I am having to manually restart the mysql service to bring it back to an acceptable level as it is making the server unresponsive.
Thanks
MySQL 5.6: Optimizer Tracing (no replies)
MySQL 5.6: Optimizer Tracing
http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html
http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html
MySQL 5.1.49 - Optimal settings for memory usage (7 replies)
Hello community!
I would like to get some feedback/tips around my MySQL system setup.
Here should be a lot of experienced user - also with huge Databases.
May I can get some good hints to use the best results for my hardware.
My system:
debian 6.0.3 x86_64 (squeeze)
64 GB RAM
2x 8 Core AMD Opteron 6134
HD: 1TB Raid 1 on 3ware 9650SE-8LPML
-----------------------------------------
my.cnf:
# generic configuration options
large-pages
port = 3306
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /data/mysql
tmpdir = /tmp
#tmpdir = /dev/shm
connect_timeout = 600
character-set-server = utf8
collation-server = utf8_bin
language = /usr/share/mysql/english
log-warnings = 3
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 16384
max_allowed_packet = 512M
binlog_cache_size = 128M
max_heap_table_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 8M
thread_cache_size = 64
thread_concurrency = 16
query_cache_size = 64M
query_cache_limit = 4M
ft_min_word_len = 4
default-storage-engine = INNODB
thread_stack = 256K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 4G
log-bin=mysql-bin
binlog_format=row
sync_binlog=1
log_slave_updates
log-error=mysql-bin.err
slow_query_log=mysql-slow.log
long_query_time = 10
#*** MyISAM Specific options
key_buffer_size = 512M
read_buffer_size = 8M
read_rnd_buffer_size = 1024M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 4
myisam_recover
# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 15G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 120
[mysqldump]
quick
quote-names
max_allowed_packet = 1024M
[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
------------------------------------------------
/etc/security/limits.conf
* - nofile -1
mysql - memlock 67108864
@mysql - memlock 67108864
root - memlock 67108864
------------------------------------------------
/etc/sysctl.d/mysql.conf
# Huge page tables reservation for mysql
vm.nr_hugepages = 13300
vm.hugetlb_shm_group = 107
kernel.shmmax = 27917287424
kernel.shmall = 6815744
------------------------------------------------
I´d read a lot of HowTos and threads, but I´m still a bit unsure how to use best the available memory.
The databases are all INNODB (at least the big ones), are around 50 GB and have round about 60 million entries (and growing fast).
I hope someone has some good hints - my goal is to keep as much as possible in memory, to reduce disk-io.
Thanks and regards, Marc
I would like to get some feedback/tips around my MySQL system setup.
Here should be a lot of experienced user - also with huge Databases.
May I can get some good hints to use the best results for my hardware.
My system:
debian 6.0.3 x86_64 (squeeze)
64 GB RAM
2x 8 Core AMD Opteron 6134
HD: 1TB Raid 1 on 3ware 9650SE-8LPML
-----------------------------------------
my.cnf:
# generic configuration options
large-pages
port = 3306
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /data/mysql
tmpdir = /tmp
#tmpdir = /dev/shm
connect_timeout = 600
character-set-server = utf8
collation-server = utf8_bin
language = /usr/share/mysql/english
log-warnings = 3
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 16384
max_allowed_packet = 512M
binlog_cache_size = 128M
max_heap_table_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 8M
thread_cache_size = 64
thread_concurrency = 16
query_cache_size = 64M
query_cache_limit = 4M
ft_min_word_len = 4
default-storage-engine = INNODB
thread_stack = 256K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 4G
log-bin=mysql-bin
binlog_format=row
sync_binlog=1
log_slave_updates
log-error=mysql-bin.err
slow_query_log=mysql-slow.log
long_query_time = 10
#*** MyISAM Specific options
key_buffer_size = 512M
read_buffer_size = 8M
read_rnd_buffer_size = 1024M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 4
myisam_recover
# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 15G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 120
[mysqldump]
quick
quote-names
max_allowed_packet = 1024M
[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
------------------------------------------------
/etc/security/limits.conf
* - nofile -1
mysql - memlock 67108864
@mysql - memlock 67108864
root - memlock 67108864
------------------------------------------------
/etc/sysctl.d/mysql.conf
# Huge page tables reservation for mysql
vm.nr_hugepages = 13300
vm.hugetlb_shm_group = 107
kernel.shmmax = 27917287424
kernel.shmall = 6815744
------------------------------------------------
I´d read a lot of HowTos and threads, but I´m still a bit unsure how to use best the available memory.
The databases are all INNODB (at least the big ones), are around 50 GB and have round about 60 million entries (and growing fast).
I hope someone has some good hints - my goal is to keep as much as possible in memory, to reduce disk-io.
Thanks and regards, Marc
Long commits (1 reply)
Hi,
I am facing a problem in which I have long commits. Can you please give me some directions on how can I troubleshoot?
Thanks in advance
Panayotis
I am facing a problem in which I have long commits. Can you please give me some directions on how can I troubleshoot?
Thanks in advance
Panayotis
Using where; Using temporary; Using filesort - slow query (4 replies)
Here is the query I am running, just wondering why it might be using a temp table and filesort?
mysql> explain SELECT c.CircuitID, c.circuitname, cu.`Customer Name` as customer_name, cu.CustomerID FROM circuits c INNER JOIN customers cu ON c.customerID = cu.CustomerID LEFT JOIN circuit_ring_assoc cra ON cra.circuit_id = c.CircuitID WHERE c.StateID = 5 AND cra.circuit_id IS NULL ORDER BY c.circuitname; +----+-------------+-------+--------+--------------------+---------+---------+-------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+--------------------+---------+---------+-------------------------+------+----------------------------------------------+ | 1 | SIMPLE | c | ref | customerID,StateID | StateID | 3 | const | 1547 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | cu | eq_ref | PRIMARY | PRIMARY | 4 | customerdb.c.customerID | 1 | | | 1 | SIMPLE | cra | index | NULL | PRIMARY | 8 | NULL | 7052 | Using where; Using index; Not exists | +----+-------------+-------+--------+--------------------+---------+---------+-------------------------+------+----------------------------------------------+ Thanks!
mysql> explain SELECT c.CircuitID, c.circuitname, cu.`Customer Name` as customer_name, cu.CustomerID FROM circuits c INNER JOIN customers cu ON c.customerID = cu.CustomerID LEFT JOIN circuit_ring_assoc cra ON cra.circuit_id = c.CircuitID WHERE c.StateID = 5 AND cra.circuit_id IS NULL ORDER BY c.circuitname; +----+-------------+-------+--------+--------------------+---------+---------+-------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+--------------------+---------+---------+-------------------------+------+----------------------------------------------+ | 1 | SIMPLE | c | ref | customerID,StateID | StateID | 3 | const | 1547 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | cu | eq_ref | PRIMARY | PRIMARY | 4 | customerdb.c.customerID | 1 | | | 1 | SIMPLE | cra | index | NULL | PRIMARY | 8 | NULL | 7052 | Using where; Using index; Not exists | +----+-------------+-------+--------+--------------------+---------+---------+-------------------------+------+----------------------------------------------+ Thanks!
show profile cpu for query 1 shows cpu_user and cpu_system as null (1 reply)
Hi,
With profiler it is showing cpu_user as null.
Please find below MySQL and OS version info.
Kindly guide how to resolve this to get cpu_user info in figure instead of null.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.17-55 |
+-----------+
1 row in set (0.00 sec)
Linux Cent OS:Linux version 2.6.18-164.el5 (mockbuild@builder10.centos.org) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)) #1 SMP Thu Sep 3 03:28:30 EDT 2009
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
| 1043 |
+----------+
1 row in set (0.01 sec)
show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000067 | NULL | NULL |
| checking permissions | 0.000009 | NULL | NULL |
Thanks.
With profiler it is showing cpu_user as null.
Please find below MySQL and OS version info.
Kindly guide how to resolve this to get cpu_user info in figure instead of null.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.17-55 |
+-----------+
1 row in set (0.00 sec)
Linux Cent OS:Linux version 2.6.18-164.el5 (mockbuild@builder10.centos.org) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)) #1 SMP Thu Sep 3 03:28:30 EDT 2009
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
| 1043 |
+----------+
1 row in set (0.01 sec)
show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000067 | NULL | NULL |
| checking permissions | 0.000009 | NULL | NULL |
Thanks.
Watch St Johnstone v Rangers Live Football Match Saturday 14 Jan 2012 (no replies)
As seen, now everyone is furious about football matches. People try their level best to get the tickets to watch the match live but not everyone succeed. Here is your chance to watch live the great game.
All teams are trying to take top position in the football league matches. Both teams are prepared well for this league match. Let’s see who will win this match.
<strong>Watch Now St Johnstone v Rangers</strong>
ENJOY @ http://entertainment.igrcs.com/?p=239
ENJOY @ http://entertainment.igrcs.com/?p=239
ENJOY @ http://entertainment.igrcs.com/?p=239
Watch St Johnstone v Rangers, live St Johnstone v Rangers, online St Johnstone v Rangers, free St Johnstone v Rangers, live St Johnstone v Rangers, highlights St Johnstone v Rangers, scores St Johnstone v Rangers,preview St Johnstone v Rangers, St Johnstone v Rangers
All teams are trying to take top position in the football league matches. Both teams are prepared well for this league match. Let’s see who will win this match.
<strong>Watch Now St Johnstone v Rangers</strong>
ENJOY @ http://entertainment.igrcs.com/?p=239
ENJOY @ http://entertainment.igrcs.com/?p=239
ENJOY @ http://entertainment.igrcs.com/?p=239
Watch St Johnstone v Rangers, live St Johnstone v Rangers, online St Johnstone v Rangers, free St Johnstone v Rangers, live St Johnstone v Rangers, highlights St Johnstone v Rangers, scores St Johnstone v Rangers,preview St Johnstone v Rangers, St Johnstone v Rangers