作为一名不称职的DBA，生产环境MySQL缺少必要的评估和测试，跌跌撞撞的跑了快一年，还好没捅出啥大娄子。马上2014了，硬着头皮梳理下业务。
1、先搞清楚访问模型，从请求峰值入手
拿数据：mysqladmin extended-status --relative --sleep=1 | grep -E "Queries|Com_select|Com_insert|Com_update" > mysqladmin_log_20131105.log
取峰值：grep -w "Queries" mysqladmin_log_20131105.log | awk '{print $4}' | sort -nr | head -n 20
11月5日到12月10日，并发在线峰值从7500增长到17200，增长率为230%。我们来看看数据库的请求峰值变化：
| 日期 | Queries峰值 | Com_update峰值 | Com_select峰值 | Com_insert峰值 |
|---|---|---|---|---|
| 11月5日 | 10822 | 400 | 1043 | 347 |
| 12月10日 | 34516 | 488 | 1698 | 126 |
2、接下来，看看全局性能指标
[root@DB_0_105 ~]# mysqladmin extended-status
+------------------------------------------+----------------+
| Variable_name | Value |
+------------------------------------------+----------------+
| Aborted_clients | 3065 |由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
| Aborted_connects | 73 |尝试已经失败的MySQL服务器的连接的次数。
| Binlog_cache_disk_use | 1528 |
| Binlog_cache_use | 429001965 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 2 |
| Bytes_received | 299890929055 |
| Bytes_sent | 4194889794608 |
| Com_admin_commands | 2733 |
| 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 | 159 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 91213249 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 145406695 |
| Com_change_master | 2 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 91039580 |
| Com_create_db | 3 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 296 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 1 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 362331 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 2 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 190 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 5 |
| Com_grant | 10 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 25454587 |
| Com_insert_select | 667 |
| Com_install_plugin | 0 |
| Com_kill | 102 |
| Com_load | 611 |
| Com_lock_tables | 64 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 90 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 4 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 173673 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 740581608 |
| Com_set_option | 583975411 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 9 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| 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 | 384 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 20 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 1 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 2874 |
| Com_show_function_status | 0 |
| Com_show_grants | 4 |
| Com_show_keys | 0 |
| Com_show_master_status | 17 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 4 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 893 |
| Com_show_status | 70262 |
| Com_show_storage_engines | 1 |
| Com_show_table_status | 392 |
| Com_show_tables | 132 |
| Com_show_triggers | 377 |
| Com_show_variables | 35 |
| Com_show_warnings | 0 |
| Com_slave_start | 5 |
| Com_slave_stop | 5 |
| Com_stmt_close | 14553297 |
| Com_stmt_execute | 14553300 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 14553417 |
| Com_stmt_reprepare | 3 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 319 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 67 |
| Com_update | 499361904 |
| Com_update_multi | 6311 |
| 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 | 143508276 |试图连接MySQL服务器的次数。
| Created_tmp_disk_tables | 1426 |
| Created_tmp_files | 165 |
| Created_tmp_tables | 24253774 |当执行语句时，已经被创造了的隐含临时表的数量。
| Delayed_errors | 0 |用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
| Delayed_insert_threads | 0 |正在使用的延迟插入处理器线程的数量。
| Delayed_writes | 0 |用INSERT DELAYED写入的行数。
| Flush_commands | 6 |执行FLUSH命令的次数。
| Handler_commit | 1972646787 |
| Handler_delete | 17324634 |请求从一张表中删除行的次数。
| Handler_discover | 0 |
| Handler_prepare | 1224889882 |
| Handler_read_first | 6901192 |请求读入表中第一行的次数。
| Handler_read_key | 6711643122 |请求数字基于键读行。
| Handler_read_last | 331666 |
| Handler_read_next | 296933019175 |请求读入基于一个键的一行的次数。
| Handler_read_prev | 1731576 |
| Handler_read_rnd | 141857756 |请求读入基于一个固定位置的一行的次数。
| Handler_read_rnd_next | 11983908820 |
| Handler_rollback | 17090 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 557424750 |请求更新表中一行的次数。
| Handler_write | 11004290957 |请求向表中插入一行的次数。
| Innodb_buffer_pool_pages_data | 7985 |
| Innodb_buffer_pool_pages_dirty | 772 |
| Innodb_buffer_pool_pages_flushed | 315861465 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_misc | 206 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 5861230 |
| Innodb_buffer_pool_read_ahead_evicted | 625111450 |
| Innodb_buffer_pool_read_requests | 94999741947 |
| Innodb_buffer_pool_reads | 1951147247 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 4033719738 |
| Innodb_data_fsyncs | 860497549 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 32131587035136 |
| Innodb_data_reads | 1954413500 |
| Innodb_data_writes | 1116113655 |
| Innodb_data_written | 11027028612096 |
| Innodb_dblwr_pages_written | 315861465 |
| Innodb_dblwr_writes | 4848990 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 481632704 |
| Innodb_log_writes | 850049744 |
| Innodb_os_log_fsyncs | 850820743 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 676483463168 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 8118361 |
| Innodb_pages_read | 1961163018 |
| Innodb_pages_written | 315861465 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 75549907 |
| Innodb_row_lock_time_avg | 1219 |
| Innodb_row_lock_time_max | 51934 |
| Innodb_row_lock_waits | 61975 |
| Innodb_rows_deleted | 17324634 |
| Innodb_rows_inserted | 64337514 |
| Innodb_rows_read | 298217554065 |
| Innodb_rows_updated | 557421267 |
| Innodb_truncated_status_writes | 0 |
| Key_blocks_not_flushed | 0 |Key_* 变量包含了 MyISAM 索引键缓冲区的度量值和计数器。
| Key_blocks_unused | 319666 |
| Key_blocks_used | 32583 |用于关键字缓存的块的数量。
| Key_read_requests | 181723060 |请求从缓存读入一个键值的次数。
| Key_reads | 25 |从磁盘物理读入一个键值的次数。
| Key_write_requests | 47785611 |请求将一个关键字块写入缓存次数。
| Key_writes | 13 |将一个键值块物理写入磁盘的次数。
| Last_query_cost | 0.000000 |显示查询计划在最近一次执行查询时的开销。
| Max_used_connections | 1001 |同时使用的连接的最大数目。
| Not_flushed_delayed_rows | 0 |在INSERT DELAY队列中等待写入的行的数量。
| Open_files | 12 |打开文件的数量。
| Open_streams | 0 |打开流的数量(主要用于日志记载）
| Open_table_definitions | 170 |
| Open_tables | 512 |打开表的数量。
| Opened_files | 13105 |
| Opened_table_definitions | 1246 |
| Opened_tables | 265761068 |已经打开的表的数量。
| 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 | 5545 |可以通过 Qcache_* 状态变量来检查查询缓存。如果处于性能期间，查询都依赖于查询缓存
| Qcache_free_memory | 18564600 |
| Qcache_hits | 62542273 |
| Qcache_inserts | 112451439 |
| Qcache_lowmem_prunes | 78489256 |
| Qcache_not_cached | 628079580 |
| Qcache_queries_in_cache | 14617 |
| Qcache_total_blocks | 34784 |
| Queries | 2412810131 |服务器端实际执行的数量。
| Questions | 2383701781 |客户端发出请求的数量。Questions = Com_* + Qcache_hits
| Rpl_status | AUTH_MASTER |
| Select_full_join | 3514 |显示一个交叉联接，或者一个在表里没有任何标准能匹配到行的联接。开销巨大。
| Select_full_range_join | 2 |一种联接的数目，它使用表 n 里的一个值去获取表 n+1 里某个引用索引范围内的行。
| Select_range | 10195699 |一种联接的数据，该联接在第一个表的索引的指定范围内做扫描。
| Select_range_check | 0 |一种联接的数据，它会为表 n 里的每一行，在表 n+1 里对索引重新估值，看看哪个开销最廉价。
| Select_scan | 29684980 |对第一个表作全表扫描的联接数据。如果第一个表里的每一行都被加入了联接的话，它就不会出错。
| Slave_heartbeat_period | 1800.000 |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | ON |
| Slow_launch_threads | 0 |
| Slow_queries | 2045 |要花超过long_query_time时间的查询数量。
| Sort_merge_passes | 0 |
| Sort_range | 61516 |
| Sort_rows | 68771006 |
| Sort_scan | 982628 |
| Table_locks_immediate | 2367829897 |有多少个锁立即被授予。
| Table_locks_waited | 5 |有多少个锁需要等待。要知道显示出来的仅仅是服务器级锁的统计信息，没包含存储引擎的。
| Tc_log_max_pages_used | 0 |记录该服务器被用作与 XA 事务协调器的次数。更多细节请查看『分布式（XA）事务』。
| Tc_log_page_size | 0 |记录该服务器被用作与 XA 事务协调器的次数。
| Tc_log_page_waits | 9 |记录该服务器被用作与 XA 事务协调器的次数。
| Threads_cached | 7 |
| Threads_connected | 82 |当前打开的连接的数量。
| Threads_created | 2088075 |
| Threads_running | 3 |不在睡眠的线程数量。
| Uptime | 7787806 |系统已经运行了多长时间，单位s。
| Uptime_since_flush_status | 7787806 |
+------------------------------------------+----------------+http://blog.sina.com.cn/s/blog_66b600260100i8pf.html
http://www.livingelsewhere.net/life/note/view/12
【关于Questions和Queries的区别】
在执行存储过程中，不论这个存储过程有多少条语句，Questions都只会加1。而Queries会加实际的查询数目。
FLUSH STATUS会将Questions计数器清0，但Queries不会，重启服务后，两者都会清0。
实际上从原理上来说：Questions 统计的是客户端发出请求的数量，而Queries是服务器端实际执行的数量。
这两个值都不仅仅是包括select,insert等数据操纵语句，如 set names utf8这样的语句也会使计数器加1。
3、高可用是通过MM实现，M1的配置：
[root@DB_0_105 ~]# cat /etc/my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
#net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
#default-character-set=utf8
max_connections = 1000
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=/data/mysql/binlog/mysql-bin.log
binlog-do-db=xx
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 2G
#innodb_additional_mem_pool_size = 64M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 512M
#innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
sync_binlog = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
#MM setting
log-slave-updates
auto_increment_offset = 1
auto_increment_increment = 2
replicate-do-db = xx
107
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
