Web API timeout

I would check size of web log tables t_web_session_Session and t_web_session_Variable: Web API database procedures query these tables extensively and their size could affect the performance. Please run in Impresario database

select COUNT(*)from t_web_session_Session

If the count is higher than five million I would suggest to purge partially the tables. I do that 3-4 times a year (with archiving deleted data from t_web_session_Session). I could share my scripts for that. 

sbasyuk@carnegiehall.org

Hi Simon,

We are not having any timeout issues, but our t_web_session_session and t_web_session_variable tables are huge and we are trying to run the UP_CLEAR_WEB_TABLES procedure without much luck.

Basically the procedure runs really slow (given the enormous size of the two tables) and in the end fills up our Impresario Transaction log file and/or consumes all available disk space on the server.

I guess you would have faced similar issues cleaning up these tables...? Would much appreciate if you can share your experience and your SQL script with me.

Many thanks,

Sujit.

Hmmm.   That's tough: are you trying to run UP_CLEAR_WEB_TABLES to clear a lot of the data (i.e. everything up the the last month or so)?  If so, you might be able to get it to work incrementally: doing it once for everything before, say, three years ago, or whatever would reduce the amount of data it was deleting in a single pass.

Thanks for your response Gawain.

Yes we did try to clear a huge chunk before, and I think the UP_CLEAR_WEB_TABLES procedure doesn't cope very well with that. You're right, doing the deletion in small chunks is the way to go looks like, would be a slow process though to get through around 4 years worth of data :)

Cheers,

Sujit.

Thanks for your response Gawain.

Yes we did try to clear a huge chunk before, and I think the UP_CLEAR_WEB_TABLES procedure doesn't cope very well with that. You're right, doing the deletion in small chunks is the way to go looks like, would be a slow process though to get through around 4 years worth of data :)

Cheers,

Sujit.

Does anyone know the recommend number of rows that should be in T_WEB_SESSION_SESSION? It looks like earlier in the thread Simon is recommending 5,000,000 rows. 

For various reasons, I would like to keep 3 years of data, which well exceeds that number in our session table.  I guess I am wondering where that 5,000,000 number came from, if it is one that has been tested and validated or just a best guess.

Any advice on this would be appreciated.

Thanks,
Patrick 

Hi Patrick

 We currently keep 3 months worth of web records (using a job to trim them back every night, so that each run only takes a minute or so). That adds up to about 4.8M session records and 14.5M session variable records at the moment. That seems remarkably similar to Simon's suggested practical limit, but ours is based purely on a perception of how long it might be useful to have records for analysis/problem-solving purposes.

In the early stages of running the system, i found them quite useful for problem-solving, and for working out how things happened underneath, but I don't think I've ever had a need to go back more than three months.

We started doing the trimming a couple of years ago when we realised that the tables were getting  ridiculously big (hundreds of millions), and there was no real reason to keep them. We couldn't point to any specific performance issues with the un-purged data, but there didn't seem any point using up database space - and it was getting a bit big... From memory (I didn't actually do it), we removed the old records in staged chunks so as not to overwhelm the system, as others in this thread have recommended.

Ken

Ken

Hi Fernando,

Yes, the up_clear_web_tables did not work for us either, it was taking forever and the website was blocked, we had 140 million records in t_web_session_variable, I think that was like 7 years of data. We did a special project here in which we exported the records that we needed, about a month of data, through BCP, then we truncated each web table and then we BCP the recent records back in. The whole process took about one hour. Now we can use the up_clear_web_table and it takes about 2 minutes once a week.

Normal 0 false false false EN-AU X-NONE X-NONE

Hello Ana –

We are having the same issue. Even running UP_CLEAR_WEB_TABLES for deleting in small chunks is proving futile.

On our Dev environment I first ran the procedure with @days_old set to 1085 (this was around the time we started using Tessitura) and this completed within a few minutes. Since then I have tried the following values for the @days_old parameter - 800 > 740 > 700 > 670 > 640 (with the DB in Simple Recovery mode).

But unfortunately in all the above cases (except 1085 days) it took an average of around 15 hours to complete and also locked web transactions whilst it was running. So we can’t afford to do this in our Live environment.

I am very interested in the special BCP project that you guys undertook to overcome this problem. Was this designed in-house or with assistance from Tessitura support? How long did the BCP process take to run, did you have to run it after hours and did it disable any system access?

Any chance of sharing the scripts / detailed process?

In addition to this, even our DB size is humungous. A database top table size report shows the “t_web_session_variable” and “t_web_session_session” as the main culprits. I am guessing you guys would have had the same problem too…? Were you able to bring down the size of your DB after the BCP process? If yes, then would be good to know the detailed steps.

Any assistance would be much appreciated.

Many thanks,

Sujit.

Hi Sujit,

I am so embarrassed, I posted this and then I did not subscribe to the replies. If you are still interested, we can share the scripts with you. We did this on our own, and yes, reducing the size of the database was our motivation. I do not remember the exact numbers, but the size of the compressed back ups was reduced considerably. I will make sure I subscribe to this discussion this time.