Gedcom Import and MySQL 8

[tngrlkrz]

I undertook some remote testing with MySQL 8 on TNG 14.0.2 with some disastrous gedcom import times.  I import all data, including media links,  from desktop software periodically after 'clearing' the table with 'replace all data'.

Connection to the database went well, with config.php set to required values.

$database_host = "localhost";
$database_port = "3308";
$database_socket = "/tmp/mysql8.sock";

Database was set to:

Storage engine = InnoDB
Collation (and all tables) = utf8mb4_0900_ai_ci  (recommended by ICDSoft, my provider).

Diagnostic on Admin had all green check with the expected versions listed.

With MySQL 5.7 ,  MyISAM storage engine and collation of "utf8mb4_general_ci" , my gedcom import of 15,000 persons and 3,000 media normally completes in a minute or so.

The database was empty, and a gedcom import was started.

With MySQL 8, I stopped the import after 1/2 hour when only 2,000 people had been imported.  At that rate, I estimate the import would take roughly 4 hours.  From looking around, there are apparently some SQL commands such as having no cache, and disabling commit, etc., that might shorten this time, but something seems seriously awry populating this transactional database.   I did google about a bit and see that slow import is not uncommon but slower by a factor of 240:1  than MySQL 5? I didn't look yet at any logs to see what might be happening.  

Would like to hear from those who import all data with acceptable speeds on MySQL 8.    I know the official expiration of MySQL 5 is this October, but support and maintenance updates will continue into 2026, do no short term urgency.  However, MySQL was intially released nearly 5 years ago, so was shocked by the import times.  

Wanted to hear from the forum first, before pass a note to Darrin. I am hoping there's something to fix or improve in gedimport.php.

Lastly, 5 of the 6 mods (listed) I use which creating tables did so with the proper collation. The one that did not was the "who is online" mod.  It installs, but the table creation fails with a popup of "-1".

Mod                                      Table

Created Tables properly with MySQL8 parameters

Cousins                           tng inlaw marriages, tng cousin marriages
Persistent Bookmarks    tng bookmarks
Show All Family              tng show all family
Sosa                                tng sosa

Failed to create table

Who is Online                  tng users online

Created table but with SQL 5 storage engine and collation

Treetops                          treetops

[tngrlkrz]

FWIW to anyone considering converting to MySQL 8.  I have ended my testing with TNG 14 and MySQL 8.  Darrin has agreed more work is necessary and will put looking into it on the front burner. Here is a part of my exchange with Darrin, and with my host provider, ICDSoft.  

Hi Darrin,
 MyISAM and utf8 (same as utf8mb3) does work with MySQL 8, and gedcom import times are good. All the data is correct, and all the TNG pages look right.
 
However, even after optimization,  table access, say for a person, can take up to 10-16 seconds, compared to 1-3 seconds with MySQL 5.7, thus an unacceptable performance hit. Likely because MySQL 8 should be using the transactional InnoDB engine, with which I was unable to get the gedcom import working, even with utf8mb3 collation.  I read also that 'partitioned MyISAM' is not supported in MySQL8, and that may be a cause.

Using the older storage engine in MySQL 5 essentially  one ends up with a ‘dumbed down’ application.  Incidentally,  ICDSoft forces the database variable Default Storage Engine = InnoDB, even though all tables are MyISAM.  I believe that has no impact on existing tables, since it's the table value that is important, except if a new table is created by a mod, it might end up being InnoDB.   From my non-technical view,  I think there are coding and design considerations to getting MySQL 8 working with InnoDB properly, particularly the import process.  So, I shall shelve this until such time as there is momentum to smooth out the performance issues with InnoDB and TNG. Better to stick with MySQL 5.7 for now.

Another reason for ending this test: I was just now working with ICDSoft, and they just alerted me to exceeding my SQL limit (didn’t know I had one) the last few days whilst I dabbled with MySQL 8.  So there’s that.Here’s ICDSoft Support’s note to me I thought might be of interest to you:

====from ICDSoft=====
Thank you for your patience. You are correct in what you wrote in your other ticket. The default engine in MySQL 8 has been changed to InnoDB. Since your tables are MyISAM, this should not matter.
We notice that your account has started going over its MySQL time limit in the last few days. You can see that in the control panel. We have attached the MySQL slow query report from Aug 27 to our reply. It shows that MySQL 8 is using almost all of the shown time. You need to ensure that your account is not regularly going over the limit.

We do not have current plans for stopping the MySQL 5 support, so if your site is better suited for it, you should consider sticking to using it for the moment.

Best regards,
Support (ICDSoft)

  
 =====from Darrin=====
Thanks Ron, I will add it to my high-priority to-do list to work on better support for InnoDB and utf8mb4.

Darrin

[RickM]

Ron,

Many Thanks on Many Levels..

A lot of work and effort gone into this, and you have provided the info in a clean, clear and 'almost 'Rick' ready' fashion..

I like to keep things up to date so if the version was available to me I would probably try it.. But NO I wont given your efforts

Thank You for sharing..

 

[bhemph]

Ron,

I saw a 30% performance drop from TNG 13 import to TNG 14 import.  I was importing about 100,000 individuals with no media in the GEDCOM into TNG 13 and TNG14 with MySQL 8.0.30 InnoDB and utf8mb4_0900_ai_ci.  I haven't been able to figure out what changes destroyed the performance like that.  I know there were others that reported substantially worse performance drops with their imports too when they started using TNG 14.

Brent

[RickM]

I'm just interested in this, and really don't know much at all.. So 'Rick' question or two.. I have learnt sometimes asking these questions one gets a much better understanding of things.. Way back in College (age abt 15/16) my Maths teacher was amazed I asked 'Rick' questions.. Lots in the class would snicker at them and me.. My teacher put a very quick end to that by saying something like 'Some of you who are snickering should listen to the reply. I KNOW you are struggling with this too!' Stopped em cold and they never did snicker again..

Would the likes of nginx or Apache versions have an impact?? Being that one might be using and up to date MySQL version with bells and whistles running (collations and whatever). And then throw php into it as well..

ANd as silly as it might sound, would the phpMyAdmin version have some form of impact?? I do get that this probably does nothing short of allowing database 'physical' access BUT as I said they are 'Rick' questions..