VuFind

The search table serves two purposes:

1.) It maintains the "search history" that you can view in every session
2.) It remembers saved searches in user accounts

As you've noticed, a lot of the search history rows seem to outlive the sessions they're associated with (there's code in VuFind to clean this up, but PHP's garbage collection isn't entirely predictable, so a lot get missed).

Fortunately, there's a tool you can run regularly (i.e. via cron job) to clean this up -- just look at expire_searches.php in VuFind's util folder.

- Demian
________________________________________
From: Eric Lease Morgan [emorgan@...]
Sent: Thursday, January 19, 2012 4:41 PM
To: vufind-tech@...
Subject: [VuFind-Tech] the search table

The MySQL database associated with my VuFind implementation is growing and getting too large. How can I trim it?

Specifically, there is a table named search with the following fields:

  * id
  * user_id
  * session_id
  * folder_id
  * created
  * title
  * saved
  * search_object

A sample record looks like this:

           id: 12
      user_id: 0
   session_id: bfo0ej7d2q2v2mb79it9v7h386
    folder_id: NULL
      created: 2011-08-08
        title: NULL
        saved: 0
search_object: O:5:"minSO":7:{s:1:"t";a:1:{i:0;a:2:{s:1:"i";s:7:"Subject";s:1:"l";s:14:"" Catechetics"";}}s:1:"f";a:1:{s:11:"topic_facet";a:1:{i:0;s:30:"Church work with migrant labor";}}s:2:"id";i:12;s:1:"i";d:1312816051.751573085784912109375;s:1:"s";d:0.075666904449462890625;s:1:"r";i:2;s:2:"ty";s:5:"basic";}

What is this table used for? Session tracking? And how might I slim it down, especially if the sessions have expired?

--
Eric Lease Morgan
University of Notre Dame




------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Vufind-tech mailing list
Vufind-tech@...
https://lists.sourceforge.net/lists/listinfo/vufind-tech

On Jan 20, 2012, at 7:44 AM, Demian Katz wrote:

> The search table serves two purposes:
> 
> 1.) It maintains the "search history" that you can view in every session
> 2.) It remembers saved searches in user accounts
> 
> As you've noticed, a lot of the search history rows seem to outlive the sessions they're associated with (there's code in VuFind to clean this up, but PHP's garbage collection isn't entirely predictable, so a lot get missed).
> 
> Fortunately, there's a tool you can run regularly (i.e. via cron job) to clean this up -- just look at expire_searches.php in VuFind's util folder.

Demian, thank you. I found this table and discovered it contained more than 4,000,000 rows. Ack! When I tried to use expire_searches.php PHP ran out of its allocated RAM. I then reverse engineered expire_searches.php and learned of the two commands I needed to use to clean the table by hand. As I ran the first command my fellow MySQL users started complaining about slow performance. Ack, again! I will run the command again, this evening, before I go home, and if that doesn't work by tomorrow morning, then I will simply purge the table. Obviously I needed to run expire_searches.php as a cron job much sooner.

-- 
Eric Lease Morgan

Good luck!  If you want, you might be able to get the PHP script to run by adding a line like this at the top:

ini_set('memory_limit','256M');

(obviously, it might take some trial and error to figure out exactly how much memory you actually need).

Also, if you do end up doing a purge, at least try to avoid deleting rows where saved=1 in order to avoid wiping out anybody's saved searches.  (Though if there aren't any such rows, go ahead and purge!)

- Demian
________________________________________
From: Eric Lease Morgan [emorgan@...]
Sent: Monday, January 23, 2012 4:06 PM
To: vufind-tech@...
Subject: Re: [VuFind-Tech] the search table

On Jan 20, 2012, at 7:44 AM, Demian Katz wrote:

> The search table serves two purposes:
>
> 1.) It maintains the "search history" that you can view in every session
> 2.) It remembers saved searches in user accounts
>
> As you've noticed, a lot of the search history rows seem to outlive the sessions they're associated with (there's code in VuFind to clean this up, but PHP's garbage collection isn't entirely predictable, so a lot get missed).
>
> Fortunately, there's a tool you can run regularly (i.e. via cron job) to clean this up -- just look at expire_searches.php in VuFind's util folder.

Demian, thank you. I found this table and discovered it contained more than 4,000,000 rows. Ack! When I tried to use expire_searches.php PHP ran out of its allocated RAM. I then reverse engineered expire_searches.php and learned of the two commands I needed to use to clean the table by hand. As I ran the first command my fellow MySQL users started complaining about slow performance. Ack, again! I will run the command again, this evening, before I go home, and if that doesn't work by tomorrow morning, then I will simply purge the table. Obviously I needed to run expire_searches.php as a cron job much sooner.

--
Eric Lease Morgan

------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Vufind-tech mailing list
Vufind-tech@...
https://lists.sourceforge.net/lists/listinfo/vufind-tech