Status

Data & BI Services Team
DB: MySQL
RESOLVED FIXED
5 years ago
4 years ago

People

(Reporter: sheeri, Unassigned)

Tracking

Details

(Reporter)

Description

5 years ago
sentry_log is 164G on dev. in bug 739881 we defrag'd production...
(Reporter)

Comment 1

5 years ago
mysql> select (data_length+index_length)/1024/1024/1024 as sizeGb from information_schema.tables where table_name='sentry_log' and table_schema='bouncer_stage';
+-----------------+
| sizeGb          |
+-----------------+
| 52.463714599609 |
+-----------------+
1 row in set (0.00 sec)

About 52G in information_schema, almost triple that on disk:
[root@dev1.db.phx1 bouncer_stage]# ls -rlth sentry_log.ibd 
-rw-rw---- 1 mysql mysql 164G Dec 16 22:06 sentry_log.ibd

We can take dev2 out of the load balancer and defrag, then failover dev1 and do the same.
(Reporter)

Comment 2

5 years ago
dev2 is out of the load balancer. 

In a screen session, this is running:
mysql> set global sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> optimize table bouncer_stage.sentry_log;
(Reporter)

Comment 3

5 years ago
Well, that's disappointing:
-rw-rw---- 1 mysql mysql 165G Dec 17 15:38 sentry_log.ibd

Will have to see if there's data we can scrub from this.
(Reporter)

Comment 4

5 years ago
mysql> select min(log_date) from sentry_log limit 1;
+---------------------+
| min(log_date)       |
+---------------------+
| 2013-04-09 21:05:01 |
+---------------------+
1 row in set (0.00 sec)


April - 31314
May - 46666
June - 45762
July - 46826
August - 47676
September - 46306
October - 47636
November - 46133
December (so far) - 25873

Total count - 384192 (done by querying, so may not be exactly the addition of the above).

It looks like there's a growth rate of 45,000+ per month, and about every 2300 rows is 1G, so about 17-18G per month. 

The production server has much less of a growth rate, but testing bouncer is a good thing. I'm OK with the growth rate, but I'd bet we can delete all but a month or 2 of data....
(Reporter)

Comment 5

5 years ago
Can we set up a lifecycle for the data on bouncer_stage? sentry_log is currently 165G in stage (it's only 274M in production! see also bug 739881)

If you check out comment 4 of this bug, you'll see we have a high growth rate here - that's OK (testing bouncer is a good thing), but can we delete the data that's older than a month or 2 to conserve space? We can set up a recurring delete.
Flags: needinfo?(nthomas)
Flags: needinfo?(nmaul)
(Reporter)

Comment 6

5 years ago
put dev2 back in the lb.
I don't see any problem with cleaning out at least as aggressively as production, but should defer to Jake about it since he actually uses staging.

It would be worth checking if the staging instance is well out of sync with production (ie checking lots of mirrors instead of ~8). And we never did figure out if anything is cleaning out data for production in bug 739881, and that might be missing in staging.
Flags: needinfo?(nthomas)
(Reporter)

Comment 8

5 years ago
Well, there's something on production keeping it small:
mysql> select min(log_date) from sentry_log;
+---------------------+
| min(log_date)       |
+---------------------+
| 2013-12-17 21:05:01 |
+---------------------+
1 row in set (0.00 sec)

Comment 9

5 years ago
Yes, we can definitely run the same cleanup on stage as we do on prod.

This isn't anywhere in the app... looks like a cron job on the DB master...

[nmaul@bouncer1 ~]$ sudo ls -l /etc/cron.d/
-rw-r--r-- 1 root root 138 May  3  2012 clean-sentry-log
-rw-r--r-- 1 root root 139 May  3  2012 defrag-sentry-log

I've deleted a few rows by hand, but it's extremely slow going... probably best to simply get those scripts on the system, run them by hand once in a screen, and then set up the cron. It looks like it has logic built in to delete lines in batches, so as to not lock things up for a long time.
Flags: needinfo?(nmaul)
(Reporter)

Comment 10

5 years ago
FWIW, the production bouncer server has:

[root@bouncer1 cron.d]# more clean-sentry-log 
MAILTO="infra-dbnotices@mozilla.com"
0 23 * * * root /usr/bin/time /root/bin/clean-sentry-log.sh > /tmp/clean-se
ntry-log-cron-output 2>&1
[root@bouncer1 cron.d]# more defrag-sentry-log 
MAILTO="infra-dbnotices@mozilla.com"
0 2 * * * root /usr/bin/time /root/bin/defrag-sentry-log.sh > /tmp/defrag-s
entry-log-cron-output 2>&1

it runs nightly and deletes sentry_log entries older than 2 hours, and then defrags.

So I think we can run a similar thing on stage. I will make sure these are puppetized.
(Reporter)

Comment 11

5 years ago
Actually, for stage, it'd be much easier to create a new table and only put the entries for the last day or so in there, and then just drop the old table. No defrag needed, this first time.
(Reporter)

Comment 12

5 years ago
mysql> create table sentry_log2 like sentry_log; insert into sentry_log2 select * from sentry_log where log_date>now()-interval 1 day; 
Query OK, 0 rows affected (0.04 sec)

 alter table sentry_log rename sentry_log_old; alter table sentry_log2 rename sentry_log;
Query OK, 1478 rows affected (7 min 28.85 sec)
Records: 1478  Duplicates: 0  Warnings: 0

mysql>  alter table sentry_log rename sentry_log_old; alter table sentry_log2 rename sentry_log;
Query OK, 0 rows affected (0.14 sec)

Query OK, 0 rows affected (0.00 sec)
(Reporter)

Comment 13

5 years ago
[root@dev1.db.phx1 bouncer_stage]# ls -rlth sentry_log*.ibd
-rw-rw---- 1 mysql mysql 166G Dec 18 18:58 sentry_log_old.ibd
-rw-rw---- 1 mysql mysql 740M Dec 18 19:16 sentry_log.ibd

Currently running this to archive the table to /root/archive and drop it if the archive was successful:
[root@dev1.db.phx1 archive]# time mysqldump bouncer_stage sentry_log_old > sentry_log_old.sql && mysql -e "drop table bouncer_stage.sentry_log_old"
(Reporter)

Comment 14

5 years ago
Sheeri-Cabral:trunk scabral$ svn commit -m "puppetizing the cleann/defrag sentry_log scripts, and setting it to also be on dev"
Sending        manifests/nodes/databases.pp
Adding         modules/mysql2/templates/scripts/clean_defrag_sentry_log
Adding         modules/mysql2/templates/scripts/clean_defrag_sentry_log.cron
Transmitting file data ...
Committed revision 79889.
(Reporter)

Comment 15

5 years ago
these crons worked on both dev and bouncer; closing.
Status: NEW → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → FIXED
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.