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

Displaying posts with tag: maintenance (reset)

By all means, learn from my mistakes as a DBA!
+1 Vote Up -0Vote Down
Here are 3 recent ' oops... wish I hadn't done that :/ ' mistakes I've made since joining moz that you might as well avoid (I'm sure there will be more, but they better not be the same) Reviewing config files for MySQL, but not all of the defaults      We recently migrated a few MySQL databases to a new datacenter, and took advantage of the migration to upgrade the MySQL version(s) at the
MySQL binlogs - Don't forget to do your homework!
+0 Vote Up -1Vote Down
Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade... We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind
Installing and administering Tungsten Replicator - Part 2 : advanced
+2 Vote Up -0Vote Down

Switching roles

To get a taste of the power of Tungsten Replicator, we will show how to switch roles. This is a controlled operation (as opposed to fail-over), where we can decide when to switch and which nodes are involved.

In our topology, host1 is the master, and we have three slaves. We can either ask for a switch and let the script select the first available slave, or tell the script which slave should be promoted. The script will show us the steps needed to perform the operation.

IMPORTANT! Please note that this operation is not risk free. Tungsten replicator is a simple replication system, not a complete management tool like Continuent Tungsten. WIth the replicator, you must make sure that the applications have stopped writing to the master before starting the switch, and then you

  [Read more...]
innodb_file_per_table, shrinking table spaces and the data dictionary
+1 Vote Up -0Vote Down
INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.

If you are running innodb_file_per_table, you will notice in your database directory

  • db.opt - database characteristics file.

  • tablename.frm - the table structure.

  • tablename.ibd - the actual innodb table space file



Imagine that you have a table with 10 million rows and you delete say 5 million rows in multiple chunks around 400K chunks, because deletes are slow. Next, you notice that the table space file did not shrink. So what do you do? OPTIMIZE tablename, tada all the wasted space is reclaimed, but here is the PROBLEM the ibdata file grew!

ibdata stores all of the UNDO LOGS thus GROWS










  [Read more...]
MySQL schema maintenance
+3 Vote Up -0Vote Down



At CodeBits I had my first session about MySQL schema maintenance. I covered the basic command line possibilities before coming to the recommended tool, MySQL Workbench.
The slides are available at slideshare.


Interesting questions: ([updated] with answers from the development team
  • [Q] Are there plans to administer MySQL Cluster with Workbench?
    [A] Not that we know of.

  • [Q] Can Workbench deal with user permission maintenance across servers? (especially in cases where development and production users










  [Read more...]
Variable's Day Out #16: innodb_log_file_size
+0 Vote Up -0Vote Down
Properties: 
Applicable To InnoDB Server Startup Option --innodb_log_file_size=<value> Scope Global Dynamic Yes Possible Values Integer: Range: 1M - 4G
<1M will be adjusted to 1M Default Value 5M Category Performance, Maintenance
Description:

This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than 4GB.

InnoDB requires these logs for recovery in case of a crash. So how come the size of these logs effect server performance? As stated in MySQL manual "The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.", these logs help InnoDB in running more






  [Read more...]
Variable's Day Out #11: large_pages
+0 Vote Up -0Vote Down

Properties:

Applicable To MySQL/Linux Server Startup Option --large-pages Scope Global Dynamic No way Possible Values True|False flag Default Value False Category Performance, Administration

Description:

This option, currently available only for Linux, if set enables the usage of large pages by MySQL. Many operating systems/system architectures support optional memory pages bigger than the default size (4 KB). Usually the large page size is 2 MB, this can be checked in the large_page_size variable in MySQL.

For applications that do a lot of memory accesses, as MySQL can, reduced TLB (Translation Lookaside Buffer) misses lead to better

  [Read more...]
Memcached, but do you need it?
+0 Vote Up -0Vote Down

With all due respect to the technology and it's advocates (myself included), after a surge in articles describing the merits of using memcached I'm just pushing a thought breakpoint for developers to think whether they actually need it or not?

Recently, I ran into cases where the developers have decided to use memcached over MySQL style architecture after reading some/many good/nice articles about it without giving a damn to their requirements. I would like to list few things as a checklist for developers to decide on their architecture. There is still no precise answer but sometimes few cases can be just ruled out :).

  • What is the total size of your data? It might be a possibility that you can keep the data in memory in each node, or MySQL can just keep the whole thing (data+indexes) in a buffer.
  • How frequently your data is updated?
  •   [Read more...]
    Variable's Day Out #10: innodb_file_per_table
    +0 Vote Up -0Vote Down

    Properties:

    Applicable To InnoDB Server Startup Option --innodb-file-per-table Scope Global Dynamic General InnoDB Engine: No
    InnoDB plug-in: Yes Possible Values Enable|Disable Flag Default Value Disabled Category Maintenance

    Description:

    This variable if enabled, makes InnoDB to create a separate .ibd file for storing indexes and data. Setting this variable makes it easy to manage disks with huge tables. Having this option enabled, makes it a lot easier to move bigger (or smaller) .ibd files to separate physical disks and/or have backups of certain tables without affecting others.

    As


      [Read more...]
    MySQL: replicate-* rules should be dynamically configurable
    +0 Vote Up -0Vote Down
    I wonder what the best way is to get a feature request more visibility (convert a feature request to an actual work item).We use replicate-do-db on all our slave servers , so after many, many, restarts of our slave servers, I checked the bug list for any feature requests surrounding this, and about 6 months ago, one was opened: replicate-* rules should be dynamically configurableSadly,
    Showing entries 1 to 10 of 12 2 Older Entries

    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.