Database latency - all suggestions appreciated, will compensate

kklosson

Background: My database is MySQL 5.6. My last update was published June 21st and it was the fourth minor update since that last BSV that included database changes, which were minor i.e., add a column. Around mid July, a user said some data was missing, when in fact, it was just taking around 30 seconds to load vs the normal 3 seconds or so. In review, many indexes were simply gone. It was a bit random, but for a table called PHS, the PHS_RID index was gone from many tables. I restored all missing indexes manually based on the structure in my Dev database and things improved greatly, of course. However, any query on the PHS table performs almost as though there are no indexes, taking around 25 seconds to display (_50K records). Since this is a multi-tenant system, all queries to this table include a WHERE clause for the Account ID, so I have focused on that.

Secondly, any save to the Account object is taking a very long time - in some cases, over a minute.

Because this was a spontaneous issue and because it totally looks and behaves like an index problem, that has been my focus. I don't currently believe there are issues with the queries or business rules since nothing had changed and the system was performing normally until the spontaneous event.

I have tried:

Normal maintenance tasks such as ANALYZE TABLE (to reset statistics) on the PHS table and several others.
OPTIMIZE TABLE on the PHS table, though this is not really necessary on MySQL INNO tables.
CHECK TABLE on the PHS and several other tables all returning OK.
I deleted and re-added the Account_RID index on the PHS table.

I am concerned about the long time to save an account record but can't see how it links to the other issue. Note that I had previously optimized the business rules on that object IAW Aware IM best practices.

Questions:

Do you think this is about indexes or something else?
Does it sound like some corruption? If so, what are some good checks?
Do you have any exploratory questions?
Is this an area of expertise or experience for you? If so, I am happy to compensate for your help and attention.

Many thanks

hpl123

I can't offer any help but why don't you buy a support ticket from Awaresoft and get Vlad to look into it?

kklosson

I would be happy to but I have no reason to think it is an Aware IM issue and I imagine he would be inclined to point to the database. It just seems to be a database issue and I think he would think so as well.

PointsWell

Run logs with SQL option set. See what is happening in the AIM log.

ACDC

1, When you run the slow query, have you monitored the memory usage. Does it spike or gradually grow. How much headroom is available afterwards. When the system is idle, reboot the server, take a memory usage reading and then run the query to establish its impact

In review, many indexes were simply gone. It was a bit random,

How did you lose these indexes?

kklosson

Good suggestions for sure. The machine has 8GB and memory usage is consistently low and stable. I have monitored machine memory as the query runs and I see no stress. This query is the user's dashboard that displays PHS records where the PHS.ID = the Account.ID and the PHS Investigator.ID = LoggedInInvestigator.ID. It has run perfectly and normally for 10+ years. Usually about 2-3 sec's to display. As I said, in July several table indexes pointing to PHS_RID just went missing and slowed down a bunch of stuff. I was able to re-add them am left with this table still responding as though an index is missing. I've done all the normal things like ANALYZE TABLE and I've reviewed the cardinality on applicable indexes. It really seems like a database/index issue but I'm tapped out on things to check or improve. I not ready to suspect the query itself since the BSV never changed. I will say the loss of those indexes point to possible corruption somewhere, but I've run CHECK TABLE (Extended) throughout the database. I am tempted to re-deploy it only because I'm out of logical ideas.

Let me know if you have any.

ACDC

As I said, in July several table indexes pointing to PHS_RID just went missing and slowed down a bunch of stuff.

In light of this, I would re-deploy.

Unless of course you have time to dig deeper. I would move a copy of the app to another machine with all the data and simulate the problem.
Start a process of elimination by first dumping the table only to a .sql and then restoring the single table, maybe you can flush something out

As to the index corruption, I have never had this with MySql. I run ver 5.7 though, highly complex mission critical AIM app , also 11 + years old with huge data.

BTW, (you might already know this ) in the old days AwareIM never used to create indexes on Single references, this led to the possibility that your app (based on its age) may have the legacy problem of absent indexes. So it may not be a corruption issue unless you went in and manually corrected them later on in the upgrade process.

If you do re-deploy, go to 5.7 there are some legitimate reasons why to do this, I cannot remember them off hand. But I moved from 5.6 to 5.7 and found more stability and efficiency. The later versions of MySql if anything slowed my system down for some reason, could never get to the bottom of it

kklosson

In testing, I note that this query selects all columns from the table, not just those defined in the query. There are about 150 columns. This seems very inefficient. In total, this is about 6,000 characters. In design, there are about 18 columns selected.
Should it do this? If so, Why?

kklosson

I believe I have exhausted all possibilities that I can conceive of.
To review:

This particular query has not changed in ages but has suddenly slowed to about 25 seconds.
I have performed all table maintenance functions on the PHS table as well as others i.e., ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE. All checks report OK.
I dumped the PHS table to .sql and restored it from the .sql file.
I have redeployed the BSV from the last one deployed, which ran perfectly for about a month before problems started spontaneously.
I have viewed the Tomcat output when this query runs and it does take a long time (TIME TAKEN TO RUN QUERY:21946) (21 seconds)
I have not reinstalled the AwareIM platform because I just can't see the logic.
At this point it seems mostly like a query problem. I am concerned about why the query selects all columns in the table (should it?), however, another query on this table with a slightly reduced scope also exhibits similar latency.

For anyone who would like to take a shot, I will certainly compensate.

MESSI

On step #2 you said that you have performed all table maintenance.
I suggest trying a professional service like https://releem.com/

kklosson

It looks like it only supports Linux or RDS servers.

Jaymer

This is too big of a carrot on a stick to pass up.
I’ll solve it.
Skype: JaymerJaymer

kklosson

See my PM.

ACDC

Have you looked at your MySql error logs for clues
Also, inspect the my.ini file for limitations that may exist due to table growth.

What about some odd character in the data thats messing with the query, I had this once before on slow queries

Jaymer

kklosson wrote
See my PM.

I see no PM

kklosson

Stuck in outbox. Sent to .me

rocketman

I had speed issues when I suddenly found about 300 items in Execution context (still haven't bottomed out why they don't get deleted, since they are all idle). bottom line - It eats your memory and TBH, 8Gb ain't that much these days.

I have also know a large EC object to cause the overnight backups to fail If an item with a large amount of data gets stuck (like a bulk email with some big attachments).

Probably nothing to do with your problem, just musing

kklosson

To ACDC,
I'm afraid I will not know what to look for. Can you expand on the character in your query?

ACDC

I'm afraid I will not know what to look for. Can you expand on the character in your query?

Dump the table to a text file or Excel and then explore the contents of each column for odd-looking things/characters that shouldn't be there.

kklosson

My current thinking is that this is a database issue (MySQL 5.6). When I query the PHS table from the Administrator's VP, I get the same latency. Another table, Candidate, has about the same number of records and displays in about 1 second. There is no reason the PHS table should take any longer. I feel I have performed all normal checks on the table with no errors.

Check Table
Analyze Table

I have dumped the table to .sql and re-imported it.
I have exported the table to .csv and manually reviewed the contents for any weirdness.

Contributors have mentioned checking mysql.ini. I'll be happy to post it if asked. Also mentioned was checking the logs, which I have. There have been no ERRORs since 2015 (ran out of space). There are some warnings that seem innocuous to me.

I think I can use a good MySQL expert. I am currently replicating the DB server to enable more intrusive tests. As I have stated, I am happy to pay your fee for time spent. If you know such an expert, your recommendation will be appreciated.