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!

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!

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

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 should address the application to the new master when the …

[Read more]
innodb_file_per_table, shrinking table spaces and the data dictionary

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 due to the deletes and …

[Read more]
MySQL schema maintenance



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 can't have …
[Read more]
Variable's Day Out #16: innodb_log_file_size

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 …

[Read more]
Variable's Day Out #11: large_pages

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 …

[Read more]
Memcached, but do you need it?

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 :).

  1. 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.
  2. How frequently your data is updated? Very frequent updates may lead to low cache hit ratio for memcached …
[Read more]
Variable's Day Out #10: innodb_file_per_table

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 …

[Read more]
MySQL: replicate-* rules should be dynamically configurable

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 »