Comment 8 for bug 576041

Yasufumi,

There is no point removing functionality as there is another table which has
similar columns but without table name and index name.
what is important is to ensure it is not intrusive - does not block
everything for long time. Perhaps it has to require mutex for each row ?

I'm also surprised why it takes so long - 3 min to lookup the name of the
table and index by ID for 40000 pool pages sounda a bit slow to me

Finally note there is typically a lot of pages which belong to the same
index of the same table. Can't we cache table lookups so we just lookup
each table only once ?

On Fri, May 7, 2010 at 6:52 AM, Vadim Tkachenko <email address hidden> wrote:

> Yasufumi,
>
> Can't we remove locking of dict_sys in innodb_buffer_pool_pages_index ?
>
> Information in innodb_buffer_pool_pages_index is not critical
> and it is OK if it is non-consistent.
>
>
> On Fri, May 7, 2010 at 1:47 AM, Yasufumi Kinoshita
> <email address hidden> wrote:
> > Peter, as I mailed.
> >
> > There seems to be 2 type of problems.
> >
> > 1.
> > I cannot improve innodb_buffer_pool_pages_index if you want columns as it
> is.
> > needs to remove name information (db, table, index). To pickup name,
> dict_sys is needed and
> > the looking up is slow to be used for all of blocks in BP.
> >
> > 2.
> > i_s_innodb_buffer_pool_pages.patch
> > uses mutex in wrong order and may cause deadlock in worse.
> >
> > I will fix 2. but I cannot fix 1 without contract of function.
> >
> > --
> > innodb_buffer_pool_pages_index performance
> > https://bugs.launchpad.net/bugs/576041
> > You received this bug notification because you are a member of Percona
> > developers, which is the registrant for Percona Server.
> >
> > Status in Percona Server with XtraDB: Triaged
> >
> > Bug description:
> > Accerssing innodb_buffer_pool_pages_index can be very slow under load,
> > it also can cause certain mutexes to be locked for long time causing
> server stall and potentially server crash (if locks are held more than
> allowed by Innodb watchdog)
> >
> > ----------
> > SEMAPHORES
> > ----------
> > OS WAIT ARRAY INFO: reservation count 4112, signal count 4016
> > --Thread 1235454272 has waited at buf/buf0lru.c line 2046 for 127.00
> seconds the semaphore:
> > Mutex at 0xde2020 '&buf_pool_mutex', lock var 1
> > waiters flag 1
> > --Thread 1247275328 has waited at buf/buf0lru.c line 698 for 127.00
> seconds the semaphore:
> > Mutex at 0xde2020 '&buf_pool_mutex', lock var 1
> > waiters flag 1
> > --Thread 1214474560 has waited at buf/buf0flu.c line 1077 for 127.00
> seconds the semaphore:
> > Mutex at 0xde2020 '&buf_pool_mutex', lock var 1
> > waiters flag 1
> > --Thread 1245944128 has waited at row/row0purge.c line 543 for 122.00
> seconds the semaphore:
> > Mutex at 0x6812220 '&dict_sys->mutex', lock var 1
> > waiters flag 1
> > Mutex spin waits 24624, rounds 144847, OS waits 3454
> > RW-shared spins 813, OS waits 483; RW-excl spins 48, OS waits 154
> > Spin rounds per wait: 5.88 mutex, 22.41 RW-shared, 104.08 RW-excl
> >
> >
> >
>
>
> --
> Vadim Tkachenko, CTO, Percona Inc.
> Phone +1-888-401-3403, Skype: vadimtk153
> Schedule meeting: http://tungle.me/VadimTkachenko
>
> --
> innodb_buffer_pool_pages_index performance
> https://bugs.launchpad.net/bugs/576041
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in Percona Server with XtraDB: Triaged
>
> Bug description:
> Accerssing innodb_buffer_pool_pages_index can be very slow under load,
> it also can cause certain mutexes to be locked for long time causing server
> stall and potentially server crash (if locks are held more than allowed by
> Innodb watchdog)
>
> ----------
> SEMAPHORES
> ----------
> OS WAIT ARRAY INFO: reservation count 4112, signal count 4016
> --Thread 1235454272 has waited at buf/buf0lru.c line 2046 for 127.00
> seconds the semaphore:
> Mutex at 0xde2020 '&buf_pool_mutex', lock var 1
> waiters flag 1
> --Thread 1247275328 has waited at buf/buf0lru.c line 698 for 127.00 seconds
> the semaphore:
> Mutex at 0xde2020 '&buf_pool_mutex', lock var 1
> waiters flag 1
> --Thread 1214474560 has waited at buf/buf0flu.c line 1077 for 127.00
> seconds the semaphore:
> Mutex at 0xde2020 '&buf_pool_mutex', lock var 1
> waiters flag 1
> --Thread 1245944128 has waited at row/row0purge.c line 543 for 122.00
> seconds the semaphore:
> Mutex at 0x6812220 '&dict_sys->mutex', lock var 1
> waiters flag 1
> Mutex spin waits 24624, rounds 144847, OS waits 3454
> RW-shared spins 813, OS waits 483; RW-excl spins 48, OS waits 154
> Spin rounds per wait: 5.88 mutex, 22.41 RW-shared, 104.08 RW-excl
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/percona-server/+bug/576041/+subscribe
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: +1 888 401 3401 ext 501 Skype: peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

Percona Training Workshops
http://www.percona.com/training/