Ticket #907 (new maintenance)
TN Drupal database size
| Reported by: | chris | Owned by: | chris |
|---|---|---|---|
| Priority: | major | Milestone: | Maintenance |
| Component: | Drupal modules & settings | Keywords: | |
| Cc: | paul, sam, ade | Estimated Number of Hours: | 0.0 |
| Add Hours to Ticket: | 0 | Billable?: | yes |
| Total Hours: | 3.125 |
Description
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?
Change History
comment:2 Changed 9 months ago by paul
- Add Hours to Ticket changed from 0.0 to 0.125
- Total Hours changed from 0.0 to 0.125
Database logging is enabled. How big is the watchdog table?
Investigating ..
comment:3 Changed 9 months ago by paul
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
comment:4 Changed 9 months ago by paul
- Add Hours to Ticket changed from 0.0 to 0.25
- Total Hours 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 |
comment:5 Changed 9 months ago by paul
- Add Hours to Ticket changed from 0.0 to 0.125
- Total Hours 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?
comment:6 in reply to: ↑ 1 Changed 9 months ago by chris
- Add Hours to Ticket changed from 0.0 to 0.15
- Total Hours 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.sqlOf 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?
comment:7 Changed 9 months ago by paul
- Add Hours to Ticket changed from 0.0 to 0.25
- Total Hours 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.
comment:8 Changed 9 months ago by chris
- Add Hours to Ticket changed from 0.0 to 0.2
- Total Hours 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.
comment:9 Changed 9 months ago by chris
- Add Hours to Ticket changed from 0.0 to 0.05
- Total Hours 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.
comment:10 in reply to: ↑ 1 Changed 9 months ago by chris
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?
comment:11 Changed 9 months ago by ade
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
comment:12 Changed 9 months ago 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
comment:13 Changed 9 months ago by ade
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
comment:14 follow-up: ↓ 15 Changed 9 months ago by ade
Do we have Drush installed on the server...? Can we use that to clear the cache? A
comment:15 in reply to: ↑ 14 ; follow-up: ↓ 16 Changed 9 months ago by chris
- Add Hours to Ticket changed from 0.0 to 0.1
- Total Hours 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.
comment:16 in reply to: ↑ 15 ; follow-up: ↓ 17 Changed 9 months ago by paul
- Add Hours to Ticket changed from 0.0 to 0.25
- Total Hours 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?
comment:17 in reply to: ↑ 16 Changed 9 months ago by chris
- Add Hours to Ticket changed from 0.0 to 1.0
- Total Hours 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...
comment:18 Changed 9 months ago by chris
- Add Hours to Ticket changed from 0.0 to 0.25
- Total Hours 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 '^cach|^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
comment:19 Changed 9 months ago by paul
- Add Hours to Ticket changed from 0.0 to 0.25
- Total Hours changed from 2.75 to 3.0
Always nice to see a a master at work. Good job Chris.
comment:20 follow-up: ↓ 22 Changed 9 months ago by ade
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. >
comment:21 Changed 9 months ago by paul
- Add Hours to Ticket changed from 0.0 to 0.125
- Total Hours 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.
comment:22 in reply to: ↑ 20 Changed 9 months ago by chris
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.
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