carillonator's questions

I am experiencing an issue with slow connections/logins to a loaded mysql server, even when connecting via the unix socket file (CentOS 6.3). Queries are completing very quickly -- (0.00 sec) in this case -- but the login takes up to several seconds, in this case:

real    0m3.128s
user    0m0.010s
sys     0m0.011s

mysqld is running with skip-name-resolve and a thread_cache_size such that no new threads are being created during this issue. It is a load-based problem, i.e. logins are very fast when the server is idle. The server is handling about 60 connections and 300 queries per second on powerful hardware, so it is well underutilized.

Here is the section of an strace where it stalls, noted with the empty lines. I am assuming the stall on read( means it is waiting for a response from the server:

$ time strace mysql -e 'select 1'
[...]
read(3, "# /etc/services:\n# $Id: services"..., 4096) = 4096
read(3, "ervice\nfinger          79/tcp\nfi"..., 4096) = 4096
read(3, "     209/udp                    "..., 4096) = 4096
read(3, "a-cluster      694/tcp          "..., 4096) = 4096
read(3, "   1494/tcp                     "..., 4096) = 4096
read(3, "603/udp                        #"..., 4096) = 4096
close(3)                                = 0
munmap(0x7f10609da000, 4096)            = 0
rt_sigaction(SIGPIPE, {SIG_IGN, [PIPE], SA_RESTORER|SA_RESTART, 0x3318c32920}, {SIG_DFL, [], 0}, 8) = 0
socket(PF_FILE, SOCK_STREAM, 0)         = 3
fcntl(3, F_SETFL, O_RDONLY)             = 0
fcntl(3, F_GETFL)                       = 0x2 (flags O_RDWR)
connect(3, {sa_family=AF_FILE, path="/var/run/mysqld/mysqld.sock"}, 110) = 0
setsockopt(3, SOL_SOCKET, SO_RCVTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
setsockopt(3, SOL_SOCKET, SO_SNDTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
setsockopt(3, SOL_IP, IP_TOS, [8], 4)   = -1 EOPNOTSUPP (Operation not supported)
setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
read(3,



"e\0\0\0\n5.5.29-ndb-7.2.10-cluster-g"..., 16384) = 105
open("/usr/lib/locale/locale-archive", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0644, st_size=99158576, ...}) = 0
mmap(NULL, 99158576, PROT_READ, MAP_PRIVATE, 4, 0) = 0x7f105216f000
close(4)                                = 0
stat("/usr/share/mysql/charsets/Index.xml", {st_mode=S_IFREG|0755, st_size=18312, ...}) = 0
brk(0x222b000)                          = 0x222b000
open("/usr/share/mysql/charsets/Index.xml", O_RDONLY) = 4
read(4, "<?xml version='1.0' encoding=\"ut"..., 18312) = 18312
close(4)                                = 0
futex(0x986300, FUTEX_WAKE_PRIVATE, 2147483647) = 0
write(3, "P\0\0\1\205\242\17\0\0\0\0\1!\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 84) = 84

For what it's worth, this mysqld is an API node in front of NDB (MySQL Cluster), but I have a feeling it has nothing to with NDB, since queries against NDB tables are also returning very quickly.

UPDATE

configuring our php application to use persistent connections (p:<hostname> with mysqli) solved the problem completely, but I am still surprised at what a low connection rate this became an issue, compared to normal mysqld and Percona XtraDB Cluster.

to answer @HTTP500's question, we don't see checking permissions state, but do see a few of these at any given time:

395354 | unauthenticated user | connecting host    | NULL    | Connect | NULL | login                             | NULL             |

Upgrading MySQL 5.1->5.5, all InnoDB, 500GB data, linux.

To rebuild the tables/indexes for 5.5, I can either dump and load everything, or run ALTER TABLE t1 ENGINE=innodb on every table (see MySQL docs).

MySQL recommends the dump and load method here (second paragraph) as does Percona here.

I completely understand the necessity of a good backup, but I will have a clean and reliable storage snapshot serving that purpose. Also, dump/load takes much much longer and requires extra disk space and I/Os.

Is there any other good reason to prefer the dump/load method?

(same question as this one, but dealing with a much larger dataset and existing backup: MySQL Upgrade Method)

I'm looking for recommendations for ext3 filesystem block size for MySQL 5.6 using InnoDB.

Running a CentOS 5.4 VM in VMware ESXi 5, VMFS 5 datastore on NetApp FibreChannel LUNs (which have 4k chunk size). Using O_DIRECT, innodb_flush_log_at_trx_commit = 2, 14G buffer pool, and the db does OLTP with some occasional large queries crunching a lot of data. Some tables are several GB or more, others very small. Table and ibdata files are on one filesystem, binlogs and ib_logfiles on another, so they can have different block sizes.

I understand InnoDB uses 16k block size, which is not user-configurable, so I'm wondering if it's worth it to set the ext3 block size to match, instead of the 4k default.

thanks!

a bash commands outputs this:

Runtime Name: vmhba2:C0:T3:L14
Group State: active
Runtime Name: vmhba3:C0:T0:L14
Group State: active unoptimized
Runtime Name: vmhba2:C0:T1:L14
Group State: active unoptimized
Runtime Name: vmhba3:C0:T3:L14
Group State: active
Runtime Name: vmhba2:C0:T2:L14
Group State: active

I'd like to pipe it to something to make it look like this:

Runtime Name: vmhba2:C0:T1:L14 Group State: active 
Runtime Name: vmhba3:C0:T3:L14 Group State: active unoptimized
Runtime Name: vmhba2:C0:T2:L14 Group State: active
[...]

i.e. remove every other newline

I tried ... |tr "\nGroup" " " but it removed all newlines and ate up some other letters as well. thanks

I'm running RHEL 5.2 connected to a NetApp over FC. The server is running a database on mpathNN devices that can not be stopped. I need to add a new LUN and give it a specific alias (rather than mpathNN). I know how to set this up in multipath.conf, but I don't know the wwid of the LUN yet. I also know this can be achieved by restarting multipathd, but don't know if this is safe.

Is it safe to restart multipathd without i/o interruption?

or, is there another way I can enable the alias specified in multipath.conf once I've presented it to the host?

thanks!

I've been on hold now for an hour waiting for VMware support and am betting serverfault can beat them to the answer!

I am running ESX 4.0 and 4.1 on 6 HP blades, using FibreChannel LUN storage. We did some FC network maintenance over the weekend and took down 2 of the 4 paths the ESX hosts have to the storage array (EMC Clariion). When this happened, all 6 ESX hosts shut down all of their VMs.

I saw the messages like this in events:

Path redundancy to storage device naa.600.... degraded. Path vmhba0:.... down. 2 remaining active paths Affected datastores: ....

this was expected. then 3 minutes later:

Guest OS shutdown for vm1 (this was by the vpxuser)

vm1 is powered off (user "User")

why would it do this if there were still good paths? I don't see any setting like this anywhere. thanks!

if I remove the physical disk associated with /dev/sdb and add a new disk in the same slot, it will be assigned /dev/sdc (or whatever the next available letter is). Is there any way powered on that I can reassign that new disk to /dev/sdb?

Running CentOS 5.4. thanks!

Because of underlying storage, I need the partitions created by the RHEL/CentOS installer to begin on a multiple of 8 sectors. By default the part command during kickstart (see this page for kickstart reference) offsets them by 63 sectors:

[root@bs-simon ~]# fdisk -lu /dev/sda

Disk /dev/sda: 85.8 GB, 85899345920 bytes
255 heads, 63 sectors/track, 10443 cylinders, total 167772160 sectors
Units = sectors of 1 * 512 = 512 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *          63      160649       80293+  83  Linux
/dev/sda2          160650   166738634    83288992+  83  Linux
/dev/sda3       166738635   167766794      514080   82  Linux swap / Solaris

part does offer --start to specify the starting cylinder, but can someone suggest a way to get part to start on a sector that is a multiple of 8? Or do I just need to give up and partition the disk manually first?

btw, this is to get the linux filesystem blocks to align with underlying VMware VMFS datastore blocks, which align with NetApp LUN blocks.

thanks!!

from multipath manpage:

-F     flush all unused multipath device maps

is this a safe operation? I have a bunch of old dead paths and would like to clear them out, but want to make sure it's absolutely safe to do. I've heard ql-scan-lun.sh -r, which is supposed to do the same thing, can interrupt visibility of all the LUNS. using qlogic qla2xxx hbas.

I need to loop through and ssh to multiple hosts and run a series of commands on each.

something like:

for i in $(jot -w '%02.0f' 14 1)
>do ssh user@host$i sudo -i "command1; command2; command3"
>done

but I can't get it to work correctly. I've seen various things on The Google like sudo sh -c, piping to sudo, etc, but can't figure it out.

• I'm ssh'ing as a regular user that can sudo with no password (ssh as root not enabled)
• command1 not returning 0 should not prevent command2 from running, etc, hence ;
• I'm running the loop from a mac, hence jot -w, which is roughly equivalent to seq -f in linux
• ssh'ing to CentOS 5.4
• I'd like it to run with root's $PATH so I don't have to specify the full path to the commands, but it's not totally necessary

thanks in advance!

I have many lines like the following being returned from a bash command, with different sourceNodeIds of varying digit lengths:

<NodeAssociation sourceNodeId="33654" [...] sourceNodeEntity="Issue" />

I'd like to pipe it to sed or awk and just return the number nnnn from sourceNodeId="nnnn"

something like:

cat blah | sed 's/.+?sourceNodeId="\(\d+\)".+/\1/'

but this isn't working. I'm on a Mac if that makes any difference (I think the version of sed may be different). I know Perl regexes, but I think sed is expecting a different kind.

Thanks!!!

I have a few hundred text files in one directory with lists of users:

&usera
&userb
userc
&userd

note that some do not lead with the &. Also, in some files, the last line does not end with a newline character.

What is the simplest way in a shell script to go through all those files and remove, say userb, ensuring that each existing user remains on its own line?

thanks!