android – 在后台服务中使用TYPE_STEP_COUNTER？

SensorManager sensorManager = (SensorManager)getSystemService(SENSOR_SERVICE);
Sensor stepCounter = mSensorManager.getDefaultSensor(Sensor.TYPE_STEP_COUNTER);
sensorManager.requestTriggerSensor(listener,stepCounter);

private TriggerEventListener listener = new TriggerEventListener(){
    @Override
    public void onTrigger(TriggerEvent event) {
        //handle step count here 
    }
}

AlarmManager alarmManager = (AlarmManager) context.getSystemService(Context.ALARM_SERVICE);
Intent i = new Intent(context,AlarmReceiver.class);
PendingIntent pending = PendingIntent.getbroadcast(context,i,PendingIntent.FLAG_CANCEL_CURRENT);
alarmManager.setInexactRepeating(AlarmManager.ELAPSED_REALTIME,AlarmManager.INTERVAL_HOUR,alarmIntent);

public class AlarmReceiver extends broadcastReceiver {

  @Override
  public void onReceive(Context context,Intent intent) {
    Intent service = new Intent(context,StepCountService.class);
    context.startService(service);
  }
}

asp.net-mvc-4 – 在MVC中的App_Start()文件夹中使用AuthConfig,BundleConfig,FilterConfig,RouteConfig和WebApiConfig

Bullet:MySQL 增强半同步参数 rpl_semi_sync_master_wait_point 值 AFTER_SYNC 和 AFTER_COMMIT 的对比实验

MySQL 5.7.22
启用增强半同步复制

MySQL 对该参数值的描述

Semisync can wait for slave ACKs at one of two points,
AFTER_SYNC or AFTER_COMMIT. AFTER_SYNC is the default value.
AFTER_SYNC means that semisynchronous replication waits just after the 
binary log file is flushed, but before the engine commits, and so 
guarantees that no other sessions can see the data before replicated to 
slave. AFTER_COMMIT means that semisynchronous replication waits just 
after the engine commits. Other sessions may see the data before it is 
replicated, even though the current session is still waiting for the commit 
to end successfully.

From: Source Code mysql-5.7.22\plugin\semisync\semisync_master_plugin.cc

Replication: Semisynchronous replication master servers now use a different wait point by default in communicating wih slaves. This is the point at which the master waits for acknowledgment of transaction receipt by a slave before returning a status to the client that committed the transaction. The wait point is controlled by the new rpl_semi_sync_master_wait_point system variable. These values are permitted:

AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.

AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

For older versions of MySQL, semisynchronous master behavior is equivalent to a setting of AFTER_COMMIT.

The replication characteristics of these settings differ as follows:

With AFTER_SYNC, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master.

In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date.

With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.

If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.

The new wait point is a behavior change, but requires no reconfiguration. The change does introduce a version compatibility constraint because it increments the semisynchronous interface version: Servers for MySQL 5.7.2 and up do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7.2 and up.

From：https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html

测试表

Create Table: CREATE TABLE `syk`.`t` (
`t` datetime DEFAULT NULL,
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into syk.t (t,a) values(now(),1);
commit;

准备 3 个会话窗口，A,B,C
A 窗口连接主库，负责更改主库的参数及表更新操作
B 窗口负责监视主库数据的变化，执行下述命令

while (true);do
mysql -uroot -pmysql-001 -e "select now(),t,a from syk.t" -s --skip-column-names 2>&1 | grep -v Warning
sleep 1
done

C 窗口负责从库，slave 的重启操作，模拟从库的 slave 停止

AFTER_SYNC
5.7.2 及以上默认值

A:

mysql> show variables like ''%rpl%point%'';
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+---------------------------------+------------+

mysql> show global status like ''%rpl%master%status%'';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+

mysql> select * from syk.t;
+---------------------+------+
| t | a |
+---------------------+------+
| 2018-08-02 14:57:15 | 1 |
+---------------------+------+

B:
此时 B 窗口一直在查询表数据
C:
停止 slave

A:

mysql> update syk.t set t=now() where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (10.00 sec)

mysql> select * from syk.t;
+---------------------+------+
| t | a |
+---------------------+------+
| 2018-08-02 15:21:51 | 1 |
+---------------------+------+
1 row in set (0.00 sec)

A 窗口的 commit 延迟了 10 秒才提交成功。受 rpl_semi_sync_master_timeout 参数影响，默认 100000 毫秒，即 10 秒。

B：

2018-08-02 15:21:50 2018-08-02 14:57:15 1
2018-08-02 15:21:51 2018-08-02 14:57:15 1 <=========
2018-08-02 15:21:52 2018-08-02 14:57:15 1
2018-08-02 15:21:53 2018-08-02 14:57:15 1
2018-08-02 15:21:54 2018-08-02 14:57:15 1
2018-08-02 15:21:55 2018-08-02 14:57:15 1
2018-08-02 15:21:56 2018-08-02 14:57:15 1
2018-08-02 15:21:57 2018-08-02 14:57:15 1
2018-08-02 15:21:58 2018-08-02 14:57:15 1
2018-08-02 15:21:59 2018-08-02 14:57:15 1
2018-08-02 15:22:00 2018-08-02 14:57:15 1
2018-08-02 15:22:01 2018-08-02 14:57:15 1
2018-08-02 15:22:02 2018-08-02 14:57:15 1
2018-08-02 15:22:03 2018-08-02 14:57:15 1
2018-08-02 15:22:04 2018-08-02 14:57:15 1
2018-08-02 15:22:05 2018-08-02 15:21:51 1 <=========

可以看到，主库的其他会话需要等待提交显示 OK 之后，才能看得到。

AFTER_COMMIT
5.7.2 之前（不含 5.7.2）的版本，的版本默认行为

A:

set global rpl_semi_sync_master_wait_point=AFTER_COMMIT;

C:
重启 slave

A:

mysql> show variables like ''%rpl%point%'';
+---------------------------------+--------------+
| Variable_name | Value |
+---------------------------------+--------------+
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+---------------------------------+--------------+
1 row in set (0.00 sec)

mysql> show global status like ''%rpl%master%status%'';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> select * from syk.t;
+---------------------+------+
| t | a |
+---------------------+------+
| 2018-08-02 15:21:51 | 1 |
+---------------------+------+
1 row in set (0.00 sec)

B:
此时 B 窗口一直在查询表数据
C:
停止 slave

A:

mysql> update syk.t set t=now() where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (10.01 sec)

mysql> select * from syk.t;
+---------------------+------+
| t | a |
+---------------------+------+
| 2018-08-02 15:38:42 | 1 |
+---------------------+------+
1 row in set (0.00 sec)

A 窗口的 commit 同样延迟了 10 秒才提交成功。受 rpl_semi_sync_master_timeout 参数影响，默认 100000 毫秒，即 10 秒。

B：

2018-08-02 15:38:41 2018-08-02 15:21:51 1
2018-08-02 15:38:42 2018-08-02 15:21:51 1
2018-08-02 15:38:43 2018-08-02 15:38:42 1 <=========
2018-08-02 15:38:44 2018-08-02 15:38:42 1
2018-08-02 15:38:45 2018-08-02 15:38:42 1
2018-08-02 15:38:46 2018-08-02 15:38:42 1
2018-08-02 15:38:47 2018-08-02 15:38:42 1
2018-08-02 15:38:48 2018-08-02 15:38:42 1
2018-08-02 15:38:49 2018-08-02 15:38:42 1
2018-08-02 15:38:50 2018-08-02 15:38:42 1
2018-08-02 15:38:51 2018-08-02 15:38:42 1
2018-08-02 15:38:52 2018-08-02 15:38:42 1
2018-08-02 15:38:53 2018-08-02 15:38:42 1
2018-08-02 15:38:54 2018-08-02 15:38:42 1
2018-08-02 15:38:55 2018-08-02 15:38:42 1
2018-08-02 15:38:56 2018-08-02 15:38:42 1

虽然 A 的提交延迟了 10 秒，但其他会话已经看到已经提交的数据。

总结：
实验的对比，可以发现与源码中的描述是一致的。
AFTER_SYNC 意味着半同步复制，在 binary log 被 flush 之后，在存储引擎 commit 前进入等待，这可以保证数据在被复制到从库前不被其他会话可见；
AFTER_COMMIT 意味着半同步复制在存储引擎 commit 之后进入等待，尽管发起 commit 的会话还未收到 commit 成功的提示，其他的会话已经可以看到 commit 后的数据。
rpl_semi_sync_master_wait_point，该参数也正如其字面意思，master 在哪个点开始等待。

Can''t create more than max_prepared_stmt_count statements (current value: 16382) MYSQL 预编译 SQL 数量超过限制问题

最近产品一直在这个错

当缓存的预编译 SQL 数量超过默认最大值时 就会产生如上错误 导致数据库直接无法使用 线上出现这个问题真是愁死人

话不多说 直接上解决思路：

当时发现这个问题的时候  查了下数据库预编译 SQL 创建次数

show global status like ''com_stmt%'';

发现 close 永远是 0 这个问题源于 vertx 以下是我在 vertx 官方提交的 issue 在下个版本会做修复

https://github.com/vert-x3/issues/issues/444

但这仍然不是导致该问题最主要的原因  主要问题是为什么会创建了将近 5 万次的预编译 SQL

在同一连接下 相同结构的预编译 SQL 是不会再被创建的  那么正常情况是不可能创建出 5w 个预编译 SQL

所以目前可以猜测  肯定是在某个地方出现了动态的预编译 SQL

SELECT * FROM PERFORMANCE_SCHEMA .prepared_statements_instances;

执行以上 SQL 查询出已经缓存的预编译 SQL

如果查出来数据为空 说明 performance_schema 参数值为 OFF  未开启  将其开启 并重启数据库即可

为了方便查看

SELECT
    count() AS 重复数，
    GROUP_CONCAT(STATEMENT_ID SEPARATOR '','') as STATEMENT_IDS,
    t.
FROM
    PERFORMANCE_SCHEMA .prepared_statements_instances t
GROUP BY
    SQL_TEXT;

使用去重查询

终于找到了 “真凶”

这条预编译 SQL 居然有个值是动态的 而不是使用？占位 这就导致了每一次 insert 都会缓存一个不同结构的预编译 SQL 这才致使预编译 SQL 创建数量超过 MAX 值。。。

写这条 SQL 的小朋友我真的要抓起来吊打一顿！

最后总算是完美解决这个问题

关于tf.estimator.RunConfig save_summary_steps 对比log_step_count_steps和tf.logging.set_verbosity的介绍已经告一段落，感谢您的耐心阅读，如果想了解更多关于android – 在后台服务中使用TYPE_STEP_COUNTER？、asp.net-mvc-4 – 在MVC中的App_Start()文件夹中使用AuthConfig,BundleConfig,FilterConfig,RouteConfig和WebApiConfig、Bullet:MySQL 增强半同步参数 rpl_semi_sync_master_wait_point 值 AFTER_SYNC 和 AFTER_COMMIT 的对比实验、Can''t create more than max_prepared_stmt_count statements (current value: 16382) MYSQL 预编译 SQL 数量超过限制问题的相关信息，请在本站寻找。