如何监视mysql的性能

这里列出了一些如何监视你安装的mysql性能的一些ideas。监视总是一个持续的过程。你需要知道哪种模式对你的数据库是好的,什么是问题的表象,甚至是危险的情况。
一下列出了用来去监视你的系统的主要参数:
– mysqladmin extended (绝对值)
– mysqladmin extended -i10 -r (相对值)
– mysqladmin processlist
– mysql -e "show innodb status"
– OS data. vmstat/iostat
– MySQL error log
– InnoDB tablespace info.
1) mysqladmin extended (绝对值)
重点去监视的值有:
* Slave_running:如果系统有一个从复制服务器,这个值指明了从服务器的健康度
* Threads_connected:当前客户端已连接的数量。这个值会少于预设的值,但你也能监视到这个值较大,这可保证客户端是处在活跃状态。
* Threads_running:如果数据库超负荷了,你将会得到一个正在(查询的语句持续)增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当Threads_running值超过预设值时并且该值在5秒内没有回落时, 要同时监视其他的一些值。
2)mysqladmin extended(计数器)
* Aborted_clients:客户端被异常中断的数值(因为连接到mysql服务器的客户端没有正常地断开或关闭)。对于一些应用程序是没有影响的,但对于另一些应用程序可能你要跟踪该值,因为异常中断连接可能表明了一些应用程序有问题。
* Questions:每秒钟获得的查询数量。也可以是全部查询的数量(注:可以根据你输入不同的命令会得到你想要的不同的值)。
* Handler_*:如果你想监视底层(low-level)数据库负载,这些值是值得去跟踪的。如果Handler_read_rnd_next值相对 于你认为是正常值相差悬殊,可能会告诉你需要优化或索引出问题了。Handler_rollback表明事务被回滚的查询数量。你可能想调查一下原因。
* Opened_tables:表缓存没有命中的数量。如果该值很大,你可能需要增加table_cache的数值。典型地,你可能想要这个值每秒打开的表数量少于1或2.
* Select_full_join: 没有主键(key)联合(Join)的执行。该值可能是零。这是捕获开发错误的好方法,因为一些这样的查询可能降低系统的性能。
* Select_scan:执行全表搜索查询的数量。在某些情况下是没问题的,但占总查询数量该比值应该是常量(注:Select_scan除以总查询数量商应该是常数)。如果你发现该值持续增长,说明需要优化,缺乏必要的索引或其他问题。
* Slow_queries:超过该值(–long-query-time)的查询数量,或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长,表明系统有性能问题。
* Threads_created:该值应该是低的。较高的值可能意味着你需要增加thread_cache的数值,或你遇到了持续增加的连接,表明了潜在的问题。
3)mysqladmin processlist or "SHOW FULL PROCESSLIST"命令
你可以通过使用其他的统计信息得到已连接线程数量和正在运行线程的数量,检查正在运行 的查询花了多长时间是一个好主意。如果有一些长时间的查询(由于很差的构思或公式),管理员可以被通知。你可能也想了解多少个查询是在"Locked"的 状态——该值作为正在运行的查询不被计算在内而是作为非活跃的。一个用户正在等待一个数据库响应。
4) "SHOW INNODB STATUS"命令
该语句产生很多信息,从中你可以得到你感兴趣的。首先你要检查的就是“从最近的XX秒计算出来的每秒的平均负载”。
* Pending normal aio reads: 该值是innodb io请求查询的大小(size)。如果该值大到超过了10—20,你可能有一些瓶颈。
* reads/s, avg bytes/read, writes/s, fsyncs/s:这些值是io统计。对于reads/writes大值意味着io子系统正在被装载。适当的值取决于你系统的配置。
* Buffer pool hit rate:这个命中率非常依赖于你的应用程序。当你觉得有问题时请检查你的命中率
* inserts/s, updates/s, deletes/s, reads/s:有一些Innodb的底层操作。你可以用这些值检查你的负载情况查看是否是期待的数值范围。
4)OS数据。查看系统状态好的工具是vmstat/iostat/mpstat,这些可以看man手册
5)MySQL错误日志—–在服务器正常完成初始化后,什么都不会写到错误日志中,因此任何在该日志中的信息都要引起管理员的注意。
6)InnoDB表空间信息。InnoDB仅有的危险情况就是表空间填满—-日志不会填满。检查的最好方式就是:show table status;你可以用任何InnoDB表来监视InnoDB表的剩余空间。

mysql中,删除重复记录的方法及疑问

用临时过渡表,这种很笨拙的方法。请达人指导。

如果数据库的设计不规范,某个表没有主健,那么肯定会有重复记录的现象,或者存在这种危险。在oracle中,可以通过rowid来删除重复记录。但在mysql中如何实现呢?
google一下,有个方法如下:

1、准备基表test,待测试。
create table test (c1 smallint,c2 smallint);
insert into test values(1,1);
insert into test values(1,1);
insert into test values(1,2);
insert into test values(2,2);
insert into test values(2,2);
2、创建一个临时表,结构与原表一致,但没有数据。
create table tmp as select * from test where 1=2;
3、将原表数据插入临时表,重复记录合为一条记录。
insert into tmp select distinct * from test;
4、查看一下基表和临时表的数据。
mysql> select * from test;
+——+——+
| c1 | c2 |
+——+——+
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
| 2 | 2 |
+——+——+
5 rows in set (0.00 sec)

mysql> select * from tmp;
+——+——+
| c1 | c2 |
+——+——+
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
+——+——+
3 rows in set (0.00 sec)

5、删除基表数据
mysql> delete from test;
Query OK, 5 rows affected (0.02 sec)
6、将临时表数据插回基表
mysql> insert into test select * from tmp;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
7、查看一下基表的数据
mysql> select * from test;
+——+——+
| c1 | c2 |
+——+——+
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
+——+——+
3 rows in set (0.00 sec)
8、数据准确,完成基表重复记录的删除。

后续问题:
1、如果这个基表很大(100万、1000万条记录),还用这个方法?
2、有没有更好的办法?

对包含150万条记录的表,做最简单的测试。

mysql下,基表较大,采用临时过渡表的方法,性能如何?
1、确认一下基表和过渡表的数据量。
mysql> select count(*) from test;
+———-+
| count(*) |
+———-+
| 1572864 |
+———-+
1 row in set (2.48 sec)

mysql> select count(*) from tmp;
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.02 sec)

2、过滤重复记录,将基表数据插入过渡表
mysql> insert into tmp select distinct * from test;
Query OK, 3 rows affected (3.27 sec)
Records: 3 Duplicates: 0 Warnings: 0

3、删除基表数据,过渡表数据回插。
mysql> truncate table test;
Query OK, 1572864 rows affected (29.44 sec)

mysql> insert into test select * from tmp;
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0

4、检查基本表数据
mysql> select * from test;
+——+——+
| c1 | c2 |
+——+——+
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
+——+——+
3 rows in set (0.00 sec)

5、ok,数据准确。

个人结论及疑问:
1、 采用临时过渡表的方式,目前尚在可以忍受的范围之内。150万条记录的表,除了truncate基本表的速度(29.44秒)超出预计,其他操作均在5秒 内,可以看出mysql的速度确实快。当然,测试仅考虑最简单的情况。如果表的字段很多,字段类型复杂,或者表上有索引,这种方式的速度可能会直线下降 (猜测而已)。
2、还是老问题,有没有更好的办法?

Oracle 中如何删除重复数据



我们可能会出现这种情况,某个表原来设计不周全,导致表里面的数据数据重复,那么,如何对重复的数据进行删除呢?
重复的数据可能有这样两种情况,第一种时表中只有某些字段一样,第二种是两行记录完全一样。

一、对于部分字段重复数据的删除

先来谈谈如何查询重复的数据吧。

下面语句可以查询出那些数据是重复的:

代码:


select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) >


将上面的>号改为=号就可以查询出没有重复的数据了。
想要删除这些重复的数据,可以使用下面语句进行删除

代码:


delete from 表名 a where 字段1,字段2 in

     (select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)


上面的语句非常简单,就是将查询到的数据删除掉。不过这种删除执行的效率非常低,对于大数据量来说,可能会将数据库吊死。所以我建议先将查询到的重复的数据插入到一个临时表中,然后对进行删除,这样,执行删除的时候就不用再进行一次查询了。如下:

代码:


CREATE TABLE 临时表 AS

   (select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)


上面这句话就是建立了临时表,并将查询到的数据插入其中。

下面就可以进行这样的删除操作了:

代码:


delete from 表名 a where 字段1,字段2 in (select 字段1,字段2 from 临时表);


这种先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。

这个时候,大家可能会跳出来说,什么?你叫我们执行这种语句,那不是把所有重复的全都删除吗?而我们想保留重复数据中最新的一条记录啊!大家不要急,下面我就讲一下如何进行这种操作。

在oracle中,有个隐藏了自动rowid,里面给每条记录一个唯一的rowid,我们如果想保留最新的一条记录,
我们就可以利用这个字段,保留重复数据中rowid最大的一条记录就可以了。
下面是查询重复数据的一个例子:

代码:


select a.rowid,a.* from 表名 a

where a.rowid !=

(

   select max(b.rowid) from 表名 b

   where a.字段1 = b.字段1 and

   a.字段2 = b.字段2

)


下面我就来讲解一下,上面括号中的语句是查询出重复数据中rowid最大的一条记录。

而外面就是查询出除了rowid最大之外的其他重复的数据了。
由此,我们要删除重复数据,只保留最新的一条数据,就可以这样写了:

代码:


delete from 表名 a

where a.rowid !=

(

   select max(b.rowid) from 表名 b

   where a.字段1 = b.字段1 and

   a.字段2 = b.字段2

)


随便说一下,上面语句的执行效率是很低的,可以考虑建立临时表,讲需要判断重复的字段、rowid插入临时表中,然后删除的时候在进行比较。

代码:


create table 临时表 as

     select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.字段1,a.字段2;

   delete from 表名 a

where a.rowid !=

(

   select b.dataid from 临时表 b

   where a.字段1 = b.字段1 and

   a.字段2 = b.字段2

);

commit;


二、对于完全重复记录的删除

对于表中两行记录完全一样的情况,可以用下面语句获取到去掉重复数据后的记录:

代码:


select distinct *

可以将查询的记录放到临时表中,然后再将原来的表记录删除,最后将临时表的数据导回原来的表中。如下:

代码:


CREATE TABLE 临时表 AS (select distinct * from 表名);

   drop table 正式表;

   insert into 正式表 (select * from 临时表);

   drop table 临时表;


如果想删除一个表的重复数据,可以先建一个临时表,将去掉重复数据后的数据导入到临时表,然后在从

临时表将数据导入正式表中,如下:

代码:


INSERT INTO t_table_bak

select distinct * from t_table;


MySQL服务器安装完之后如何调节性能

原文作者: Peter Zaitsev
原文来源: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation
译者:叶金荣(Email:),转载请注明译者和出处,并且不能用于商业用途,违者必究。

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

在面试MySQL DBA或者那些打算做MySQL性能优化的人时,我最喜欢问题是:MySQL服务器按照默认设置安装完之后,应该做哪些方面的调节呢?

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

令我很惊讶的是,有多少人对这个问题无法给出合理的答案,又有多少服务器都运行在默认的设置下。

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

尽管你可以调节很多MySQL服务器上的变量,但是在大多数通常的工作负载下,只有少数几个才真正重要。如果你把这些变量设置正确了,那么修改其他变量最多只能对系统性能改善有一定提升。

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

key_buffer_size – 这对MyISAM表来说非常重要。如果只是使用MyISAM表,可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 — 记住,MyISAM表会使用操作系统的缓存来缓存数据,因此需要留出部分内存给它们,很多情况下数据比索引大多了。尽管如此,需要总是检查是否所有的 key_buffer 都被利用了 — .MYI 文件只有 1GB,而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表,那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_buffer_pool_size – 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大了。

innodb_additional_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_additional_pool_size – 这个选项对性能影响并不太多,至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大),因此就需要看一下Innodb其他需要分配的内存有多少。

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。我经常设置为 64-512MB,跟据服务器大小而异。

innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_log_buffer_size 默认的设置在中等强度写入负载以及较短事务的情况下,服务器性能还可以。如果存在更新操作峰值或者负载较大,就应该考虑加大它的值了。如果它的值设置太高 了,可能会浪费内存 — 它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

innodb_flush_logs_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,而这相当耗费资源,尤其是没有电池备用缓存时。很多应用程序,尤其是从 MyISAM转变过来的那些,把它的值设置为 2 就可以了,也就是不把日志刷新到磁盘上,而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置 为 0 就快很多了,不过也相对不安全了 — MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。

table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

table_cache — 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁,所以通常要加大缓存数量,使得足以最大限度 地缓存打开的表。它需要用到操作系统的资源以及内存,对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话,那么设置为 1024 也许比较合适(每个线程都需要打开表),如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

thread_cache — 线程的创建和销毁的开销可能很大,因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大,那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。

query_cache If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

query_cache — 如果你的应用程序有大量读,而且没有应用程序级别的缓存,那么这很有用。不要把它设置太大了,因为想要维护它也需要不少开销,这会导致MySQL变慢。通 常设置为 32-512Mb。设置完之后最好是跟踪一段时间,查看是否运行良好。在一定的负载压力下,如果缓存命中率太低了,就启用它。

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

注意:就像你看到的上面这些全局表量,它们都是依据硬件配置以及不同的存储引擎而不同,但是会话变量通常是根据不同的负载来设定的。如果你只有一些简单的查询,那么就无需增加 sort_buffer_size 的值了,尽管你有 64GB 的内存。搞不好也许会降低性能。
我通常在分析系统负载后才来设置会话变量。

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.

P.S,MySQL的发行版已经包含了各种 my.cnf 范例文件了,可以作为配置模板使用。通常这比你使用默认设置好的多了。

前两天碰到的一个mysql怪问题

mysql数据库有一台单独的服务器在做,WEB服务器有4台机器做集群。4台机器的web分别去连接mysql的数据库。

这个架构进行一段时间后,连续出现了几次怪问题,WEB连接mysql回常慢。光连接时间就达到十几秒。重启mysql,会好一点,但过不了多久,问题又会出现。

在故障排除的时候我发现一个问题,如果在mysql服务器上连接mysql并不慢。这样我又开始 怀疑是服务器间的网络问题了,但是服务器间copy文件速度非常快。说明网络是好的。

后来怀疑了很多问题,都被一一排除了。

我的mysql服务器上有两块网卡,发现:

mysql -h 127.0.0.1 很快,比较正常

mysql -h 192.168.x.x 和mysql -h 218.x.x.x就比较慢了。

这样看起来像是IP地址反向解析的问题。我在自己的dns server上加了一个mysql服务器IP192.168.x.x的反向解析。发现mysql -h 192.168.x.x 就正常了。

说明确实是反向解析的问题了。查找mysql手册,发现其实mysql有一个参数skip-name-resolve可以解决这个问题。加入/etc/my.cnf内便可。看看手册内的解释:

你可以用–skip-name-resolve选项启动mysqld来禁用DNS主机名查找。然而,在这种情况下,你只可以使用MySQL中的授权表中的IP号。

如果你有一个很慢的DNS和许多主机,你可以通过用–skip-name-resolve禁用DNS查找或增加HOST_CACHE_SIZE定义(默认值:128)并重新编译mysqld来提高性能。

这样就可以得出一个结论了:

客户端去连mysql时默认是要反向解析的,因为权限的原因。mysql接到客户端第一次连接时会去做反向解析,并存在Host_cache内。下次就从cache内读了。所以除了第一次比较慢以外,后来的连接都是很快的。

因为我有多台服务器频繁的去连mysql数据库,造成host_cache不够用,所以每次连接时都要做反向解析,造成连接速度缓慢了。