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

Displaying posts with tag: purge (reset)

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:

  • It marks the record as deleted by setting a bit in the control bits of the record.
  • Stores the before image of the modified columns to the UNDO log
  • Updates the system columns DB_TRX_ID and DB_ROLL_PTR in the clustered index record. DB_TRX_ID identifies the transaction that made the last change, and DB_ROLL_PTR points to the new UNDO log record. This UNDO log record contains the old values of DB_TRX_ID and DB_ROLL_PTR, possibly pointing to an
  •   [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 python script which can run regularly in cron.Features :
    • Checks the slaves connected to the master (I have limit it to 3 for now.)
    • Checks the last binary log file which is being used by the

      [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.
    But users are human, and an unpredictable amount of unplanned events can happen everywhere. When I was consulting, the above cases were quite common.
    Before MySQL 5.1, the only method to clean up the process list was by hand, or using a cron job to do it from

      [Read more...]
    Showing entries 1 to 3

    Planet MySQL © 1995, 2014, 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.