smallfish

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

看到有人在坛子里询问在GAE如何发布web.py有关问题，就尝试了一把。具体安装和使用过程如下，请对照自己本地路径相应修改：

1. 复制本地对应web.py目录到GAE对应应用目录

比如：D:\Python25\Lib\site-packages\web 到 e:\googleapp\pynotes

2. 写测试代码
app.yaml

application: pynotes
version: 1
runtime: python
api_version: 1

handlers:
-  url: /.*
   script: home.py

home.py

import web
render = web.template.render('templates/')
urls = (
    '/', 'index'
)
class index:
    def GET(self):
        web.header('Content-type', 'text/html')
        name = 'smallfish'
        return render.index(name)
app = web.application(urls, globals())
main = app.cgirun() # 这行是发布到GAE的关键

templates/index.html

$def with (name)
hello, $name. test by web.py

3. 发布到GAE，测试

e:\googleapp>appcfg.py update pynotes/

到这里，一个简单web.py应用就完成了，然后刷新。GAE显示500 Error！

看后台GAE Log显示错误信息：”No module named templates“，去web.py官方溜达了一圈，发现在其cookbook里有一篇文档《How to use templates on Google App Engine》，里面说的很明白啦。

因为web.py的模板在GAE上文件系统会有所限制，所有本地得compile一下，具体命令是：python web/template.py –compile templates 最后一个参数是本地对应模板目录templates，如果有多个模板目录则一次运行一次。运行完会在templates会生成一个__init__.py，里面内容有兴趣可以看看，很眼熟的哦。

4. 再次发布到GAE，可以看到OK拉！

Mako是什么？Moko是Python写的一个模板库，Python官网python.org用的就是它哦。其他废话也就不累赘了，直接来点代码，方便阅读与了解把。

(Mako官网地址：http://www.makotemplates.org/ ，可以下载安装包，推荐使用easy_install安装)

from mako.template import Template
mytemplate = Template("hello world!")
print mytemplate.render()
mytemplate = Template("hello, ${name}!")
print mytemplate.render(name="jack")

代码可以参考官方doc部分

mytemplate = Template(filename='/docs/mytmpl.txt')
print mytemplate.render()

还可以从设置模板为文件，设置filename属性

mytemplate = Template(filename='/docs/mytmpl.txt', module_directory='/tmp/mako_modules')
print mytemplate.render()

文件还可以缓存到某个目录下，下面的/docs/mytmpl.txt会产生一个py：/tmp/mako_modules/docs/mytmpl.txt.py

from mako.lookup import TemplateLookup
mylookup = TemplateLookup(directories=['/docs'])
mytemplate = Template("""<%include file="header.txt"/> hello world!""", lookup=mylookup)

查找模板，方便统一模板路径使用。

mylookup = TemplateLookup(directories=['/docs'], module_directory='/tmp/mako_modules')
def serve_template(templatename, **kwargs):
mytemplate = mylookup.get_template(templatename)
print mytemplate.render(**kwargs)

改良了上面的查找方式

mylookup = TemplateLookup(directories=['/docs'], output_encoding='utf-8',
                                       encoding_errors='replace')
mytemplate = mylookup.get_template("foo.txt")
print mytemplate.render()

设置输出编码，以及编码错误时候处理方式

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)