Transition Technology: Ticket #907: TN Drupal database size

ade — Wed, 02 Mar 2016 11:25:06 GMT

Interesting.
When you say the database dump has increased is there an actual DB that has
increased or are you thinking the 'sqldump' folder has increased?
From your other email it looks like the 'sqldump' folder is made up of:-
drwx------ 2 root root 4.0K Jan 19 01:05 .
 drwx------ 3 root root 4.0K Apr 30  2013 ..
 -rw------- 1 root root 2.5M Mar  2 01:02 annesley.sql
 -rw------- 1 root root 9.3M Mar  2 01:02 conference15.sql
 -rw------- 1 root root 7.8M Mar  2 01:02 cop21.sql
 -rw------- 1 root root 3.7M Mar  2 01:02 information_schema.sql
 -rw------- 1 root root 542K Mar  2 01:02 mysql.sql
 -rw------- 1 root root  17K Mar  2 01:02 performance_schema.sql
 -rw------- 1 root root  11K Mar  2 01:02 phpmyadmin.sql
 -rw------- 1 root root  63M Jun  2  2014 recon.sql
 -rw------- 1 root root  57M Mar  2 01:02 reconomy.sql
 -rw------- 1 root root  53M Mar  2 01:03 tc.sql
 -rw------- 1 root root 1.3K Mar  2 01:03 test.sql
 -rw------- 1 root root 424M Mar  2 01:06 tnro.sql
 -rw------- 1 root root 1.9M Mar  2 01:06 ts.sql
 -rw------- 1 root root 807M Mar  2 01:08 ttt.sql
 -rw------- 1 root root 1.9M Mar  2 01:08 wpdev.sql
Of which I cannot see the 'transitionnetw_0.sql' included?
If it is 'transitionnetw_0.sql' that has trippled in size then it would
obviously indicate that something is amiss, as we haven't been that data
busy.
I have noticed that we have had three (could be more) down periods for 3
mins at 1.25-1.30am over the last week. I would also suggest this is the
traffic spike that disconnected/ceased up the database the other day.
Is it possible that we are getting an DB injection somewhere along the
lines?
Another thought is, if it is the site db that is suddenly tripled in size
do we have a back up of the small database from 6 weeks ago? Am happy to do
a comparison of the two DB's if they can be made available to see where the
increase in size is occurring?
On 2 March 2016 at 10:20, Transition Technology Trac <
trac@tech.transitionnetwork.org> wrote:
> #907: TN Drupal database size
> -------------------------------------+-------------------------------------
>                  Reporter:  chris    |                Owner:  chris
>                      Type:           |               Status:  new
>   maintenance                        |            Milestone:  Maintenance
>                  Priority:  major    |             Keywords:
>                 Component:  Drupal   |  Add Hours to Ticket:  0
>   modules & settings                 |          Total Hours:  0
> Estimated Number of Hours:  0        |
>                 Billable?:  1        |
> -------------------------------------+-------------------------------------
>  6 weeks ago the datadase dump was 447M, see trac:ticket/896#comment:3 but
>  now it is 1.8G:
>
>  {{{
>  ls -lah /var/backups/mysql/sqldump/transitionnetw_0.sql
>  -rw------- 1 root root 1.8G Mar  2 01:23
>  /var/backups/mysql/sqldump/transitionnetw_0.sql
>  }}}
>
>  Anyone have any idea what happened to cause this? Are we keeping too many
>  log entries?
>
> --
> Ticket URL: <https://tech.transitionnetwork.org/trac/ticket/907>
> Transition Technology <https://tech.transitionnetwork.org/trac>
> Support and issues tracking for the Transition Network Web Project.
>
--
Ade Stuart
Web Manager - Transition network
07595 331877
The Transition Network is a registered charity
address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
website: www.transitionnetwork.org
TN company no: 6135675 TN charity no: 1128675

hours, totalhours changed

paul — Wed, 02 Mar 2016 11:29:04 GMT

hours changed from 0.0 to 0.125
totalhours changed from 0.0 to 0.125

Database logging is enabled. How big is the watchdog table?

Investigating ..

paul — Wed, 02 Mar 2016 11:30:07 GMT

Database logging is enabled. How big is the watchdog table?
On Wed, Mar 2, 2016 at 11:20 AM, Ade Stuart <adestuart@transitionnetwork.org
> wrote:
> Interesting.
> When you say the database dump has increased is there an actual DB that
> has increased or are you thinking the 'sqldump' folder has increased?
>
> From your other email it looks like the 'sqldump' folder is made up of:-
>
> drwx------ 2 root root 4.0K Jan 19 01:05 .
>  drwx------ 3 root root 4.0K Apr 30  2013 ..
>  -rw------- 1 root root 2.5M Mar  2 01:02 annesley.sql
>  -rw------- 1 root root 9.3M Mar  2 01:02 conference15.sql
>  -rw------- 1 root root 7.8M Mar  2 01:02 cop21.sql
>  -rw------- 1 root root 3.7M Mar  2 01:02 information_schema.sql
>  -rw------- 1 root root 542K Mar  2 01:02 mysql.sql
>  -rw------- 1 root root  17K Mar  2 01:02 performance_schema.sql
>  -rw------- 1 root root  11K Mar  2 01:02 phpmyadmin.sql
>  -rw------- 1 root root  63M Jun  2  2014 recon.sql
>  -rw------- 1 root root  57M Mar  2 01:02 reconomy.sql
>  -rw------- 1 root root  53M Mar  2 01:03 tc.sql
>  -rw------- 1 root root 1.3K Mar  2 01:03 test.sql
>  -rw------- 1 root root 424M Mar  2 01:06 tnro.sql
>  -rw------- 1 root root 1.9M Mar  2 01:06 ts.sql
>  -rw------- 1 root root 807M Mar  2 01:08 ttt.sql
>  -rw------- 1 root root 1.9M Mar  2 01:08 wpdev.sql
>
> Of which I cannot see the 'transitionnetw_0.sql' included?
>
>
> If it is 'transitionnetw_0.sql' that has trippled in size then it would
> obviously indicate that something is amiss, as we haven't been that data
> busy.
>
> I have noticed that we have had three (could be more) down periods for 3
> mins at 1.25-1.30am over the last week. I would also suggest this is the
> traffic spike that disconnected/ceased up the database the other day.
> Is it possible that we are getting an DB injection somewhere along the
> lines?
>
> Another thought is, if it is the site db that is suddenly tripled in size
> do we have a back up of the small database from 6 weeks ago? Am happy to do
> a comparison of the two DB's if they can be made available to see where the
> increase in size is occurring?
>
> On 2 March 2016 at 10:20, Transition Technology Trac <
> trac@tech.transitionnetwork.org> wrote:
>
>> #907: TN Drupal database size
>>
>> -------------------------------------+-------------------------------------
>>                  Reporter:  chris    |                Owner:  chris
>>                      Type:           |               Status:  new
>>   maintenance                        |            Milestone:  Maintenance
>>                  Priority:  major    |             Keywords:
>>                 Component:  Drupal   |  Add Hours to Ticket:  0
>>   modules & settings                 |          Total Hours:  0
>> Estimated Number of Hours:  0        |
>>                 Billable?:  1        |
>>
>> -------------------------------------+-------------------------------------
>>  6 weeks ago the datadase dump was 447M, see trac:ticket/896#comment:3 but
>>  now it is 1.8G:
>>
>>  {{{
>>  ls -lah /var/backups/mysql/sqldump/transitionnetw_0.sql
>>  -rw------- 1 root root 1.8G Mar  2 01:23
>>  /var/backups/mysql/sqldump/transitionnetw_0.sql
>>  }}}
>>
>>  Anyone have any idea what happened to cause this? Are we keeping too many
>>  log entries?
>>
>> --
>> Ticket URL: <https://tech.transitionnetwork.org/trac/ticket/907>
>> Transition Technology <https://tech.transitionnetwork.org/trac>
>> Support and issues tracking for the Transition Network Web Project.
>>
>
>
>
> --
> Ade Stuart
> Web Manager - Transition network
>
> 07595 331877
>
> The Transition Network is a registered charity
> address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
> website: www.transitionnetwork.org
> TN company no: 6135675 TN charity no: 1128675
>
>
>
>
--
Paul Booker
Drupal Support for Websites and Linux Servers
Website: http://www.paulbooker.co.uk
Tel: +44 01922 861636

hours, totalhours changed

paul — Wed, 02 Mar 2016 11:38:56 GMT

hours changed from 0.0 to 0.25
totalhours changed from 0.125 to 0.375

It looks as though it's some kind of cache problem.

MariaDB [(none)]> use transitionnetw_0;
Database changed
MariaDB [transitionnetw_0]> SELECT
    ->      table_schema as `Database`,
    ->      table_name AS `Table`,
    ->      round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    -> FROM information_schema.TABLES
    -> ORDER BY (data_length + index_length) DESC;
+--------------------+-----------------------------------------------+------------+
| Database           | Table                                         | Size in MB |
+--------------------+-----------------------------------------------+------------+
| transitionnetw_0   | cache_advagg_bundle_reuse                     |    1056.66 |
| transitionnetw_0   | cache_views_data                              |     295.11 |
| transitionnetw_0   | cache_form                                    |     260.66 |
| transitionnetw_0   | watchdog                                      |     192.20 |
| transitionnetw_0   | search_index                                  |     185.42 |
| transitionnetw_0   | cache_page                                    |     179.91 |

hours, totalhours changed

paul — Wed, 02 Mar 2016 11:43:22 GMT

hours changed from 0.0 to 0.125
totalhours changed from 0.375 to 0.5

Maybe we could turn off database logging and clear the caches. Take a baseline. Review the problem again tomorrow?

hours, totalhours changed

chris — Wed, 02 Mar 2016 12:58:30 GMT

hours changed from 0.0 to 0.15
totalhours changed from 0.5 to 0.65

Replying to ade:

When you say the database dump has increased is there an actual DB that has increased or are you thinking the 'sqldump' folder has increased?

Both, one is a consequence of the other, the dump of the database, which is in the sqldump directory, has gone from 447M six weeks ago to 1.8G last night.

From your other email it looks like the 'sqldump' folder is made up of:-

drwx------ 2 root root 4.0K Jan 19 01:05 .
 drwx------ 3 root root 4.0K Apr 30  2013 ..
 -rw------- 1 root root 2.5M Mar  2 01:02 annesley.sql
 -rw------- 1 root root 9.3M Mar  2 01:02 conference15.sql
 -rw------- 1 root root 7.8M Mar  2 01:02 cop21.sql
 -rw------- 1 root root 3.7M Mar  2 01:02 information_schema.sql
 -rw------- 1 root root 542K Mar  2 01:02 mysql.sql
 -rw------- 1 root root  17K Mar  2 01:02 performance_schema.sql
 -rw------- 1 root root  11K Mar  2 01:02 phpmyadmin.sql
 -rw------- 1 root root  63M Jun  2  2014 recon.sql
 -rw------- 1 root root  57M Mar  2 01:02 reconomy.sql
 -rw------- 1 root root  53M Mar  2 01:03 tc.sql
 -rw------- 1 root root 1.3K Mar  2 01:03 test.sql
 -rw------- 1 root root 424M Mar  2 01:06 tnro.sql
 -rw------- 1 root root 1.9M Mar  2 01:06 ts.sql
 -rw------- 1 root root 807M Mar  2 01:08 ttt.sql
 -rw------- 1 root root 1.9M Mar  2 01:08 wpdev.sql

Of which I cannot see the 'transitionnetw_0.sql' included?

This is because the above directory listing is from ParrotServer not PuffinServer.

If it is 'transitionnetw_0.sql' that has trippled in size then it would obviously indicate that something is amiss, as we haven't been that data busy.

Right.

I have noticed that we have had three (could be more) down periods for 3 mins at 1.25-1.30am over the last week. I would also suggest this is the traffic spike that disconnected/ceased up the database the other day. Is it possible that we are getting an DB injection somewhere along the lines?

I doubt it, I think this is the time that the database is dumped each night and when it was less that 500MB it didn't cause any downtime creating the dump, but now it is 3 times the size it is causing the site to be unavailable for a few moments.

Another thought is, if it is the site db that is suddenly tripled in size do we have a back up of the small database from 6 weeks ago? Am happy to do a comparison of the two DB's if they can be made available to see where the increase in size is occurring?

I could put a backup from 6 weeks ago somwehere for you to download if needs be, but it might be less work to try Paul's suggestion first?

Replying to paul:

Maybe we could turn off database logging and clear the caches. Take a baseline. Review the problem again tomorrow?

hours, totalhours changed

paul — Wed, 02 Mar 2016 16:00:34 GMT

hours changed from 0.0 to 0.25
totalhours changed from 0.65 to 0.9

I turned of database logging and cleared the caches but didn't see any change in the size of the cache tables. Was I looking at the wrong database? I couldn't figure this out quickly. It's normally found in the settings.php file.

MariaDB [(none)]> SELECT  table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`  FROM information_schema.TABLES  WHERE table_schema = "transitionnetw_0" ORDER BY round(((data_length + index_length) / 1024 / 1024), 2) DESC LIMIT 10;
+---------------------------+------------+
| Table                     | Size in MB |
+---------------------------+------------+
| cache_advagg_bundle_reuse |    1056.66 |
| cache_views_data          |     295.11 |
| cache_form                |     261.66 |
| watchdog                  |     192.20 |
| search_index              |     185.42 |
| cache_page                |     179.91 |
| node_revisions            |      99.98 |
| cache_block               |      84.30 |
| cache_filter              |      84.17 |
| mail_logger               |      82.17 |
+---------------------------+------------+
10 rows in set (0.66 sec)

Could it be that it was the stage databases that were ~ 400 MB?

MariaDB [(none)]> SELECT table_schema  "DB Name",
    -> Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
    -> FROM   information_schema.tables
    -> GROUP BY table_schema
    -> ORDER BY Round(Sum(data_length + index_length) / 1024 / 1024, 1) DESC;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| transitionnetw_0   |        3035.0 |
| bookerstage20150   |         469.8 |
| bookerstagesamtr   |         458.1 |

I'll pick this up again tomorrow.

hours, totalhours changed

chris — Wed, 02 Mar 2016 16:57:26 GMT

hours changed from 0.0 to 0.2
totalhours changed from 0.9 to 1.1

The database is transitionnetw_0, I have just used ninjahelper to dum the databases and we did have these backup dumps:

ls -lah /var/backups/mysql/sqldump/
total 4.8G
drwx------ 2 root root 4.0K Jun 19  2015 ./
drwx------ 3 root root 4.0K Dec 15  2012 ../
-rw------- 1 root root 221M Aug  7  2014 bookerstage20140.sql
-rw------- 1 root root 221M Dec  5  2014 bookerstage20141.sql
-rw------- 1 root root 232M Mar  2 01:10 bookerstage20150.sql
-rw------- 1 root root 3.3M Mar 19  2015 bookerstage201_0.sql
-rw------- 1 root root 221M Mar 19  2015 bookerstage201_1.sql
-rw------- 1 root root 3.0M Mar  2 01:10 bookerstage201_2.sql
-rw------- 1 root root 224M Mar  2 01:10 bookerstagesamtr.sql
-rw------- 1 root root 866K Mar  4  2014 iirstestclonetra.sql
-rw------- 1 root root 966K Aug 11  2014 iirstesttransi_0.sql
-rw------- 1 root root 902K Mar  4  2014 iirstesttransiti.sql
-rw------- 1 root root  50M Mar  2 01:09 information_schema.sql
-rw------- 1 root root 5.0M Mar  2 01:11 masterpuffinwe_0.sql
-rw------- 1 root root 4.5M May  1  2015 masterpuffinweba.sql
-rw------- 1 root root 513K Mar  2 01:11 mysql.sql
-rw------- 1 root root 3.8M Mar 19  2015 newstransition_0.sql
-rw------- 1 root root  38M Mar  2 01:11 newstransitionne.sql
-rw------- 1 root root 212M Jul 31  2014 pbstage20130212t.sql
-rw------- 1 root root 106M Jul 31  2014 pbstage2014040_0.sql
-rw------- 1 root root  17K Mar  2 01:11 performance_schema.sql
-rw------- 1 root root 3.1M Jun 23  2013 spacestransition.sql
-rw------- 1 root root 2.7M Jan  7  2014 spacetesttransit.sql
-rw------- 1 root root 5.7M Aug  8  2014 spacetransitio_0.sql
-rw------- 1 root root 4.7M Dec  1  2014 spacetransitionn.sql
-rw------- 1 root root 107M Jan 12  2014 stg2transition_0.sql
-rw------- 1 root root 186M Jan  9  2014 stg2transition_1.sql
-rw------- 1 root root 186M Jan  9  2014 stg2transition_2.sql
-rw------- 1 root root 109M Jun 12  2014 stg2transitionne.sql
-rw------- 1 root root 459K Jun 12  2014 stg3transitionne.sql
-rw------- 1 root root 393K Jun 12  2014 stg4transitionne.sql
-rw------- 1 root root 390K Mar 26  2014 stg6transitionne.sql
-rw------- 1 root root 393K Aug  7  2014 stgsamtransition.sql
-rw------- 1 root root 106M Jul 31  2014 stgtransitionn_0.sql
-rw------- 1 root root 180M Oct 17  2013 stgtransitionn_1.sql
-rw------- 1 root root 180M Oct 17  2013 stgtransitionn_2.sql
-rw------- 1 root root 107M Jan 12  2014 stgtransitionnet.sql
-rw------- 1 root root 310K Mar  2 01:11 teststgtransit_0.sql
-rw------- 1 root root 399K Nov  1  2013 teststgtransit_1.sql
-rw------- 1 root root 5.4M May  1  2015 tnpuffinwebarc_0.sql
-rw------- 1 root root 5.0M Mar  2 01:11 tnpuffinwebarchn.sql
-rw------- 1 root root 1.8G Mar  2 01:23 transitionnetw_0.sql
-rw------- 1 root root 251M Mar 19  2015 transitionnetwor.sql
-rw------- 1 root root 104M Jan 23  2013 v01newdevtransit.sql

Note the times on the files created this morning, 5.0M Mar 2 01:11 tnpuffinwebarchn.sql and 1.8G Mar 2 01:23 transitionnetw_0.sql -- it took 12 mins to create the transitionnetw_0.sql dump and this is also the time at which the site becomes unavailable.

We now have:

ls -lah /var/backups/mysql/sqldump/
total 4.8G
drwx------ 2 root root 4.0K Jun 19  2015 ./
drwx------ 3 root root 4.0K Dec 15  2012 ../
-rw------- 1 root root 221M Aug  7  2014 bookerstage20140.sql
-rw------- 1 root root 221M Dec  5  2014 bookerstage20141.sql
-rw------- 1 root root 232M Mar  2 16:49 bookerstage20150.sql
-rw------- 1 root root 3.3M Mar 19  2015 bookerstage201_0.sql
-rw------- 1 root root 221M Mar 19  2015 bookerstage201_1.sql
-rw------- 1 root root 3.0M Mar  2 16:49 bookerstage201_2.sql
-rw------- 1 root root 224M Mar  2 16:49 bookerstagesamtr.sql
-rw------- 1 root root 866K Mar  4  2014 iirstestclonetra.sql
-rw------- 1 root root 966K Aug 11  2014 iirstesttransi_0.sql
-rw------- 1 root root 902K Mar  4  2014 iirstesttransiti.sql
-rw------- 1 root root  49M Mar  2 16:48 information_schema.sql
-rw------- 1 root root 5.0M Mar  2 16:49 masterpuffinwe_0.sql
-rw------- 1 root root 4.5M May  1  2015 masterpuffinweba.sql
-rw------- 1 root root 513K Mar  2 16:49 mysql.sql
-rw------- 1 root root 3.8M Mar 19  2015 newstransition_0.sql
-rw------- 1 root root  38M Mar  2 16:49 newstransitionne.sql
-rw------- 1 root root 212M Jul 31  2014 pbstage20130212t.sql
-rw------- 1 root root 106M Jul 31  2014 pbstage2014040_0.sql
-rw------- 1 root root  17K Mar  2 16:49 performance_schema.sql
-rw------- 1 root root 3.1M Jun 23  2013 spacestransition.sql
-rw------- 1 root root 2.7M Jan  7  2014 spacetesttransit.sql
-rw------- 1 root root 5.7M Aug  8  2014 spacetransitio_0.sql
-rw------- 1 root root 4.7M Dec  1  2014 spacetransitionn.sql
-rw------- 1 root root 107M Jan 12  2014 stg2transition_0.sql
-rw------- 1 root root 186M Jan  9  2014 stg2transition_1.sql
-rw------- 1 root root 186M Jan  9  2014 stg2transition_2.sql
-rw------- 1 root root 109M Jun 12  2014 stg2transitionne.sql
-rw------- 1 root root 459K Jun 12  2014 stg3transitionne.sql
-rw------- 1 root root 393K Jun 12  2014 stg4transitionne.sql
-rw------- 1 root root 390K Mar 26  2014 stg6transitionne.sql
-rw------- 1 root root 393K Aug  7  2014 stgsamtransition.sql
-rw------- 1 root root 106M Jul 31  2014 stgtransitionn_0.sql
-rw------- 1 root root 180M Oct 17  2013 stgtransitionn_1.sql
-rw------- 1 root root 180M Oct 17  2013 stgtransitionn_2.sql
-rw------- 1 root root 107M Jan 12  2014 stgtransitionnet.sql
-rw------- 1 root root 310K Mar  2 16:49 teststgtransit_0.sql
-rw------- 1 root root 399K Nov  1  2013 teststgtransit_1.sql
-rw------- 1 root root 5.4M May  1  2015 tnpuffinwebarc_0.sql
-rw------- 1 root root 5.0M Mar  2 16:49 tnpuffinwebarchn.sql
-rw------- 1 root root 1.8G Mar  2 16:53 transitionnetw_0.sql
-rw------- 1 root root 251M Mar 19  2015 transitionnetwor.sql
-rw------- 1 root root 104M Jan 23  2013 v01newdevtransit.sql

So I'm afraid the size of the database hasn't changed, it is still 1.8G Mar 2 16:53 transitionnetw_0.sql.

hours, totalhours changed

chris — Wed, 02 Mar 2016 17:15:46 GMT

hours changed from 0.0 to 0.05
totalhours changed from 1.1 to 1.15

Note that creating these backup dumps also made the site go off-line, confirming that it is the backups causing the downtime just after 1am each day.

chris — Thu, 03 Mar 2016 08:02:52 GMT

Replying to ade:

Another thought is, if it is the site db that is suddenly tripled in size do we have a back up of the small database from 6 weeks ago? Am happy to do a comparison of the two DB's if they can be made available to see where the increase in size is occurring?

On Parrot server the tnro database is the 1.8G one from yesterday minus the users (hashed) passwords and the tnro2 database is the one from 6 weeks ago minus the users table -- are you OK using phpMyAdmin to compare them?

ade — Thu, 03 Mar 2016 12:20:09 GMT

Hi Chris,
No worries. If you could make it available from Parrot, can access it from
there. Can currently see 'tnro' and 'tnro2'.
cheers
Ade
On 3 March 2016 at 08:02, Transition Technology Trac <
trac@tech.transitionnetwork.org> wrote:
> #907: TN Drupal database size
> -------------------------------------+-------------------------------------
>            Reporter:  chris          |                      Owner:  chris
>                Type:  maintenance    |                     Status:  new
>            Priority:  major          |                  Milestone:
>           Component:  Drupal         |  Maintenance
>   modules & settings                 |                 Resolution:
>            Keywords:                 |  Estimated Number of Hours:  0.0
> Add Hours to Ticket:  0              |                  Billable?:  1
>         Total Hours:  1.15           |
> -------------------------------------+-------------------------------------
>
> Comment (by chris):
>
>  Replying to [comment:1 ade]:
>  >
>  > Another thought is, if it is the site db that is suddenly tripled in
>  size
>  > do we have a back up of the small database from 6 weeks ago? Am happy to
>  do
>  > a comparison of the two DB's if they can be made available to see where
>  the
>  > increase in size is occurring?
>
>  On Parrot server the `tnro` database is the 1.8G one from yesterday minus
>  the users (hashed) passwords and the `tnro2` database is the one from 6
>  weeks ago minus the `users` table -- are you OK using phpMyAdmin to
>  compare them?
>
> --
> Ticket URL: <https://tech.transitionnetwork.org/trac/ticket/907#comment:10
> >
> Transition Technology <https://tech.transitionnetwork.org/trac>
> Support and issues tracking for the Transition Network Web Project.
>
--
Ade Stuart
Web Manager - Transition network
07595 331877
The Transition Network is a registered charity
address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
website: www.transitionnetwork.org
TN company no: 6135675 TN charity no: 1128675

ade — Thu, 03 Mar 2016 12:25:04 GMT

Doh,
just reread your last email and can see that you have already explained it.
Will look at it from here :)
cheers then
A
On 3 March 2016 at 12:19, Ade Stuart <adestuart@transitionnetwork.org>
wrote:
> Hi Chris,
> No worries. If you could make it available from Parrot, can access it from
> there. Can currently see 'tnro' and 'tnro2'.
>
> cheers
> Ade
>
> On 3 March 2016 at 08:02, Transition Technology Trac <
> trac@tech.transitionnetwork.org> wrote:
>
>> #907: TN Drupal database size
>>
>> -------------------------------------+-------------------------------------
>>            Reporter:  chris          |                      Owner:  chris
>>                Type:  maintenance    |                     Status:  new
>>            Priority:  major          |                  Milestone:
>>           Component:  Drupal         |  Maintenance
>>   modules & settings                 |                 Resolution:
>>            Keywords:                 |  Estimated Number of Hours:  0.0
>> Add Hours to Ticket:  0              |                  Billable?:  1
>>         Total Hours:  1.15           |
>>
>> -------------------------------------+-------------------------------------
>>
>> Comment (by chris):
>>
>>  Replying to [comment:1 ade]:
>>  >
>>  > Another thought is, if it is the site db that is suddenly tripled in
>>  size
>>  > do we have a back up of the small database from 6 weeks ago? Am happy
>> to
>>  do
>>  > a comparison of the two DB's if they can be made available to see where
>>  the
>>  > increase in size is occurring?
>>
>>  On Parrot server the `tnro` database is the 1.8G one from yesterday minus
>>  the users (hashed) passwords and the `tnro2` database is the one from 6
>>  weeks ago minus the `users` table -- are you OK using phpMyAdmin to
>>  compare them?
>>
>> --
>> Ticket URL: <
>> https://tech.transitionnetwork.org/trac/ticket/907#comment:10>
>> Transition Technology <https://tech.transitionnetwork.org/trac>
>> Support and issues tracking for the Transition Network Web Project.
>>
>
>
>
> --
> Ade Stuart
> Web Manager - Transition network
>
> 07595 331877
>
> The Transition Network is a registered charity
> address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
> website: www.transitionnetwork.org
> TN company no: 6135675 TN charity no: 1128675
>
>
>
>
--
Ade Stuart
Web Manager - Transition network
07595 331877
The Transition Network is a registered charity
address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
website: www.transitionnetwork.org
TN company no: 6135675 TN charity no: 1128675

ade — Thu, 03 Mar 2016 13:05:04 GMT

Having looked at the field sizes I can see the following are obvious,
mainly within the caching fields.
There's nearly a Gb there alone...
'cache_advagg_bundle_reuse' gone from 144k to 815mb
'cache_block' gone from 80k to 54mb
cache_form' gone from 84mb to 210mb
'cache_page' gone from 1.5mb to 177mb
'cache_views_data' gone from 1.5mb to 177mb
'Flood'  gone from 8mb to 26mb
'WatchDog' gone from 21mb to 191mb
Ade
On 3 March 2016 at 12:25, Transition Technology Trac <
trac@tech.transitionnetwork.org> wrote:
> #907: TN Drupal database size
> -------------------------------------+-------------------------------------
>            Reporter:  chris          |                      Owner:  chris
>                Type:  maintenance    |                     Status:  new
>            Priority:  major          |                  Milestone:
>           Component:  Drupal         |  Maintenance
>   modules & settings                 |                 Resolution:
>            Keywords:                 |  Estimated Number of Hours:  0.0
> Add Hours to Ticket:  0              |                  Billable?:  1
>         Total Hours:  1.15           |
> -------------------------------------+-------------------------------------
>
> Comment (by ade):
>
>  {{{
>  Doh,
>  just reread your last email and can see that you have already explained
>  it.
>  Will look at it from here :)
>
>  cheers then
>  A
>
>  On 3 March 2016 at 12:19, Ade Stuart <adestuart@transitionnetwork.org>
>  wrote:
>
>  > Hi Chris,
>  > No worries. If you could make it available from Parrot, can access it
>  from
>  > there. Can currently see 'tnro' and 'tnro2'.
>  >
>  > cheers
>  > Ade
>  >
>  > On 3 March 2016 at 08:02, Transition Technology Trac <
>  > trac@tech.transitionnetwork.org> wrote:
>  >
>  >> #907: TN Drupal database size
>  >>
>  >>
>
>  -------------------------------------+-------------------------------------
>  >>            Reporter:  chris          |                      Owner:
>  chris
>  >>                Type:  maintenance    |                     Status:  new
>  >>            Priority:  major          |                  Milestone:
>  >>           Component:  Drupal         |  Maintenance
>  >>   modules & settings                 |                 Resolution:
>  >>            Keywords:                 |  Estimated Number of Hours:  0.0
>  >> Add Hours to Ticket:  0              |                  Billable?:  1
>  >>         Total Hours:  1.15           |
>  >>
>  >>
>
>  -------------------------------------+-------------------------------------
>  >>
>  >> Comment (by chris):
>  >>
>  >>  Replying to [comment:1 ade]:
>  >>  >
>  >>  > Another thought is, if it is the site db that is suddenly tripled in
>  >>  size
>  >>  > do we have a back up of the small database from 6 weeks ago? Am
>  happy
>  >> to
>  >>  do
>  >>  > a comparison of the two DB's if they can be made available to see
>  where
>  >>  the
>  >>  > increase in size is occurring?
>  >>
>  >>  On Parrot server the `tnro` database is the 1.8G one from yesterday
>  minus
>  >>  the users (hashed) passwords and the `tnro2` database is the one from
>  6
>  >>  weeks ago minus the `users` table -- are you OK using phpMyAdmin to
>  >>  compare them?
>  >>
>  >> --
>  >> Ticket URL: <
>  >> https://tech.transitionnetwork.org/trac/ticket/907#comment:10>
>  >> Transition Technology <https://tech.transitionnetwork.org/trac>
>  >> Support and issues tracking for the Transition Network Web Project.
>  >>
>  >
>  >
>  >
>  > --
>  > Ade Stuart
>  > Web Manager - Transition network
>  >
>  > 07595 331877
>  >
>  > The Transition Network is a registered charity
>  > address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
>  > website: www.transitionnetwork.org
>  > TN company no: 6135675 TN charity no: 1128675
>  >
>  >
>  >
>  >
>
>
>  --
>  Ade Stuart
>  Web Manager - Transition network
>
>  07595 331877
>
>  The Transition Network is a registered charity
>  address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
>  website: www.transitionnetwork.org
>  TN company no: 6135675 TN charity no: 1128675
>
>  }}}
>
> --
> Ticket URL: <https://tech.transitionnetwork.org/trac/ticket/907#comment:12
> >
> Transition Technology <https://tech.transitionnetwork.org/trac>
> Support and issues tracking for the Transition Network Web Project.
>
--
Ade Stuart
Web Manager - Transition network
07595 331877
The Transition Network is a registered charity
address: 43 Fore St, Totnes, Devon, TQ9 5HN, UK
website: www.transitionnetwork.org
TN company no: 6135675 TN charity no: 1128675

ade — Thu, 03 Mar 2016 13:20:09 GMT

Do we have Drush installed on the server...?
Can we use that to clear the cache?
A

hours, totalhours changed

chris — Thu, 03 Mar 2016 13:45:38 GMT

hours changed from 0.0 to 0.1
totalhours changed from 1.15 to 1.25

Replying to ade:

Do we have Drush installed on the server...? Can we use that to clear the cache?

Yes, I expect the fact that they are not being cleared is a side-effect from all the BOA cron jobs being commented out, ticket:893, Paul are you able to create a new cronjob to delete these caches each night?

If this is done before 1am then the MySQL dumps created by backupninja won't have unneeded cache data in them.

I don't know if the state of the permissions on the server allows a cronjob to run as a non-root users, but that would be ideal if possible.

hours, totalhours changed

paul — Fri, 04 Mar 2016 11:15:45 GMT

hours changed from 0.0 to 0.25
totalhours changed from 1.25 to 1.5

Replying to chris:

Replying to ade:

Do we have Drush installed on the server...? Can we use that to clear the cache?

Yes, I expect the fact that they are not being cleared is a side-effect from all the BOA cron jobs being commented out, ticket:893, Paul are you able to create a new cronjob to delete these caches each night?

If this is done before 1am then the MySQL dumps created by backupninja won't have unneeded cache data in them.

I don't know if the state of the permissions on the server allows a cronjob to run as a non-root users, but that would be ideal if possible.

I can have a look into this on Monday. My suggestion now would be to truncate these cache tables and then monitor how these tables grow over the weekend. They may all grow a lot more slowly now that database error reporting has been disabled - this used to be disabled.

@Chris Do you want to take a recent backup of the database and truncate the large cache tables?

hours, totalhours changed

chris — Fri, 04 Mar 2016 12:49:26 GMT

hours changed from 0.0 to 1.0
totalhours changed from 1.5 to 2.5

Replying to paul:

@Chris Do you want to take a recent backup of the database and truncate the large cache tables?

OK, I think we might be best off using our own script to do the nightly dumps as we can then incorporate the truncating, so I have written this script, which is /usr/local/bin/mysql-dump:

#!/bin/bash
# Directory for root to dump all database files
DUMP_DIR="/var/backups/mysql/sqldump"
if [[ ! -d "${DUMP_DIR}" ]]; then
  mkdir "${DUMP_DIR}" -p
  chmod 700 "${DUMP_DIR}"
fi
# All the MySQL databases, excluding ones we don't want
DATABASES=$(mysql -NBA -e 'show databases' | grep -vw 'mysql' | grep -vw 'information_schema' | grep -vw 'performance_schema' )
#echo "DATABASES: ${DATABASES}"
# Loop through the databases and dump them
for d in ${DATABASES} ; do
  # Dump the databases to DUMP_DIR
  echo "Dumping ${d}"
  mysqldump --add-drop-table "${d}" > "${DUMP_DIR}/${d}.sql" || exit 1
done

And I have deleted the old backups and run it:

rm -rf /var/backups/mysql/sqldump/*
/usr/local/bin/mysql-dump

I'm afraid this caused some downtime for the site, I have now amended the script so it uses nice and this should help for the future.

I have copied the latest backup:

cp /var/backups/mysql/sqldump/transitionnetw_0.sql /var/backups/mysql/sqldump/transitionnetw_0.2016-03-04.sql

So we now have:

ls -lah /var/backups/mysql/sqldump/
total 4.1G
drwx------ 2 root root 4.0K Mar  4 12:12 ./
drwx------ 3 root root 4.0K Dec 15  2012 ../
-rw-r--r-- 1 root root 232M Mar  4 11:51 bookerstage20150.sql
-rw-r--r-- 1 root root 3.0M Mar  4 11:51 bookerstage201_2.sql
-rw-r--r-- 1 root root 224M Mar  4 11:52 bookerstagesamtr.sql
-rw-r--r-- 1 root root 5.0M Mar  4 11:52 masterpuffinwe_0.sql
-rw-r--r-- 1 root root  38M Mar  4 11:52 newstransitionne.sql
-rw-r--r-- 1 root root 308K Mar  4 11:52 teststgtransit_0.sql
-rw-r--r-- 1 root root 5.0M Mar  4 11:53 tnpuffinwebarchn.sql
-rw-r--r-- 1 root root 1.8G Mar  4 12:12 transitionnetw_0.2016-03-04.sql
-rw-r--r-- 1 root root 1.8G Mar  4 12:11 transitionnetw_0.sql

Based on this thread is is safe to truncate all cache_* tables and also watchdog and flood, so I have updated the script to:

#!/bin/bash
# Directory for root to dump all database files
DUMP_DIR="/var/backups/mysql/sqldump"
if [[ ! -d "${DUMP_DIR}" ]]; then
  mkdir "${DUMP_DIR}" -p
  chmod 700 "${DUMP_DIR}"
fi
# All the MySQL databases, excluding ones we don't want
DATABASES=$(mysql -NBA -e 'SHOW DATABASES' | grep -vw 'mysql' | grep -vw 'information_schema' | grep -vw 'performance_schema' )
# Loop through the databases and dump them
for d in ${DATABASES} ; do
  # Find the cache tables
  CACHE_TABLES=$(mysql -NBA -e 'show tables' ${d} | egrep '^cach|flood|watchdog')
  for c in ${CACHE_TABLES}; do
    mysql -NBA -e "TRUNCATE ${c}" ${d}
  done
  # Dump the databases to DUMP_DIR
  echo "Dumping ${d}"
  nice -n19 mysqldump --add-drop-table ${d} > ${DUMP_DIR}/${d}.sql || exit 1
done

And the result is no downtime and these files:

ls -lah /var/backups/mysql/sqldump/
total 2.6G
drwx------ 2 root root 4.0K Mar  4 12:12 ./
drwx------ 3 root root 4.0K Dec 15  2012 ../
-rw-r--r-- 1 root root 222M Mar  4 12:40 bookerstage20150.sql
-rw-r--r-- 1 root root 3.0M Mar  4 12:40 bookerstage201_2.sql
-rw-r--r-- 1 root root 221M Mar  4 12:40 bookerstagesamtr.sql
-rw-r--r-- 1 root root 4.5M Mar  4 12:40 masterpuffinwe_0.sql
-rw-r--r-- 1 root root 3.2M Mar  4 12:41 newstransitionne.sql
-rw-r--r-- 1 root root 302K Mar  4 12:41 teststgtransit_0.sql
-rw-r--r-- 1 root root 4.4M Mar  4 12:41 tnpuffinwebarchn.sql
-rw-r--r-- 1 root root 1.8G Mar  4 12:12 transitionnetw_0.2016-03-04.sql
-rw-r--r-- 1 root root 379M Mar  4 12:41 transitionnetw_0.sql

So that looks good, uninstalling backupninja:

aptitude remove backupninja

Documenting the script here, wiki:MysqlBackup.

Adding a crontab to run as root:

15 01 * * * /usr/local/bin/mysql-backup

PS I'd really rather have a development server to sort things like this out on first, doing it on the live database on the live server isn't ideal...

hours, totalhours changed

chris — Fri, 04 Mar 2016 14:22:23 GMT

hours changed from 0.0 to 0.25
totalhours changed from 2.5 to 2.75

I have just updated the query to find the cache tables from:

CACHE_TABLES=$(mysql -NBA -e 'show tables' ${d} | egrep '^cach|flood|watchdog')

To (note I had omitted a e by mistake and the above version would match things like floodlights etc):

CACHE_TABLES=$(mysql -NBA -e 'show tables' ${d} | egrep '^cache|^flood$|^watchdog$')

Just to be safe, this results in:

mysql -NBA -e 'show tables' transitionnetw_0 | egrep '^cache|^flood$|^watchdog$'
cache
cache_admin_menu
cache_advagg
cache_advagg_bundle_reuse
cache_advagg_files_data
cache_block
cache_content
cache_emfield_xml
cache_filter
cache_form
cache_hierarchical_select
cache_location
cache_media_youtube_status
cache_menu
cache_mollom
cache_page
cache_path
cache_rules
cache_update
cache_views
cache_views_data
flood
watchdog

hours, totalhours changed

paul — Fri, 04 Mar 2016 16:39:14 GMT

hours changed from 0.0 to 0.25
totalhours changed from 2.75 to 3.0

Always nice to see a a master at work. Good job Chris.

ade — Fri, 04 Mar 2016 18:50:04 GMT

Out of curiosity, why did your comment email add .15 to the hours total?
Paul?
On 4 Mar 2016 4:39 p.m., "Transition Technology Trac" <
trac@tech.transitionnetwork.org> wrote:
> #907: TN Drupal database size
> -------------------------------------+-------------------------------------
>            Reporter:  chris          |                      Owner:  chris
>                Type:  maintenance    |                     Status:  new
>            Priority:  major          |                  Milestone:
>           Component:  Drupal         |  Maintenance
>   modules & settings                 |                 Resolution:
>            Keywords:                 |  Estimated Number of Hours:  0.0
> Add Hours to Ticket:  0.25           |                  Billable?:  1
>         Total Hours:  2.75           |
> -------------------------------------+-------------------------------------
> Changes (by paul):
>
>  * hours:  0.0 => 0.25
>  * totalhours:  2.75 => 3.0
>
>
> Comment:
>
>  Always nice to see a a master at work. Good job Chris.
>
> --
> Ticket URL: <https://tech.transitionnetwork.org/trac/ticket/907#comment:19
> >
> Transition Technology <https://tech.transitionnetwork.org/trac>
> Support and issues tracking for the Transition Network Web Project.
>

hours, totalhours changed

paul — Fri, 04 Mar 2016 22:34:13 GMT

hours changed from 0.0 to 0.125
totalhours changed from 3.0 to 3.125

I took 15 minutes going through the previous comments on the issue - looking over the code, and thinking things over to see if I could contribute further.

chris — Thu, 10 Mar 2016 10:58:42 GMT

Replying to ade:

Out of curiosity, why did your comment email add .15 to the hours total?

For what it is worth we did have a verbal agreement with Ed, from several years ago, that the minimum amount of time to record for a chunk of work would be 15 mins even if it only took 5 or 10 mins, however this isn't something that we have strictly stuck to, you will find lots of example of time less than 15 mins recorded on Trac.