MariaDb 10.4: Deleting Databases is slow / causes timeout


Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"


Top Level » MySQL / MariaDB

MariaDb 10.4: Deleting Databases is slow / causes timeoutLast Modified: Sep 5, 2019, 10:48 pm
As of version 10.4, MariaDB has replaced the mysql.user table with mysql.globa_priv table, and mysql.user becomes a view:
https://mariadb.com/kb/en/library/mysqluser-table/

For the most part, this really shouldn't matter, as DA would be using the mysql_use_new_user_methods=1 (for ALTER USER, etc, instead of direct mysql.user control).

We had a report of slow response time for deleting database, where DA does some fancy queries to figure out if a User is on mysql databases or not, so it would only delete the User if it's only on the one being deleted.  The short of it is that DA was looking at a few rows that were not specifically indexes, thus the lookup would have been done in a linear fashion (I believe), hence very slow response time, as this server had 45,000 database accounts (yes this is a lot, but there is no limit).

To optimize things, one can added 3 new index files on the related tables:
  1. mysql.global_priv

    • user
    • host
  2. mysql.db

    • db
Where, I believe the biggest gain was mysql.db:db.

To add a simple index to a database, login to /phpMyAdmin with your da_admin account (/usr/local/directadmin/conf/mysql.conf),
and go to the "mysql" database.  Click the table you'd like (eg: global_priv), and go to the "Structure" tab.
About 1/2 way down there is a line that says:

Create an index on [1] columns [GO]

Click that GO button with 1 in the form.
For "Index Choice" change "UNIQUE" to be "INDEX", select the column of your choice (eg: 'user'), and click "Go".

An Index is a lookup table, so MariaDB can find a specific value in that row more quickly.  If there is no index for a row that is heavily used, performance can be affected.  Note that these tables already do have "PRIMARY" indexes combining sometimes 3 different columns, but if DA is only referencing just 1 column, then you should see performance gains by giving that one column it's own index.

The before and after results were faily clear it worked:
  • Before:

    MariaDB [mysql]> select count(*) from user;
    +----------+
    | count(*) |
    +----------+
    |    45196 |
    +----------+
    1 row in set (4 min 48.356 sec)

  • After:

    MariaDB [mysql]> select count(*) from user;
    +----------+
    | count(*) |
    +----------+
    |    45201 |
    +----------+
    1 row in set (0.001 sec)


Perhaps this is something the MariaDB team could look at in the future, but for now this seems to work.




Similar report on MariaDB 10.3 which does not use a view, solution was to index:
  1. mysql.user

    • user
    • host
  2. mysql.db

    • db



Related: Rewrite clearing of access hosts for system account during DB removal

© 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST