New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Database Structure view is very slow as of version 4 #13711

Closed
rinu opened this Issue Sep 29, 2017 · 17 comments

Comments

Projects
None yet
2 participants
@rinu

rinu commented Sep 29, 2017

Using phpMyAdmin 3, the database Structure view loads quickly, 0.5 to 1 seconds, regardless of size.
Using phpMyAdmin 4, the bigger the database, the longer the loading takes. With the example size below, from several seconds (locally) to minutes (remote).

I've learned to avoid this view at all costs but it's useful for an overview of tables sizes. I'd like to not wait that long to get that view.

Steps to reproduce

  1. Have a database with ~250 tables and at least ~3 GB data
  2. Click on a database's name which goes to Structure view
  3. Wait for a long time

Server configuration

Operating system:
Ubuntu 16.04

Web server:
Nginx

Database:
MariaDB 15 from Ubuntu package

PHP version:
PHP 7.0 from Ubuntu package

phpMyAdmin version:
master branch (4.8.0-dev)

Client configuration

Browser:
Chrome

Operating system:
Ubuntu 16.04

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 2, 2017

Member

Can you try if enabling $cfg['Servers'][$i]['DisableIS'] will help?

Member

nijel commented Oct 2, 2017

Can you try if enabling $cfg['Servers'][$i]['DisableIS'] will help?

@nijel nijel added the bug label Oct 2, 2017

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

I tried adding $cfg['Servers'][$i]['DisableIS'] = true; to my config.
That might have made it marginally faster, difficult to say.
But it didn't solve the issue.

rinu commented Oct 2, 2017

I tried adding $cfg['Servers'][$i]['DisableIS'] = true; to my config.
That might have made it marginally faster, difficult to say.
But it didn't solve the issue.

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 2, 2017

Member

Thanks for trying that, some MySQL versions had problems with informational schema performance and I just wanted to rule out this...

Member

nijel commented Oct 2, 2017

Thanks for trying that, some MySQL versions had problems with informational schema performance and I just wanted to rule out this...

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

I tried this request in my profiling machine (with Zend Server) and these are the general results:
Entire request: 4,630 ms
All database queries and fetching data combined: 1,574 ms (263 SQL requests, most of them are COUNT(*) requests for each table in database)
One table seems to be special, calling COUNT(*) on it takes over 500 ms, others are much faster.

Some SQL queries are executed multiple times:
SELECT @@version, @@version_comment * 2
SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''root''@''my.localnet''' AND PRIVILEGE_TYPE='EVENT' * 2
SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''root''@''my.localnet''' AND PRIVILEGE_TYPE='TRIGGER' * 2
SET CHARACTER SET 'utf8mb4'; * 2
SET collation_connection = 'utf8mb4_unicode_ci'; * 2
SET lc_messages = 'en_US'; * 2
SHOW TABLES FROM `my_db`; * 3

Some notable function calls:
file_exists() called 2,299 times, 654 ms
is_dir() called 4,653 times, 636 ms
is_file() called 1,034 times, 393 ms
is_writable() called 1,548 times, 213 ms
Twig_Environment::addExtension() called 15,480 times, 54 ms (200 ms inclusive)
PhpMyAdmin\Template::render() called 1,548 times, 41 ms (1,546 ms inclusive)
get_class(), called 34,056 times, 23 ms

rinu commented Oct 2, 2017

I tried this request in my profiling machine (with Zend Server) and these are the general results:
Entire request: 4,630 ms
All database queries and fetching data combined: 1,574 ms (263 SQL requests, most of them are COUNT(*) requests for each table in database)
One table seems to be special, calling COUNT(*) on it takes over 500 ms, others are much faster.

Some SQL queries are executed multiple times:
SELECT @@version, @@version_comment * 2
SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''root''@''my.localnet''' AND PRIVILEGE_TYPE='EVENT' * 2
SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''root''@''my.localnet''' AND PRIVILEGE_TYPE='TRIGGER' * 2
SET CHARACTER SET 'utf8mb4'; * 2
SET collation_connection = 'utf8mb4_unicode_ci'; * 2
SET lc_messages = 'en_US'; * 2
SHOW TABLES FROM `my_db`; * 3

Some notable function calls:
file_exists() called 2,299 times, 654 ms
is_dir() called 4,653 times, 636 ms
is_file() called 1,034 times, 393 ms
is_writable() called 1,548 times, 213 ms
Twig_Environment::addExtension() called 15,480 times, 54 ms (200 ms inclusive)
PhpMyAdmin\Template::render() called 1,548 times, 41 ms (1,546 ms inclusive)
get_class(), called 34,056 times, 23 ms

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

General results for tags/RELEASE_3_4_3_2 in the same machine on the same data:
Entire request: 580 ms
226 SQL requests, no special ones this time.

I can't compare other data.

rinu commented Oct 2, 2017

General results for tags/RELEASE_3_4_3_2 in the same machine on the same data:
Entire request: 580 ms
226 SQL requests, no special ones this time.

I can't compare other data.

nijel added a commit that referenced this issue Oct 2, 2017

Cache temp dir path
The Template class can be constructed many times during single request,
so better to cache temp dir lookup which can take several steps.

Issue #13711

Signed-off-by: Michal Čihař <michal@cihar.com>
@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 2, 2017

Member

Is that table big? Or what is special on it? Maybe lowering $cfg['MaxExactCount'] could help...

Many of the is_dir and is_writable calls should disappear with 4064aec.

However quite big penalty seems to come from Twig rendering, do you have correctly configured $cfg['TempDir']?

Member

nijel commented Oct 2, 2017

Is that table big? Or what is special on it? Maybe lowering $cfg['MaxExactCount'] could help...

Many of the is_dir and is_writable calls should disappear with 4064aec.

However quite big penalty seems to come from Twig rendering, do you have correctly configured $cfg['TempDir']?

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

I have not configured it. The default location of ./tmp is actively being used.

rinu commented Oct 2, 2017

I have not configured it. The default location of ./tmp is actively being used.

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

Yes, the table is very big. But the point was this is more of a defect of my profiling machine. You can freely subtract that 500 ms from the query and total request times.

rinu commented Oct 2, 2017

Yes, the table is very big. But the point was this is more of a defect of my profiling machine. You can freely subtract that 500 ms from the query and total request times.

nijel added a commit that referenced this issue Oct 2, 2017

Avoid constructing twig loader for every template
This adds serious overhead and the loader can be still reused.

Issue #13711

Signed-off-by: Michal Čihař <michal@cihar.com>
@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 2, 2017

Member

I've further improved performance of twig templates in d7d3b57, can you please retry with current master in your environment how that helps?

Member

nijel commented Oct 2, 2017

I've further improved performance of twig templates in d7d3b57, can you please retry with current master in your environment how that helps?

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

Yes, the current master is a lot faster.
2,773 ms for the entire request (can subtract 550 for the one slow count)
Still 263 SQL queries.

file_exists() is still called 2,219 times, taking 549 ms
Other than that, very major improvements everywhere.

Still around 4x slower than phpMyAdmin 3 was but an impressive result.

rinu commented Oct 2, 2017

Yes, the current master is a lot faster.
2,773 ms for the entire request (can subtract 550 for the one slow count)
Still 263 SQL queries.

file_exists() is still called 2,219 times, taking 549 ms
Other than that, very major improvements everywhere.

Still around 4x slower than phpMyAdmin 3 was but an impressive result.

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 2, 2017

Member

The file_exists has to be there until we fully migrate to twig (it's currently used to determine whether to use twig or old purely PHP based templates). But it should be gone for final release.

There is clearly more room for improvements here, I've tried to simplify the API to remove features we seem to never use in #13717

Member

nijel commented Oct 2, 2017

The file_exists has to be there until we fully migrate to twig (it's currently used to determine whether to use twig or old purely PHP based templates). But it should be gone for final release.

There is clearly more room for improvements here, I've tried to simplify the API to remove features we seem to never use in #13717

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

I discovered another thing.
phpMyAdmin 3 estimates the number of rows in a table in some cases. For that "special" table of mine, no COUNT(*) request is ever made and the result is showing ~65,013
I know that's not the correct count because the new version actually makes the count query and comes up with 72,409

Is there a way to enable this counting optimization for InnoDB tables in newer versions as well?

rinu commented Oct 2, 2017

I discovered another thing.
phpMyAdmin 3 estimates the number of rows in a table in some cases. For that "special" table of mine, no COUNT(*) request is ever made and the result is showing ~65,013
I know that's not the correct count because the new version actually makes the count query and comes up with 72,409

Is there a way to enable this counting optimization for InnoDB tables in newer versions as well?

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 2, 2017

Member

The MaxExactCount switch is supposed to do that, just set it to lower value.

Member

nijel commented Oct 2, 2017

The MaxExactCount switch is supposed to do that, just set it to lower value.

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Oct 2, 2017

I'm so sorry I completely missed it before. I didn't think it would do something like this. The default value for this must have changed drastically at some point.
Setting it to 5000 brings the total request time down to 1.1 seconds. Very nice.

rinu commented Oct 2, 2017

I'm so sorry I completely missed it before. I didn't think it would do something like this. The default value for this must have changed drastically at some point.
Setting it to 5000 brings the total request time down to 1.1 seconds. Very nice.

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Oct 2, 2017

Member

It was changed in 3f21378, see #10195 (we've changed issue tracker since then).

Member

nijel commented Oct 2, 2017

It was changed in 3f21378, see #10195 (we've changed issue tracker since then).

@nijel nijel closed this in 99f2b16 Oct 3, 2017

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Nov 2, 2017

Member

It would be great if you could rerun the tests in your environment on current master, as #13297 has been implemented and most of those file_exists calls should be gone now.

Member

nijel commented Nov 2, 2017

It would be great if you could rerun the tests in your environment on current master, as #13297 has been implemented and most of those file_exists calls should be gone now.

@rinu

This comment has been minimized.

Show comment
Hide comment
@rinu

rinu Nov 2, 2017

In general, it worked. But loading a lot more twig can cause new issues.
I now have mixed results with the autoloader taking from 200 ms to over 700 ms, seemingly randomly. This is probably caused by the shared filesystem between my main host and the vm running the profiler, also not enough opcache. Best case overall request time is under 300 ms on my main host and 900 ms on the profiler.
So depending on the filesystem, this can be seen as not an issue.

Some mentions from the profiler results:
PhpMyAdmin\Config\FormDisplayTemplate::displayTabsTop() takes almost 100 ms
PhpMyAdmin\Util::getIcon() is called 4890 times and takes 94 ms

rinu commented Nov 2, 2017

In general, it worked. But loading a lot more twig can cause new issues.
I now have mixed results with the autoloader taking from 200 ms to over 700 ms, seemingly randomly. This is probably caused by the shared filesystem between my main host and the vm running the profiler, also not enough opcache. Best case overall request time is under 300 ms on my main host and 900 ms on the profiler.
So depending on the filesystem, this can be seen as not an issue.

Some mentions from the profiler results:
PhpMyAdmin\Config\FormDisplayTemplate::displayTabsTop() takes almost 100 ms
PhpMyAdmin\Util::getIcon() is called 4890 times and takes 94 ms

nijel added a commit that referenced this issue Nov 2, 2017

Cache already loaded templates
This should improve performance when loading many small templates.

Issue #13711

Signed-off-by: Michal Čihař <michal@cihar.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment