jonathanvx

pt-query-digest mysql-slowquery.log --limit=25 > slow.txt 
pt-query-digest mysql-slowquery.log --limit=8 --order-by=Lock_time:sum > locked.txt 2>/dev/null 
pt-query-digest mysql-slowquery.log --filter '($event->{Rows_examined} > 0) && ($event->{Row_ratio} = $event->{Rows_sent} / ($event->{Rows_examined})) && 1' --limit=8 > select_ratio.txt 

pt-query-digest mysql-slowquery.log --filter '($event->{fingerprint} =~ m/^(!?select)/) && ($event->{Rows_examined} > 0) && ($event->{fingerprint} !~ m/(information_schema|performance_schema|mysql)/)' --no-report --output=slowlog >select.log

pt-query-digest mysql-slowquery.log --filter '$event->{fingerprint} =~ m/table/' --no-report --output=slowlog >table.log
pt-query-digest table.log --limit=100% > table.txt 

jonathanvx

import timeout_filter as tf

def test_timeout_filter():
#TODO: create fake data with expected results from timeout_filter
  test_data_users = "{  }"
  test_data_venues= "{  }"
 
  print tf.filter_venues(test_data_users, test_data_venues)
#TODO: compare filter results with expected results

jonathanvx

#!/bin/bash

cmd='aws rds modify-db-parameter-group --db-parameter-group-name mysql57-performance'
$cmd --parameters="ParameterName=tmp_table_size, ParameterValue=67108864, ApplyMethod=pending-reboot" 
$cmd --parameters="ParameterName=max_heap_table_size, ParameterValue=67108864, ApplyMethod=pending-reboot" 
#Max size a tmp table can be in memory
$cmd --parameters="ParameterName=join_buffer_size, ParameterValue=2097152, ApplyMethod=pending-reboot" 
#Helps when a join doesn’t have an index or an index wont be more helpful than a full table scan
$cmd --parameters="ParameterName=sort_buffer_size, ParameterValue=2097152, ApplyMethod=pending-reboot"
#Helps with ORDER BY queries.

jonathanvx

-- based on http://stackoverflow.com/questions/21767780/how-to-find-size-of-database-schema-table-in-redshift
SELECT name AS table_name, ROUND((COUNT(*) / 1024.0),2) as "Size in Gigabytes"
FROM stv_blocklist
INNER JOIN
(SELECT DISTINCT id, name FROM stv_tbl_perm) names
ON names.id = stv_blocklist.tbl
GROUP BY name
ORDER BY "Size in Gigabytes" DESC

jonathanvx

#!/bin/bash
(cat <<EOF
[mysqld]

## Performance Specific ##
##########################

innodb_buffer_pool_size 	= 12G		###	How much innodb data to store in memory. Higher = faster performance
innodb_file_per_table		= 1		###	Each innodb table is its own file on disk. 
innodb_flush_log_at_trx_commit 	= 2		### Flushes to disk in batches instead of per change

jonathanvx

[mysqld]

## Performance Specific ##
##########################

innodb_buffer_pool_size 	= 12G		###	How much innodb data to store in memory. Higher = faster performance
innodb_file_per_table		= 1			###	Each innodb table is its own file on disk. 
innodb_flush_log_at_trx_commit 	= 2			### Flushes to disk in batches instead of per change
innodb_io_capacity		= 800		###	Min IO speed expected to write to disk. 
read_buffer_size 		= 2M		###	Helps with reading temp tables faster, bulk inserts and nested queries

jonathanvx

/*
'year' and 'month' columns are in seperate integer columns.
This creates an issue when wanting reports between two different dates.
How can we fix this? One solution, a generated column with an index on it. 
*/
MBP@landregistry> select * from summary order by total_price desc limit 3;
+----------+------+-------+------------+----------------+--------------+-------------+
| postcode | year | month | county     | district       | total_price  | houses_sold |
+----------+------+-------+------------+----------------+--------------+-------------+
| W1T      | 2015 |     7 | CAMDEN     | GREATER LONDON | 367075000.00 |           7 |

jonathanvx

delimiter $$
drop procedure if exists county_summary$$
create procedure county_summary(query JSON)
BEGIN
DECLARE i_county varchar(255) default null;
DECLARE i_year int default null;
DECLARE i_month int default null;

set i_county = JSON_UNQUOTE(JSON_EXTRACT(query,'$.county'));
set i_year = JSON_EXTRACT(query,'$.year');

jonathanvx

127.0.0.1      100PercentFedUp.com
127.0.0.1      21stcenturywire.com
127.0.0.1      369news.net
127.0.0.1      70news.wordpress.com
127.0.0.1      asheepnomore.net
127.0.0.1      abcnews.com.co
127.0.0.1      www.aim.org
127.0.0.1      latitudes.org
127.0.0.1      www.activistpost.com
127.0.0.1      addictinginfo.org

jonathanvx

echo $sql | mysql 2>&1 | awk '{print $0 ">", sql}' sql="$sql" >> $errorlog