MySQL issues after new installation

[Doctrg]

We have moved our old server to a new server. We updated 1.5.5 to 1.5.9 on Ubuntu 18.04.6 and moved the database to the new server which is Ubuntu 20.04.3 and the MySQL process is getting hammered by our 1000+ clients checking in. There’s alot of changes from the old one to the new one and we are having issues tuning MySQL 8. Any suggestions where to look to start tuning MySQL 8?

[Sebastian Roth]

@Doctrg Did your old DB run without tuning?? Just wondering how it would make a difference if the old server was facing just as many clients.

There’s alot of changes from the old one to the new one …

What changes do you mean here?

[george1421]

@doctrg Post a picture of the top command sorted by Processor.

If mysql is at the top and a lot (like 15) of php-fpm modules are below, then lets run this command in the mysql cli.

SELECT TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'fog';

If the table format is MyISAM then we can probably fix the slowness.

[Doctrg]

@george1421 We checked and all tables are ISAM.

[Adam Taylor]

We had done tunning on the old box but a bunch of our tune options are no longer available in MySQL 8. The query_cache_size variable is what what made it work on the old box well enough…that is no longer an option in 8 however.

I’m Don’s boss and I am the original person who set this up back in the .2X days…LONG time ago…LOL I have been out of the loop for a while and man…LOTS of changes since I last looked at it.

In a nut shell, the DB is just slowing to a unusable crawl once all the clients start checking in and the fpm processes just stall because of it.

I sware when I set it up the engine was INNODB on a very old box…but somewhere it changed back to MyISAM…now we know the issue…MyISAM isn’t capable of what we are asking of it…

So george1421…how we get it over to INNODB becuase I don’t have a clue TBH. DBs make my head hurt…lol

[Adam Taylor]

I found this: https://forums.fogproject.org/topic/14254/high-cpu-usage/23?page=2

Working on it now.

[george1421]

@adam-taylor The only thing is you might run into an invalid date format on two or three tables when you go to convert them. because of two new startup parameters that mysql uses. If you run into that we can work through it too.

I really need to get a solid tutorial on this.

Until you get the tables converted over you can relieve some of the back pressure on the database by changing your client checkin time to 300 or 600 seconds.

[Adam Taylor]

OMG…why is the default not INNODB?

I cleared out the invalid times and…10% CPU instead of 70+% for mysqld now…wow…

The system is super happy now but we have some testing to do to make sure we stay good but as I said…OMG that is MUCH better!!!

[Adam Taylor]

NOTE, we did run into a issue you all need to be aware of. There is a table called “groups”. Apparently that is now a protected name in MySQL 8 and you HAVE to put ticks around the groups table name for commands or it will throw an SQL syntax error. Took me a while to figure out that one!

[george1421]

@adam-taylor said in MySQL issues after new installation:

OMG…why is the default not INNODB?

The default was changed in FOG 1.5.8 or 1.5.9 I think. But that would be only for a new install. The fog installer does not change the table configuration during an upgrade.

The issue was only uncovered when larger sites (1000s of clients) brought out the inefficiencies in the MyISAM engine. The short answer is MyISAM does table level locking on an update where the INNODB engine uses row level locking on an update. So with MyISAM all of the clients had to get in line one after the other to update the check in tables. The MyISAM engine is the mysql default engine if you were to just go in and issue a create database command.

Now that you have it worked out you might want to slow your client check in time to 8 or 10 minutes from the FOG default of 5.

[Sebastian Roth]

@george1421 said in MySQL issues after new installation:

The default was changed in FOG 1.5.8 or 1.5.9 I think. But that would be only for a new install. The fog installer does not change the table configuration during an upgrade.

I might be wrong here but I don’t think it is yet. @Tom-Elliott ?