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
--- 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
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
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]
maintaining size of a db
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!! Scott . __ 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 noob can't get msyslog to connect to mysql
I want to set up a central log server, doing this on RH9. Using msyslog, a replacement for syslog which can log to a mysql db. I originally had msyslogd logging successfully to mysql. Great, but I want to be able to view my logs via http, and after having trouble setting up apache with PHP support and the tie-in to mysql, I decided to use XAMPP (formerly LAMPP) ver 1.3, which installs all the components I need apache, PHP, mysql, PHPAdmin (and other items) as a package. (I uninstalled mysql and apache first.) Very nice but now I can't get msyslog to connect to mysql. In the logs, I have: om_mysql: sending messages to localhost, database msyslog, table syslogTB. om_mysql_write: Lost connection! [Can't connect to local MySQL server through socket '/var/lib/mysq That last line is truncated in the log. I presume it would complete with the socket file, which would be defined in my /etc/my.conf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Can anyone tell me where to start on this? Thanks!! Scott = -- 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: mysql noob can't get msyslog to connect to mysql
Re-posted (corrected a typo in config file name): I want to set up a central log server, doing this on RH9. Using msyslog, a replacement for syslog which can log to a mysql db. I originally had msyslogd logging successfully to mysql. Great, but I want to be able to view my logs via http, and after having trouble setting up apache with PHP support and the tie-in to mysql, I decided to use XAMPP (formerly LAMPP) ver 1.3, which installs all the components I need apache, PHP, mysql, PHPAdmin (and other items) as a package. (I uninstalled mysql and apache first.) Very nice but now I can't get msyslog to connect to mysql. In the logs, I have: om_mysql: sending messages to localhost, database msyslog, table syslogTB. om_mysql_write: Lost connection! [Can't connect to local MySQL server through socket '/var/lib/mysq That last line is truncated in the log. I presume it would complete with the socket file, which would be defined in my /etc/my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Can anyone tell me where to start on this? Thanks!! Scott __ 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]
