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:

1. Normal maintenance tasks such as ANALYZE TABLE (to reset statistics) on the PHS table and several others.
2. OPTIMIZE TABLE on the PHS table, though this is not really necessary on MySQL INNO tables.
3. CHECK TABLE on the PHS and several other tables all returning OK.
4. 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:
1. Do you think this is about indexes or something else?
2. Does it sound like some corruption? If so, what are some good checks?
3. Do you have any exploratory questions?
4. 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

2.

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:

1. This particular query has not changed in ages but has suddenly slowed to about 25 seconds.
2. 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.
3. I dumped the PHS table to .sql and restored it from the .sql file.
4. I have redeployed the BSV from the last one deployed, which ran perfectly for about a month before problems started spontaneously.
5. 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)
6. I have not reinstalled the AwareIM platform because I just can't see the logic.
7. 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]

See my PM.

I see no PM