批提交mysql
单线程的批提交
nohup python fromRedisoToMysqlSingleThreadOneConnBatchInsert.py 100 10.24.192.192 > 100-Single.3434.nohup
cursor.executemany(sql['sql'], sql['args'])
def mysql_write(conn, sql, mysql_key=mysql_key, executemany=False, charset_='utf8'):
try:
cursor = conn.cursor()
if executemany:
cursor.executemany(sql['sql'], sql['args'])
else:
cursor.execute(sql)
conn.commit()
cursor.close()
return True
except Exception as e:
print(sql[0:120])
print(e)
try:
conn.close()
except:
pass
return False
import sys
batch_step = int(sys.argv[1])
REDIS_HOST, REDIS_PORT, REDIS_PASSWORD, REDIS_DB = '127.0.0.1', 6379, 'testu', 0
REDIS_HOST = sys.argv[2]
conn = pymysql.connect(host=h, port=pt, user=u, passwd=p, db=db, charset=charset_)
rds = Redis(host=REDIS_HOST, port=REDIS_PORT, password=REDIS_PASSWORD, db=REDIS_DB)
q_executemany, a_executemany = {}, {}
q_executemany['sql'], a_executemany['sql'] = sql_q_executemany, sql_a_executemany
q_executemany['args'], a_executemany['args'] = [], []
while True:
try:
format_s = rds.spop(redis_info_tab).decode('utf-8')
d = json.loads(format_s)
dbid, q, a = d['dbid'], d['q'], d['a']
tm = int(time.time())
id, title, number, created, content = dbid, q[0:50], len(d['a']), tm, ''
q_executemany['args'].append((id, title, number, created, content))
if len(q_executemany['args']) == batch_step:
r = mysql_write(conn, q_executemany, executemany=True)
print(r)
q_executemany['args'] = []
for k in d['a']:
qid, content, created = dbid, d['a'][k], tm
a_executemany['args'].append((qid, content, created))
if len(a_executemany['args']) >= batch_step:
r = mysql_write(conn, a_executemany, executemany=True)
print(r)
a_executemany['args'] = []
except Exception as e:
print(e)
show global status where Variable_name in('com_select','com_insert','com_delete','com_update')
1
Com_delete
33771
2
Com_insert
274325
3
Com_select
4777895
4
Com_update
1218504
1
Com_delete
33773
2
Com_insert
274368
3
Com_select
4778305
4
Com_update
1218556
Stam He的博客 » 【转】MySQL TPS 和 QPS 的统计和IOPS，附一个python脚本 http://www.stamhe.com/?p=1134
show global status
Variable_name Value
Aborted_clients 5693344
Aborted_connects 137664
Binlog_cache_disk_use 7686
Binlog_cache_use 13983742
Binlog_stmt_cache_disk_use 0
Binlog_stmt_cache_use 44308
Bytes_received 41504207372
Bytes_sent 36163064325
Com_admin_commands 45151
Com_assign_to_keycache 0
Com_alter_db 0
Com_alter_db_upgrade 0
Com_alter_event 0
Com_alter_function 0
Com_alter_instance 0
Com_alter_procedure 0
Com_alter_server 0
Com_alter_table 17
Com_alter_tablespace 0
Com_alter_user 0
Com_analyze 0
Com_begin 57
Com_binlog 0
Com_call_procedure 0
Com_change_db 5217
Com_change_master 1
Com_change_repl_filter 0
Com_check 0
Com_checksum 0
Com_commit 13927534
Com_create_db 1
Com_create_event 0
Com_create_function 0
Com_create_index 0
Com_create_procedure 0
Com_create_server 0
Com_create_table 44242
Com_create_trigger 6
Com_create_udf 0
Com_create_user 1
Com_create_view 3
Com_dealloc_sql 0
Com_delete 44295
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 3
Com_drop_trigger 6
Com_drop_user 0
Com_drop_view 0
Com_empty_query 0
Com_execute_sql 0
Com_explain_other 0
Com_flush 70912
Com_get_diagnostics 0
Com_grant 29
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 13984866
Com_insert_select 3
Com_install_plugin 0
Com_kill 56
Com_load 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 36
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 1
Com_resignal 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 1
Com_rollback_to_savepoint 0
Com_savepoint 0
Com_select 475777
Com_set_option 7748370
Com_signal 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 3
Com_show_collations 3
Com_show_create_db 0
Com_show_create_event 0
Com_show_create_func 0
Com_show_create_proc 0
Com_show_create_table 96
Com_show_create_trigger 0
Com_show_databases 83
Com_show_engine_logs 0
Com_show_engine_mutex 0
Com_show_engine_status 100
Com_show_events 0
Com_show_errors 0
Com_show_fields 3850
Com_show_function_code 0
Com_show_function_status 38
Com_show_grants 0
Com_show_keys 9
Com_show_master_status 1
Com_show_open_tables 0
Com_show_plugins 0
Com_show_privileges 0
Com_show_procedure_code 0
Com_show_procedure_status 38
Com_show_processlist 4999
Com_show_profile 0
Com_show_profiles 0
Com_show_relaylog_events 0
Com_show_slave_hosts 0
Com_show_slave_status 183055
Com_show_sql_filters 0
Com_show_status 10021
Com_show_storage_engines 25
Com_show_table_status 285
Com_show_tables 284
Com_show_triggers 28
Com_show_variables 596102
Com_show_warnings 274372
Com_show_create_user 0
Com_shutdown 0
Com_slave_start 2
Com_slave_stop 2
Com_group_replication_start 0
Com_group_replication_stop 0
Com_stmt_execute 43362
Com_stmt_close 43353
Com_stmt_fetch 0
Com_stmt_prepare 43362
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 127
Com_uninstall_plugin 0
Com_unlock_tables 0
Com_update 3675
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
Com_show_slave_lag 0
Com_rds_show_actual_processlist 0
Com_rds_inner_backup_begin 0
Com_rds_inner_backup_end 0
Com_sync_binary_logs 0
Com_show_proxy_protocol_ips 0
Com_stmt_reprepare 0
Connection_errors_accept 0
Connection_errors_internal 0
Connection_errors_max_connections 137379
Connection_errors_peer_address 0
Connection_errors_select 0
Connection_errors_tcpwrap 0
Connections 7247429
Created_tmp_disk_tables 5107
Created_tmp_files 4526
Created_tmp_tables 616109
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 55914312
Handler_delete 4888
Handler_discover 0
Handler_external_lock 29522621
Handler_mrr_init 0
Handler_prepare 55822614
Handler_read_first 9001
Handler_read_key 153025
Handler_read_last 189
Handler_read_next 448500020
Handler_read_prev 173123376
Handler_read_rnd 733
Handler_read_rnd_next 2661245131
Handler_rollback 4781
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 26997418
Handler_write 382510776
Innodb_buffer_pool_dump_status Dumping of buffer pool not started
Innodb_buffer_pool_load_status Buffer pool(s) load completed at 181211 9:37:38
Innodb_buffer_pool_resize_status
Innodb_buffer_pool_pages_data 40949
Innodb_buffer_pool_bytes_data 670908416
Innodb_buffer_pool_pages_dirty 36314
Innodb_buffer_pool_bytes_dirty 594968576
Innodb_buffer_pool_pages_flushed 18647453
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_misc 11
Innodb_buffer_pool_pages_total 40960
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 1275836
Innodb_buffer_pool_read_ahead_evicted 447
Innodb_buffer_pool_read_requests 4041604543
Innodb_buffer_pool_reads 8218110
Innodb_buffer_pool_wait_free 1938743
Innodb_buffer_pool_write_requests 757461961
Innodb_data_fsyncs 17245576
Innodb_data_pending_fsyncs 5
Innodb_data_pending_reads 4
Innodb_data_pending_writes 6
Innodb_data_read 217377083904
Innodb_data_reads 13266371
Innodb_data_writes 35987605
Innodb_data_written 727385224192
Innodb_dblwr_pages_written 20343171
Innodb_dblwr_writes 1977481
Innodb_log_waits 0
Innodb_log_write_requests 103799159
Innodb_log_writes 13402885
Innodb_os_log_fsyncs 13505798
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 60770642432
Innodb_page_size 16384
Innodb_pages_created 1613832
Innodb_pages_read 13267531
Innodb_pages_written 20344711
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 1121313
Innodb_row_lock_time_avg 21563
Innodb_row_lock_time_max 51656
Innodb_row_lock_waits 52
Innodb_rows_deleted 4946
Innodb_rows_inserted 51991632
Innodb_rows_read 2620885976
Innodb_rows_updated 26995095
Innodb_num_open_files 39
Innodb_truncated_status_writes 0
Innodb_available_undo_logs 128
Key_blocks_not_flushed 0
Key_blocks_unused 13396
Key_blocks_used 0
Key_read_requests 0
Key_reads 0
Key_write_requests 0
Key_writes 0
Locked_connects 0
Max_execution_time_exceeded 0
Max_execution_time_set 0
Max_execution_time_set_failed 0
Max_used_connections 383
Max_used_connections_time 2018-12-18 19:41:18
Not_flushed_delayed_rows 0
Ongoing_anonymous_transaction_count 0
Open_files 24
Open_streams 0
Open_table_definitions 227
Open_tables 345
Opened_files 9909
Opened_table_definitions 525
Opened_tables 814
Performance_schema_accounts_lost 0
Performance_schema_cond_classes_lost 0
Performance_schema_cond_instances_lost 0
Performance_schema_digest_lost 0
Performance_schema_file_classes_lost 0
Performance_schema_file_handles_lost 0
Performance_schema_file_instances_lost 0
Performance_schema_hosts_lost 0
Performance_schema_index_stat_lost 0
Performance_schema_locker_lost 0
Performance_schema_memory_classes_lost 0
Performance_schema_metadata_lock_lost 0
Performance_schema_mutex_classes_lost 0
Performance_schema_mutex_instances_lost 0
Performance_schema_nested_statement_lost 0
Performance_schema_prepared_statements_lost 0
Performance_schema_program_lost 0
Performance_schema_rwlock_classes_lost 0
Performance_schema_rwlock_instances_lost 0
Performance_schema_session_connect_attrs_lost 0
Performance_schema_socket_classes_lost 0
Performance_schema_socket_instances_lost 0
Performance_schema_stage_classes_lost 0
Performance_schema_statement_classes_lost 0
Performance_schema_table_handles_lost 0
Performance_schema_table_instances_lost 0
Performance_schema_table_lock_stat_lost 0
Performance_schema_thread_classes_lost 0
Performance_schema_thread_instances_lost 0
Performance_schema_users_lost 0
Prepared_stmt_count 0
Qcache_free_blocks 1
Qcache_free_memory 3128864
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Qcache_not_cached 475776
Qcache_queries_in_cache 0
Qcache_total_blocks 1
Queries 39103107
Questions 38971241
Rpl_semi_sync_master_clients 1
Rpl_semi_sync_master_net_avg_wait_time 0
Rpl_semi_sync_master_net_wait_time 0
Rpl_semi_sync_master_net_waits 13188049
Rpl_semi_sync_master_no_times 9
Rpl_semi_sync_master_no_tx 35908
Rpl_semi_sync_master_status ON
Rpl_semi_sync_master_timefunc_failures 0
Rpl_semi_sync_master_tx_avg_wait_time 305
Rpl_semi_sync_master_tx_wait_time 3940560635
Rpl_semi_sync_master_tx_waits 12890354
Rpl_semi_sync_master_wait_pos_backtraverse 0
Rpl_semi_sync_master_wait_sessions 0
Rpl_semi_sync_master_yes_tx 13022290
Rpl_semi_sync_slave_status OFF
Select_full_join 358
Select_full_range_join 10
Select_range 25
Select_range_check 4
Select_scan 1223381
Slave_open_temp_tables 0
Slow_launch_threads 0
Slow_queries 74394
Sort_merge_passes 0
Sort_range 0
Sort_rows 733
Sort_scan 397
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_server_not_after
Ssl_server_not_before
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 676093
Table_locks_waited 4905
Table_open_cache_hits 14845146
Table_open_cache_misses 805
Table_open_cache_overflows 0
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 67
Threads_connected 172
Threads_created 911
Threads_running 35
Uptime 709492
Uptime_since_flush_status 709492
show global status where Variable_name in('Com_commit','com_insert','com_delete','com_update')
Variable_name Value
Com_commit 13991200
Com_delete 44439
Com_insert 14048689
Com_update 3922
MySQL 事务 | 菜鸟教程 http://www.runoob.com/mysql/mysql-transaction.html
MySQL 事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
-
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
-
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
事务控制语句：
-
BEGIN或START TRANSACTION；显式地开启一个事务；
-
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
-
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
-
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
-
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
-
ROLLBACK TO identifier；把事务回滚到标记点；
-
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
事务测试
mysql> use RUNOOB; Database changed mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec) mysql> select * from runoob_transaction_test; Empty set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) mysql> insert into runoob_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec) mysql> select * from runoob_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) mysql> rollback; # 回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql>
PHP中使用事务实例
MySQL ORDER BY 测试：
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'RUNOOB' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行
mysqli_begin_transaction($conn); // 开始事务定义
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
mysqli_query($conn, "ROLLBACK"); // 判断当执行失败时回滚
}
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)"))
{
mysqli_query($conn, "ROLLBACK"); // 判断执行失败时回滚
}
mysqli_commit($conn); //执行事务
mysqli_close($conn);
?>
查全局变量
SHOW GLOBAL VARIABLES;
auto_increment_increment 1
auto_increment_offset 1
autocommit ON
SHOW GLOBAL VARIABLES LIKE "%user%";
maintain_user_list aurora,replicator,aliyun_root
max_user_connections 832
| 规格族:通用型 | 数据库类型:MySQL 5.7 | CPU:1 核 |
| 数据库内存:1024MB | 最大IOPS:600 | 最大连接数:300 |
832的统计链接是包含了阿里云内部链接的数量了。300只是针对客户的限制了。
7257190 aurora 11.192.114.100:58448 Sleep 5028
7257191 aurora 11.218.166.161:48102 Sleep 5025
7257192 aurora 11.192.114.100:58454 Sleep 5025
7257196 aurora 11.192.114.100:58461 Sleep 5015
7257198 aurora 11.218.166.161:48112 information_schema Sleep 5018
7257200 aurora 11.218.166.161:48116 Sleep 5016
7257203 aurora 11.218.166.161:48120 Sleep 5015
7257204 aurora 11.218.166.161:48122 Sleep 5015
7257213 aurora 11.192.114.100:58480 Sleep 4836
7257217 aurora 11.218.166.161:48142 Sleep 4333
7257225 aurora 11.192.114.100:58500 Sleep 5000
aurora
这个不是具体会话对应 某个功能的，至后端管控平台会建立长连接后，举例：您在控制台创建账户，设置密码 等 都是管控平台通过上面的连接数据库做设置的，所以这个您可以忽略。
SHOW GLOBAL VARIABLES LIKE "%SHOW%";
Variable_name Value
opt_rds_enable_show_slave_lag ON
show_compatibility_56 OFF
show_ipk_info OFF
show_ipk_user_list
show_old_temporals OFF
skip_show_database OFF
sql_quote_show_create ON
本实例
SHOW GLOBAL VARIABLES LIKE "%VERSION%";
Variable_name Value
innodb_version 5.7.20
protocol_version 10
rds_audit_log_version MYSQL_V1
rds_version 20
slave_type_conversions
tls_version TLSv1,TLSv1.1,TLSv1.2
version 5.7.20-log
version_comment Source distribution
version_compile_machine x86_64
version_compile_os Linux
MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server System Variables https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56
The INFORMATION_SCHEMA has tables that contain system and status variable information (see Section 24.11, “The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables”, and Section 24.10, “The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables”). As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 25.12.13, “Performance Schema System Variable Tables”, and Section 25.12.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.