After long and useful discussion with Mr. Rick James about how to change to innoDb , and after one week of changing a big table from myisam to innoDb i feel there is som improvements can be don, and i am seeking for community advice.
Please note :
Mysql 5.5
Mysql on SSD Drive.
Please Advice .
+++++++++++++++++++++++++++
Now let me show you what i have :
let's start with the table in question:
CREATE TABLE `status` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`md5id` char(32) CHARACTER SET ascii NOT NULL DEFAULT '0',
`ptype` char(1) CHARACTER SET ascii NOT NULL DEFAULT '0',
`media_id` varchar(50) CHARACTER SET ascii NOT NULL DEFAULT '0',
`media_url` varchar(100) CHARACTER SET ascii NOT NULL,
`media_photo` varchar(255) CHARACTER SET ascii NOT NULL,
`user_id` varchar(50) CHARACTER SET ascii NOT NULL DEFAULT '0',
`user_name` varchar(100) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`reason` text COLLATE utf8_swedish_ci,
PRIMARY KEY (`id`),
KEY `user_id_md5id` (`user_id`,`md5id`),
KEY `md5id_mediaid_ptype` (`md5id`,`media_id`,`ptype`),
KEY `md5id_ptype_date` (`md5id`,`ptype`,`date`),
KEY `md5_user_id_ptype` (`md5id`,`user_id`,`ptype`),
KEY `md5_id` (`md5id`)
) ENGINE=InnoDB AUTO_INCREMENT=11717643 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci
this table has now (11,929,637 record) and the size of the table is 10.1 GiB
the Slow Query i have is :
EXPLAIN SELECT * FROM status WHERE md5id='4d40640b8dcf37c5e2348e91677a8b8c' ORDER BY `id` DESC LIMIT 0, 18
1 SIMPLE status ref md5id_mediaid_ptype,md5id_ptype_date,md5_user_id_ptype,md5_id md5id_ptype_date 32 const 14436 Using where; Using filesort
and
EXPLAIN SELECT
count(1)
AS `counter`
FROM `status` `s1`
LEFT JOIN (`status`) on(((`status`.`user_id` = `s1`.`user_id`)
AND (`status`.`ptype` = 'u')
AND (`status`.`md5id` = 'e4011cdd03ab543b2268a6acce51e8b4')))
WHERE ((`s1`.`md5id` = 'e4011cdd03ab543b2268a6acce51e8b4')
AND (`s1`.`ptype` = 'f')
AND status.md5id IS NULL
AND (`s1`.`date` < ((now() - interval 2 DAY))))
1 SIMPLE s1 range md5id_mediaid_ptype,md5id_ptype_date,md5_user_id_ptype,md5_id md5id_ptype_date 37 1448 Using where
1 SIMPLE status ref user_id_md5id,md5id_mediaid_ptype,md5id_ptype_date,md5_user_id_ptype,md5_id user_id_md5id 84 myinstar_ranker.s1.user_id,const 1 Using where; Not exists
Now Let go to the server specification
Server Ram
root@server [/output]# free -h
total used free shared buffers cached
Mem: 62G 60G 1.8G 2.4G 770M 43G
-/+ buffers/cache: 16G 45G
Swap: 1.9G 1.0G 914M
root@server [/output]#
CPU
model name : Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz × 2
cpu MHz : 2000.022
cache size : 15360 KB
My.cnf
[mysqld]
table_definition_cache = 800
table_open_cache = 2000
open_files_limit= 50000
innodb_buffer_pool_instances=16
innodb_buffer_pool_size=30G
innodb_log_file_size=512M
innodb_file_per_table=1
innodb_stats_on_metadata = OFF
max_allowed_packet=268435456
myisam_sort_buffer_size=256M
sort_buffer_size=4M
thread_cache_size = 30
key_buffer_size=1G
default_storage_engine=InnoDB
join_buffer_size = 256k
tmp_table_size = 512M
max_heap_table_size = 512M
max_connections = 600
slow-query-log = 1
slow-query-log-file = /output/mysql-slow.log
long_query_time = 2
datadir=/disk3/mysql
query_cache_type = OFF
query_cache_size = 0
and here is mysql> SHOW GLOBAL STATUS;
mysql> SHOW GLOBAL STATUS;
+------------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------------+--------------+
| Aborted_clients | 5322 |
| Aborted_connects | 2566 |
| Bytes_received | 7604570459 |
| Bytes_sent | 565788289502 |
| Com_admin_commands | 8879 |
| Com_assign_to_keycache | 0 |
| Com_alter_table | 2622 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 347 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 101660 |
| Com_change_master | 0 |
| Com_check | 43 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_table | 1754 |
| Com_delete | 553932 |
| Com_empty_query | 9270 |
| Com_flush | 11 |
| Com_grant | 10 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1252280 |
| Com_insert_select | 2 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 570 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_replace | 6873 |
| 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 | 36080433 |
| Com_set_option | 1593014 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 3 |
| Com_show_charsets | 0 |
| Com_show_client_statistics | 0 |
| Com_show_collations | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 345 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 14384 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 167 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 345 |
| Com_show_errors | 0 |
| Com_show_fields | 80488 |
| Com_show_function_status | 467 |
| Com_show_grants | 2232 |
| Com_show_index_statistics | 0 |
| Com_show_keys | 139 |
| Com_show_master_status | 4 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 467 |
| Com_show_processlist | 755 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 1 |
| Com_show_slave_status | 5 |
| Com_show_status | 4 |
| Com_show_storage_engines | 1 |
| Com_show_table_statistics | 0 |
| Com_show_table_status | 29377 |
| Com_show_tables | 162321 |
| Com_show_thread_statistics | 0 |
| Com_show_triggers | 14328 |
| Com_show_user_statistics | 0 |
| Com_show_variables | 114 |
| Com_show_warnings | 14 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 3920 |
| Com_stmt_execute | 1080860 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 3969 |
| Com_stmt_reprepare | 31 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 70 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 936 |
| Com_update | 4294537 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 1460159 |
| Created_tmp_disk_tables | 231781 |
| Created_tmp_files | 57 |
| Created_tmp_tables | 485739 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 39574502 |
| Handler_delete | 613851 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 778114 |
| Handler_read_key | 658437844 |
| Handler_read_last | 52930 |
| Handler_read_next | 7223040955 |
| Handler_read_prev | 49538168 |
| Handler_read_rnd | 20375684 |
| Handler_read_rnd_next | 447639325 |
| Handler_rollback | 351 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 4470545 |
| Handler_write | 5101819 |
| Innodb_buffer_pool_pages_data | 585066 |
| Innodb_buffer_pool_bytes_data | 9585721344 |
| Innodb_buffer_pool_pages_dirty | 9090 |
| Innodb_buffer_pool_bytes_dirty | 148930560 |
| Innodb_buffer_pool_pages_flushed | 2088070 |
| Innodb_buffer_pool_pages_free | 1358946 |
| Innodb_buffer_pool_pages_misc | 22060 |
| Innodb_buffer_pool_pages_total | 1966072 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 38190 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 11822384418 |
| Innodb_buffer_pool_reads | 496564 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 42278196 |
| Innodb_data_fsyncs | 7608493 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 8976240640 |
| Innodb_data_reads | 551508 |
| Innodb_data_writes | 8892650 |
| Innodb_data_written | 79100924416 |
| Innodb_dblwr_pages_written | 2088070 |
| Innodb_dblwr_writes | 784069 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 17412042 |
| Innodb_log_writes | 6006220 |
| Innodb_os_log_fsyncs | 6019710 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 10672267776 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 37030 |
| Innodb_pages_read | 548036 |
| Innodb_pages_written | 2088070 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 26727 |
| Innodb_row_lock_time_avg | 13 |
| Innodb_row_lock_time_max | 1230 |
| Innodb_row_lock_waits | 1971 |
| Innodb_rows_deleted | 598933 |
| Innodb_rows_inserted | 1236489 |
| Innodb_rows_read | 7778473186 |
| Innodb_rows_updated | 4260391 |
| Innodb_truncated_status_writes | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 856377 |
| Key_blocks_used | 12274 |
| Key_read_requests | 87030658 |
| Key_reads | 58397 |
| Key_write_requests | 490822 |
| Key_writes | 64119 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 189 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 3041 |
| Open_streams | 0 |
| Open_table_definitions | 1971 |
| Open_tables | 2000 |
| Opened_files | 1494405 |
| Opened_table_definitions | 224527 |
| Opened_tables | 240175 |
| 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 | 2 |
| 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 | 45674940 |
| Questions | 45658201 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 24318 |
| Select_full_range_join | 271 |
| Select_range | 591127 |
| Select_range_check | 0 |
| Select_scan | 604167 |
| 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 | 72 |
| Sort_merge_passes | 0 |
| Sort_range | 734887 |
| Sort_rows | 28391767 |
| Sort_scan | 239143 |
| 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 | 43580698 |
| Table_locks_waited | 258 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 6 |
| Threads_connected | 111 |
| Threads_created | 18879 |
| Threads_running | 1 |
| Uptime | 134928 |
| Uptime_since_flush_status | 134928 |
+------------------------------------------+--------------+
317 rows in set (0.00 sec)
and here is mysqltuner.pl , report ,
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 13h 18m 42s (45M q [338.307 qps], 1M conn, TX: 524G, RX: 7G)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Physical Memory : 62.7G
[--] Max MySQL memory : 34.4G
[--] Other process memory: 7.0G
[--] Total buffers: 31.5G global + 4.9M per thread (600 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 32.4G (51.67% of installed RAM)
[OK] Maximum possible memory usage: 34.4G (54.79% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (68/45M)
[OK] Highest usage of available connections: 31% (189/600)
[OK] Aborted connections: 0.18% (2564/1454236)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 970K sorts)
[!!] Joins performed without indexes: 24282
[!!] Temporary tables created on disk: 47% (230K on disk / 483K total)
[OK] Thread cache hit rate: 98% (18K created / 1M connections)
[!!] Table cache hit rate: 1% (2K open / 169K opened)
[OK] Open file limit used: 5% (2K/50K)
[OK] Table locks acquired immediately: 99% (43M immediate / 43M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.5% (198M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/751.4M
[OK] Read Key buffer hit rate: 99.9% (86M cached / 57K reads)
[!!] Write Key buffer hit rate: 13.1% (489K cached / 64K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 30.0G/10.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.33333333333333 %): 512.0M * 2/30.0G should be equal 25%
[!!] InnoDB buffer pool instances: 16
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (11766329291 hits/ 11766825830 total)
[!!] InnoDB Write Log efficiency: 65.5% (11348015 hits/ 17326167 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5978152 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /disk3/mysql/server.maximagroup.net.err file
Control error line(s) into /disk3/mysql/server.maximagroup.net.err file
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `modsec`.`hits`; -- can free 2609.17892074585 MB
Total freed space after theses OPTIMIZE TABLE : 2609.17892074585 Mb
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Beware that open_files_limit (50000) variable
should be greater than table_open_cache (2000)
Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
join_buffer_size (> 256.0K, or always use indexes with joins)
table_open_cache (> 2000)
performance_schema = OFF disable PFS
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=15G) if possible.
innodb_buffer_pool_instances(=30)