Hi All-
I have searched around a bit and haven't found anything that addresses
my exact question...
I have a db that is moderately busy (a couple million records added
over a day, 60-300 db hits per second, average about 120 or so).
The database is in constant use and the server can't be stopped under
normal circumstances.
I need to be able to remove data from the db at periodic intervals,
generally at least once a day, depending on the sizes of the tables.
I've been doing this via scripts that first do a mysql dump for a
date/time interval, then a delete for the same date/time interval (if
the record numbers are above a certain threshold). I also do periodic
checks and optimizes.
At times this seems to have an adverse affect on the mysqlserver
performance. I then split out the tables to be backedup and deleted
individually, which kind of works.
My main question is what is the best way to do this (i.e. backup and
then remove data from an operating db) with the least impact on
operation? I haven't seen any option for mysqldump to remove data,
which of course doesn't mean that I haven't missed something like that.
Any help would be appreciated...
Thanks
Greg
-
2  2497 
>I have a db that is moderately busy (a couple million records added over a day, 60-300 db hits per second, average about 120 or so).
The database is in constant use and the server can't be stopped under
normal circumstances.
I need to be able to remove data from the db at periodic intervals,
generally at least once a day, depending on the sizes of the tables.
I've been doing this via scripts that first do a mysql dump for a
date/time interval, then a delete for the same date/time interval (if
the record numbers are above a certain threshold). I also do periodic
checks and optimizes.
mysqldumping a table may acquire a read lock during that dump, which
can block other queries to the same table. Some of this can be
affected by the options given to mysqldump.
At times this seems to have an adverse affect on the mysqlserver
performance. I then split out the tables to be backedup and deleted
individually, which kind of works.
My main question is what is the best way to do this (i.e. backup and
then remove data from an operating db) with the least impact on
operation? I haven't seen any option for mysqldump to remove data,
which of course doesn't mean that I haven't missed something like that.
One possibility is to use replication. Do the mysqldump on your
SLAVE server, which won't affect queries on the master. You can
also do a STOP SLAVE on the slave to freeze changes while you take
a whole dump of the database, then start it up again. Unfortunately,
you still need to do your delete on the master (which will propagate
to the slave).
Gordon L. Burditt
Thanks for the replies, Gordon. I'll have to look more into
replication (haven't used it before); I guess I had always thought that
the replication overhead would have been too expensive, but it sounds
like it might be less impact overall, including the mysqldumps.
Thanks again
Greg
Gordon Burditt wrote: I have a db that is moderately busy (a couple million records added
over a day, 60-300 db hits per second, average about 120 or so).
The database is in constant use and the server can't be stopped under
normal circumstances.
I need to be able to remove data from the db at periodic intervals,
generally at least once a day, depending on the sizes of the tables.
I've been doing this via scripts that first do a mysql dump for a
date/time interval, then a delete for the same date/time interval (if
the record numbers are above a certain threshold). I also do periodic
checks and optimizes.
mysqldumping a table may acquire a read lock during that dump, which
can block other queries to the same table. Some of this can be
affected by the options given to mysqldump.
At times this seems to have an adverse affect on the mysqlserver
performance. I then split out the tables to be backedup and deleted
individually, which kind of works.
My main question is what is the best way to do this (i.e. backup and
then remove data from an operating db) with the least impact on
operation? I haven't seen any option for mysqldump to remove data,
which of course doesn't mean that I haven't missed something like that.
One possibility is to use replication. Do the mysqldump on your
SLAVE server, which won't affect queries on the master. You can
also do a STOP SLAVE on the slave to freeze changes while you take
a whole dump of the database, then start it up again. Unfortunately,
you still need to do your delete on the master (which will propagate
to the slave).
Gordon L. Burditt
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Raj |
last post by:
Hi all,
Can anyone help me with a script which would delete files or
move them to a different folder at some scheduled time..!
Please.....!!!
Thanks in advance...
|
by: DrUg13 |
last post by:
In java, this seems so easy. You need a new object
Object test = new Object() gives me exactly what I want.
could someone please help me understand the different ways to do the
same thing in...
|
by: Ben |
last post by:
Right now I have 1 table. The first part is the first and last name
along with address etc. There is about 10-15 fields here. The second
part consists of times, penalties and if they enter this...
|
by: jimjim |
last post by:
Hello,
This is a simple question for you all, I guess .
int main(){
double *g= new double;
*g = 9;
delete g;
cout<< sizeof(g)<<" "<<sizeof(double)<<" "<<sizeof(*g)<<" "<<*g<<" "<<endl;
*g =...
|
by: Paul Wagstaff |
last post by:
Hi All
Can I have some feedback on the best way to access data held in an mdb on a
separate server over a LAN (and WAN for that matter). I currently use DoCmd
to bring in the relevant tables...
|
by: Dave |
last post by:
(My apologies for posting this on two forums. I have just found out the other
one was the incorrect location)
I am writing a VB.NET 2003 web application to operate on my company's
intranet. It...
|
by: parasuram |
last post by:
Hi friends .............
this is a question regarding the data structures trees
Pleas post it if possible with in 2 days
I will thankful if some body could help doing this.
Operating...
|
by: AS |
last post by:
Hello,
consider the following statements,
int *pBuf = new int; //this will allocate 10*sizeof(int) memory
delete pBuf // this will delete all the memory allocated to pBuf
Question: How...
|
by: Francesco Pietra |
last post by:
Please, how to adapt the following script (to delete blank lines) to delete
lines containing a specific word, or words?
f=open("output.pdb", "r")
for line in f:
line=line.rstrip()
if line:...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
