ceejayoz's questions

I have a three node Percona XtraDB Cluster (5.5) setup.

Every night, we shut down MySQL on one randomly selected node in order to take backups of the data directory.

When our traffic is reasonably busy, this causes a couple (2-4) error alerts along the lines of SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '140577' for key 'PRIMARY'. Primary key conflict, obviously, except we're using auto_increment columns as the primary key on these tables. As each node has an offset assigned by the cluster engine, this shouldn't be occurring.

My suspicion is that taking the node out of the cluster causes the other two nodes to change their auto increment offsets, in a way that's causing them to potentially conflict while the change takes place. I'm at a loss as to why this wouldn't be an atomic action as well as how I might fix it.

Has anyone encountered this? Is there a way to temporarily freeze the auto increment settings in the cluster so they don't shuffle around during the backup process or some other solution I'm not thinking of?

I have a four node Percona XtraDB cluster. Each night, one node shuts down for a backup and subsequently rejoins the cluster. This has run fine for quite some time now.

Last night, the backup node refused to rejoin the cluster. The other three nodes are humming along nicely, but any attempt to join the failed node is unsuccessful.

Luckily, we provision all of our nodes using Ansible, so we've got a repeatable recipe for blowing away a failed node and replacing it with a brand new, from scratch database node.

Unluckily, new nodes also fail. No changes to the Ansible scripts have been made recently and we've successfully launched instances since. This is all running on a two-AZ VPC in Amazon AWS, if that makes any difference.

The new server's MySQL log says:

131226 04:44:50 mysqld_safe Starting mysqld daemon with databases from /data/mysql
131226 04:44:50 mysqld_safe Skipping wsrep-recover for empty datadir: /data/mysql
131226 04:44:50 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position
131226  4:44:50 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'
131226  4:44:50 [Note] WSREP: Read nil XID from storage engines, skipping position init
131226  4:44:50 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/libgalera_smm.so'
131226  4:44:50 [Note] WSREP: wsrep_load(): Galera 3.2(r170) by Codership Oy <[email protected]> loaded successfully.
131226  4:44:50 [Note] WSREP: CRC-32C: using hardware acceleration.
131226  4:44:50 [Warning] WSREP: Could not open saved state file for reading: /data/mysql//grastate.dat
131226  4:44:50 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1
131226  4:44:50 [Note] WSREP: Passing config to GCS: base_host = 10.0.1.226; base_port = 4567; cert.log_conflicts = no; gcache.dir = /data/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 16; gcs.fc_master_slave = NO; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = NO; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.proto_max = 5
131226  4:44:50 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
131226  4:44:50 [Note] WSREP: wsrep_sst_grab()
131226  4:44:50 [Note] WSREP: Start replication
131226  4:44:50 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
131226  4:44:50 [Note] WSREP: protonet asio version 0
131226  4:44:50 [Note] WSREP: Using CRC-32C (optimized) for message checksums.
131226  4:44:50 [Note] WSREP: backend: asio
131226  4:44:50 [Note] WSREP: GMCast version 0
131226  4:44:50 [Note] WSREP: (7497477a-6de8-11e3-9a71-7af4d47d85be, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
131226  4:44:50 [Note] WSREP: (7497477a-6de8-11e3-9a71-7af4d47d85be, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
131226  4:44:50 [Note] WSREP: EVS version 0
131226  4:44:50 [Note] WSREP: PC version 0
131226  4:44:50 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer '10.0.2.180:,10.0.1.226:,10.0.1.21:,10.0.2.236:'
131226  4:44:50 [Warning] WSREP: (7497477a-6de8-11e3-9a71-7af4d47d85be, 'tcp://0.0.0.0:4567') address 'tcp://10.0.1.226:4567' points to own listening address, blacklisting
131226  4:44:53 [Warning] WSREP: no nodes coming from prim view, prim not possible
131226  4:44:53 [Note] WSREP: view(view_id(NON_PRIM,7497477a-6de8-11e3-9a71-7af4d47d85be,1) memb {
    7497477a-6de8-11e3-9a71-7af4d47d85be,0
} joined {
} left {
} partitioned {
})
131226  4:44:54 [Warning] WSREP: last inactive check more than PT1.5S ago (PT3.5049S), skipping check
131226  4:45:23 [Note] WSREP: view((empty))
131226  4:45:23 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
     at gcomm/src/pc.cpp:connect():141
131226  4:45:23 [ERROR] WSREP: gcs/src/gcs_core.c:gcs_core_open():196: Failed to open backend connection: -110 (Connection timed out)
131226  4:45:23 [ERROR] WSREP: gcs/src/gcs.c:gcs_open():1291: Failed to open channel 'my_wsrep_cluster' at 'gcomm://10.0.2.180,10.0.1.226,10.0.1.21,10.0.2.236': -110 (Connection timed out)
131226  4:45:23 [ERROR] WSREP: gcs connect failed: Connection timed out
131226  4:45:23 [ERROR] WSREP: wsrep::connect() failed: 7
131226  4:45:23 [ERROR] Aborting

131226  4:45:23 [Note] WSREP: Service disconnected.
131226  4:45:24 [Note] WSREP: Some threads may fail to exit.
131226  4:45:24 [Note] /usr/sbin/mysqld: Shutdown complete

131226 04:45:24 mysqld_safe mysqld from pid file /data/mysql/mysql.pid ended

The existing nodes in the cluster spit the following out whenever the new node attempts to join:

131226  4:45:23 [Warning] WSREP: unserialize error invalid flags 2: 71 (Protocol error)
 at gcomm/src/gcomm/datagram.hpp:unserialize():101

Has anyone encountered this? Googling reveals little and it makes me anxious that our repeatable server config is not currently repeatable.

I'd like to prevent a particular xinetd check from logging START/EXIT messages as it's a lot of logging I don't need.

Is ther a way to set log_on_success = NONE or something? I'm able to find ways of adding/removing individual items from the check, but not preventing success logging entirely.

I've installed PHP's pecl_http extension using the Amazon Linux php54-pecl-http Yum package.

• php -i shows the http.ini file loaded
• http.ini has the extension=http.so line in it
• php -m shows the HTTP module as installed
• pecl list shows pecl_http 2.0.0beta1 as installed.

However, when I attempt to use any of its functions - new HttpRequest(), http_build_url(), etc. - they come back as nonexistent. I am running everything on the CLI (this is a utility server) so no chance of disparate php.ini files between environments.

php -i's http block states:

http

HTTP Support => enabled
Extension Version => 2.0.0beta1

Used Library => Compiled => Linked
libz => 1.2.5 => 1.2.5
libcurl => 7.24.0 => 7.24.0
libevent => 1.4 or greater => 2.0.18-stable
libserf => disabled => disabled

                            Persistent Handles                            
Provider => Ident => Used => Free
http_client.curl => N/A => 0 => 0
http_client_pool.curl => N/A => 0 => 0
http_client_datashare.curl => N/A => 0 => 0

Directive => Local Value => Master Value
http.etag.mode => crc32b => crc32b
http.persistent_handle.limit => -1 => -1

Has anyone experienced this? Any ideas?

I'm trying to figure out something that's counter-intuitive for me in Graphite.

I am collecting (for now) test data (with statsd). In Graphite's dashboard, I'm seeing lower peaks for the same set of data for a longer time series.

My retention is set to:

retentions = 10s:15m,60s:1d,5m:7d,1h:4w,1d:5y

Why wouldn't the second chart showing 30 minutes not have peaks at least as high as the highest peaks in the 15 minute chart? How can I get this chart to accurately reflect these stats over time?

I have a circularly replicating MySQL cluster, and a newer cluster of Percona XtraDB instances that we're in the process of moving to. During the move, we're running some projects on the old cluster and other projects on the new cluster.

I'd like to have the old cluster replicate changes to most tables in one specific database across to the new cluster. Unfortunately, the binlog contains changes for every table in every database, and there doesn't seem to be a way to compile different sets of binlogs.

Is there a reasonable way to replicate only certain tables separately from pre-existing circular replication? The clusters are in separate datacenters with different providers, so I don't want to send the entire binlog over the wire when I only need a vanishingly small portion of it.

I'm serving webapps out of arbitrary subfolders on a domain. i.e.

example.com/app-one (with example.com/app-one/page served by app-one)
example.com/folder/app-two (with example.com/folder/app-two/page served by app-two)

I have a nginx config that works just fine for the root level, but I'm a little stumped on how to handle subfolders. I could make location /app-one blocks, of course, but we have dozens of these and we'd like to avoid having to expliticly specify each one.

Is there any way to get try_files to essentially recurse up the folder structure until it hits an index.php file?

server {
    server_name example.com

    location / {
        root /srv/web/example.com;
        index index.php index.html;
        try_files $uri $uri/ /index.php?q=$q&$args;
    }

    # pass *.php files to the PHP tier
    location ~ \.php$ {
        root /srv/web/example.com;
        include /etc/nginx/fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_pass php:9000;
    }
}

I'm using percona-clustercheck (which comes with Percona's XtraDB Cluster packages) with xinetd and I'm getting an error when trying to curl the clustercheck service.

/usr/bin/clustercheck:

#!/bin/bash 
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Author: Olaf van Zandwijk <[email protected]>
# Documentation and download: https://github.com/olafz/percona-clustercheck
#
# Based on the original script from Unai Rodriguez 
#

MYSQL_USERNAME="clustercheckuser" 
MYSQL_PASSWORD="clustercheckpassword!" 
ERR_FILE="/dev/null" 
AVAILABLE_WHEN_DONOR=0

#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=`mysql --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} -e "SHOW STATUS LIKE 'wsrep_local_state';" 2>${ERR_FILE} | awk '{if (NR!=1){print $2}}' 2>${ERR_FILE}` 

if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
then 
    # Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
    /bin/echo -en "HTTP/1.1 200 OK\r\n" 
    /bin/echo -en "Content-Type: text/plain\r\n" 
    /bin/echo -en "\r\n" 
    /bin/echo -en "Percona XtraDB Cluster Node is synced.\r\n" 
    /bin/echo -en "\r\n" 
    exit 0
else 
    # Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
    /bin/echo -en "HTTP/1.1 503 Service Unavailable\r\n" 
    /bin/echo -en "Content-Type: text/plain\r\n" 
    /bin/echo -en "\r\n" 
    /bin/echo -en "Percona XtraDB Cluster Node is not synced.\r\n" 
    /bin/echo -en "\r\n"
    exit 1 
fi

/etc/xinetd.mysqlchk:

# default: on
# description: mysqlchk
service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
  disable = no
  flags = REUSE
  socket_type = stream
  port = 9200
  wait = no
  user = nobody
  server = /usr/bin/clustercheck
  log_on_failure += USERID
  only_from = 10.0.0.0/8 127.0.0.1
  # recommended to put the IPs that need
  # to connect exclusively (security purposes)
  per_source = UNLIMITED
}

When attempting to curl the service, I get a valid response (HTTP 200, text) but a 'connection reset by peer' notice at the end:

HTTP/1.1 200 OK
Content-Type: text/plain

Percona XtraDB Cluster Node is synced.

curl: (56) Recv failure: Connection reset by peer

Unfortunately, Amazon ELB appears to see this as a failed check, not a succeeded one.

How can I get clustercheck to exit gracefully in a manner that curl doesn't see a connection failure?

I need to delete a set of MySQL tables for a client in a manner that's compliant with the Department of Defense Data Clearing and Sanitizing Standard. I've a list of acceptable tools including wipe, but I'm not clear on how I might be able to drop a MySQL table in such a manner.

Is there a way to tell MySQL to drop the table without deleting the physical files on disk, so I can securely overwrite them manually?

We've got several hundred InnoDB tables in a database, and we use phpMyAdmin to manage them. Unfortunately, phpMyAdmin does a SHOW TABLE STATUS query whenever the list of tables is shown, and this seems to dig into each InnoDB table to get an approximate row count.

This seems to lock up the entire database, which subsequently means all other queries to this (busy) database all queue up until the database hits the max users.

1. Can SHOW TABLE STATUS be sped up in a reasonable manner?
2. Can phpMyAdmin be easily modified to not do a full SHOW TABLE STATUS query, or at least not lock the entire database at once for it?

We just picked up a new development server that I've loaded ESXi on.

The room it's in is well ventilated during the day but at night the door is closed and it gets quite toasty in there. I've been asked to have the box shut down nightly, as we won't be using it anyways when we're not in the office.

Is there a way to do this?

I've got mod_rpaf installed to get the user's IP address from the X-Forwarded-For header in PHP, Apache logs, etc.

Unfortunately, it looks like I have to explicitly set which IPs that header will be respected from via the RPAFproxy_ips setting.

Given that ELBs don't have a static IP, how can I tell mod_rpaf to accept the header from any IP, or even any 10.* IP?

I have a database table that I want all valid MySQL users to have access to (it's for tracking a particular set of statistics across multiple databases). Is there a way to do this?

I know you can wildcard hosts, but you don't appear to be able to wildcard usernames. Essentially, I want to do this:

REVOKE ALL PRIVILEGES ON  `database`.`table` FROM  ''@'%';
GRANT INSERT, UPDATE ON  `database`.`table` TO  ''@'%';

My current sshd PAM config:

#%PAM-1.0
auth       include      system-auth
account    required     pam_nologin.so
account    include      system-auth
password   include      system-auth
session    optional     pam_keyinit.so force revoke
session    include      system-auth
session    required     pam_loginuid.so

I'm adding Google Authenticator but we're not ready to roll it out to all users.

I believe the following should require the Google Authenticator PAM for users in the 'gauth' group, but was hoping someone could check my work before I go locking myself out of SSH...

#%PAM-1.0
auth       include      system-auth
account    required     pam_nologin.so
account    include      system-auth
password   include      system-auth
auth [success=1 default=ignore] pam_succeed_if.so quiet user notingroup gauth
auth required pam_google_authenticator.so
session    optional     pam_keyinit.so force revoke
session    include      system-auth
session    required     pam_loginuid.so

Am I understanding correctly that "success=1" means "skip the next line if this is successful"?

We're attempting to scale our Drupal installations up and have decided on some dedicated MySQL boxes. Unfortunately, we're running into extreme slowness when we attempt to use the remote DB - page load times go from ~200 milliseconds to 5-10 seconds.

Latency between the servers is minimal - a tenth or two of a millisecond.

PING 10.37.66.175 (10.37.66.175) 56(84) bytes of data.
64 bytes from 10.37.66.175: icmp_seq=1 ttl=64 time=0.145 ms
64 bytes from 10.37.66.175: icmp_seq=2 ttl=64 time=0.157 ms
64 bytes from 10.37.66.175: icmp_seq=3 ttl=64 time=0.157 ms
64 bytes from 10.37.66.175: icmp_seq=4 ttl=64 time=0.144 ms
64 bytes from 10.37.66.175: icmp_seq=5 ttl=64 time=0.121 ms
64 bytes from 10.37.66.175: icmp_seq=6 ttl=64 time=0.122 ms
64 bytes from 10.37.66.175: icmp_seq=7 ttl=64 time=0.163 ms
64 bytes from 10.37.66.175: icmp_seq=8 ttl=64 time=0.115 ms
64 bytes from 10.37.66.175: icmp_seq=9 ttl=64 time=0.484 ms
64 bytes from 10.37.66.175: icmp_seq=10 ttl=64 time=0.156 ms

--- 10.37.66.175 ping statistics ---
10 packets transmitted, 10 received, 0% packet loss, time 8998ms
rtt min/avg/max/mdev = 0.115/0.176/0.484/0.104 ms

Drupal's devel.module timers show the database queries aren't running any slower on the remote DB - about 150 microseconds whether it's the local or the remote server. Profiling with XHProf shows PHP execution times that aren't out of whack, either.

Number of queries doesn't seem to make a difference - we seem the same 5-10 second delay whether a page has 12 queries or 250.

Any suggestions about where I should start troubleshooting here? I'm quite confused.

I'm setting up a meeting and I'd like to have the computers here (which are for accessing the meeting website only) blocked from accessing the public Internet.

I'd like to set up bind to return the same IP for ALL DNS queries - no passing upstream, no "not found". Is this possible?

I'm setting up a large Drupal (Pressflow) site and this is my current plan. Have I gone and done anything blatantly stupid? Does anyone have any experience hosting a large, multi-server Drupal installation like this?

Google's indexing bot (edit: yes, it's Google, IP resolves) seems to be adding arbitrary query strings to our home page.

xx.xxx.xx.xxx - - [30/Jun/2009:10:14:37 -0400] "GET /?key=61680 HTTP/1.1" 200 3334 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
xx.xxx.xx.xxx - - [30/Jun/2009:10:16:58 -0400] "GET /?term=byron HTTP/1.1" 200 3184 "-" "DoCoMo/2.0 N905i(c100;TB;W24H16) (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)"

Any idea what these are meant for?

Is there a way to share configuration directives across two nginx server {} blocks? I'd like to avoid duplicating the rules, as my site's HTTPS and HTTP content are served with the exact same config.

Currently, it's like this:

server {
  listen 80;
  ...
}

server {
  listen 443;

  ssl on; # etc.
  ...
}

Can I do something along the lines of:

server {
  listen 80, 443;
  ...

  if(port == 443) {
    ssl on; #etc
  }
}

I'm setting up nginx to serve a Drupal install, and I seem to find conflicting information on the nginx worker processes setting. One site says you need two, one says you need five, one says one per processor...

How should I pick the number of nginx worker processes? If it makes a difference, it's a guest VM on a VMWare cluster, and I've given it one virtual processor.