Speeding up webapps

2009/07/05 by admin in General | Leave a comment

Some quick notes on useful apps for speeding up webapps. I’ve been working on one application that was suffering some performance issues and needed immediate help. So, with a combination of the following three apps and some hours of configuration tuning, I have the page loads down from 10 seconds to 2 seconds. Not too bad. Still more tuning to go of course.

PHP Acceleration: http://eaccelerator.net
Memcached http://www.danga.com/memcached
XtraDB http://www.percona.com/docs/wiki/percona-xtradb:start

JFS and RHEL5

2009/07/01 by admin in General | 2 Comments

Quick one here; If you’re thinking to yourself “why can’t I make the MySQL data partition JFS on my RHEL5 server…” here’s a good how to: http://phaq.phunsites.net/2008/02/04/enabling-reiserfs-xfs-jfs-on-redhat-enterprise-linux/

Is this RPM 32bit or 64bit?

2009/06/11 by admin in General | 3 Comments

Simple question with a simple answer. Let’s say you’re on Redhat 4 or 5 and you need to know if an installed RPM is 32bit or 64bit…
shell> rpm -q --qf '%{NAME}-%{VERSION}(%{ARCH})\n' libaio libaio-0.3.107(x86_64)

When to tune query cache settings

2008/03/24 by admin in General, Monolith | Leave a comment

I saw some bad utilization of the query cache on one of my clusters the other day. I haven’t tuned the cache since installing the standard cnf file so let’s take the following usage graph that is output by Monolith.

Queries not cached: 40,000
Queries in cache: 250

Queries not cached: 7500
Queries in cache: 200

In the first image we see there are dramatically less queries in the cache vs. the number of queries not cached. This was pulled with the following variables:

query_cache=32M
query_cache_limit=1M

Take the second graph after changing the settings to:

query_cache=8M
query_cache_limit=128K

So after seeing the visual relation between queries in the cache and queries not cached, tuning and checking again, we have a much better utilization of the cache – less queries not cached. After watching the server more we might want to change settings again to further tune this. If I increase the query_cache size now, even more queries will stay in the cache which will allow more efficient utilization of the cache, but that’s for another time.

One thing to watch here in the trend is that the queries not cached does not continue to grow linearly in the manner that the first graph shows.

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.

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)

Certified

2008/01/28 by admin in General | 2 Comments

I passed the MySQL 5.0 DBA I and II certification tests today with very nice results. Just figured I’d post that so it’s verifiable that I’m not entirely spewing nonsense and general rabble. Today is a good day, and I will celebrate with the finest wines, and choicest of meats.

Exam Date: Monday, January 28, 2008 at 9:00 AM
Candidate: Matthew Reid
Candidate ID: MYSQL005372
Registration #: xxxxx2740
Exam Series: 005-002
Exam: Certified MySQL 5.0 DBA Part I
Validation #: xxxxx5662
Grade: pass

Exam Date: Monday, January 28, 2008 at 10:30 AM
Candidate: Matthew Reid
Candidate ID: MYSQL005372
Registration #: xxxxx7164
Exam Series: 006-002
Exam: Certified MySQL 5.0 DBA Part II
Validation #: xxxxxx5956
Grade: pass

MySQL Competency

2008/01/17 by admin in General | Leave a comment

Here’s something I wrote up a while back for basic MySQL knowledge. MySQL Competency

This is also a useful list of interview questions for potential hires. DBA Interview Questions

Got an error reading communication packets

2007/12/20 by admin in General | 1 Comment

If you look at your error log file for MySQL you’ll inevitably see the warning: “got an error reading communication packets”. The common solution is to increase the max_allowed_packet size.

I’ve seen this set as high as 1GB but typically the problem is alleviated by running it as max_allowed_packet=64M. If you set this higher – either by issuing a `set global max_allowed_packet=64M;` or by editing the cnf and restarting the mysqld process – and the error persists, I’d recommend reading the following links. Very informative.

http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
http://dev.mysql.com/doc/refman/5.0/en/making-trace-files.html