对比下一些两个数据库常用的操作。分别使用自带的client程序。
MySQL命令行：
mysql -u 用户名 -h 主机地址 -P 端口号 数据库名 -p
PostgreSQL命令行：
psql -U 用户名 -h 主机地址 -p 端口号 数据库名
操作对比：
mysql psql SHOW DATABASES; \l USE db-name; \c db-name SHOW TABLES; \d SHOW USERS; \du SHOW COLUMNS; \d table-name SHOW PROCESSLIST; SELECT * FROM pg_stat_activity; SELECT now()\G \x 可以打开和关闭类似\G功能 SOURCE /path.sql \i /path.sql LOAD DATA INFILE ... \copy ... \h \?
mysql自带的这个玩意挺好使的，可以对慢查询里的sql进行排序、计算等操作。
首先得配置my.cnf：
log_slow_queries = /path/slow.log # 定义log位置，注意要有写入的权限
具体的使法如下：
mysqldumpslow -s c -t 40 /path/slow.log
出来的结果是访问次数最多的40个sql，几个参数大概意思如下：
-t 显示多少条 -s 排序，默认是at。c是次数，t是时间，l是lock时间，r是返回结果。如果是ac，at，al，ar则是倒序 -g 可以用正则匹配部分语句
可以参考mysqldumpslow –help，通过这个工具可以看到哪些锁表，或者其他性能问题，还能看到某些SQL_NO_CACHE提示呢，去想办法优化把！
发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错，整个同步也会因此而卡在那，最初的办法只是手动去出错的机器，执行下面三条sql语句，跳过错误即可。
slave stop; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; slave start;
一台slave机器用这样方法还行，多台就麻烦了，就顺手写了个简单的perl脚本，方便统一跳过错误，代码如下：
#!/usr/bin/env perl
use strict;
use warnings;
# get slave status
sub get_status {
my ($ip, $usr, $pass) = @_;
my $info = `mysql -u$usr -p$pass -h$ip -e 'show slave status\\G;'`;
if (($info =~ /Slave_IO_Running: Yes/) && ($info =~ /Slave_SQL_Running: No/)) {
return 1;
}
return 0;
}
# mysql slave skip
sub slaveskip {
my ($ip, $usr, $pass) = @_;
print "slave error **\n";
system("mysql -u$usr -p$pass -h$ip -e 'slave stop;'");
system("mysql -u$usr -p$pass -h$ip -e 'set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;'");
system("mysql -u$usr -p$pass -h$ip -e 'slave start;'");
}
my @hosts = qw/
192.168.0.101:root:tt1234
192.168.0.102:root: tt1234
192.168.0.103:root: tt1234
/;
foreach (@hosts) {
my ($ip, $usr, $pass) = split ':';
print "// ----- $ip\n";
my $count = 1;
while ($count < 100000) {
my $status = get_status($ip, $usr, $pass);
print "i: $count status: $status\n";
last if $status == 0;
slaveskip($ip, $usr, $pass);
select(undef, undef, undef, 0.1);
$count++;
}
print "\n";
}
exit;
MySQL Query Profile MySQL 5.0.37 以上开始支持 MySQL Query Profiler, 可以查询到此 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 System lock, Table lock 花多少时间等等.
详细可以参见官方文档：Using the New MySQL Query Profiler
启动
mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec)
测试查询
mysql> select count(*) from client where broker_id=2; +----------+ | count(*) | +----------+ | 200 | +----------+ 1 row in set (0.00 sec)
查看profiles
mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 0 | 0.00007300 | set profiling=1 | | 1 | 0.00044700 | select count(*) from client where broker_id=2 | +----------+------------+-----------------------------------------------+ 2 rows in set (0.00 sec)
查看单条profile
mysql> show profile for query 1; +--------------------+------------+ | Status | Duration | +--------------------+------------+ | (initialization) | 0.00006300 | | Opening tables | 0.00001400 | | System lock | 0.00000600 | | Table lock | 0.00001000 | | init | 0.00002200 | | optimizing | 0.00001100 | | statistics | 0.00009300 | | preparing | 0.00001700 | | executing | 0.00000700 | | Sending data | 0.00016800 | | end | 0.00000700 | | query end | 0.00000500 | | freeing items | 0.00001200 | | closing tables | 0.00000800 | | logging slow query | 0.00000400 | +--------------------+------------+ 15 rows in set (0.00 sec) mysql> alter table t engine=myisam; Query OK, 112050 rows affected (0.64 sec) Records: 112050 Duplicates: 0 Warnings: 0 mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 0 | 0.00007300 | set profiling=1 | | 1 | 0.00044700 | select count(*) from client where broker_id=2 | | 2 | 0.00003400 | set profiling=0 | | 3 | 0.00007400 | set profiling=1 | | 4 | 0.63789700 | alter table t engine=myisam | | 5 | 0.00004000 | set profiling=0 | +----------+------------+-----------------------------------------------+ 6 rows in set (0.00 sec) mysql> show profile for query 4; +----------------------+------------+ | Status | Duration | +----------------------+------------+ | (initialization) | 0.00002900 | | checking permissions | 0.00000800 | | init | 0.00004000 | | Opening table | 0.00009400 | | System lock | 0.00000500 | | Table lock | 0.00000700 | | setup | 0.00004200 | | creating table | 0.00195800 | | After create | 0.00010900 | | copy to tmp table | 0.52264500 | | rename result table | 0.11289400 | | end | 0.00004600 | | query end | 0.00000700 | | freeing items | 0.00001300 | +----------------------+------------+ 14 rows in set (0.00 sec)
查看cpu资源等信息
mysql> show profile cpu for query 4; +----------------------+------------+------------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+------------+------------+------------+ | (initialization) | 0.00002900 | 0.00000000 | 0.00000000 | | checking permissions | 0.00000800 | 0.00000000 | 0.00000000 | | init | 0.00004000 | 0.00000000 | 0.00000000 | | Opening table | 0.00009400 | 0.00100000 | 0.00000000 | | System lock | 0.00000500 | 0.00000000 | 0.00000000 | | Table lock | 0.00000700 | 0.00000000 | 0.00000000 | | setup | 0.00004200 | 0.00000000 | 0.00000000 | | creating table | 0.00195800 | 0.00000000 | 0.00100000 | | After create | 0.00010900 | 0.00000000 | 0.00000000 | | copy to tmp table | 0.52264500 | 0.55591600 | 0.04199300 | | rename result table | 0.11289400 | 0.00199900 | 0.00000000 | | end | 0.00004600 | 0.00000000 | 0.00000000 | | query end | 0.00000700 | 0.00000000 | 0.00000000 | | freeing items | 0.00001300 | 0.00000000 | 0.00000000 | +----------------------+------------+------------+------------+ 14 rows in set (0.00 sec)
其他属性列表
* ALL – displays all information
* BLOCK IO – displays counts for block input and output operations
* CONTEXT SWITCHES – displays counts for voluntary and involuntary context switches
* IPC – displays counts for messages sent and received
* MEMORY – is not currently implemented
* PAGE FAULTS – displays counts for major and minor page faults
* SOURCE – displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
* SWAPS – displays swap counts
设定profiling保存size
mysql> show variables where variable_name='profiling_history_size'; # 默认15条
关闭
mysql> set profiling=0;
其实MySQL-python安装很简直，以前一直也没在意，今天发觉换了1.2.3新版本，ms蹦出很多问题来了。
做个记录，防止以后有问题无处可查。
一般步骤是：
1. 安装easy_install
shell > wget http://peak.telecommunity.com/dist/ez_setup.py shell > python ez_setup.py
会自动根据本机的py版本选择对应的egg，安装完可以看到有/usr/bin/easy_install程序了
2. 安装MySQL-python
shell > easy_install MySQL-python
到这里安装算是完成了，不过接下来测试就郁闷了。
在import MySQLdb出现了两个错误：
a). ImportError: libmysqlclient_r.so.15: cannot open shared object file: No such file or directory
这个错误一般解决比较简单，把路径加入到LD_LIBRARY_PATH即可，不过偶的现象比较强，因为没装MySQL，哈哈
b). ImportError: /lib/tls/libc.so.6: version `GLIBC_2.4' not found
解决这个错误的办法是不用easy_install了，直接下载MySQL-python-1.2.2.tar.gz包，然后就是三步走：
shell > tar zxvf MySQL-python-1.2.2.tar.gz shell > cd MySQL-python-1.2.2 shell > python setup.py install
MySQLdb默认查询结果都是返回tuple，输出时候不是很方便，必须按照0，1这样读取，无意中在网上找到简单的修改方法，就是传递一个cursors.DictCursor就行。
默认程序：
import MySQLdb
db = MySQLdb.connect(host = 'localhost', user = 'root', passwd = '123456', db = 'test')
cursor = db.cursor()
cursor.execute('select * from user')
rs = cursor.fetchall()
print rs
# 返回类似如下
# ((1000L, 0L), (2000L, 0L), (3000L, 0L))
修改后：
import MySQLdb
import MySQLdb.cursors
db = MySQLdb.connect(host = 'localhost', user = 'root', passwd = '123456', db = 'test',
cursorclass = MySQLdb.cursors.DictCursor)
cursor = db.cursor()
cursor.execute('select * from user')
rs = cursor.fetchall()
print rs
# 返回类似如下
# ({'age': 0L, 'num': 1000L}, {'age': 0L, 'num': 2000L}, {'age': 0L, 'num': 3000L})
或者也可以用下面替换connect和cursor部分
db = MySQLdb.connect(host = 'localhost', user = 'root', passwd = '123456', db = 'test') cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
