Improving performance of a Specific MySQL Query

Question

I am using an enterprise application at multiple sites that requires the use of a database. I am not a developer of the application, thus I cannot modify how the application queries the database. Presently, we are trying to migrate from MySQL to PostgreSQL because it happens that the application works better on PostgreSQL.

The application stores data across thousands of tables in a particular database instance. The application has a built-in migration feature that transfers the data from one database vendor (e.g. MySQL) to another. (e.g. PostgreSQL) During the application's migration process, it performs the following two queries for each table in the instance:

SHOW FULL TABLES FROM 'xxx' LIKE 'DATASERIES_yyy'

SELECT COUNT(1) FROM 'DATASERIES_yyy'

Where 'xxx' is the database instance and 'yyy' is an arbitrary, non-sequential integer.

Each of the above queries is run on each 'DATASERIES_yyy' table before data migration actually begins. The process of running the above two queries is by FAR the largest bottleneck of the migration process. I see from MySQL's Information Schema that running the above two queries takes on average .25 seconds to complete, and the majority of that time is spent on 'Checking Permissions'. This is true for powerful servers or even my modest work laptop.

I have scoured the internet for ways that might improve the performance of these two queries. Because I can't modify the way the application queries the database, I am only able to focus on MySQL itself.

Things I have attempted to speed them up: 1. Ensured innodb_stats_on_metadata is off. 2. Set innodb_read_io_threads = 64. 3. Ensured query caching is turned off. 4. Removed all users except the 1 user name the application uses to log in to the database. 5. Set query_cache_type = 0. 6. Set innodb_io_capacity = 1000. 7. Buffer Pools have been configured to use about 70% of server memory long ago.

I am not expecting any miracles, but are there other things I can try to help this process speed up?

Because the MySQL database will be discarded after the migration is complete, I am able to use non-traditional settings just to get the process going faster, so feel free to make recommendations that would be considered temporary or short-term. (Obviously, data integrity can't be compromised.)

BTW, I know I can't use innodb_read_only because the application writes four UPDATES at the beginning of the process and DELETEs them at the end so that it knows the process began and ended correctly.

These sites are all MySQL instances running in Windows Environments. Most sites are running MySQL versions 5.7. The others are 5.5.

Any guidance appreciated. Thank you!

Edit: 1. User permissions are set globally, not per table. 2. It is not at all untypical for there to be 10,000 of the 'DATASERIES_yyy' tables. 3. Yes, the underscore is in the 'DATASERIES_yyy' table name. I have no control over the naming of the tables.

Answer 1

The permissions in MySQL can be granted globaly, or on a table by table basis. Perhaps the user for this application has been granted permissions on individual tables, making the amount of data quite large. It's also the case that the permissions are stored in a MyISAM database (the "mysql" database), so none of the InnoDB settings have an impact on its performance.

Use the SHOW GRANTS command to inspect the permissions for the user. If there are a great many such permissions, and if you are game to do so, grant those permissions on *.* instead of the many individual tables.

If that isn't the issue, then it may just be an unavoidable side-effect of doing permissions checking on a very large number of tables. There must be a great many of them if a quarter of a second per table is significant delay.

It also strikes me that the LIKE 'DATASERIES_yyy' part is a pattern if the underscore is an actual underscore. The pattern matching part could be slowing things down on a large number of tables. The only way to deal with that would be to rename tables so that they don't contain underscores.

Answer 2

The things you tuned should be either helpful or at least harmless.

This would provide all the info in a single step, and might take a total of a couple of seconds:

USE xxx;
SHOW TABLE STATUS LIKE 'DATASERIES%';

The latter will list all the table names, an estimate of the number of rows, and some other stuff. Alas, it will not necessarily give you the exact row count for InnoDB tables; is that critical?

10K tables means 10K (or more) files in a single directory. Windows does not do well with large directories; suggest rethinking the need for so many table.

Were all the actions done in a single connection? Or did you reconnect for each table? Reconnecting would cost something; it is not possible to predict how much.