Jay Janssen jayjanssen

jayjanssen / gist:4018968

Last active October 12, 2015 11:18

haproxy config -- shared healthchecks PXC

jayjanssen / gist:4039319

Created November 8, 2012 15:02

sysctl tuning for HAproxy

	net.core.somaxconn = 32768
	net.ipv4.conf.all.send_redirects = 1
	net.ipv4.ip_nonlocal_bind = 1
	net.ipv4.tcp_abort_on_overflow = 0
	net.ipv4.tcp_fin_timeout = 10
	net.ipv4.tcp_keepalive_time = 300
	net.ipv4.tcp_max_orphans = 262144
	net.ipv4.tcp_max_syn_backlog = 16384
	net.ipv4.tcp_max_tw_buckets = 262144
	net.ipv4.tcp_mem = 200000 280000 300000

jayjanssen / gist:4118362

Created November 20, 2012 14:47

split mydumper output file into chunks

awk -v lines=1000000 -v fmt="database.table.%06d.sql" '{print>sprintf(fmt,1+int((NR-1)/lines))}' ../export-20121119-062335/database.table.sql

jayjanssen / gist:4226660

Last active October 13, 2015 16:48

All tables without PRIMARY keys

	select all_tables.*
	FROM
	(select table_schema, table_name from information_schema.statistics group by table_schema, table_name ) all_tables
	LEFT JOIN
	(select table_schema, table_name from information_schema.statistics where index_name='PRIMARY' group by table_schema, table_name ) primary_tables
	USING ( table_schema, table_name)
	WHERE primary_tables.table_name is null;

jayjanssen / gist:4276856

Created December 13, 2012 14:51

Size of all databases

	SELECT table_schema,
	CONCAT(ROUND(sum(table_rows) / 1000000, 2), 'M') rows,
	CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
	CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
	CONCAT(ROUND(( sum(data_length) + sum(index_length) ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
	ROUND(sum(index_length) / sum(data_length), 2) idxfrac
	FROM information_schema.TABLES
	group by table_schema
	ORDER BY data_length + index_length DESC

jayjanssen / gist:4496889

Created January 9, 2013 20:59

	CREATE TABLE `ids` (
	`id` BIGINT(21) UNSIGNED NOT NULL AUTO_INCREMENT,
	`stub` CHAR(1) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY (`stub`)
	)


	CREATE TABLE `ids` (
	`id` BIGINT(21) UNSIGNED NOT NULL AUTO_INCREMENT,

jayjanssen / gist:4612216

Created January 23, 2013 19:51

disable transparent huge pages

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled

jayjanssen / gist:4613620

Created January 23, 2013 21:24

modify limits on a running process

	echo -n "Max processes=X:Y" > /proc/`pidof mysqld`/limits

	X = soft limit
	Y = hard limit

jayjanssen / gist:4674892

Created January 30, 2013 17:27

Running perf

	install perf
	install galera-debuginfo (if PXC)
	install PS/PXC debuginfo

	# collect 1 pid
	perf record -g -p 16004

	# collect all procs
	perf record -a -g

jayjanssen / gist:4944530

Last active December 13, 2015 16:58

PXC haproxy config example