In the last months I noticed how the database for my Drupal site was over 100 megabytes, I knew something needed to be done but I did not know what, until now.

While switching from GoDaddy to Dreamhost I learned that Dreamhost sets you up with a unique sub-domain for your database, something like http://db.yourdomain.com that you can access without logging in to your host account and finding your way to phpMyAdmin. This feature made it easy for me to find out which parts (tables) of my database were the ones slowing down my Drupal installation.

I learned that the SESSIONS and WATCHDOG tables were 126 megabytes and 36 megabytes respectively. While it sounded like the WATCHDOG table was some kind of log related to spam activity, I was a little uneasy about clearing (Truncating) this SESSIONS table.

Basically, what this table does is keep a registry of, yes, authenticated and anonymous sessions of your site. This includes logins and it may pose a security risk when in a public computer. I knew beforehand that by truncating the SESSIONS table anybody logged in would be immediately logged out, including me, but I was ok with it so I truncated it and now my database is only 10 megabytes and my site is quick and responsive.

While this helped with the database size, it will continue to grow exponentially if I do not change some settings in the SETTINGS.PHP file inside yourdomain/sites/default or yourdomain/sites/yoursite

I changed these values from:

ini_set('arg_separator.output', '&');
ini_set('magic_quotes_runtime', 0);
ini_set('magic_quotes_sybase', 0);
ini_set('session.cache_expire', 200000);
ini_set('session.cache_limiter', 'none');
ini_set('session.cookie_lifetime', 2000000);
ini_set('session.gc_maxlifetime', 200000);
ini_set('session.save_handler', 'user');
ini_set('session.use_cookies', 1);
ini_set('session.use_only_cookies', 1);
ini_set('session.use_trans_sid', 0);
ini_set('url_rewriter.tags', '');

to:

ini_set('arg_separator.output', '&');
ini_set('magic_quotes_runtime', 0);
ini_set('magic_quotes_sybase', 0);
ini_set('session.cache_expire', 2880);
ini_set('session.cache_limiter', 'none');
ini_set('session.cookie_lifetime', 0);
ini_set('session.gc_maxlifetime', 20000);
ini_set('session.save_handler', 'user');
ini_set('session.use_cookies', 1);
ini_set('session.use_only_cookies', 1);
ini_set('session.use_trans_sid', 0);
ini_set('url_rewriter.tags', '');

Let's elaborate!

ini_set('session.cache_expire', 2880);
Means my session will reload after 2 days of inactivity. The 2880 integer is the minutes it will take for the session to be cached again. This value determines how often should visits be reloaded.

ini_set('session.cookie_lifetime', 0);
By setting this value to ZERO seconds, it will be close the session as soon as you quit/close your browser or tab. If you want to allow visitors to remain logged in after quitting/closing their browser you should increase this value, as long as they never log out. This is what allows Gmail users to quit/close their browsers and be always logged in.

ini_set('session.gc_maxlifetime', 172800);
This value determines when a session should expire. It is the seconds the session should remain active, after the maxlifetime of 172800 seconds (2 days) the session will expire and you will be prompted to log back in. Ever written text in a website just to find out your session has expired?
It is this value's fault!

So I think these values will work for me, but you should try to find some middle ground where your users are happy and your website is quick and responsive.

Good luck!