LonelyCache

the stat numbers have not changed in the last 48 hours?
last update was 05 Feb.2013 03:10:06

That's strange, I'll take a look at this. Thanks for the heads up.

Just an update here.

In another thread, I wrote about this:

Corfman Clan wrote:The query we're having problems with (an insert from query) is for the Overall Backcountry leader board. In the past, this query as been completing in under 90 seconds, but since Wednesday morning hasn't completed after 30 minutes and is then cancelled because it's taking too long. I'd imagine something changed to cause a minimum 20x increase in processing time. We don't know what that is and will have to work with the web hosting company to figure that out. Maybe they have a sick DB server. Maybe they changed some configuration for something else that inadversely impacted LonelyCache. I really don't know. I hope to hear back from the DB admin tomorrow morning and with some luck, he'll have some ideas on what is going on.

Friday morning I was in touch with the DB Admin, and nothing had changed on the server side. So now our working assumption is that due to amount of data or something like that, when the query is run, the database management system is "optimizing" the query differently than before. So some condition changed that is causing the DBMS to make a decision on how the query is run that causes it to run poorly.

I have currently scheduled the next site update for Monday morning, 7:30 eastern when the DBA will monitor the server to see what's happening and how we can get it back on track.

Since the 05 Feb. update prob occured, I have noticed that every time I go to the PHBB Forum , It requires me to log back in even if I check the 'Log me on automatically box each visit".? IF I switch to the stats board them back.. I have to log back in to the Forum?..
anyone elses having this problem? I have read the FAQ and find no reason for this to occur..
It worked well before thte 05 Feb update prob on the stats board.. I don't know if the two probs are related or not. I don't see how, but just giving another a heads up.. ( sorry )

I have had exactly the same problem with the Forum "'Log me on automatically box each visit" check-box not working for me for the last several months. It used to work OK but I can't remember exactly when it stopped working. I'm using the Safari browser, version 5.1.7 on a Mac. Although I have LoneyCache cookies I don't see any phpBB cookies so I guess for some reason the cookie isn't getting stored. I have the same problem using Firefox.

desert dawg wrote:Since the 05 Feb. update prob occured, I have noticed that every time I go to the PHBB Forum , It requires me to log back in even if I check the 'Log me on automatically box each visit".? IF I switch to the stats board them back.. I have to log back in to the Forum?..
anyone elses having this problem? I have read the FAQ and find no reason for this to occur..
It worked well before thte 05 Feb update prob on the stats board.. I don't know if the two probs are related or not. I don't see how, but just giving another a heads up.. ( sorry )

gjhiker wrote:I have had exactly the same problem with the Forum "'Log me on automatically box each visit" check-box not working for me for the last several months. It used to work OK but I can't remember exactly when it stopped working. I'm using the Safari browser, version 5.1.7 on a Mac. Although I have LoneyCache cookies I don't see any phpBB cookies so I guess for some reason the cookie isn't getting stored. I have the same problem using Firefox.

I can't see these being related to the site update issue, except that when the site is stuck in an update attempt, there may be some adverse performance issues that might impact that.

My suggestion would be to clear all cookies with LonelyCache in the name and see if that helps.

Corfman Clan wrote:Just an update here.
I have currently scheduled the next site update for Monday morning, 7:30 eastern when the DBA will monitor the server to see what's happening and how we can get it back on track.

The DBA noticed some interesting things going on with the server this morning. He will be performing some server maintenance early Tuesday morning and will watch the site update again afterwards. Let's keep our fingers crossed that that will resolve the issue. The next update is scheduled at 7:30 AM eastern time tomorrow morning.

Corfman Clan wrote:My suggestion would be to clear all cookies with LonelyCache in the name and see if that helps.

Actually, the way to delete the forum cookies is to go to the bottom right of any forum page and click on the "delete all board cookies" link. After that I'd suggest logging out and then back in.

Corfman Clan wrote:The DBA noticed some interesting things going on with the server this morning. He will be performing some server maintenance early Tuesday morning and will watch the site update again afterwards. Let's keep our fingers crossed that that will resolve the issue. The next update is scheduled at 7:30 AM eastern time tomorrow morning.

Unfortunately, the maintenance work had no effect on the stats being updated. It looks like the easy fix isn't coming so Redfist and I will need to figure out how to give the server a hint on how to run the query. We'll do our best to figure this out but it may take several more days to resolve so please be patient.

Today, the DBA asked me to try running the query with a specific option. So tonight I updated the code to do that and I also increased the query timeout to one hour. At about 8:10pm AZ time, the stats update began. The problem query completed just shy of one hour. Another 30 seconds and it would have timed out. At any rate, it squeaked through and the stats are now updated. In all, it took close to 2:27 hours to run. I'll bump the timeout up to 1:15 hours to give us some wiggle room so for now we should have the stats updated. However, we still need to correct the issue that is causing it to take so long to run.

WTG.. CC..... we all knew it was just a matter of time until you would get it up and running again... thanks..
Happy Valentine's Day

desert dawg wrote: WTG.. CC..... we all knew it was just a matter of time until you would get it up and running again... thanks..
Happy Valentine's Day

Well, yes, we're getting the stats updated but it's like wrapping duct tape around your radiator hose. It'll let you limp along but the real problem still needs to be fixed.

Wrapping Duct Tape around it. Finally some computer lingo I can get a grip on.

wow.. this is the first time in a while I visited the forum and I was still logged in .. them COOKIES are good... things are lookin up and no duct tape required....

So far we've tried a couple things to get the database query to run in the same time frame as it used to but none have helped. It appears the condition is worsening since a different query is now timing out when stats are being updated. At this time, I don't know if that is a result of another change I made but it seems possible. I'll back that out today and see if stats run to completion again or if the issue that caused the original problem is just expanding.

This is difficult to track down, is out of my area of expertise, and I have a lot on my plate at work right now so I really have no idea when this will be resolved.

Corfman Clan wrote:...It appears the condition is worsening since a different query is now timing out when stats are being updated. At this time, I don't know if that is a result of another change I made but it seems possible. I'll back that out today and see if stats run to completion again or if the issue that caused the original problem is just expanding.

Last night I backed the change out and the site update completed this morning. Well, that is a relief.

Here's what happened:

We have a database table, Log, that we store geocache found logs in. The table has five columns: ID, CacheID, CacherID, LogDate and LogType. Right now, there are around nine million found logs in LonelyCache. To help search through all these logs, we've added indices to the table. We have had an index on CacheID, and CacherID. The indices make it a quick operation to find all the logs for a given cache or for a given cacher. Well the other day, I added an index on LogDate so that it would be quicker to find recent logs, for say the recent notable finds.

One of the things that is updated each day is the recent activity for each geocacher. We do this for the past month (30 days) and the past six months (180 days). Calculating this each day took four separate operations about 30 seconds to 50 seconds each. Well, when I added the index on LogDate, the database system (DBMS) decided the best way to perform these operations would be to use it. Unfortunately for us, using it actually caused the operations to take much, much longer. Once I removed the index, the DBMS reverted back to the way it previously performed the operations and everything is good. Well, except we no longer have the index to help speed up other operations...

Corfman Clan wrote:

Corfman Clan wrote:...It appears the condition is worsening since a different query is now timing out when stats are being updated. At this time, I don't know if that is a result of another change I made but it seems possible. I'll back that out today and see if stats run to completion again or if the issue that caused the original problem is just expanding.

Last night I backed the change out and the site update completed this morning. Well, that is a relief.

Here's what happened:

We have a database table, Log, that we store geocache found logs in. The table has five columns: ID, CacheID, CacherID, LogDate and LogType. Right now, there are around nine million found logs in LonelyCache. To help search through all these logs, we've added indices to the table. We have had an index on CacheID, and CacherID. The indices make it a quick operation to find all the logs for a given cache or for a given cacher. Well the other day, I added an index on LogDate so that it would be quicker to find recent logs, for say the recent notable finds.

One of the things that is updated each day is the recent activity for each geocacher. We do this for the past month (30 days) and the past six months (180 days). Calculating this each day took four separate operations about 30 seconds to 50 seconds each. Well, when I added the index on LogDate, the database system (DBMS) decided the best way to perform these operations would be to use it. Unfortunately for us, using it actually caused the operations to take much, much longer. Once I removed the index, the DBMS reverted back to the way it previously performed the operations and everything is good. Well, except we no longer have the index to help speed up other operations...

Well Done !

Corfman Clan wrote:... Well the other day, I added an index on LogDate so that it would be quicker to find recent logs, for say the recent notable finds.

One of the things that is updated each day is the recent activity for each geocacher. We do this for the past month (30 days) and the past six months (180 days). Calculating this each day took four separate operations about 30 seconds to 50 seconds each. Well, when I added the index on LogDate, the database system (DBMS) decided the best way to perform these operations would be to use it. Unfortunately for us, using it actually caused the operations to take much, much longer. Once I removed the index, the DBMS reverted back to the way it previously performed the operations and everything is good. Well, except we no longer have the index to help speed up other operations...

I'm glad you found what was causing the problem and were able to fix it without damaging the database (adding or removing fields can be dangerous). That's interesting that the LogDateID field caused a problem, but I would have been more surprised if it had actually improved the speed. <nerd alert > I would expect the DB to internally store every date as an integer serial number (number of days elapsed since some arbitrary epoch, e.g. 01 Jan 1900) -- it it's being stored as text, that's a problem! -- and as such would have many or all of the characteristics of a "key" already. So replacing the LogDate field with a LogDateID and adding another table that correlates the ID with the date would probably slow things down, in my inexperienced opinion. I wouldn't have guess that it would cause the system to bog down to timing out, unless it was already pushing the time out before the change. </nerd>

Interesting -- thanks for sharing what happened and thanks especially for making the fix!

rocketsciguy wrote:That's interesting that the LogDateID field caused a problem, but I would have been more surprised if it had actually improved the speed. <nerd alert > I would expect the DB to internally store every date as an integer serial number (number of days elapsed since some arbitrary epoch, e.g. 01 Jan 1900) -- it it's being stored as text, that's a problem! -- and as such would have many or all of the characteristics of a "key" already. So replacing the LogDate field with a LogDateID and adding another table that correlates the ID with the date would probably slow things down, in my inexperienced opinion. I wouldn't have guess that it would cause the system to bog down to timing out, unless it was already pushing the time out before the change. </nerd>

Umm, well no, not really

I'm not sure where you came up with replacing LogDate with LogDateID and then another table that correlates the ID with the date. We're not doing anything of the sort. The LogDate field is of type Date. I don't know, nor care how the DBMS stores and manages dates. It's not my concern. I trust that the DBMS developers are smart and date type operations can be performed efficiently on Date objects.

Now the problem is, for example, how do I find all logs that are at most 30 days old? Well, first I need to compare the date of each and every log and see if it is less than the date 30 days ago. Right now that's close to 9 million logs to compare the date to. Well, that seems like a lot of work and is described as O(N) for Order of N. That is, if you have N things, the amount of effort for the computation is on the order of some constant value for each thing. Think how much faster I could find the correct logs if they were sorted by date. If they were sorted, then I could find the logs in O(logN), which for a big N saves a lot (You're a rocket scientist, so I imagine you're familiar with logarithmic functions ). Essentially, that's what adding an index on a column in a database table does. It creates a sorted list of that column.

An index into the database table acts similarly to an index at the end of a book. If, for some bizarre reason you want to find out what a diagonal matrix is and a linear algebra book just happens to be handy, will you page through the book to find the section or will you look up what page it is at using the book's index? Unless you're being nostalgic to those bygone college days, you'll probably use the index; it's just a lot faster than going through the book page by page. Me, I'd probably just google it

So adding the index on LogDate should allow searching for recent logs to happen more quickly right? Yes it does in certain situations. One is for finding the recent notable finds LonelyCache wide, or for any region. Well, what about for finding a certain cacher's recent finds. Will that index speed up that search? Maybe, maybe not. Remember we already have an index on the CacherID field in the Log table. So now what index is better for the DBMS to use? Should we first search for all the recent logs, and then search the results for that cacher or should we first search for all the logs for the cacher and then search the results for the recent finds? The DBMS will try to make the best choice, but sometimes it won't. That is essentially what happened with updating the stats. The DBMS started using the index on LogDate but it would have been better off not.

Corfman Clan wrote:

rocketsciguy wrote:That's interesting that the LogDateID field caused a problem, but I would have been more surprised if it had actually improved the speed. <nerd alert > I would expect the DB to internally store every date as an integer serial number (number of days elapsed since some arbitrary epoch, e.g. 01 Jan 1900) -- it it's being stored as text, that's a problem! -- and as such would have many or all of the characteristics of a "key" already. So replacing the LogDate field with a LogDateID and adding another table that correlates the ID with the date would probably slow things down, in my inexperienced opinion. I wouldn't have guess that it would cause the system to bog down to timing out, unless it was already pushing the time out before the change. </nerd>

Umm, well no, not really

I'm not sure where you came up with replacing LogDate with LogDateID and then another table that correlates the ID with the date. We're not doing anything of the sort. The LogDate field is of type Date. I don't know, nor care how the DBMS stores and manages dates. It's not my concern. I trust that the DBMS developers are smart and date type operations can be performed efficiently on Date objects.

Now the problem is, for example, how do I find all logs that are at most 30 days old? Well, first I need to compare the date of each and every log and see if it is less than the date 30 days ago. Right now that's close to 9 million logs to compare the date to. Well, that seems like a lot of work and is described as O(N) for Order of N. That is, if you have N things, the amount of effort for the computation is on the order of some constant value for each thing. Think how much faster I could find the correct logs if they were sorted by date. If they were sorted, then I could find the logs in O(logN), which for a big N saves a lot (You're a rocket scientist, so I imagine you're familiar with logarithmic functions ). Essentially, that's what adding an index on a column in a database table does. It creates a sorted list of that column.

An index into the database table acts similarly to an index at the end of a book. If, for some bizarre reason you want to find out what a diagonal matrix is and a linear algebra book just happens to be handy, will you page through the book to find the section or will you look up what page it is at using the book's index? Unless you're being nostalgic to those bygone college days, you'll probably use the index; it's just a lot faster than going through the book page by page. Me, I'd probably just google it

So adding the index on LogDate should allow searching for recent logs to happen more quickly right? Yes it does in certain situations. One is for finding the recent notable finds LonelyCache wide, or for any region. Well, what about for finding a certain cacher's recent finds. Will that index speed up that search? Maybe, maybe not. Remember we already have an index on the CacherID field in the Log table. So now what index is better for the DBMS to use? Should we first search for all the recent logs, and then search the results for that cacher or should we first search for all the logs for the cacher and then search the results for the recent finds? The DBMS will try to make the best choice, but sometimes it won't. That is essentially what happened with updating the stats. The DBMS started using the index on LogDate but it would have been better off not.

I see my mistake. When you said "I added an index on LogDate" for some reason I interpreted that as meaning an ID, and I extrapolated that meant replacing the LogDate field with a DateID and creating a new table correlating DateID with LogDate, which would be a silly thing to do, hence my post. Of course making a field an index is something completely different -- my mistake.

I agree, I would have expected doing that would have sped up operations relying heavily on matching dates, and I would have done it too. But think about how it works, I'm not sure if having an index would help or hurt operations that compared dates (i.e. Select * where CacherID=xxx and LogDate>=Today()-30), whether that would be an O(n) or O(log n) operation -- where it was causing the database to time out, it may have inadvertently become a O(n^2) computation! I think indexing works best when you're trying to get an exact match. Referring back to the book analogy, this operation would be more like "Count the number of 6-letter words in the book that start with the letters T through Z." Referring to the index at the back (even if it contained every word in the book) might actually slow you down compared with just plowing through it.

I would agree that most of the time an index helps, but it looks like it hurt in a big way with the recent activity stat update. Good job tracking down the problem and getting it fixed without breaking stuff!