MySQL EXPLAIN Notes

mysql> explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

EXPLAIN 支持 SELECT, DELETE, INSERT, REPLACE, UPDATE
id 查询编号，有几次 SELECT 操作就有几个编号
select_type 查询类型
- SIMPLE 简单查询，查询中不包含子查询和 UNION
- PRIMARY 复杂查询最外层的 SELECT
- UNION 在 UNION 中的第二个及随后的 SELECT
- UNION RESULT 从 UNION 临时表进行的 SELECT
- SUBQUERY 子查询中的第一个 SELECT
- DERIVED 包含在 FROM 子句中的子查询
- UNCACHEABLE SUBQUERY 不能被缓存的子查询，每次都要计算，是非常耗时的操作
- UNCACHEABLE UNION UNION 中不能被缓存的查询
table: 当前查询访问的表名，如果有子查询或 UNION 会有以下几种格式
- <unionM,N>
- <derivedN>
- <subqueryN>
type 查找访问类型，说明 MySQL 使用哪个索引在该表中找到对应的记录。按最优 - 最差依次是：
1. system
2. const 表中最多有一个匹配行，速度最快
3. eq_ref primary key 或 unique key 索引的所有部分被连接使用，最多只返回一条符合条件的记录
4. ref 不使用唯一索引，而是用普通索引，可能会找到多个符合条件的记录
5. fulltext 使用全文索引的时候才会出现
6. ref_or_null 和 ref 类似，但 MySQL 会额外一个查询来看哪些行包含了 NULL
7. index_merge 使用了索引合并优化
8. unique_subquery 比 eq_ref 复杂的地方是使用了 IN 子查询
9. index_subquery 类似 unique_subquery 但在子查询里使用的是非唯一索引
10. range 指定范围的扫描
11. index 和 ALL 类似，不同的是只扫描索引
12. ALL 全表扫描
possible_keys 可能用到了哪些索引来查找
key 实际扫描使用的索引
key_len 实际使用的索引长度
ref 查找时所用到的列或常量
rows 预估要读取的行数
Extra 额外补充信息
- Distinct
- Using index 只使用了索引信息，没有访问行记录
- Using where 读取行记录后使用 where 判断检查
- Using temporary MySQL 创建了临时表来处理查询，需要索引优化
- Using filesort 读取结果后进行了排序操作，需要优化

Spark Notes

Some Spark articles are worth deep reading:

spark-notes

leave 1 core per node for Hadoop/Yarn/OS deamons
leave 1G + 1 executor for Yarn ApplicationMaster
3-5 cores per executor for good HDFS throughput

Full memory requested to yarn per executor = spark.executor.memory + spark.yarn.executor.memoryOverhead

spark.yarn.executor.memoryOverhead = Max(384MB, 7% of spark.executor.memory)

So, if we request 15GB per executor, actually we got 15GB + 7% * 15GB = ~16G

MemoryOverhead

nodes
cores per node
50GB per node


5 cores per executor: --executor-cores = 5
num cores available per node: 8-1 = 7
total available cores in cluster: 7 * 4 = 28
available executors: (total cores/num-cores-per-executor), 28/5 = 5
leave one executor for Yarn ApplicationMaster: --num-executors = 5-1 = 4
number of executors per node: 4/4 = 1
memory per executor: 50GB/1 = 50GB
cut heap overhead: 50GB - 7%*50GB = 46GB, --executor-memory=46GB

executors, 46GB and 5 cores each

3 cores per executor: --executor-cores = 3
num cores available per node: 8-1 = 7
total available cores in cluster: 7 * 4 = 28
available executors: (total cores/num-cores-per-executor), 28/3 = 9
leave one executor for Yarn ApplicationMaster: --num-executors = 9-1 = 8
number of executors per node: 8/4 = 2
memory per executor: 50GB/2 = 25GB
cut heap overhead: 25GB * (1-7%) = 23GB, --executor-memory=23GB

executors, 23GB and 3 cores each

Spark + Cassandra, All You Need to Know: Tips and Optimizations

Spark on HDFS has low cost, used in most cases
Spark with Cassandra in same cluster, will have best performance in throughput and low latency
Deploy Spark with an Apache Cassandra cluster
Spark Cassandra Connector
Cassandra Optimizations for Apache Spark

Spark Optimizations

Narrow transformations than Wide transformations
minimize data shuffles
filter data as early as possible
set the right number of partitions, 4x of partitions to the number of cores
avoid data skew
broadcast for small table joins
repartition before expensive or multiple joins
repartition before writing to storage
be remember that repartition is an expensive operation
set right number of executors, cores and memory
get rid of the the Java Serialization, use Kryo Serialization
Minimize data shuffles and maximize data locality
Use Data Frames or Data Sets high level APIs to take advantages of the Spark optimizations
Apache Spark Internals: Tips and Optimizations

～/.forward

echo '[email protected]' > ~/.forward

This will make smtpd forwards email to the special address. On AWS EC2, SES can be used to forward email to your Gmail.

AWS Data Transfer Costs

via The Open Guide to Amazon Web Services

Patch Notes

Create patch file: diff -u file1 file2 > name.patch, or git diff > name.patch
Apply path file: patch [-u] < name.patch
Backup before apply patch: patch -b < name.patch
Validate patch without apply: patch --dry-run < name.patch
Reverse applied path: patch -R < name.patch

8:24

R.I.P. KOBE

8:24

audit.sh

Use PROMPT_COMMAND for bash, and precmd for zsh.

mkdir -p /var/log/.audit
touch /var/log/.audit/audit.log
chown nobody:nobody /var/log/.audit/audit.log
chmod 002 /var/log/.audit/audit.log
chattr +a /var/log/.audit/audit.log

Save to /etc/profile.d/audit.sh：

HISTSIZE=500000
HISTTIMEFORMAT=" "
export HISTTIMEFORMAT
export HISTORY_FILE=/var/log/.audit/audit.log
export PROMPT_COMMAND='{ curr_hist=`history 1|awk "{print \\$1}"`;last_command=`history 1| awk "{\\$1=\"\" ;print}"`;user=`id -un`;user_info=(`who -u am i`);real_user=${user_info[0]};login_date=${user_info[2]};login_time=${user_info[3]};curr_path=`pwd`;login_ip=`echo $SSH_CONNECTION | awk "{print \\$1}"`;if [ ${login_ip}x == x ];then login_ip=- ; fi ;if [ ${curr_hist}x != ${last_hist}x ];then echo -E `date "+%Y-%m-%d %H:%M:%S"` $user\($real_user\) $login_ip [$login_date $login_time] [$curr_path] $last_command ;last_hist=$curr_hist;fi; } >> $HISTORY_FILE'

echo "local6.*  /var/log/commands.log" > /etc/rsyslog.d/commands.conf
systemctl restart rsyslog.service
precmd() { eval 'RETRN_VAL=$?;logger -p local6.debug "$(whoami) [$$]: $(history | tail -n1 | sed "s/^[ ]*[0-9]\+[ ]*//" ) [$RETRN_VAL]"' }