Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 5

Displaying posts with tag: purge (reset)

About the new warnings generated during 'PURGE BINARY LOGS' execution
Employee +0 Vote Up -0Vote Down

In this blog, I will discuss about warnings that users might face if they are trying to purge active/in_use binary log which are introduced in MySQL-5.6.12/MySQL-5.7.2 releases.  Before jumping directly to that, I will be covering few basic things about PURGE BINARY LOGS command, about safe way to purge the binary logs on a replication forum. If you are good at basics on "PURGE BINARY LOGS" , you can choose to directly move to the section (at the end of the blog) where I was talking about these new warnings.

A binary log file contains “events” that describe …

  [Read more...]
Circular Partitioning scheme for a fully Automated Archival / Purge process with Events
+0 Vote Up -0Vote Down

Partitioning The advantage of partitioning is that we can drop / truncate the partitions which is real quick and done in seconds. It does lock the table (metadata lock) during this operation but only for few seconds. That duration depends on the number of pages in use in the buffer pool. With MySQL 5.6 it is possible to exchange the partitions , which helps in moving archived data to another table with similar structure.
Fully Automated archival / purge 

Consider a fast growing table which is partitioned to help the queries and help quick archival/purge. The queries if specify the …

  [Read more...]
MySQL 5.6: Multi threaded purge
Employee +9 Vote Up -0Vote Down


What does purge exactly do and why is it needed? If you have ever wondered then read on. It is really a type of garbage collector. When a user issues a DML like “DELETE FROM t WHERE c = 1;”, InnoDB doesn’t remove the matching record. This is what happens under the hood:

  1. It marks the record as deleted by setting a bit in the control bits of the record.
  2. Stores the before image of the modified columns to the UNDO log
  3. Updates the system columns DB_TRX_ID and DB_ROLL_PTR in the clustered index record. DB_TRX_ID identifies the transaction …
  [Read more...]
Purging binary logs.
+2 Vote Up -0Vote Down

Being a MySQL DBA , one faces a common issue in replication environment -> Disk space issue on master, since the number of binary logs have increased.Now, one of the solution to this would be using expire_logs_days parameter in your mysql config file. But what if, the slave is lagging by few hours or if the slave is broken since few days and the binary logs are removed due to the parameter set. Whenever the salve comes up, it will go bonkers, knowing that the binary log where it last stopped no more exists.
I faced this issue a couple of times until I decided to automate it using a script. Herewith I am attaching the URL to my …

  [Read more...]
Using the event scheduler to purge the process list
+0 Vote Up -0Vote Down

Two of the most common tasks for database administrators are cleaning the process list from unresponsive queries and remove idle connections that are filling the connection pool.
Both tasks are related to poor usage of the database. In a perfect world, users would only run queries designed, tested, and benchmarked by the DBA or the project manager, and the application servers would never allocate more connections than planned.
  [Read more...]
Showing entries 1 to 5

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.