MySQL 5.6 vs 5.7 performance comparison

.gitignore

env/
*.swp

clusters.csv

get_stats.py

import csv
import logging

from collections import OrderedDict, defaultdict

from wikia.common.kibana import Kibana

PERIOD=24*3600  # get query for the last 24 hours
STATS_FIELD='50.0'  # report 50th percentile

# set up logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s %(name)-35s %(levelname)-8s %(message)s',
    datefmt="%Y-%m-%d %H:%M:%S"
)


def get_query_perf_from_cluster(cluster):
	stats = Kibana(period=PERIOD, index_prefix='logstash-mediawiki').get_aggregations(
		query='appname: "mediawiki" AND @fields.datacenter: "sjc" AND @fields.environment: "prod" ' + \
			'AND @message: "SQL" AND NOT @message: "action=delete" AND @context.cluster: "{}"'.format(cluster),
		group_by='@context.method.keyword',
		stats_field='@context.elapsed'
	)

	# print(stats)
	return stats


def main():
	logger = logging.getLogger('mysql_perf')
	clusters = map(lambda i: 'c' + str(i), xrange(1,8))

	logger.info("Checking clusters: {}".format(clusters))

	# collect per-cluster stats
	cluster_stats = OrderedDict()

	for cluster in clusters:
		cluster_stats[cluster] = get_query_perf_from_cluster(cluster)

	# now compose per-method stats grouped by cluster
	per_method_stats = defaultdict(dict)
	methods = cluster_stats[clusters[0]].keys()

	for method in methods:
		for cluster in clusters:
			per_method_stats[method][cluster] = cluster_stats[cluster].get(method)

	logger.info("Generating CSV...")

	# now write CSV
	with open('perf.csv', 'w') as csvfile:
		writer = csv.writer(csvfile, delimiter=',')
		writer.writerow(['Method'] + clusters)

		for method in sorted(per_method_stats.keys()):
			row = [method]

			# per-cluster stats for a given method
			stats = per_method_stats[method]
			for cluster in clusters:
				if stats[cluster] is not None:
					row.append('{:.4f}'.format(
						1000.0 * stats[cluster].get(STATS_FIELD)  # [ms]
					))
				else:
					row.append('-')

			writer.writerow(row)

	logger.info("Done")

	# import json; print(json.dumps(per_method_stats, indent=True))


if __name__ == "__main__":
	main()

per_cluster_stats.py

import csv
import logging

from wikia.common.kibana import Kibana

PERIOD=24*3600  # get query for the last 24 hours

# set up logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s %(name)-35s %(levelname)-8s %(message)s',
    datefmt="%Y-%m-%d %H:%M:%S"
)


def main():
	stats = Kibana(period=PERIOD, index_prefix='logstash-mediawiki').get_aggregations(
		query='appname: "mediawiki" AND @fields.datacenter: "sjc" AND @fields.environment: "prod" ' + \
			'AND @message: "SQL" AND NOT @message: "action=delete" AND @context.cluster: *',
		group_by='@context.cluster.keyword',
		stats_field='@context.elapsed'
	)

	clusters = sorted(stats.keys())

	with open('clusters.csv', 'w') as csvfile:
		writer = csv.writer(csvfile, delimiter=',')
		writer.writerow([''] + clusters)

		writer.writerow(['Median query time'] +  ['{:.4f}'.format(1000.0 * stats[cluster].get('50.0')) for cluster in clusters])
		writer.writerow(['Queries count'] +  [stats[cluster].get('count') for cluster in clusters])
		writer.writerow(['QPS'] +  ['{:.2f}'.format(100.0 * stats[cluster].get('count') / PERIOD) for cluster in clusters])  # x100 - we log SQL queries with 1% sampling


if __name__ == "__main__":
	main()

perf.csv

requirements.txt

wikia-common-kibana==2.2.1