oracle如何优化大并发下的进程切换

wolfop

另外mysql在大并发下并不见得好，不是connect pool就能解决所有问题，MYSQL这种没执行计划缓存的数据库实现，大并发必须靠sharding，提供更多的CPU，否则解析SQL耗死你

zergduan

wolfop 发表于 2015-9-2 13:08
另外mysql在大并发下并不见得好，不是connect pool就能解决所有问题，MYSQL这种没执行计划缓存的数据库实现 ...

头像是 索尔维会议 的合照么？ 太小了，看着很像~

Yong Huang

> MYSQL这种没执行计划缓存的数据库实现

According to "A Practical Look at the MySQL Query Cache"
ftp://ftp.fu-berlin.de/unix/data ... ql-query-cache.html

"Code caches are used to primarily lessen the impact of "hard parse" activity ... Examples include Oracle's shared pool/library cache, and SQL Server's and Sybase's SQL/Procedure caches."

So MySQL is not one of these databases. But the article goes on to say

"the query cache stores the SELECT queries issued by clients to the MySQL database server. In this respect, it acts a lot like traditional database SQL caches, in that identically issued queries will be located and re-issued by the database engine without hard parse activity"

So I'm confused. Isn't MySQL query cache equivalent to Oracle's library cache? (The article also says, not quoted here, the query cache also stores query result, which I think can be compared to Oracle's result cache. But that's not my interest for now.)

wolfop

Yong Huang 发表于 2015-9-8 22:13
> MYSQL这种没执行计划缓存的数据库实现

According to "A Practical Look at the MySQL Query Cache"

Query cache is not a execution plan cache neither like the library cache. It is more like the oracle result cache which was introduced in 11G but with different implentation.

Yong Huang

> Query cache is not a execution plan cache neither like the library cache.

Thanks. How do we understand "the query cache stores the SELECT queries issued by clients to the MySQL database server"? Do you think it's incorrect? Or it's correct but the query cache only stores the query text strings without execution plans? Or something else?

wolfop

Yong Huang 发表于 2015-9-10 00:03
> Query cache is not a execution plan cache neither like the library cache.

Thanks. How do we und ...

You understand should be right. No execution plan in Mysql.

myownstars

wolfop 发表于 2015-9-10 20:46
You understand should be right. No execution plan in Mysql.

确切的说，mysql不缓存重用执行计划，即每次sql都要硬解析；
个人观点：mysql只实现nest-loop join且没有直方图，硬解析选择执行计划的cost应该小很多(相比oracle)，因此不需要缓存执行计划；
另外，我听过的说法，mysql大并发大场景下，全是因为上下文切换导致load飙高，很少有文章说因为sql解析导致

Yong Huang

> mysql大并发大场景下，全是因为上下文切换导致load飙高

Where do you read that? Is it high sys CPU? If it's high user CPU, that doesn't look like it.

I think MySQL already uses thread model. That is, one mysql process contains many threads, each *thread* corresponding to one client connection, unlike Oracle on UNIX/Linux where one *process* corresponds to one client connection by default. If MySQL uses thread model, why would there be high context switch?

wolfop

myownstars 发表于 2015-9-10 22:06
确切的说，mysql不缓存重用执行计划，即每次sql都要硬解析；
个人观点：mysql只实现nest-loop join且没有 ...

和是否nest loop join无关，join一般是DW用。DW是否有执行计划缓存反而不太重要。
mysql 大并发上下文切换？呵呵mysql用内核线程，context switch必然也有，不过ORACLE用process，恐怕context switch更高。估计还是mysql的实现的问题。
想想可能还是informix ids的架构理论上大并发比较好，因为是用用户线程实现，应该没什么内核的上下文切换。但是那个产品已经被IBM给废了。另外一个问题是你看不出MYSQL到底解析SQL用了多少资源，又没有AWR，而且解析应该是不用context switch