MySQL Bugs: #88422: MySQL 5.7 innodb purge thread get oldest readview could block other transaction

Bug #88422	MySQL 5.7 innodb purge thread get oldest readview could block other transaction
Submitted:	9 Nov 2017 12:20	Modified:	14 Nov 2017 12:53
Reporter:	raolh rao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 5.6 ,  it only to find the last one of trx_sys->view_list, which is the oldest readview.  this process  is very quick.

storage/innobase/read/read0read.cc :  read_view_purge_open()

but in MySQL 5.7, it  may need to  find many ones from the last of global readview list (MVCC::m_views)  and  skip the one whose close flag is true.  if  there are many closed readview  in the global readview list,  it would spend long time to find the oldest readview.

storage/innobase/read/read0read.cc : MVCC::get_oldest_view()

what's worse，during the process of finding oldest readview, it should hold the trx_sys->mutex,  which would block all other transaction begin, commit and so on. then lead low performance in high concurrence.

How to repeat:
start many readonly transaction（whose readview is closed after commit) and many read write transaction， then you could find this problem happen.

Suggested fix:
no

Hi!

Thank you for your bug performance report.

I have analysed carefully what you wrote and it is my opinion that code in 5.7 is much more streamlined. First of all, there is no direct one-to-one correspondence between 5.6 functions and 5.7 methods. Second, 5.6 holds system mutex for a short period, while 5.7 methods holds no mutex what so ever. The only code dealing in that 5.7 method is executed only in debug mode and it only checks whether a thread is owning the mutex in question.

If you have experienced significant slowdown in highly concurrent read/write load between 5.6 and 5.7, we would like to hear about it . We would need data on the slowdown, plus entire data on how we can repeat what you have experienced. That means a fully repeatable test case.

Hi Sinisa,

How about checking the code that calls the one mentioned here, like this:

void
MVCC::clone_oldest_view(ReadView* view)
{
        mutex_enter(&trx_sys->mutex);

        ReadView*       oldest_view = get_oldest_view();

        if (oldest_view == NULL) {
...

IMHO it is easy to find out that trx_sys->mutex is set whenever the get_oldest_view() method is called.

Hi Sinisa,

I have describled this problem and its happening scene in this article:

https://yq.aliyun.com/articles/223320

but it is writed in chinese,  and a little hard to translate to english. you can just watch pictures attached to it, may those be help for you.

This problem is very easy to recur in our  online product environment and offline simulate  environment.  I would try to write sysbench test case, but can't promise stable
 recuring

Hi Mr. Rao,

I am eagerly awaiting your test case. Regarding repeating the results, this should not be a problem, as I intend of running the test case several times ...

Hi!

We did a lot of additional analysis. It turns out that a lot of code was changed between 5.6 and 5.7, which resulted in having SELECTs and UPDATEs much faster. However, you have noted that purge might have got slower, due to all these changes. Although SELECT and UPDATE are much faster, slower purge is something that could be , eventually, improved.

Hence, this is not a bug, but this is a full fledged and proper feature request, so I am verifying it as such.

Hi!

A final decision has been made that part of the problem will be treated as a bug, while another part will be a feature defined in one Worklog.