[Phpmyadmin-devel] testing master on a server with many databases
Hi, With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users. In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern. I tested with root, since the reports we got about the server being slow seem to be occurring for root. Good news --------- First, the login. It takes about 7 seconds before seeing the initial page after login. In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables). Browsing tables, grid editing, searching inside a table behave normally. Using Server > Databases works correctly, including switching pages. Problems -------- Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help. Database filter: I got mixed results. Sometimes it works (after about 10 seconds) but sometimes it stalls and does not find anything. I could not find a consistent way to reproduce. Server > Users: "Error in processing request. Error code: 500. Error text: Internal server error". -- Marc Delisle | phpMyAdmin
Le 2014-10-19 08:04, Marc Delisle a écrit :
Hi,
With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users.
I forgot to mention that the test is being done with this: $cfg['Servers'][$i]['DisableIS'] = true;
In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern.
I tested with root, since the reports we got about the server being slow seem to be occurring for root.
Good news ---------
First, the login. It takes about 7 seconds before seeing the initial page after login.
In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables).
Browsing tables, grid editing, searching inside a table behave normally.
Using Server > Databases works correctly, including switching pages.
Problems --------
Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help.
Database filter: I got mixed results. Sometimes it works (after about 10 seconds) but sometimes it stalls and does not find anything. I could not find a consistent way to reproduce.
Server > Users: "Error in processing request. Error code: 500. Error text: Internal server error".
-- Marc Delisle | phpMyAdmin
Hi Marc, On Sun, Oct 19, 2014 at 5:34 PM, Marc Delisle <marc@infomarc.info> wrote:
Hi,
With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users.
In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern.
I tested with root, since the reports we got about the server being slow seem to be occurring for root.
In bug #4243 [1] the slowness was reported for unprivileged user. Can you also test this scenario?
Good news ---------
First, the login. It takes about 7 seconds before seeing the initial page after login.
In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables).
Browsing tables, grid editing, searching inside a table behave normally.
Using Server > Databases works correctly, including switching pages.
Great!
I saw a couple of queries we can avoid when loading the navigation panel. I'll be working on them today
Problems --------
Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help.
Database filter: I got mixed results. Sometimes it works (after about 10 seconds) but sometimes it stalls and does not find anything. I could not find a consistent way to reproduce.
Server > Users: "Error in processing request. Error code: 500. Error text: Internal server error".
I'll try to recreate these issues in my environment and update you all on
the progress. Did you perform these test on the server provided by JM? It would be great if I can would get access to that, so I can test my commits immediately. Btw, does this server functions as our demo, automatically updating to new commits? Were all these tests done with grouping enabled in the navigation? I'm quite interested in seeing how the performance would be when the it is disabled. [1] https://sourceforge.net/p/phpmyadmin/bugs/4243/ -- Thanks and Regards, Madhura Jayaratne
Madhura Jayaratne a écrit :
Hi Marc,
On Sun, Oct 19, 2014 at 5:34 PM, Marc Delisle <marc@infomarc.info> wrote:
Hi,
With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users.
In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern.
I tested with root, since the reports we got about the server being slow seem to be occurring for root.
In bug #4243 [1] the slowness was reported for unprivileged user. Can you also test this scenario?
Yes, and it's not pretty. A user having a wildcard privilege (for example on db1\_%) has to wait 2 min 15 seconds after login, to see the navi and main panel for his db named "db1_". Not sure why, but after opening the db, browsing the first table took 65 seconds (this is MyISAM). After I have browsed one table, I can browse the other tables in the db (they are the same size) quickly. If I logout/login, the browsing slowness is back (just for the first table).
Good news ---------
First, the login. It takes about 7 seconds before seeing the initial page after login.
In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables).
Browsing tables, grid editing, searching inside a table behave normally.
Using Server > Databases works correctly, including switching pages.
Great! I saw a couple of queries we can avoid when loading the navigation panel. I'll be working on them today
Problems --------
Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help.
Database filter: I got mixed results. Sometimes it works (after about 10 seconds) but sometimes it stalls and does not find anything. I could not find a consistent way to reproduce.
Server > Users: "Error in processing request. Error code: 500. Error text: Internal server error".
I'll try to recreate these issues in my environment and update you all on the progress.
Did you perform these test on the server provided by JM? It would be great if I can would get access to that, so I can test my commits immediately.
Yes, it would be great.
Btw, does this server functions as our demo, automatically updating to new commits?
Yes.
Were all these tests done with grouping enabled in the navigation? I'm quite interested in seeing how the performance would be when the it is disabled.
I have no access to config.inc.php.
-- Marc Delisle (phpMyAdmin)
On Sun, Oct 19, 2014 at 5:34 PM, Marc Delisle <marc@infomarc.info> wrote:
Hi,
With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users.
In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern.
I tested with root, since the reports we got about the server being slow seem to be occurring for root.
Good news ---------
First, the login. It takes about 7 seconds before seeing the initial page after login.
In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables).
Browsing tables, grid editing, searching inside a table behave normally.
Using Server > Databases works correctly, including switching pages.
Problems --------
Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help.
Database filter: I got mixed results. Sometimes it works (after about 10 seconds) but sometimes it stalls and does not find anything. I could not find a consistent way to reproduce.
There was a UI problem which caused the throbber to be removed before searching has finished and I suspect the above problem is due to that. The problem is fixed with [1]. Can you try with again with the commit. [1] https://github.com/phpmyadmin/phpmyadmin/commit/d2aec03f2b0ef8bfc8d45fe582b9... -- Thanks and Regards, Madhura Jayaratne
Madhura Jayaratne a écrit :
On Sun, Oct 19, 2014 at 5:34 PM, Marc Delisle <marc@infomarc.info> wrote:
Hi,
With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users.
In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern.
I tested with root, since the reports we got about the server being slow seem to be occurring for root.
Good news ---------
First, the login. It takes about 7 seconds before seeing the initial page after login.
In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables).
Browsing tables, grid editing, searching inside a table behave normally.
Using Server > Databases works correctly, including switching pages.
Problems --------
Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help.
Database filter: I got mixed results. Sometimes it works (after about 10 seconds) but sometimes it stalls and does not find anything. I could not find a consistent way to reproduce.
There was a UI problem which caused the throbber to be removed before searching has finished and I suspect the above problem is due to that. The problem is fixed with [1]. Can you try with again with the commit.
[1] https://github.com/phpmyadmin/phpmyadmin/commit/d2aec03f2b0ef8bfc8d45fe582b9...
I can no longer reproduce the problem. -- Marc Delisle (phpMyAdmin)
On Sun, Oct 19, 2014 at 5:34 PM, Marc Delisle <marc@infomarc.info> wrote:
Hi,
With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users.
In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern.
I tested with root, since the reports we got about the server being slow seem to be occurring for root.
Good news ---------
First, the login. It takes about 7 seconds before seeing the initial page after login.
In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables).
Browsing tables, grid editing, searching inside a table behave normally.
Using Server > Databases works correctly, including switching pages.
Problems --------
Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help.
Commits 5a4d8a385529a2e3bec24edf62d1dc30c835895c, 2abae135e67a044db6bdd4940704da01917d20d7 and e277e4d80f334f38c2e6276cf1341426c886bdf6 should improve the situation here. These are from the pull request 1381 [1] and not yet merged to master branch. [1] https://github.com/phpmyadmin/phpmyadmin/pull/1381/commits -- Thanks and Regards, Madhura Jayaratne
On Tue, Oct 21, 2014 at 12:26 PM, Madhura Jayaratne <madhura.cj@gmail.com> wrote:
On Sun, Oct 19, 2014 at 5:34 PM, Marc Delisle <marc@infomarc.info> wrote:
Hi,
With current master (8785ea0) I made some tests on a MySQL 5.5.37 server containing about 81000 databases and about 81000 users.
In the mysql.db tables there are about 81000 grant entries; each one grants some wildcard access to databases starting with a certain pattern.
I tested with root, since the reports we got about the server being slow seem to be occurring for root.
Good news ---------
First, the login. It takes about 7 seconds before seeing the initial page after login.
In the navi panel, there is a db page selector with 107 page numbers. Going to any page takes about 7 seconds. Opening a db takes about 4 second (each db seems to contain about 31 tables).
Browsing tables, grid editing, searching inside a table behave normally.
Using Server > Databases works correctly, including switching pages.
Problems --------
Going to a MyISAM table, Structure, and trying to change a column's structure takes 30 seconds before seeing the dialog for this column. Switching this table to InnoDB does not help.
Commits 5a4d8a385529a2e3bec24edf62d1dc30c835895c, 2abae135e67a044db6bdd4940704da01917d20d7 and e277e4d80f334f38c2e6276cf1341426c886bdf6 should improve the situation here. These are from the pull request 1381 [1] and not yet merged to master branch.
[1] https://github.com/phpmyadmin/phpmyadmin/pull/1381/commits
This pull request has been merged to the master branch. -- Thanks and Regards, Madhura Jayaratne
participants (2)
-
Madhura Jayaratne -
Marc Delisle