Apr 29
   之前每次重启机器或重起MySql或都重启IIS后,没过多久服务器会摊掉,一看是MySql引起,点CPU100%,服务器奇慢,远程连接也不容易连接上,看Mysql进程,开的几百个连接全满,其中大部分是DZ论坛的帖子浏览量更新语句“update ..set views=views+1 ..”,上DZ官方论坛发帖问也没解决,然后把论坛的延时更新浏览量都设为不使用,还是一样会死,最后在GOOGLE狂搜,查到的差不多都是一样的结果,照着可能性去配置,把MySql的临时空间加大到200M,一直到现在都正常,估计是MySql临时表间空不足时会执行排队,造成更新锁定的情况。

Quotation
  å…³é”®å­—: MYSQL, tmp_table_size, CPU, 100%, 索引, index, MYSQL 性能提升, left join, where,
摘要 : 早上帮朋友一台服务器解决了Mysql cpu 占用100% 的问题。经验总结如下:1. 增加tmp_table_size值;2.为 where, left join 等子句条件建立索引 Index
 MYSQL CPU 占用 100% 的现象描述

  早上帮朋友一台服务器解决了 Mysql cpu 占用 100% 的问题。稍整理了一下,将经验记录在这篇文章里:《解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记》 http://www.xiaohui.com/weekly/20070307.htm

  朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试,导致 mysqld-nt.exe cpu 占用奇高的是网站A,一旦在 IIS 中将此网站停止服务,CPU 占用就降下来了。一启用,则马上上升。

 MYSQL CPU 占用 100% 的解决过程
  http://www.xiaohui.com/weekly/20070307.htm

  今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000,PageView 为 3万左右。网站A 用的 database 目前有39个表,记录数 60.1万条,占空间 45MB。按这个数据,MySQL 不可能占用这么高的资源。

  于是在服务器上运行命令,将 mysql 当前的环境变量输出到文件 output.txt:
d:\web\mysql> mysqld.exe --help >output.txt
  发现 tmp_table_size 的值是默认的 32M,于是修改 My.ini, 将 tmp_table_size 赋值到 200M:
d:\web\mysql> notepad c:\windows\my.ini
[mysqld]
tmp_table_size=200M
  然后重启 MySQL 服务。CPU 占用有轻微下降,以前的CPU 占用波形图是 100% 一根直线,现在则在 97%~100%之间起伏。这表明调整 tmp_table_size 参数对 MYSQL 性能提升有改善作用。但问题还没有完全解决。

  于是进入 mysql 的 shell 命令行,调用 show processlist, 查看当前 mysql 使用频繁的 sql 语句:
mysql> show processlist;
  反复调用此命令(每秒刷两次),发现网站 A 的两个 SQL 语句经常在 process list 中出现,其语法如下:
SELECT t1.pid, t2.userid, t3.count, t1.date
FROM _mydata AS t1
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid
ORDER BY t1.pid
LIMIT 0,15
  调用 show columns 检查这三个表的结构 :
mysql> show columns from _myuser;
mysql> show columns from _mydata;
mysql> show columns from _mydata_body;
  终于发现了问题所在:_mydata 表,只根据 pid 建立了一个 primary key,但并没有为 userid 建立索引。而在这个 SQL 语句的第一个 LEFT JOIN ON 子句中:
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
  _mydata 的 userid 被参与了条件比较运算。于是我为给 _mydata 表根据字段 userid 建立了一个索引:
mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )
  建立此索引之后,CPU 马上降到了 80% 左右。看到找到了问题所在,于是检查另一个反复出现在 show processlist 中的 sql 语句:
SELECT COUNT(*)
FROM _mydata AS t1, _mydata_key AS t2
WHERE t1.pid=t2.pid and t2.keywords = '孔雀'
  经检查 _mydata_key 表的结构,发现它只为 pid 建了了 primary key, 没有为 keywords 建立 index。_mydata_key 目前有 33 万条记录,在没有索引的情况下对33万条记录进行文本检索匹配,不耗费大量的 cpu 时间才怪。看来就是针对这个表的检索出问题了。于是同样为 _mydata_key 表根据字段 keywords 加上索引:
mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
  建立此索引之后,CPU立刻降了下来,在 50%~70%之间震荡。

  再次调用 show prosslist,网站A 的sql 调用就很少出现在结果列表中了。但发现此主机运行了几个 Discuz 的论坛程序, Discuz 论坛的好几个表也存在着这个问题。于是顺手一并解决,cpu占用再次降下来了。(2007.07.09 附注:关于 discuz 论坛的具体优化过程,我后来另写了一篇文章,详见:千万级记录的 Discuz! 论坛导致 MySQL CPU 100% 的 优化笔记 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)

 解决 MYSQL CPU 占用 100% 的经验总结
增加 tmp_table_size 值。mysql 的配置文件中,tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。 这是 mysql 官方关于此选项的解释:
tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。
根据 mysql 的开发文档:
索引 index 用于:
快速找出匹配一个WHERE子句的行
当执行联结(JOIN)时,从其他表检索行。
对特定的索引列找出MAX()或MIN()值
如果排序或分组在一个可用键的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有键值部分跟随DESC,键以倒序被读取。
在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。

假定你发出下列SELECT语句:
mysql> select * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1和col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
  开发人员做 SQL 数据表设计的时候,一定要通盘考虑清楚。


Nov 24
Not Exists允许用户使用相关子查询已排除一个表中能够与另一个表成功连接的所有记录。

Select a.mobileid
  from Log_user a
where not exists
  (select b.mobileid from magazineitem b
     where b.mobileid=a.mobileid);

对于外查询的每条记录(Log_user),not exists字查询都要测试。如果这条记录于Magazineitem连接后返回一行,则子查询成功。Not Exists 通知查询对返回的代码取反,这样,Log_user 表中与Log_User连接成功的行都不为外查询返回。剩下的行就是那些在magazineitem中没有记录的。
Not Exists用到了连接,能够发挥已经建好的索引的作用,而Not In不能使用索引。

Not In是最慢的方式,要同每条记录比较,在数据量比较大的查询中不建议使用这种方式。

关于Exists和IN:
Select name,skill
  from workerstill ws
  where exists
   (select * from workskill
    where ws.name=name
     group by name
      having count(skill)>1);

检查每个外查询的值,ExistS都要测试,如果为真,那么外查询选择一个姓名和技能

EXISTS是存在性的一种测试,特点:
1。不能匹配一列或者多列
2。只能用于相关的子查询
Tags: , ,
Oct 25
/*--特别注意

请按步骤进行,未进行前面的步骤,请不要做后面的步骤
否则可能损坏你的数据库.


一般不建议做第4,6两步
第4步不安全,有可能损坏数据库或丢失数据
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.
--*/

--下面的所有库名都指你要处理的数据库的库名

1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG

2.截断事务日志:
BACKUP LOG 库名 WITH NO_LOG

3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

也可以用SQL语句来完成
--收缩数据库
DBCC SHRINKDATABASE(库名)

--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles
DBCC SHRINKFILE(1)

4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
a.分离数据库:
企业管理器--服务器--数据库--右键--分离数据库

b.在我的电脑中删除LOG文件

c.附加数据库:
企业管理器--服务器--数据库--右键--附加数据库

此法将生成新的LOG,大小只有500多K

或用代码:
下面的示例分离 pubs,然后将 pubs 中的一个文件附加到当前服务器。

a.分离
EXEC sp_detach_db @dbname = '库名'

b.删除日志文件

c.再附加
EXEC sp_attach_single_file_db @dbname = '库名',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\库名.mdf'

5.为了以后能自动收缩,做如下设置:
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"

--SQL语句设置方式:
EXEC sp_dboption '库名', 'autoshrink', 'TRUE'

6.如果想以后不让它日志增长得太大
企业管理器--服务器--右键数据库--属性--事务日志
--将文件增长限制为xM(x是你允许的最大数据文件大小)

--SQL语句的设置方式:
alter database 库名 modify file(name=逻辑文件名,maxsize=20)

Sep 26
1、这是oracle的规定,不能对执行触发器的表进行操作。  
可以对new.xxx进行操作啊,

对于oracle行级触发器(for   each   row),不能对本表做任何操作,包括读取

原则:  
  åœ¨before   insert触发器中,可以实现对本表的访问;  
  åœ¨after   insert触发器中,不能实现对本表的访问;  
  åœ¨before/after   update/delete触发器中,都不能实现对本表的访问  
    
  å…¶å®žåŽŸå› å¾ˆç®€å•ï¼Œå°±æ˜¯ä¸ºäº†é˜²æ­¢è„è¯»  

2、写oracle行级触发器时,不能操作本表,报"表 *** 发生了变化,触发器/函数不能读"的错误的解决办法
原因已经很明显了就是行级的触发器代码中不能操作该表,包括select,是挺郁闷的
当然解决方法就是要根据原因了,正因为限定了行级触发器的操作,只能选择表级的触发器了,但是在表级的触发器又不能获得:new和:old的值,那就只能采取两种触发器并用的方法了,并且还要包或者临时表加以辅助.

首先在行级触发器中将所需的,:new或者:old的值,写到包或者临时表中

然后在表级触发器中处理包或者临时表中已经写入的数据,操作成功后可以按照需求再删除临时表的数据.
3、 ORACLE 触发器

ORACLE产生数据库触发器的语法为:

create [or replace] trigger 触发器名 触发时间 触发事件

on 表名

[for each row]

pl/sql 语句

其中:

触发器名:触发器对象的名称。由于触发器是数据库自动执行

的,因此该名称只是一个名称,没有实质的用途。

触发时间:指明触发器何时执行,该值可取:

before---表示在数据库动作之前触发器执行;

after---表示在数据库动作之后出发器执行。

触发事件:指明哪些数据库动作会触发此触发器:

insert:数据库插入会触发此触发器;

update:数据库修改会触发此触发器;

delete:数据库删除会触发此触发器。

表 名:数据库触发器所在的表。

for each row:对表的每一行触发器执行一次。如果没有这一

选项,则只对整个表执行一次。

举例:下面的触发器在更新表auths之前触发,目的是不允许在

周末修改表:

create trigger auth_secure

before insert or update or delete //对整表更新前触发

on auths

begin

if(to_char(sysdate,'DY')='SUN'

RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');

end if;

end
Sep 4
使用sys角色或拥有JAVASYSPRIV权限的用户登陆TOAD 执行以下脚本!

调用JAVA程序尽量简单,不要引用过多的包或自定义JAR包(引用外挂包比较麻烦,还没试过)。为了减少这oracle引入JAVA包的麻烦,采用了申请URL取回结果的方法。


登陆SYS用户,执行以下代码


创建使用函数


试一下是否成功!

select GETCUSTINFOTYPE('0809050002') from dual
Pages: 7/8 First page Previous page 2 3 4 5 6 7 8 Next page Final page [ View by Articles | List ]