We use innodb table per file. our log grew to 45 million records, and what I did recently was just delete all records where the id was less than 22 million. That took about 10 minutes, then i did an optimize table which took about 12 minutes.
What I had to worry about, was - is the free disk space enough to create the new table. THe original mdl_log table file was 10 gigs. After a successful optimize table, it dropped to about 5 gigs. So it was critical that I have adequate space for that 5 gig table. Once the optimize was done, i had an extra 5 gigs of space on that file system.
I do this manually once a year or every 9 months or so. I create and save a backup of the entire moodle database first, just to keep of copy of the logs in case I need them in the future.
What i might do, is get the timestampe of the record in the middle and do a conversion @
Or i might just do a delete from ... where timestamp < a specific date
I ALWAYS do this on a test system before the production system so I know how long its going to take; and that I just then have to copy/paste the commands so I don't make a typo.
I also ensure I have DOWNTIME for moodle during this process, I don't want anything writing to the logs.
I don't have automated pruning of that table done.
The other file you have to worry about is the grade_grades_history table.
it is the 2nd largest growing table in moodle.
My suggestions would be -
1. manually do these actions every certain 6 months or year.
2. do these steps on a test server first
3. depending upon how much free disk space you have on your db server, will dictate how large you can let it grow.
4. if you have a good mysql dba, I would highly recommend using innodb file per table config.