[MDEV-9866] "show tables" slow with many tables compared to information_schema.tables Created: 2016-04-04  Updated: 2016-04-05  Resolved: 2016-04-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - MyISAM
Affects Version/s: 10.0.24
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Dreas van Donselaar Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: need_feedback
Environment:

Debian Linux


 Description   

The query "show tables" on a database with many tables (>2M) is much slower than "select table_name from information_schema.tables where table_schema='database1'". I'm not sure if this is the same issue as MDEV-191.

Compare:

~ # time mysql -e "select table_name from information_schema.tables where table_schema='database1';" | wc -l
2498519

real 0m37.912s
user 0m1.048s
sys 0m0.132s

With:

~ # time mysql -e "use database1; show tables" | wc -l
2498575

real 2m16.279s
user 0m1.132s
sys 0m0.124s

 Comments   
Comment by Elena Stepanova [ 2016-04-04 ]

Could you please attach or paste your cnf file(s)?

I don't have 2M tables, but with 1M I get nothing of the sort:

time mysql -uroot -e "select table_name from information_schema.tables where table_schema='db'" | wc -l
 
1000001
 
 
 
real    0m8.275s
 
user    0m0.220s
 
sys     0m0.168s


time mysql -uroot -e "use db; show tables" | wc -l
 
1000001
 
 
 
real    0m8.063s
 
user    0m0.284s
 
sys     0m0.080s

Comment by Dreas van Donselaar [ 2016-04-05 ]

I'm also unable to reproduce it in a testing environment. On the specific server there were some issues with "opening tables" on queries, so this bug report should be closed as invalid (as this seems to be a symptom of an unrelated issue).

Comment by Elena Stepanova [ 2016-04-05 ]

dreas
Are you planning to file a bug report about the issues with opening tables, or did you find out it was an environmental problem?

Comment by Dreas van Donselaar [ 2016-04-05 ]

Unfortunately I cannot consistently reproduce that issue. We have to kill MariaDB to get things working again. I'll reach out / report what I can as soon as it reoccurs.

Comment by Elena Stepanova [ 2016-04-05 ]

Closing as suggested above.

Generated at Thu Feb 08 07:37:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.