Database latency - all suggestions appreciated, will compensate

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
kklosson
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Database latency - all suggestions appreciated, will compensate

Post by 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
Last edited by kklosson on Thu Aug 17, 2023 7:35 pm, edited 1 time in total.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
hpl123
Posts: 2572
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: Database latency - all suggestions appreciate, will compensate

Post by 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?
Henrik (V8 Developer Ed. - Windows)
kklosson
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency - all suggestions appreciate, will compensate

Post by 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.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
PointsWell
Posts: 1450
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Database latency - all suggestions appreciated, will compensate

Post by PointsWell »

Run logs with SQL option set. See what is happening in the AIM log.
ACDC
Posts: 1135
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Database latency - all suggestions appreciated, will compensate

Post by 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
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency - all suggestions appreciated, will compensate

Post by 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.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
ACDC
Posts: 1135
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Database latency - all suggestions appreciated, will compensate

Post by 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
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency - all suggestions appreciated, will compensate

Post by 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?
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
kklosson
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency - all suggestions appreciated, will compensate

Post by 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.
Last edited by kklosson on Sun Aug 20, 2023 2:48 pm, edited 1 time in total.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
MESSI
Posts: 14
Joined: Thu Dec 29, 2022 11:09 pm

Re: Database latency - all suggestions appreciated, will compensate

Post by MESSI »

On step #2 you said that you have performed all table maintenance.
I suggest trying a professional service like https://releem.com/
AwareIM 8.5, 8.7 - MySQL, MariaDB
kklosson
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency - all suggestions appreciated, will compensate

Post by kklosson »

It looks like it only supports Linux or RDS servers.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
Jaymer
Posts: 2412
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Database latency - all suggestions appreciated, will compensate

Post by Jaymer »

This is too big of a carrot on a stick to pass up.
I’ll solve it.
Skype: JaymerJaymer
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
kklosson
Posts: 1616
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Database latency - all suggestions appreciated, will compensate

Post by kklosson »

See my PM.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
ACDC
Posts: 1135
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Database latency - all suggestions appreciated, will compensate

Post by 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
Posts: 2412
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Database latency - all suggestions appreciated, will compensate

Post by Jaymer »

kklosson wrote: Sun Aug 20, 2023 4:41 pmSee my PM.
I see no PM
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Post Reply