Basic requirements of production database environments

2008/02/26 by admin in General | Leave a comment

I just need to get some basics off of my chest here, it’s by no means a full list but it’s the most basic list I can think of to start with, and it’s basic because I am surprised by some of the slop I’ve seen in production environments.

1. Highly available server clusters – this is different than load balancing cluster, if confused see here.

2. Disaster recovery

-> this means daily,weekly,monthly backups as well as off site backups, and tertiary backups as well as a plan to get those backups imported and running in production as fast as possible. Backups should have consistency checking when they are created.

3. Security

-> perimeter on the network, VLAN’d databases from the web/app servers, firewall, ACLs, etc

-> system level: strong passwords on OS and database accounts (no blank passwords – that *should* be obvious but you’d be surprised what I’ve run into), file permissions, encryption of sensitive database information.

4. Monitoring: monitor everything possible. Log files, disk partitions, service ports, service details (traffic for a service, memory used, tuning parameters: query cache usage, etc), CPU/RAM usage, logged in users, and most importantly being alerted about monitored services. If you’re not getting called when something has passed a threshold, you need to pay more attention to the infrastructure.

Read More

For the love of god please use the following on high traffic servers

2008/02/20 by admin in General, Replication | 1 Comment

Let’s begin by assuming you have a server that runs MySQL and lots and lots of traffic flows through it everyday, let’s say… something like 50% of the size of the partition that the mysql binary logs are written to is on, then we will assume the binary log is turned on. Then we assume that expire_logs_days is not set.

What happens? Nagios/etc alerts that the partition is reaching a usage threshold because – low and behold the binary logs are filling up the partition. Tuning this variable is also important. It may need to be set to as low as 1 day, in which case I would say we need a bigger partition for binary logs, but setting it so low can cause replication problems if the slave(s) gets behind more than 1 day – god help us if it does – then those binary logs that the slave is reading are no longer available, and rebuilding replication will be next on the task list. (or using maatkit)

While I’m at it here are some good ones for relieving tension. By no means is this a full list but some things on the top of my head at the moment.


expire_logs_days = x
myisam_recover
skip_bdb
sql_mode = NO_AUTO_CREATE_USER
max_binlog_size = x
long_query_time = x
log_slow_queries
log_warnings
transaction-isolation=REPEATABLE-READ (or SERIALIZABLE)

Read More

Monolith MySQL DBA Console – version 1.2 Released

2008/02/15 by admin in Monolith | Leave a comment

I’m pleased to announce that after almost one year since the last release, the new version is available. There are many new features this time around. Originally it was titled Monolith MySQL Backup Manager, but because of the new features it has been changed to the new name. You can download here: http://sourceforge.net/projects/monolith-mysql

Screenshots:

Monitor1 Monitor2 Monitor3

Features:

  1. Graphing / Trending with variable date reporting
    • Query rate + Data growth
    • Connections + Uptime
    • Query cache in + Query cache hit
    • Queries not cached + Queries in cache
    • Memory allocation: max_allocated + max_configured
    • Data transfer: bytes_sent + bytes_received
    • Created temp files + Created temp tables
    • Open tables + Created temp disk tables
    • Threads cached + Threads created + Thread utilization ratio
    • Threads connected + Threads running
  2. Alerting for the following tuning settings
    • Slow Query
    • Long Query
    • Used Connections
    • Thread Status
    • Key Buffer
    • Query Cache
    • Sort Merge Operations
    • Join Merge settings
    • Temporary Tables
    • Open Files
    • Table Cache
    • Table Locks
    • Table Scans
    • InnoDB settings
    • Memory Usage
  3. Talkback backup scripts for local disk backup – scripts report back from remote servers to Monolith server with status of backup. Talkback report per server tab, and itemized for all servers in summarization page.
  4. Data+Index size reporting with data summarization page for all servers
  5. Misc server meta data tracking
  6. Change request feature that show recommended cnf file changes for performance tuning + full audit report in html

Note:

The installation process is a bit involved, and I know of one hangup about the stored procedures – you must connect the monolith user to the IP address of the server and not localhost. Feel free to email me or get me on #mysql as Lumberg if you have any questions or need help installing.

Read More

Rotating General Query & Slow Logs

2008/02/05 by admin in Backups, Scripts | 4 Comments

Sometimes you need to have the general query log on and even though it causes more disk I/O than you may want, it’s good for troubleshooting. This log can and probably will fill up your disks rather quickly. Then there’s the slow query log – setting log_slow_queries and log_queries_not_using_indexes will write out the queries that take longer than long_query_time to execute, as well as any query not using an index.

So, since MySQL does not apply the expire_logs_days value to these logs – only to the binary log (log_bin), we need another solution. There are probably a bunch of custom scripts out there that do this, but big surprise – we have one as well. This was originally written by Jim Wood until I got my hands on it and made some changes. The changes are listed in the head of the script. This little guy will rotate the logs out to another directory and gzip them. Ideally you’ll run this from cron as such:

#mysql rotate logs
01 * * * * root /usr/local/bin/mysql_rotatelogs > /dev/null 2>&1

The script can be downloaded here.

Read More

More Monolith Graphs

2008/02/04 by admin in Monolith | Leave a comment

Decided to add some more reporting graphs to the Monlith Monitor. As follows…..

1. Bytes_sent + Bytes_received {graph-data_transfer.php}
2. Open_tables + Created_tmp_disk_tables {graph-tables.php}
3. Created_tmp_files + Created_tmp_tables {graph-tmp_created.php}
4. Threads_connected + Threads_running {graph-threads_now.php}
5. Threads_cached + Threads_created {graph-threads_hist.php}

Read More