Re: maintaining size of a db
Got it Harald, thanks. OK, I've got this working now, so I'll do a quick overview of what I've learned... for the archives: I am setting up mysql with msyslog to be a centralized logging server. My servers (Windows Red Hat) will send their logs to this mysql box. To keep the mysql db from growing beyond a certain size, I first estimate the size of my average record and divide by the total byte size I want to allow on disk, to determine about how many records I want as a maximum (of course, one must look at and consider the size of any indexes for your db, also, and leave some extra room for error). For this example, I'll say I've figured out that I can allow a maximum of 250,000 records, and I have: - a db named msyslog - and a table within it named syslogTB syslogTB has an autoincrement field seq. What I do is set up a cron job to run a scan of the database periodically, and yank out all records beyond 250,000. The cron job runs as sql-user with password PASSWORD and calls a plain text file /root/delete_old.sql for it's input. The cron job will thus run this as its command: /path/mysql -u sql-user --password=PASSWORD msyslog /root/delete_old.sql ...and in /root/delete_old.sql, there is only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq DESC limit 25,1; delete from syslogTB where seq @aa; Thanks everyone for your help!! Scott --- Harald Fuchs wrote: Scott H wrote: That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? That's exactly what the two statements above do, except that MySQL has user variables (the @aa shown above) instead of environment variables. = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
two last gotchas I thought of... 1- the routine will erase all the old records, but the day's logs will increment between executions, so you may want to give it a day's worth of 'padding' if the 20GB is a hard limit (disk size). No worries if it's flexible 2- until you have filled to your size limit, you may want to run it manually as you won't have your 250,000 records in the table yet, so your initial query will return null (I think), which is very likely to mess up the delete statement following it -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 12:30 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: maintaining size of a db Got it Harald, thanks. OK, I've got this working now, so I'll do a quick overview of what I've learned... for the archives: I am setting up mysql with msyslog to be a centralized logging server. My servers (Windows Red Hat) will send their logs to this mysql box. To keep the mysql db from growing beyond a certain size, I first estimate the size of my average record and divide by the total byte size I want to allow on disk, to determine about how many records I want as a maximum (of course, one must look at and consider the size of any indexes for your db, also, and leave some extra room for error). For this example, I'll say I've figured out that I can allow a maximum of 250,000 records, and I have: - a db named msyslog - and a table within it named syslogTB syslogTB has an autoincrement field seq. What I do is set up a cron job to run a scan of the database periodically, and yank out all records beyond 250,000. The cron job runs as sql-user with password PASSWORD and calls a plain text file /root/delete_old.sql for it's input. The cron job will thus run this as its command: /path/mysql -u sql-user --password=PASSWORD msyslog /root/delete_old.sql ...and in /root/delete_old.sql, there is only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq DESC limit 25,1; delete from syslogTB where seq @aa; Thanks everyone for your help!! Scott --- Harald Fuchs wrote: Scott H wrote: That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? That's exactly what the two statements above do, except that MySQL has user variables (the @aa shown above) instead of environment variables. = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maintaining size of a db
Scott H [EMAIL PROTECTED] wrote: Repost - Haven't gotten any response on this and can't find an answer. If no one on the mysql list knows, where does a fellow turn? Help! Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maintaining size of a db
--- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
cronjob a sql script that runs a delete statement for old jobs daily -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:40 AM To: MySQL Mailing List Subject: Re: maintaining size of a db --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: cronjob a sql script that runs a delete statement for old jobs daily --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id @aa (do subqueries work with a limit clause?) -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 11:19 AM To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: cronjob a sql script that runs a delete statement for old jobs daily --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
From the manual: 1.8.4.1 Subqueries Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1. Hope that helps, Mike -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: 12 November 2003 17:45 To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the (@aa:=id) thing? I don't follow that. thanks. --- Dan Greene [EMAIL PROTECTED] wrote: What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id @aa (do subqueries work with a limit clause?) -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 11:19 AM To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: cronjob a sql script that runs a delete statement for old jobs daily --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 .. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
you may be able to put both statements to a text file, let's call it deleteold.sql then your cron job would be : mysql (put your connect stuff here) deleteold.sql -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:17 PM To: Michael McTernan; Dan Greene Cc: MySQL Mailing List Subject: RE: maintaining size of a db Well, it sort of helps. But that section is about future enhancements intended for mysql. I need to set something up now, with the current stable version. One thing I read (can't find it now) indicated that the current version (I'm actually running 4.0.15a) has limited support for subqueries - but I don't know exactly how far that goes. So let me set the stage a bit more - I'll assume for now there is no reasonably simple way to work with the actual size of the database on disk, and instead will go with the idea that I can expect the size of any one record to be of some average. So, according to Dan's suggestion, if I do a little math, and control the number of records, I can control the size of the db -- approximately. That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? Other ideas? thanks, scott --- Michael McTernan wrote: From the manual: 1.8.4.1 Subqueries Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1. Hope that helps, Mike From: Scott H OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the (@aa:=id) thing? I don't follow that. thanks. --- Dan Greene [EMAIL PROTECTED] wrote: What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id @aa (do subqueries work with a limit clause?) -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 11:19 AM To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: cronjob a sql script that runs a delete statement for old jobs daily --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) -- MySQL General Mailing List For list archives: http
RE: maintaining size of a db
Cool idea, but I don't think you can really do it. When I try, mysql just gives me back the syntax stuff. sigh I'm a bit perplexed - I would have thought this would be a semi-common issue in db maintenance, but no one seems to have set this up. I'm still trying, please send any other ideas/suggestions my way... thx!! --- Dan Greene wrote: you may be able to put both statements to a text file, let's call it deleteold.sql then your cron job would be : mysql (put your connect stuff here) deleteold.sql From: Scott H Well, it sort of helps. But that section is about future enhancements intended for mysql. I need to set something up now, with the current stable version. One thing I read (can't find it now) indicated that the current version (I'm actually running 4.0.15a) has limited support for subqueries - but I don't know exactly how far that goes. So let me set the stage a bit more - I'll assume for now there is no reasonably simple way to work with the actual size of the database on disk, and instead will go with the idea that I can expect the size of any one record to be of some average. So, according to Dan's suggestion, if I do a little math, and control the number of records, I can control the size of the db -- approximately. That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? Other ideas? thanks, scott --- Michael McTernan wrote: From the manual: 1.8.4.1 Subqueries Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1. Hope that helps, Mike From: Scott H OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the (@aa:=id) thing? I don't follow that. thanks. --- Dan Greene [EMAIL PROTECTED] wrote: What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id @aa (do subqueries work with a limit clause?) -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 11:19 AM To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: cronjob a sql script that runs a delete statement for old jobs daily --- Egor Egorov wrote: Scott H wrote: Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. You can't restrict size of the database only
RE: maintaining size of a db
When I run: mysql -p password -u username log_db_name deleteold.sql ...and within deleteold.sql, I have only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq limit 1000,1; delete from syslogTB where seq @aa; I just get back a screen full of syntax suggestions. Where is my mistake here? --- Dan Greene wrote: for example your script would be mysql --user=myuserid --password=mypassword -h hostname deleteold.sql see: http://www.mysql.com/doc/en/Batch_mode.html -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:22 PM To: Dan Greene Cc: MySQL Mailing List Subject: RE: maintaining size of a db Cool idea, but I don't think you can really do it. When I try, mysql just gives me back the syntax stuff. sigh I'm a bit perplexed - I would have thought this would be a semi-common issue in db maintenance, but no one seems to have set this up. I'm still trying, please send any other ideas/suggestions my way... thx!! --- Dan Greene wrote: you may be able to put both statements to a text file, let's call it deleteold.sql then your cron job would be : mysql (put your connect stuff here) deleteold.sql From: Scott H Well, it sort of helps. But that section is about future enhancements intended for mysql. I need to set something up now, with the current stable version. One thing I read (can't find it now) indicated that the current version (I'm actually running 4.0.15a) has limited support for subqueries - but I don't know exactly how far that goes. So let me set the stage a bit more - I'll assume for now there is no reasonably simple way to work with the actual size of the database on disk, and instead will go with the idea that I can expect the size of any one record to be of some average. So, according to Dan's suggestion, if I do a little math, and control the number of records, I can control the size of the db -- approximately. That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? Other ideas? thanks, scott --- Michael McTernan wrote: From the manual: 1.8.4.1 Subqueries Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1. Hope that helps, Mike From: Scott H OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the (@aa:=id) thing? I don't follow that. thanks. --- Dan Greene [EMAIL PROTECTED] wrote: What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id @aa (do subqueries work with a limit clause?) -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 11:19 AM To: Dan Greene; MySQL Mailing List Subject: RE: maintaining size of a db
RE: maintaining size of a db
Hey - Progress! But first, I had to correct: can't use -p password, must use the longer form: --password=password Then I can run it! One problem. It deleted the first 1000 records, rather than leaving the last 1000 records in the db. What twist is needed to get that right? ? --- Scott H [EMAIL PROTECTED] wrote: When I run: mysql -p password -u username log_db_name deleteold.sql ...and within deleteold.sql, I have only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq limit 1000,1; delete from syslogTB where seq @aa; I just get back a screen full of syntax suggestions. Where is my mistake here? --- Dan Greene wrote: for example your script would be mysql --user=myuserid --password=mypassword -h hostname deleteold.sql see: http://www.mysql.com/doc/en/Batch_mode.html -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:22 PM To: Dan Greene Cc: MySQL Mailing List Subject: RE: maintaining size of a db Cool idea, but I don't think you can really do it. When I try, mysql just gives me back the syntax stuff. sigh I'm a bit perplexed - I would have thought this would be a semi-common issue in db maintenance, but no one seems to have set this up. I'm still trying, please send any other ideas/suggestions my way... thx!! --- Dan Greene wrote: you may be able to put both statements to a text file, let's call it deleteold.sql then your cron job would be : mysql (put your connect stuff here) deleteold.sql From: Scott H Well, it sort of helps. But that section is about future enhancements intended for mysql. I need to set something up now, with the current stable version. One thing I read (can't find it now) indicated that the current version (I'm actually running 4.0.15a) has limited support for subqueries - but I don't know exactly how far that goes. So let me set the stage a bit more - I'll assume for now there is no reasonably simple way to work with the actual size of the database on disk, and instead will go with the idea that I can expect the size of any one record to be of some average. So, according to Dan's suggestion, if I do a little math, and control the number of records, I can control the size of the db -- approximately. That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? Other ideas? thanks, scott --- Michael McTernan wrote: From the manual: 1.8.4.1 Subqueries Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1. Hope that helps, Mike From: Scott H OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the (@aa:=id) thing? I don't follow that. thanks. --- Dan Greene [EMAIL PROTECTED] wrote: What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number
Re: maintaining size of a db
Repost - Haven't gotten any response on this and can't find an answer. If no one on the mysql list knows, where does a fellow turn? Help! Can't seem to find this one in the manual or archives - how do I control a db to maintain its size to an arbitrary value, say 20 GB? I want to just rotate records, deleting those that are oldest. Thanks!! . = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
