ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 知识点笔记之innodb_thread_concurrency/straight_join/rand

知识点笔记之innodb_thread_concurrency/straight_join/rand

原创 Linux操作系统 作者:myownstars 时间:2013-07-15 15:08:12 0 删除 编辑

Innodb_thread_concurrency

http://ourmysql.com/archives/811

如果参数设置大于0,则表示检查机制开启,允许进入的线程数就是参数的值.

5.5 innodb_thread_concurrency默认为0

设置此值可允许单个sql一次性执行多个请求,从而减少上下文切换,

 

在新的MySQL线程调用Innodb接口前,Innodb会检查已经接受的请求线程数,如已经超过innodb_thread_concurrency设置的限制,则该请求线程会等待innodb_thread_sleep_delay微秒后尝试重新请求,如果第二次请求还是无法获得,则该线程会进入线程队列休眠。重试两次的机制是为了减少CPU的上下文切换的次数,以降低CPU消耗,这和Oraclelatchspin机制是同样的道理。如果请求被Innodb接受,则会获得一个次数为innodb_concurrency_tickets(默认500)的通行证,在次数用完之前,该线程重新请求时无须再进行前面所说innodb_thread_concurrency的检查。

 

另外,如果是一个已经持有lock的线程,则通过调用srv_conc_force_enter_innodb函数可以无视该检查,这是为了避免线程长时间持有锁影响性能,且可能增加死锁的机率。除此之外,slave线程也是有无视检查直接通行的权限。

 

/* The following controls how many threads we let inside InnoDB concurrently:

threads waiting for locks are not counted into the number because otherwise

we could get a deadlock. MySQL creates a thread for each user session, and

semaphore contention and convoy problems can occur without this restriction.

Value 10 should be good if there are less than 4 processors + 4 disks in the

computer. Bigger computers need bigger values. Value 0 will disable the

concurrency check. */

 

因为检查机制需要Mutex保护(Mutex-based Model),所以开启检查本身也有性能消耗,并且扩展性也会受到限制,在MySQL5.4版本中引入了一种新的机制(Timer-based Model),这里就不讨论了,有兴趣的可以参考这里(http://mikaelronstrom.blogspot.co.uk/2009/05/mysql-54-patches-innodb-thread.html )

 

部分情况下 mutex竞争可通过降低此参数减缓http://forums.mysql.com/read.php?24,411949,412273#msg-412273

 

 

Innodb adaptive flushing

http://ourmysql.com/archives/852

plugin 1.0.4提供,innodb2种情况下触发flush dirty page1 日志切换,称为flush_list flush 2 没有空闲buffer,通过LRUdirty page写入数据文件,称为LRU_list Flush

对系统IO影响较大的为前者;

原理:

1 计算切换前剩余时间:日志剩余量/产生速度

2 计算刷新速度:脏页数/剩余时间

减去LRU_list flush速度

 

Straight_join的优化

http://itblog.cc/409.html

SELECT post.*

FROM post

INNER JOIN post_tag ON post.id = post_tag.post_id

WHERE post.status = 1 AND post_tag.tag_id = 123

ORDER BY post.created DESC

LIMIT 100

+----------+---------+-------+-----------------------------+

| table    | key     | rows  | Extra                       |

+----------+---------+-------+-----------------------------+

| post_tag | tag_id  | 71220 | Using where; Using filesort |

| post     | PRIMARY |     1 | Using where                 |

+----------+---------+-------+-----------------------------+

优化,post_tag作为驱动表但以post.created字段排序,故采用using filesort,强制post为驱动表,虽然候选行数更多但省去了file sort

SELECT post.*

FROM post

STRAIGHT_JOIN post_tag ON post.id = post_tag.post_id

WHERE post.status = 1 AND post_tag.tag_id = 123

ORDER BY post.created DESC

LIMIT 100   试着用EXPLAIN查询一下SQL执行计划(篇幅所限,结果有删减)

 

+----------+----------------+--------+-------------+

| table    | key            | rows   | Extra       |

+----------+----------------+--------+-------------+

| post     | status_created | 119340 | Using where |

| post_tag | post_id        |      1 | Using where |

+----------+----------------+--------+-------------+

 

 

Mysql如何选择索引和join顺序

http://ourmysql.com/archives/1236

MySQL优化器只有两个自由度:顺序选择;单表访问方式;

explain

select *

from

  employee as A,department as B

where

      A.LastName = 'zhou'

  and B.DepartmentID = A.DepartmentID

  and B.DepartmentName = 'TBX';

join模式为A|BB|A,而每个表均有3种访问方式:全表扫描或两个字段索引

 

优化器工作包括如下部分:

Query Rewrite(包括Outer Join转换等)const table detectionrange analysisJOIN optimization(顺序和访问方式选择)plan refinement

range analysis入手

3

该阶段包括rangeindex merge成本评估

本案例中,range analysis会针对A表的条件A.LastName = 'zhou'B表的B.DepartmentName = 'TBX'分别做分析。其中:

A A.LastName = 'zhou' found records: 51

B B.DepartmentName = 'TBX' found records: 1

   这两个条件都不是range,但是这里计算的值仍然会存储,在后面的ref访问方式评估的时候使用。这里的值是根据records_in_range接口返回,而对于InnoDB每次调用这个函数都会进行一次索引页的采样,这是一个很消耗性能的操作,对于很多其他的关系数据库是使用"直方图"的统计数据来避免这次操作

4

JOIN optimization

通过枚举left-deep tree,找到最优执行顺序和访问方式

4.1  决定驱动表,根据found records排序,记录少的放前面,在此顺序为B/A

4.2  当表数量默认63),采用穷举搜索left-deep tree,另外为减少开销采用prune_level(默认on),至少3表关联才可行

(*) 选择第一个JOIN的表为B

  (**) 确定B表的访问方式

  (**) 从剩余的表中穷举选出第二个JOIN的表,这里剩余的表为:A

  (**) A表加入JOIN,并确定其访问方式

    (***) IND_L_D A.LastName = 'zhou'

    (***) IND_DID B.DepartmentID = A.DepartmentID

    (***) IND_L_D成本为25.2IND_DID成本为1.2,所以选择后者为当前表的访问方式

  (**) 确定A使用索引IND_DID,访问方式为ref

  (**) JOIN顺序B|A,总成本为:1.2+1.2 = 2.4

 

(*) 选择第一个JOIN的表为A

  (**) 确定A表的访问方式

  (**) 这里访问A表的成本已经是25.2,比之前的最优成本2.4要大,忽略该顺序

 

在打开了参数prune_level(默认开启)后,MySQL不再使用穷举的方式扩展执行计划,而是通过一些规则跳过一些看似消耗更大的执行计划,而是在剩余表中直接选取访问最少纪录数的表通过这种"启发式"的方式忽略一些执行计划,借此可以大大减少需要穷举的执行计划。按照MySQL手册上的描述是:根据经验来看,这种”educated guess”基本不会漏掉最优的执行计划,但是却可以大大(dramatically )缩小搜索空间。要是你怀疑漏掉了某个最优的执行计划,你可以考虑关闭参数试试,当然这会导致搜索空间增大,优化器执行时间偏长

 

 

获取随机数据

http://ourmysql.com/archives/524

不推荐order by rand() limit n,因为

rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

普遍采用max(id) * rand()然后join原表

SELECT *

FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2

WHERE t1.id >= t2.id

ORDER BY t1.id ASC LIMIT 5;

15万行数据只需0.01

 

Order by优化

http://space.itpub.net/15480802/viewspace-757553

http://space.itpub.net/15480802/viewspace-757562

尽量使用索引排序

1

Order by使用了最左前缀的列或者前N列都为const

2

多表连接时order by的列均为outter table的最左前缀列

Filesort分两种:双路排序和单路排序,后者受max_length_for_sort_data影响

内部临时表

使用memory引擎存于内存,或使用MyISAM引擎存于磁盘

何时生成

使用order  by /group by的列并非全来自于join queue的第一个表

Distinctorder by联合使用

多表连接需要保存中间结果集

如何保存

SQL_SMALL_RESULT会使用内存临时表,除非包含必须使用磁盘临时表的条件:

    当表包含blob/text列,或group by/distinct的列大于512字节时,必须使用磁盘临时表;

    当临时表> min(tmp_table_size, max_heap_table_size)时,会自动将内存临时表转化为磁盘临时表

可通过状态变量created_tmp_tables/created_tmp_disk_tables查看内部临时表的使用情况

 

Group by默认排序, order by null可去除

 

 

Memory引擎默认hash索引

不支持range,可显示创建Btree索引

create table t1_memory (

id int unsigned not null auto_increment primary key,

a1 decimal(15,12),

a2 decimal(15,12),

remark varchar(200) not null,

key idx_u1 (a1,a2)

) engine memory;

 

alter table t1_memory drop key idx_u1, add key idx_u1 using btree (a1,a2);

 

 

innodb plugin 1.0新特性

http://ourmysql.com/archives/1059

启动日志

InnoDB: The InnoDB memory heap is disabled –采用了OS slab allocation,禁用了内置分配器,innodb_use_sys_malloc=on

InnoDB: Mutexes and rw_locks use GCC atomic built-ins –使用GCC atomic builtins执行互斥和读写所,比pthread_mutex_t更高效

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

100609 17:17:11  InnoDB: Setting file ./ibdata1 size to 512 MB

InnoDB: Database physically writes the file full: wait

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:15  InnoDB: Log file xxx/ib_logfile0 did not exist: new to be created

InnoDB: Setting log file  xxx/ib_logfile0 size to 512 MB

InnoDB: Database physically writes the file full: wait

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:21  InnoDB: Log file xxx/ib_logfile1 did not exist: new to be created

InnoDB: Setting log file xxxx/ib_logfile1 size to 512 MB

InnoDB: Database physically writes the file full: wait

InnoDB: Progress in MB: 100 200 300 400 500

InnoDB: Doublewrite buffer not found: creating new http://www.orczhou.com/index.php/2010/02/innodb-double-write/

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

100609 17:17:26 InnoDB Plugin 1.0.6-unknown started; log sequence number 0

100609 17:17:28 [Note] Event Scheduler: Loaded 0 events

100609 17:17:28 [Note] /usr/xtradb/libexec/mysqld: ready for connections.

Version: 5.1.45-log  socket: xxx/mysqld.sock  port: 4331  Source distribution

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-766300/,如需转载,请注明出处,否则将追究法律责任。

上一篇: awk知识点小结1
下一篇: mysql 的thread pool
请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3086828