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  2513 
>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 C++. I find my self sometimes, trying
Object app = Object();
Object *app = Object();
Object app = new Object();
|
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 event or not.
With 30 events max times 3 thats 90 fields. Although within limits even
I know this is not the way to do it. I want to split the table into 2. 1
for the personal info and the other for the events.
Not sure what the best way to link...
|
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 = 111;
cout<< sizeof(g)<<" "<<sizeof(double)<<" "<<sizeof(*g)<<" "<<*g<<" "<<endl;
|
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 linked to the external mdb & (tdf.delete to
remove 'em when I'm done). I then use standard CurrentDB, db.OpenRecordset
(strSQL), stuff to get at the treasure therein (being: 2 tables (1 - M) of
c.1250 records comprising c.10 fields) . I'll need to...
| | |
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 accesses data in an SQL Server database. I have developed a
couple of pages that display data successfully. However, there is one area
that I am having trouble getting a handle on, despite purchasing a couple of
Wrox books.
Up until now, I have...
|
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 system: windows xp
compiler : Dev c++
Question is as folllows :
Genaration of keys :
Assume that your keys are character strings of length 10 obtained by scannig a c++ program. If a string has less than 10 characters make it up to 10 characters by...
|
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 come the application knows that it has to delete memory of
pBuf for 10 * sizeof(int) from the statement "delete pBuf "?
|
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:
print line
f.close()
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
