iMySQL | 老叶茶馆

原文来自： http://onlinesolutionsmysql.blogspot.com/2007/03/questions-and-answers-during-first.html

On Server and Storage Engines

Q from Alessandro: What about the filesystem for MyISAM in a small company? Better ReiserFS or EXT3?
As Anders said, it's a matter of taste. XFS is a good alternative too. Ext3 is stable, but sometimes slow and a bit outdated. Many like Reiser, but it is sometimes difficult to manage. XFS often gives you good performance and reliability out of the box.

Q from Florian: MySQL 5.1 == Falcon?

原文来自： http://www.feedblog.org/2007/05/fun_with_optero.html

We've been doing a lot of performance analysis of MySQL and SATA disks over the last few days and I wanted to get some thoughts out in the open to see what you guys thought.
Now that Debian Etch is out we're seriously looking at making the jump to a full 64bit OS with 8G of memory on each of our boxes.
This also involves benchmarking SATA and potentially migrating away from MyISAM which, while very trusty, is seriously showing its age for us.
First off is raw IO tuning.
XFS benchmarks show about a 20% performance boost from bonnie++ over ext3. This is pretty obvious. Tuning readahead with 'blockdev' didn't seem to help performance much. SATA seems pretty tuned when compared to hdparm and IDE drives.

原文来自： http://onlinesolutionsmysql.blogspot.com/2007/05/questions-and-answers-in-second-session.html

Correction on the INSERT DELAYED
In slide 20 I have mentioned that the INSERT DELAYED statement can increase performance on the slave. This is wrong, since the DELAYED keyword is ignored by the SQL thread on the slave server. The INSERT DELAYED statement can increase the overall performance of an application since the control is returned to the client as soon as the row is queued into the list of inserts to execute. The INSERT DELAYED can be used with MyISAM, MEMORY and ARCHIVE.

Q from Filip: Does master & slave have to be the same db-version, and the same Operative system?

原文来自：
http://blog.csdn.net/heiyeshuwu/archive/2007/05/03/1595606.aspx

太简单的就不说了，随便找手册可以找到，这里就说说一些小技巧吧，也是最近使用给逼出来的学习，呵呵，不过挺方便的。

1.
全局替换
(1) v + G + $ 选定全部，然后输入 :s/原始字符串/目标字符串/
(2) :%s/原始字符串/目标字符串/

2.
清除页面中所有行尾的空白符：
:%s/\s\+$//

3. 清除所有空白
:%s/\(\s*\n\)\+/\r/

4.
去掉所有的//注释
:%s!\s*//.*!!

5. 去掉所有的/* */注释
:%s!\s*/\*\_.\{-}\*/\s*! !

6.
做某些内部数据重复替换

之前写过了 如何通过 PHP 取得 MySQL procedure 结果，不过按照那种方法还不能适应多结果集的情况。所谓的多结果集，就是指在存储过程中，有多次 SELECT 查询，行程多个结果集。解决的办法还是用 mysqli 提供的函数 mysqli_multi_query() 来做，如下例：

$mysqli = new mysqli("localhost", "root", "", "test");

if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

$query = "call yejr();";

$mysqli->multi_query($query);
while($mysqli->more_results())
{
  if ($result = $mysqli->store_result()) {
    while ($row = $result->fetch_row()) {

在复制中，如果启用参数 replicate-ignore-db / replicate-do-db 后想要让复制正常运行，只需在连接数据库后不执行 "use db" 语句即可，如果是在php中，连接数据库后，不再执行 mysql_select_db() 即可。这是因为复制机制会判断是否使用了 replicate-do(ignore)-db 参数，然后判断当前数据库是否为指定的数据库，如果是，才执行相应的binlog，否则略过。如果不指定数据库的话，就可以忽略这个环节了。

my.cnf 中设置了：

log-bin=log_bin
log-bin-index=log_bin.index

但是启动后，总是报告如下错误：

mysqld: File './log_bin.index' not found (Errcode: 13) 
070428 14:35:59 [ERROR] Aborting 

070428 14:35:59 [Note] mysqld: Shutdown complete

自己 touch 一个文件出来，不行。
修改文件 log_bin.index 的权限为 777，也不行。

[root@localhost]#/usr/local/mysql/bin/perror 13

System error:  13 = Permission denied

看来还是权限的问题，突然想起来 datadir 权限设置可能不对。

[root@localhost]#ls -l /usr/local/mysql | grep data

drw-------   3 nobody nobody  4096 Apr 29 11:17 data

原文来自： http://mysqldba.blogspot.com/2007/04/mysql-60.html

mysql 5.2 is now mysql 6.0:

6.0 is basically the falcon release with subquery optimizer additions.

6.1 Online backup, more subquery optimizations, foreign keys for main engines, and more performance diagnostics.

Online Backup Detail:

Nitro Engine:

原文来自： http://mysqldatabaseadministration.blogspot.com/2007/04/innodb-performan...

I am sitting in the session "InnoDB Performance Optimization" by Heikki Tuuri and Peter Zaitsev. This is going to be the last session of a very enjoyable and informative MySQL Conference and Expo 2007.

General Application Design is Paramount
Of course, you should design your schema, indexes and queries right. Storage engines aspects are often fine tuning. Storage engine selection may affect your schema layout and indexes.

Each storage engine has unique design and operating properties. App written for 1 storage engine may not perform best with other storage engines. Special optimizations exist for each storage engine.

原文来自： http://mysqldatabaseadministration.blogspot.com/2007/04/google-and-mysql...

Chip Turner and Mark Callaghan are presenting the session "MySQL: The Real Grid Database"

Data is sharded vertically and they have a lots of replicas. Resharding is a bigger pain than sharding. Make really smart software and manage with least human resources as possible. They are going to talk about problems that matter to them.

The Grid database approach: deploy a large number of small servers.
Use highly redundant commodity components.
Added capacity has a low incremental cost.
Not much capacity lost when a server fails.
Which allows them to support many servers with a few DBAs.