MySQL业务分析小记
来源：互联网 发布：文明6好玩吗 知乎 编辑：程序博客网 时间：2024/05/16 09:14
作为一名不称职的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%。我们来看看数据库的请求峰值变化：
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_passwordport = 3306socket = /tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port = 3306socket = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 1Mtable_open_cache = 512sort_buffer_size = 2M#net_buffer_length = 8Kread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64M#default-character-set=utf8max_connections = 1000thread_cache_size = 8query_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_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 replicationlog-bin=/data/mysql/binlog/mysql-bin.logbinlog-do-db=xx# binary logging format - mixed recommendedbinlog_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 omittedserver-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 = 16Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50sync_binlog = 1[mysqldump]quickmax_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 = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout#MM settinglog-slave-updatesauto_increment_offset = 1auto_increment_increment = 2replicate-do-db = xx
0 0
- MySQL业务分析小记
- 业务分析
- mysql 小记
- mysql小记
- mysql小记
- mysql 小记
- mysql小记
- mysql小记
- mysql小记
- mysql小记
- mysql小记
- mysql 小记
- mysql执行sql语句优化分析命令explain命令小记
- mysql执行sql语句优化分析命令explain命令小记
- MySQL存储引擎分析及业务使用场景
- 做好业务分析
- PushMail业务分析
- 业务分析建模
- Linux内核的early_param原理追踪
- IOS高级教程1:处理1000张图片的内存优化
- JDK动态代理实现原理
- 传值与传址的区别与优缺点
- java动态代理原理及简单模拟
- MySQL业务分析小记
- 验证码之父：路易斯·范·安
- coreseek-4.1-win32 安装
- 垃圾回收GC:.Net自动内存管理 上(一)内存分配
- poj 2718 Smallest Difference
- jpeglib库实现MJPEG图像的压缩与解压缩
- 机器学习算法与Python实践之（四）支持向量机（SVM）实现
- S5PV210 金手指接口核心板Sate210-F 配套开发板全部硬件功能测试通过
- 闲话iBatis
