Yasufumi,

OK I see. It makes sense. See my another email I proposed to join these 3
innodb_buffer_pool pages tabels we have now into one.

On Sun, May 9, 2010 at 5:54 PM, Yasufumi Kinoshita <
<email address hidden>> wrote:

> Vadim,
>
> Simply removing dict_sys->mutex may cause crash as result of the
> inconsistency.
> Only removing name columns allows to remove the lock.
>
> Peter,
>
> innodb_buffer_pool_pages_index is really nonsense for me from the view
> point of performance.
> There are no index at the table list, index list in the dictionary on
> memory.
> (It was removed because of another performance problem from maintaining the
> indexes in InnoDB's history.
>  Basically InnoDB doesn't use index_id to specify the index information
> internal.
>  So, to maintain indexes is only waste of cpu and cause useless mutex
> contention.)
>
> innodb_buffer_pool_pages_index is the cartesian join of non-indexed tables.
> 3min is natural, if you use many tables and indexes in the on-memory
> dictionary.
>
> innodb_buffer_pool_pages_index should not have name columns.
> If you want names, you should join with index_id (after aggregate) to the
> another dict view.
> (InnoDB must not provide index_id index for dictionary only for
> innodb_buffer_pool_pages_index
>  with sacrifice ordinary all other performance)
>
> So I recommend to remove the name columns from
> innodb_buffer_pool_pages_index.
>
> If I implemented it, I never implement name columns.
>
> --
> 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/