Startup performance regression : schema update/validate

Description

When using schema update/validate, Hibernate needs to access the current state of the schema on the database server.

In 4.3, Hibernate was executing "SHOW FULL TABLES and SHOW FULL COLUMNS with LIKE '%' " in order to discover the complete database tables columns. Starting in 5.0 however Hibernate is no longer using "%" but it is giving as parameter all individual columns from every table it discovered. The amount of queries to the database during startup increased from ~200 to ~2700. Due to the constant load to which the server is exposed from other applications, each query takes 100-150ms, thus increasing the startup time of our application from 10-20 seconds to 4-5 minutes.

The number of queries can be easily seen by enabling MySQL query log.

Environment

Ubuntu Server 14.04 LTS x64, Percona Server 5.6.

Activity

Show:
Sergiu Hlihor
August 5, 2016, 12:33 PM

Correct. Schema update/validation phase of startup.

Steve Ebersole
August 5, 2016, 12:43 PM

I can see how that would be problematic. However the flip side is that we had many problems trying to get the different handling for casing of the db-object names as returned from the database in terms of determining if the name is quoted or not and then being able to match those names up with those from the mappings.

But now that we have this working correctly it is a good time to go back and look at making it faster.

Sergiu Hlihor
August 9, 2016, 9:34 AM

As extra notes, even in 4.3 schema update/validation accounts for the most of the startup time so any optimizations beyond 4.3 levels would be highly appreciated.

Gail Badner
October 4, 2016, 6:38 PM

Reopening to add label.

Gail Badner
January 11, 2017, 9:06 AM

Add 5.1.4 as fix version.

Assignee

Andrea Boriero

Reporter

Sergiu Hlihor

Fix versions

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure