This checklist will help you make the most of your time with Percona by ensuring access to your servers is correctly set up before we begin working with you. Please take a moment to review it.
We look forward to working with you. Please make sure you've provided us with the items listed so we'll be able to log in to your servers. If there's more than one server, please tell us the servers' functions (e.g. “master” or “read-only slave”) and all other information that applies for each server. This will help ensure we are working on the intended servers. Set up a motd (message of the day) indicating the server's function and be sure to tell us if there are critical things we should be careful not to disturb.
The Short Version
If you're a competent sysadmin and you are comfortable with granting us lots of privileges, the following may be all you need to know:
- Create a user named percona, install our SSH key in percona's account and grant sudo with no password.
- Create a MySQL user named percona, grant all privileges on *.*, and give us the password or leave it in a file in percona's home directory.
The Long Version
If the process above is not clear, here is more detail on the items you should check and send us details on. Including all the necessary details for access to your system will reduce your billable time.
- Hostname or IP address of the server
- Hostname or IP address of the server
- Password, unless you're using our SSH key (see below)
- Port, if you're using a non-standard port (port 22 is standard for SSH).
- If you're using a firewall, open the SSH port to our IP addresses:
- bm01.jh.percona.com (22.214.171.124)
- bm02.jh.percona.com (126.96.36.199)
- he01.jh.percona.com (188.8.131.52)
- he02.jh.percona.com (184.108.40.206)
- sl1.jh.percona.com (220.127.116.11)
If you're using VPN, do not restrict the port to a specific IP address.
We need to examine the MySQL data, logs, operating system information (such as RAID controller status), and other system configuration. The easiest thing to do is grant us sudo access, because we may need to run a wide variety of commands as root to do our work. At an absolute minimum, we need to be able to read the MySQL slow log and error log.
We also need a MySQL account on your database server. Please check any of the following that apply:
- What is the username and password of the MySQL account we should use?
- Have you granted SELECT, SUPER, PROCESS, RELOAD ON *.* to the user?
- What is the IP address or hostname of the MySQL server?
- On what IP address does MySQL listen, if bind_address is set in its configuration?
- What is the port or socket, if they are non-standard?
- If possible, create a 'percona' database and give us all privileges to it (to create temporary tables while optimizing queries.) This is completely optional but it's helpful to us. You can do it by running CREATE DATABASE percona; GRANT ALL ON percona.* TO 'user'@'host';
For security and accountability reasons, please do not do any of the following:
- Don't permit Percona to log into a shared account. Create a dedicated Percona user.
- Don't install our SSH key in root's home directory.
- Don't permit Percona to use a common MySQL account such as root. Create a dedicated Percona user in MySQL.
- Don't permit our SSH key to be forwarded through an agent to another host. If we must access your systems through an intermediary "bastion" server, please create an SSH key on that server, and install matching the public key on the systems we should be able to access.
Other Types of Access
We can work with nearly any type of access, or even none at all. However, direct SSH access to the server is most efficient, and any more elaborate types of access, such as proprietary VPN programs, platform-dependent technologies such as GoToMyPC, etc., may be less efficient, sometimes significantly less efficient. Many of these programs cannot coexist, so they require elaborate tricks with virtual machines, etc. Multiple hops are also less efficient; keystroke latency is a problem. And the use of a graphical desktop on a remote system is also difficult. This will directly affect the cost to you and the time it takes us to complete our work. In general, Cisco VPN and OpenVPN are the most efficient VPNs to use.
If you're using VPN, please tell us all the necessary information:
- What type of VPN system are we connecting to? Do we need to download a client?
- Please send us the .PCF file if you're using a Cisco VPN
- Otherwise, please tell us all of the following that apply: the domain, server's hostname and IP address, username, password, group name, group password
- VPN may not work well with DNS, so please tell us IP addresses for servers
If you're using any other type of connectivity, such as VNC, Remote Desktop, GoToMyPC, or similar, please provide the details.
To install our SSH keys
Use the instructions on our encryption key page to install our SSH keys. The best way to do this is to copy and paste the commands off the page. Please be sure you perform this action while logged in as the user you want to grant access to. The most common reason we can't log in initially is because the key is installed for a different user than you told us to log in as; the next most common reason is because there are line breaks, incorrect permissions, or other problems with the key file. After you've set the key up, please let us know so we can test it.
Note that VPN and our shared SSH key do not play well together. For security reasons, consultants do not have the passphrase to our shared SSH key, so they are unable to load it on their own computers. And we cannot use VPN on our shared login host, which we use for SSH-keyed access. If you are using VPN, consultants will log in from their own machines, so do not restrict access to any specific range of IP addresses, and be sure to provide password authentication or an SSH key of your choosing.
Performance Audit Checklist
A performance audit is our most popular service. In order to expedite the performance audit, please ensure that the following tools are installed on each machine. If you can't install any one for some reason, it's OK, but the following are very helpful for observing what the machine is doing.
- Perl DBI and DBD::mysql
- vmstat, iostat, and optionally innotop, mpstat, and dstat
- If there is no outside network access, Percona Toolkit
Part of our standard procedure is to review the MySQL slow query logs. Having them enabled before we begin can save time. If your slow query log is not enabled, you can enable it by adding the following lines to my.cnf. This will require a MySQL restart to take effect in standard MySQL 5.0 and earlier. Please check the MySQL error log and ensure that it contains no errors after the restart; if the slow query log file can't be created for some reason, the error log will alert you to this fact.
log-slow-queries = /path/to/file/slow.log log-queries-not-using-indexes long_query_time = 1000
A short architectural overview can go a long way towards giving us a “head start” on the performance audit. Please provide brief documentation that includes:
- Language(s) used
- Deployment Diagrams (if any)
- Components used (load balancers, caching systems, etc...)
- Database Replication / Sharding Information
- Planned Upgrades / Changes – Alerting us in advance of any new features or changes planned during the time of the audit can save significant time and rework
- Anything else you feel is worth mentioning.
You don't have to go to extremes with any of this. Too much information can cause extra work, too (lists of tables and columns are not necessary). Keep in mind that we have probably seen dozens of systems like yours, so just giving us “hints” about what's unique or noteworthy about your systems can be very effective. For example, “it's a social networking application using Ruby On Rails and we are not sharded yet, but we do a lot of caching” tells us a great deal.
We highly recommend that all of our customers have alerting and monitoring systems in place. Although there are many of these packages available, we have found the combination of Nagios and Cacti to be very effective. Nagios MySQL plugins are available from www.nagiosexchange.com
and MySQL Cacti templates can be downloaded from here
If you need help installing or configuring them, please let us know. We are glad to assist.