limiting the size of the data kept

I have about 10 tables in mySQL. 3 of them are for logging user's data and I am using GroceryCrud so admins can display each of these three tables.  I was (am) concerned about the "little mySQL" database being hammered since it also has tables that make the whole application work. For this reason, besides keeping the logged data in mySQL, I am also logging it into DynamoDB which is an AWS noSQL database. 

I would like to limit the amount of logged user data that i am keeping in mySQL to "the last 30 records". If user want the whole complete list they can download a CSV file from DynamaoDB.  I am hoping to relieve the little mySQL database from getting totally overworked. 

I have done some research and found ORDER BY and LIMIT and somehow want to use these to delete older records from mySQL (there will of course still remain a copy in DynamoDB). 

When should I do this? I don't want to check for this every time a user sends in a message from a browser. It would slow the system down to a crawl. I also don't want ot do this when a user logs into the app because it will cause the app to appear sluggish.  I was thinking perhaps nightly at midnight, I can run a small app that will clean up the database (ie keep only the last 30 records for each user). But how do I do this within the CI/PHP world? My app only "wakes up" when a user logs in (or a browser sends a message). 

Someone mentioned running a CRON job. Can I write a CI/PHP app that gets kicked off by CRON?

proof that an old dog can learn new tricks

Yes, see https://www.codeigniter.com/user_guide/general/cli.html

Simpler is always better

Thanks Don. Perfect. One more question. I suspect that when I run this CRON in the middle of the night I will also optimize these tables and also backup the entire mySQL database. This is the main reason I am trying to limit the amount of data I hold in my own mySQL. I figure I probably want to shut down my system while I am doing this, and then bring it back up when I am done with the CRON.

Is there anyway I could "signal" my CI apps to kick everyone off, and not allow any logins while this is going on? I think that PHP is running separate processes for each user. Is there any "shared memory" that each process could check before deciding to loggoff everyone?

Put another way, IS there a global variable (ie semaphore) that all the CI/PHP applications can see? I don't need the apps to wait on that semaphore since this is a multi process app.

proof that an old dog can learn new tricks

We put special file in writeable directory, if it exists, users will see "Down for maintenance" page, if not, they will see the page they were after.

You could also use environmental variables to set it on server side, which saves you specifically trying to load another file and makes it available in $_SERVER global variable.

We put special file in writeable directory, if it exists, users will see "Down for maintenance" page, if not, they will see the page they were after.

You could also use environmental variables to set it on server side, which saves you specifically trying to load another file and makes it available in $_SERVER global variable.

>>You could also use environmental variables to set it on server side, which saves you specifically trying to load another file and makes it available in $_SERVER global variable.

That would be perfect!

proof that an old dog can learn new tricks

We put special file in writeable directory, if it exists, users will see "Down for maintenance" page, if not, they will see the page they were after.

You could also use environmental variables to set it on server side, which saves you specifically trying to load another file and makes it available in $_SERVER global variable.

Can I create my own environment variable? 

Or do I need to hijack one of the existing ones? Who knows what I will break!

proof that an old dog can learn new tricks

@richb201,

You can create your own Defined variables in the constants.php file under the config directory. Useful link ( http://php.net/manual/en/language.constants.php ).