google-cloud-sql - 调试 IO 限制

Question

我正在尝试了解负载下使用 google cloud SQL 的 IO 等待。我使用应该允许 100 个并发查询的 D1 计划。我在我的仪表板中看不到有关并发查询的任何统计信息，但在本地我有一个 64 线程池，因此我永远不会超过 64 个并发查询(有时还会超过 2 个)。此外，我的每秒 IO 操作峰值为 20。

转向更大的计划有帮助，但作用很小。

查询也很简单——选择1，随机显性选择小订单。

我的所有 64 个线程都卡在 socketRead() 的 google SQL IO 上，如下所示:

at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
- locked <5f74f841> (a com.mysql.jdbc.util.ReadAheadInputStream)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3049)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
- locked <7856a45c> (a com.mysql.jdbc.JDBC4Connection)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
- locked <7856a45c> (a com.mysql.jdbc.JDBC4Connection)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
at org.jboss.jca.adapters.jdbc.CheckValidConnectionSQL.isValidConnection(CheckValidConnectionSQL.java:74)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(BaseWrapperManagedConnectionFactory.java:1173)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.checkValid(BaseWrapperManagedConnection.java:461)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.getInvalidConnections(BaseWrapperManagedConnectionFactory.java:999)
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.validateConnections(SemaphoreArrayListManagedConnectionPool.java:870)
at org.jboss.jca.core.connectionmanager.pool.validator.ConnectionValidator$ConnectionValidatorRunner.run(ConnectionValidator.java:277)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
at java.lang.Thread.run(Thread.java:662)

您能建议如何理解瓶颈吗？

编辑:另外，这是卡住后最终出现的超时异常:

The last packet successfully received from the server was 13,969 milliseconds ago.  The last packet sent successfully to the server was 13,969 milliseconds ago.
        at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown Source) [:1.6.0_45]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) [rt.jar:1.6.0_45]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513) [rt.jar:1.6.0_45]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
        at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:107)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
        at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source) [:1.6.0_45]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [rt.jar:1.6.0_45]
        at java.lang.reflect.Method.invoke(Method.java:597) [rt.jar:1.6.0_45]
        at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
        ... 49 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
        ... 62 more

EDIT2:消费者是一个谷歌云计算实例，与数据库并置。

Answer 1

Using filesort 表示需要进行排序。如果数据量大于 sort_buffer_size，则会创建一个临时文件。 Cloud SQL 中的临时文件很慢，因此避免它们对于获得良好的性能至关重要/

sort_buffer_size 的默认值为 2M。它既是全局变量又是 session 变量，因此可以在需要更多内存的查询中进行更改。

mysql> SHOW VARIABLES LIKE 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 2097152 |
+------------------+---------+
1 row in set (0.00 sec)

mysql> 

每次 sort_buffer_size 太小时，Created_tmp_files 状态变量都会递增。

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp_files';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Created_tmp_files | 663   |
+-------------------+-------+
1 row in set (0.01 sec)

mysql> 

有关使用文件排序 的更多信息，请参阅 Baron Schwartz's post on MySQL Performance Blog .可以在 Sergey Petrunia’s blog post 中找到对“ORDER BY”的深入讨论。 .

我写了一个小程序(mysql-sorttest)专门测试MySQL的排序性能。

在默认配置(1000 行)中，数据适合内存(Created_tmp_files 在测试后不会递增)所以查询很快:

$ ./sort -dsn="root:root@tcp(x.x.x.x:3306)/sorttest" prepare
2014/08/24 12:22:03 Start number of rows: 0
2014/08/24 12:22:06 End number of rows: 1000
$ ./sort -dsn="root:root@tcp(x.x.x.x:3306)/sorttest" run
2014/08/24 12:22:10 Sleep 10s
2014/08/24 12:22:10 Start oneRun
2014/08/24 12:22:20 Done 3000 requests
2014/08/24 12:22:20 Total requests: 300.00/s
$

对于 5000 行，数据无法放入内存并且速度会大幅下降:

$ ./sort -dsn="root:root@tcp(x.x.x.x:3306)/sorttest" -table_size=5000 prepare
2014/08/24 12:22:53 Start number of rows: 1000
2014/08/24 12:23:08 End number of rows: 5000
$ ./sort -dsn="root:root@tcp(x.x.x.x:3306)/sorttest" run
2014/08/24 12:23:15 Sleep 10s
2014/08/24 12:23:15 Start oneRun
2014/08/24 12:23:25 Done 91 requests
2014/08/24 12:23:25 Total requests: 9.10/s
$

关于 ORDER BY RAND() 的评论:如 numerous places 中所示最好避免这种类型的查询，因为它会强制 MySQL 为每一行生成随机数，然后对其进行排序。这是一个 CPU 密集型操作，使用足够多的线程执行此操作最终会消耗所有 CPU(或者可能达到 mysqld 中的其他一些限制)。