Month: 七月 2018

MySQL 性能小技巧和在 Django 中的应用

对于 delete update insert 等语句一定要使用 limit 子句限制影响的行数,避免一次更改特别多的行,造成数据库假死

while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了,退出!
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

2. 垂直分割

把不会用作索引的,或者是过长的字段可以考虑使用其他存储引擎,比如 rocksdb 等。

3. IPv4 地址可以存为 uint32

使用 uint32 存储 IP 地址不光可以节省空间,而且可以按区间查询。

4. 避免 select *

从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和应用服务器是两台独立的服务器的话,这还会增加网络传输的负载。

所以,你应该养成一个需要什么就取什么的好的习惯。

不要使用:

SELECT * FROM user WHERE user_id = 1

使用:

SELECT username FROM user WHERE user_id = 1

在 django 中,可以使用 only

books = Book.objects.filter(author="Jim").only('book_name')

5. 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。

在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1)

SELECT * FROM user WHERE country = 'China'
SELECT 1 FROM user WHERE country = 'China' LIMIT 1

在 Django 中可以使用 [:1] 来添加 limit 1

6. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

7. 尽量让查询能 fit 进内存中

参考:

  1. https://coolshell.cn/articles/1846.html

每个程序员都应该知道的延迟数字

动作 时间 换算
L1 缓存访问 0.5 ns 0.5ns
分支预测错误 5 ns 5ns
L2 缓存访问 7 ns 7ns
互斥锁/解锁 25 ns 25ns
内存访问 100 ns 100ns
使用 Zippy压缩 1KiB 3,000 ns 3 µs
通过 1 Gbps 网络发送 2KiB 20,000 ns 20 µs
SSD 随机读取 150,000 ns 150 µs
内存中连续读取 1 MB 250,000 ns 250 µs
同一个数据中心的来回 500,000 ns 0.5 ms
从 SSD 上连续读取 1 MB* 1,000,000 ns 1 ms
机械磁盘寻道 10,000,000 ns 10 ms
机械磁盘连续读取 1 MB 20,000,000 ns 20 ms
发送数据包 加州->荷兰->加州 150,000,000 ns 150 ms
  • 假设 ~1GB/sec SSD

如果把这些时长都乘以 10 亿的话:

动作 时长 相当于
L1 缓存访问 0.5 s 一次心跳 (0.5 s)
分支预测错误 5 s 打个哈欠
L2 缓存访问 7 s 打个长哈欠
互斥锁/解锁 25 s 冲一杯咖啡
内存访问 100 s 刷牙
使用 Zippy压缩 1KiB 50 min 一集电视剧(包括广告)
通过 1 Gbps 网络发送 2KiB 5.5 hr 从午餐到下午工作结束
SSD 随机读取 1.7 days 一个普通的周末
内存中连续读取 1 MB 2.9 days 一个长周末
同一个数据中心的来回 5.8 days 一个普通假期
从 SSD 上连续读取 1 MB* 11.6 days 等快递等了两周
机械磁盘寻道 16.5 weeks 大学里的一个学期
机械磁盘连续读取 1 MB 7.8 months 几乎能造个人了
上面两个加起来 1 year 整整一年
发送数据包 加州->荷兰->加州 4.8 years 快能读个博士了

可视化网页:
https://people.eecs.berkeley.edu/~rcs/research/interactive_latency.html

这些数字的作用是什么?

了解这些时间的量级有助于比较不同的解决方案。通过这些数字,你可以看出来从远程服务器的内存中读一些数据时比直接从硬盘上读取快的。在一般的程序中,这也就意味着使用磁盘存储比使用数据库服务器要慢,因为数据库通常把所有东西都放在内存里了。而且这也说明了为什么在服务全球客户的时候 CDN 至关重要。从北美到欧洲的一个 ping 就要花上 100+ ms,所以从地理上来说,内容应该是分布式部署的。

The idea is more about knowing the scale of time it takes to help compare different solution. With those number you can see that it’s faster to get data in memory from a distant server than to read the same data from disk. In common application that means using disk storage is less efficient that storing it in a database on an other server, since it usually keeps almost everything in memory. It also tells a lot about why CDN are needed if you are serving client worldwide. A ping from North America to Europe will always takes 100+ ms, so having geographically distributed content is a good idea.

对于互斥锁的开启锁定时间
mutex lock/unlock time is important for anyone writing code that depends on code that is accessing the same data structures at the same time. If you don’t know that there is a considerable cost to writing such code, now you do; and this number can quantify it.

还需要注意顺序读写和批量读写带来的提速

常见问题

随着摩尔定律的发展,这些数字会不会不太准确了?

首先摩尔定律基本上经失效了。其次,这些数字的重点是他们之间的比例,而不是具体数字。

延迟, 带宽和吞吐之间的区别和联系?

Tube

  • 延迟表示通过管道需要花费的时间
  • 带宽表示管道的宽度
  • 每秒钟流过的水的数量就是吞吐

吞吐 = 延迟 x 带宽

然而不幸的是, 吞吐这个词经常被用作两个意思. 有时候吞吐的意思是指的系统总荷载, 有时候又指
的是每秒吞吐量, 也就是带宽(当然单位不同).

参考:

  1. https://gist.github.com/hellerbarde/2843375
  2. https://softwareengineering.stackexchange.com/questions/312485/how-can-jeff-deans-latency-numbers-every-programmer-should-know-be-accurate-i
  3. https://stackoverflow.com/questions/36949735/what-is-the-difference-between-latency-bandwidth-and-throughput

MySQL 中的 wait_timeout 是做什么的?

Mysql 中默认的 wait_timeout 和 interactive_timeout 的值是八小时,也就是一个连接(交互式和非交互式的)只有在 8 小时没有活动之后才会被关闭掉。对于互联网公司来说,这个值实在太大了,一个库可能被很多脚本和服务访问,可能只是一个简短的查询就不需要数据库了,如果每个查询都占据了8小时的时间,那么 mysql 很快连接数就会满了,报出 too many connections 错误。

mysql 默认的连接数可以修改 max_connections 参数,但是一个服务器能支撑的连接数显然是由硬件决定的。

设置 wait_timeout 过短可能会造成一些问题,如果在 django 中两次查询的之间时间大于 wait_timeout,就会报 (2006, ‘MySQL server has gone away’)。django 官方给的建议是:

  1. 当你的脚本不需要使用数据库的时候,主动关闭连接,比如在 django 中使用 from django.db import connection; connection.close()
  2. 增大 wait_timeout 的值

不过django默认 CONN_MAX_AGE 是 0,也就是在查询数据库之后会立即关闭链接,理论上应该不会报这个错误。但是这样不能复用链接,会造成对数据压力很大。

CONN_MAX_AGE 应该小于数据库本身的最大连接时间wait_timeout,否则应用程序可能会获取到连接超时的数据库连接,这时会出现MySQL server has gone away的报错。

可以在 settings.py 中动态地获取并填充这个值,然后写到 CONN_MAX_AGE 中

理论上这样就不会再报错了,但是难免数据库重启或者什么时候会报错,总是使用 close_old_connections 还是很烦。

有一种思路是在检测到和数据库链接断开的时候,自动重连,但是这样会破坏 django.db.atomic,但是可以实现一种不同的backend。可以参考这两个:

  1. https://github.com/django/django/pull/2740/commits/38f58aa4d751ad83f1dc76d5b945a1036239584f

  2. https://github.com/django/django/pull/2454/commits/36b8bf870cab183b7ad63c0d8e7e8c02e314a053#diff-f8a587a973ef4c3a94d7550a5b85342c

还有一种解决思路是使用 connection pooling,我们可以使用 sqlalchemy 的 连接池作为django连接数据库的工具。参考这里:http://menendez.com/blog/mysql-connection-pooling-django-and-sqlalchemy/, 不过这种方法比较 hack。

参考

  1. https://code.djangoproject.com/ticket/21597#no2
  2. https://github.com/django/django/commit/2ee21d9f0d9eaed0494f3b9cd4b5bc9beffffae5
  3. https://stackoverflow.com/questions/1125504/django-persistent-database-connection
  4. django 优化
  5. https://docs.djangoproject.com/en/2.1/ref/databases/#persistent-connections
  6. 如何设置 max_age