MySQL索引优化小记

发布在 数据库

有2种数据类型,一种重复率高、一种重复率低,选择这2种类型哪个建立索引好?为什么?

之前确实没考虑过这个问题,但觉得既然是使用空间换时间,数据结构本身重复率那么高索引的作用应该就不大了吧。

后来结束到家查看资料后,发现这个名词叫做”离散率”。对于DBA这个问题应该是最最基础的了。

阅读全文

原文地址,原文中Hierarchical Data直译为 分层结构,这里我翻译成 树状结构

补充资源:

  1. https://django-mptt.github.io/django-mptt/ ,如果你也使用python和django,这个是现成的APP。

另外,个人觉得这种方法对于搜索的效率提升最大,而相应的新增、删除等操作则会变慢,个人猜测未经测试。

个人总结的核心:如果一个节点A是节点B的子节点,那么A的左值一定大于B的左值,A的右值一定小于B的右值。或者说,A的左值一定在B的左值和右值之间。
阅读全文

MySQL数据库修复

发布在 数据库

把博客服务器搬到香港之后,发现这个供应商的服务器经常自动重启,不过mysql、nginx都设定了开机自启动,所以也就没当事。结果今天出了大问题:服务器ip可以ping的通,但博客就是无法访问,登录到服务器一看,我擦——整个数据盘不见了!由于把网站放在了/home目录下,而现在home目录空荡荡的啥也没有。

阅读全文

原文:http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/


当我们被聘请去做MYSQL性能审计时,我们被期望审查MYSQL配置文件并提出改进建议。在大多数情况下很多人都感到惊奇,因为我们仅仅在成百上千的变量中改变了很少的一部分。这篇文章的目的就是告诉你一些至关重要的设置项。
我们在几年前曾经在博客中给出过一些建议,但从那之后MYSQL的世界发生了很多改变!

阅读全文

最近公司有个异地多机房数据同步需求,mysql原生支持双主同步,所以只能另寻他法,于是找到了Percona XtraDB Cluster。这个可以理解为给Mysql打了个补丁,以便支持多主同步。

测试环境:centos 6.5

IP分配:

  1. 192.168.0.154(DB)
  2. 192.168.0.152(DB)
  3. 192.168.0.153(DB)
  4. 192.168.0.151(HA)
    首先安装Percona XtraDB Cluster的源:
    yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

然后
yum install Percona-XtraDB-Cluster-56

安装完毕后,修改/etc/my.cnf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[mysqld]
server_id=4
datadir=/var/lib/mysql
user=mysql
# Path to Galeralibrary
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connectionURL contains the IPs of node#1, node#2 and node#3----所有节点的ip
wsrep_cluster_address=gcomm://192.168.0.152,192.168.0.154,192.168.0.153
# In order for Galerato work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storageengine has only experimental support
default_storage_engine=InnoDB
# This changes howInnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address----本机ip
wsrep_node_address=192.168.0.154
# SST method----节点间同步的方式
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authentication forSST method----来做节点间数据同步的账号密码
wsrep_sst_auth="root:asdasd"
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log

注意,修改不同节点的server_id以及 wsrep_node_address。

然后在192.168.0.152上执行/etc/init.d/mysql bootstrap-pxc

网上有些文章说需要修改 wsrep_cluster_address=gcomm://,在新版本中不需要了,上面这句就是初始化集群。结果如下:

1
2
[root@localhost ~]# /etc/init.d/mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).. SUCCESS!

然后根据配置文件修改用于同步的用户名和密码,由于是实验环境我偷懒直接使用root了:

1
mysqladmin -u root password asdasd

进入mysql终端后可以看当前信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
...
| wsrep_incoming_addresses | 192.168.0.152:3306 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
...
| wsrep_ready | ON |
+----------------------------+--------------------------------------+

当第一个节点成功启动后,启动其他节点,注意此时命令是

1
/etc/init.d/mysql start

正常情况下很快就会启动完成,如果启动了很长时间后出现如下提示:

1
2
3
4
Shutting down MySQL (Percona XtraDB Cluster)..... SUCCESS!
Starting MySQL (Percona XtraDB Cluster).................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... ERROR!
ERROR! MySQL (Percona XtraDB Cluster) server startup failed!
ERROR! Failed to restart server.

但日志里没相关错误信息,那么请 检查selinux是否关闭以及防火墙4444和4567端口 !!!(我就忘了防火墙的原因纠结了好久)

启动成功提示如下:

1
2
3
4
5
6
7
8
9
10
11
[root@test4 ~]# service mysql start
Starting MySQL (Percona XtraDB Cluster)...... SUCCESS!
[root@test4 ~]# netstat -anp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 963/sshd
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 5184/mysqld
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1041/master
tcp 0 0 192.168.0.154:22 192.168.0.37:38500 ESTABLISHED 1080/sshd
tcp 0 0 192.168.0.154:4567 192.168.0.153:53681 ESTABLISHED 5184/mysqld
tcp 0 0 192.168.0.154:59348 192.168.0.152:4567 ESTABLISHED 5184/mysqld

可以看出,4567端口也处于监听状态。此时,在任意机器上进行数据库操作其他2个也会自动同步了。

此时,手动关闭152,然后在154上插入数据,153也同步了,同时自动把152从集群中删除了:

1
| wsrep_incoming_addresses     | 192.168.0.154:3306,192.168.0.153:3306 |

再启动152后,数据也自动同步了。

如果非常非常不幸,集群中所有节点都挂掉了,修复后需要在最后挂掉的节点上执行bootstrap-pxc命令,这样才能拯救多一些的数据。

如果重启时候报错:

1
2
3
4
5
6
[root@test3 ~]# service mysql start
ERROR! MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists
Stale sst_in_progress file in datadir
Starting MySQL (Percona XtraDB Cluster)State transfer in progress, setting sleep higher
.. ERROR! The server quit without updating PID file (/var/lib/mysql/test3.pid).
ERROR! MySQL (Percona XtraDB Cluster) server startup failed!

直接删除/var/lock/subsys/mysql即可。还有一点需要注意的,数据库表需要使用INNODB而不是MYISAM引擎,否则会出现表结构同步了而数据无法同步的情况。

安装HAproxy:yum install haproxy

修改配置文件,位于/etc/haproxy/haproxy.cfg:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
#---------------------------------------------------------------------
# Example configuration for a possibleweb application. See the
# full configuration options online.
# http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option tothe SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like thefollowing can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
# turn on stats unix socket
stats socket /var/lib/haproxy/stats
#---------------------------------------------------------------------
# common defaults that all the'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode http
log global
option tcplog
option dontlognull
# option http-server-close
# option forwardfor except127.0.0.0/8
option redispatch
retries 3
maxconn 2000
timeout connect 5s
timeout client 50s
timeout server 50s
# timeout http-keep-alive 10s
timeout check 10s
listen mysql-cluster 0.0.0.0:3306
mode tcp
balance roundrobin
server node1 192.168.0.152:3306 check
server node2 192.168.0.153:3306 check
server node3 192.168.0.154:3306 check
listen status 192.168.0.151:8080
stats enable
stats uri /status
stats auth admin:admin
stats realm (haproxy\ statistic)

启动服务后,访问192.168.0.151:8080/status登录即可看到界面。对外则使用192.168.0.151:3306访问数据库即可。

—-20150120更新—-

对于上面的HA配置,默认是监控第4层,换言之如果由于某情况下3306端口开放而MYSQL实际并没提供服务时,HA就无法解决这种情况。为了模拟这种情况,停止某个节点的MYSQL服务后,使用NC监听3306端口,成功欺骗了HA。为了解决这中情况,我们就需要针对应用层进行监控。

首先在节点上安装xinetd:yum install -y xinetd

然后编辑/etc/services,添加

1
mysqlchk        9200/tcp

然后编辑/usr/bin/clustercheck,修改

1
2
MYSQL_USERNAME="${1-root}"
MYSQL_PASSWORD="${2-asdasd}"

这里我偷懒使用root,大家根据实际情况修改。保存后启动xinetd服务

1
2
[root@test4 ~]# /etc/init.d/xinetd start
Starting xinetd: [ OK ]

此时9200端口应该已经处于了监听状态,执行检测命令:

1
2
3
4
5
6
[root@test5 ~]# clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.

接下来修改HA节点的配置文件,修改成:

1
2
3
4
5
6
7
listen mysql-cluster 0.0.0.0:3306
mode tcp
balance roundrobin
option httpchk
server node1 192.168.0.152:3306 check port 9200 inter 12000 rise 3 fall 3
server node2 192.168.0.153:3306 check port 9200 inter 12000 rise 3 fall 3
server node3 192.168.0.154:3306 check port 9200 inter 12000 rise 3 fall 3

保存后重启HAPROXY即可,如果使用了KEEPALIVE(这里)把备份HA配置也修改成一样的。此时就是基于应用层的监控了。

评论和分享

今天把django从1.5.5升级到了1.6.2,结果使用mysql-python查询数据库时候就报了这个错误:“not all arguments converted during string formatting”

貌似这个问题应该和django没什么关系,正好今天就看看mysql-python的源码吧。想看如何解决的请直接跳到最后。

查询部分简化后如下:

1
2
3
posistion = "top"
order = r"select id,p_name,p_explain,p_href from pictures where p_position = %s and p_show = 1 order by -id"
self.cur.execute(order, position)

mysql-python中execute函数:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
def execute(self, query, args=None):
"""Execute a query.
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.
Returns long integer rows affected, if any
"""
del self.messages[:]
db = self._get_db()
if isinstance(query, unicode):
query = query.encode(db.unicode_literal.charset)
if args is not None:
if isinstance(args, dict):
query = query % dict((key, db.literal(item))
for key, item in args.iteritems())
else:
query = query % tuple([db.literal(item) for item in args]) ##########################这里!!
try:
r = None
r = self._query(query)
except TypeError, m:
if m.args[0] in ("not enough arguments for format string",
"not all arguments converted"):
self.messages.append((ProgrammingError, m.args[0]))
self.errorhandler(self, ProgrammingError, m.args[0])
else:
self.messages.append((TypeError, m))
self.errorhandler(self, TypeError, m)
except (SystemExit, KeyboardInterrupt):
raise
except:
exc, value, tb = sys.exc_info()
del tb
self.messages.append((exc, value))
self.errorhandler(self, exc, value)
self._executed = query
if not self._defer_warnings: self._warning_check()
return r

错误地方就是后面加了#的语句。

可以看到,因为字符串也是可序列化的,所以一个字符串被拆分成了单个的字符进行db.literal()处理。这个db是什么呢?

往上找:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
def __init__(self, connection):
from weakref import proxy
self.connection = proxy(connection)
self.description = None
self.description_flags = None
self.rowcount = -1
self.arraysize = 1
self._executed = None
self.lastrowid = None
self.messages = []
self.errorhandler = connection.errorhandler
self._result = None
self._warnings = 0
self._info = None
self.rownumber = None
def _get_db(self):
if not self.connection:
self.errorhandler(self, ProgrammingError, "cursor closed")
return self.connection
```
可以看出,这个db就是connection的一个弱引用。再进一步,这个connnection哪来的呢?

```python
def open(self):
""" 返回一个默认的数据库连接游标 """
try:
self.conn = MySQLdb.Connection(
self.db_ip,
self.db_un,
self.db_pd,
self.db_db,
charset=self.db_ch)
self.cur = self.conn.cursor()
except MySQLdb.Error as e:
print e
`

mysqldb-python的connection.py中:

1
2
3
class Connection(_mysql.connection):
"""MySQL Database Connection Object"""
default_cursor = cursors.Cursor

那这个Cursor又是什么呢?

1
2
3
4
class Cursor(CursorStoreResultMixIn, CursorTupleRowsMixIn,
BaseCursor):
"""This is the standard Cursor class that returns rows as tuples
and stores the result set in the client."""

这个Cursor这些类的子类,其中最上面出错函数就是BaseCursor的一个方法。

有点跑偏,先看看那literal函数吧:

1
2
3
4
5
6
7
8
9
10
11
def literal(self, o):
print o,type(o)
print self.encoders
"""
If o is a single object, returns an SQL literal as a string.
If o is a non-string sequence, the items of the sequence are
converted and returned as a sequence.
Non-standard. For internal use; do not use this in your
applications.
"""
return self.escape(o, self.encoders)

print是我添加的,输出如下:

1
2
3
4
5
6
t <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}
o <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}
p <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}

就像上面说的,”top”被拆分成了3个,可是我们语句中的占位符%s只有一个!所以报错 not all arguments converted during string formatting。最初这里我理解成了是哪里转化出了错误,最后醒悟这意思是因为占位符个数和传入参数个数不一样导致!英语不过关阿~不过以前怎么没报错呢??奇怪……

解决办法有2个,简单的方法就是执行语句修改如下:self.cur.execute(order, (position,)) #注意逗号。

更推荐的方法就是使用字典传值:

1
2
3
4
5
if show:
order = r"select id,p_name,p_explain,p_href from pictures where p_position = %(position)s and p_show = 1 order by -id"
else:
order = r"select id,p_name,p_explain,p_href,p_uploaddate,p_show from pictures where p_position = %(position)s"
self.cur.execute(order, {'position': position})

这样虽然多打了几个字母,不过却是一种最稳妥的方式,不用担心由于参数顺序造成错误了。

mysql-python源码写的狠帅阿!

评论和分享

  • 第 1 页 共 1 页
作者的图片

Roy

微信公众号:hi-roy


野生程序猿


China