Nowadays data volumes are growing very quickly. Today Hundreds of gigabytes database is not even being considered as a huge one. Naturally cloud services like Amazon Relational Database Service (RDS) or Google Cloud SQL services can be successfully used for such cases. This is very handy, you don’t even need to spend your time tuning server side settings, but when with volumes growth the performance usually start to degrade. At this point one needs to start optimizing databases and techniques of working with it.<\/p>\n
We’ve been using Google Cloud SQL for MySQL quite a lot recently and indeed it’s a powerful MySQL solution. One of the most common ways to start analyzing is a slow queries log. The thing is Google uses JSON with timestamps to store them. And JSON is convenient for robots, not for humans. This is why we wrote a simple Python3 script to parse JSON files.<\/p>\n
You just need to grab the logs to some Linux box:<\/p>\n
And concatenate all files into on big log:<\/p>\n To get classic and familiar format run:<\/p>\n And run<\/p>\n Nowadays data volumes are growing very quickly. Today Hundreds of gigabytes database is not even being considered as a huge one. Naturally cloud services like Amazon Relational Database Service (RDS) or Google Cloud SQL services can be successfully used for such cases. This is very handy, you don’t even need to spend your time tuning… <\/p>\ngsutil cp gs:\/\/slow_log\/cloudsql.googleapis.com\/mysql-slow.log\/2019\/02\/11\/* .<\/code><\/p>\ncat *.json >> json_log<\/code><\/p>\n#!\/usr\/bin\/env python3\nimport json\n\nsql = \"\"\ntimestamp = \"\"\npretimestamp = \"\"\nwith open(\"json_log\") as infile:\n for line in infile:\n if line.strip()[0] != \"{\":\n continue\n k = json.loads(line)\n sql = k['textPayload']\n print(sql)\n pretimestamp = k['timestamp'].strip()<\/pre>\npython js_sl.py >> slowlog<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"