architecture

MySQL-Oslayer-Performance-Optimization

December 13, 2014 Architecture, MYSQL, performance No comments

upload on 2014.12

important tips of MySQL database design for better performance

Read : mysql-system-opt

Webm: MySQL database performance web monitor

September 28, 2014 Architect, MYSQL, performance No comments , ,

You can download webm from github: https://github.com/ylouis83/webm

webm: mysql web key performance monitor

webm is a tool that display key value graph on website and webm was developed by javascript and mysqlmon ( mysql data collection tool wrote by AnySQL)

Environment need:

Linux version 5+ php5 Apache server

You can also run this tool on windows platform (install xampp )

and webo will come soon ( oracle web monitor tools 🙂 )

login webm system
webm_login

mysql redo/binlog size per 10 seconds
webm1

mysql insert/update (little delete) per 10 seconds
webm2

mysql select (QPS) per 10 seconds
webm3

关于Dell 推出第13代服务器的一些想法

September 12, 2014 Architect, hardware No comments

戴尔近日推出了旗下的13G服务器,其主力机型为R730xd,包含了诸多的特性,为其成为主流db server以及规模存储集群打下了良好的基础。

具体参考:http://www.storagereview.com/dell_poweredge_13g_r730xd_review
http://www.storagereview.com/dell_poweredge_gen13_servers_released

https://www.youtube.com/watch?v=YSGzB7umHa0

具体增强为:

1.CPU 为intel haswall最新架构,减少了功能的损耗
2.更多的插槽,扩展为可支持18块1.8寸SSD的槽位 以及多种磁盘混插的模式
3.DDR4 memory 拥有更高的主频
4.更加智能的基于iDRAC的装机模式
5.扩展的万兆网卡
6.基于iDRAC8的自动管理功能 包括服务器性能的监控,邮件报警(app端)等等
7.Sandisk的缓存技术取代之前的LSI的(是否与LSI被希捷收购有关 ?)
8.增强的新一代的RAID卡 更大的内存以及基于RAID卡的直接系统日志收集等(依然采用电池)
9.NFC技术的运用(自动扫描bios信息等)
10.NVMe协议的支持 (支持 NVMe_SSD 全面拥抱Intel ?)

等等

根据戴尔sales的描述,R730xd为下一代db-server,hadoop server 以及云计算server.在这里针对hadoop server持保留意见,其18块ssd的插槽扩展虽然增加了ssd的整体容量,但对于hadoop这类应用,或者对于目前hadoop的软件架构,SSD是否能发挥其应有的性能,facebook的测试给出了答案。

http://hadoopblog.blogspot.com/2012/05/hadoop-and-solid-state-drives.html

Also, a SSD device can support 100K to 200K operations/sec while a spinning disk controller can possibly 
issue only 200 to 300 ops/sec. This means that random reads/writes are not a bottleneck on SSDs. 
On the other hand, most of our existing database technology is designed to store data in spinning disks, 
so the natural question is "can these databases harness the full potential of the SSDs"?

结合两张图我们来看结论:

HdfsPreadImageCache4G

结论为现在HADOOP/hbase 并不能将SSD的性能优势发挥的玲离尽致 hadoop修改代码后的瓶颈依然存在(JAVA DFSClient),hbase线程锁导致cpu利用率低下,这归根于传统的数据库基于机械硬盘IO的设计,不过这一点在oracle上解决的非常好(oracle 在unix/linux是基于进程的数据库)。

最后如Dhruba Borth所说

@Sujoy: you are absolutely right. In fact, we currently run multiple servers instances per SSD 
just to be able to utilize all the IOPs. This is kindof-a-poor man's solution to the problem. 
Also, you have to have enough CPU power on the server to be able to drive multiple database 
instances on the same machine.

Facebook通过多实例并用server来以最小的成本达到硬件的最大性能,这类似于早期的mysql,mysql的多线程架构并不能在SMP NUMA架构的机器中充分利用CPU的能力,所以衍生出了NUMA多实例,多种绑定CPU的策略。所以在传统的数据库架构下要契合最新的硬件并不是一件很轻松的事。

另外针对线程以及进程(在unix时代对线程支持不是非常好,所以如oracle pg等数据库采用了进程的方式,mysql采用线程在早期对CPU的利用也是十分低下的) 可以暂且认为线程是近代DB的一种趋势(不知道准不准确)因为线程本省对于进程来说是具有一定优势的(内存的共享 以及更小的创建代价,更低的CPU上下文切换代价)

Some data collection display

August 21, 2014 MYSQL, performance No comments , , ,

Reference Oracle AWR. These two was developed by python and perl.

Both of these are following data collection method.

Use these tools can help quick identify problem.

MyAWR v2 –collection of mysql and os information

MongoAWR v1 — collection of mongodb and os information

Talk about some MySQL HA architecture

August 12, 2014 Architecture, MYSQL No comments ,

Some tips for MySQL architecture

read : JKDB-mysql-arc

小型机迁移x86平台之老生常谈

July 17, 2014 migration, oracle, system, unix No comments

You can view this presentation click here: x86_architect
注意使用chrome打开文件 不然可能乱码

MySQL Fabric HA&Sharding solutions

June 18, 2014 Architecture, MYSQL No comments

MySQL Fabric

Download : MySQL fabric solutions

news in mysqlbinlog – Back Up Master Binary Log Files

May 13, 2014 Architecture, MYSQL, replication No comments

news in mysqlbinlog – Back Up Master Binary Log Files

从mysql5.6开始 mysqlbinlog开始支持远程读取master主机的binlog写入本地,极大的加强了binlog的备份策略,由于在mysql cluster复制环境中,binlog的存在极大的决定的数据恢复的完整性,所以binlog的备份显得特别重要。在诸多HA方案中,例如MHA,使用主库的binlog去恢复主备库之间的数据差,在主库物理机器down机无法重启的情况下,binlog的备份可以直接用来recover slave.所以这一特性提升mysql 容灾级别,使得mysql的灾备方案显得不是那么的单调唯一。

使用”–raw”,”–read-from-remote-server” 选项可以直接控制读取方式与读取server,可以采用管理机器统一读取多master binlog。
Facebook 采用类似semi-sync的方式重构了mysqlbinlog用来替代semi-sync方式的slave机器,达到多份复制的目的。

"We extended mysqlbinlog to speak Semisync protocol. The reason of the enhancement is that we wanted to use "semisync mysqlbinlog" as a replacement of local semisync slaves. We usually run slaves on remote datacenters, and we don't always need local slaves to serve read requests / redundancy. On the other hand, as described at above "Requirements for Semisync Deployment" section, in practice at least two local semisync readers are needed to make 
semisync work. We didn't like to run additional two dedicated slaves per master just for semisync. So we invented semisync mysqlbinlog and use it instead of semisync slaves, as shown in the below figure."

我们采用mysqlbinlog的这种方式备份多台master的binlog.配合MHA的异地binlog复制,以达到最小的数据丢失。

[root@pajk-super-master /usr/local/dbadmin/backup]
#nohup python binlog_backup_main.py &
#ps -ef | grep -i daemon
dbus      1056     1  0 May06 ?        00:00:00 dbus-daemon --system
root     24010 32696  0 10:58 pts/0    00:00:00 binlog_backup_daemon all    
root     24319 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.115':'3306' 
root     24330 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.116':'3306' 
root     24341 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.117':'3306' 

[root@pajk-super-master /usr/local/dbadmin/backup]
#ls -ltr /tmp/backup/binlog_backup/10.0.128.115.3306/
total 250908
-rw-r--r-- 1 root root     27732 May 13 10:12 mysql-bin.000001
-rw-r--r-- 1 root root   1063490 May 13 10:12 mysql-bin.000002
-rw-r--r-- 1 root root       126 May 13 10:12 mysql-bin.000003
-rw-r--r-- 1 root root       143 May 13 10:12 mysql-bin.000005
-rw-r--r-- 1 root root     14000 May 13 10:12 mysql-bin.000004
-rw-r--r-- 1 root root     64918 May 13 10:12 mysql-bin.000006
-rw-r--r-- 1 root root   1216094 May 13 10:12 mysql-bin.000007
-rw-r--r-- 1 root root       143 May 13 10:12 mysql-bin.000008
-rw-r--r-- 1 root root 183388823 May 13 10:12 mysql-bin.000009
-rw-r--r-- 1 root root  20839355 May 13 10:12 mysql-bin.000010
-rw-r--r-- 1 root root  50039255 May 13 10:12 mysql-bin.000011
-rw-r--r-- 1 root root    250816 May 13 11:00 mysql-bin.000012

同时MHA 0.56 开始支持从binlog server上恢复日志:

Binlog server
Starting from MHA version 0.56, MHA supports new section [binlogN]. In binlog section, you can define mysqlbinlog streaming servers. When MHA does GTID based failover, MHA checks binlog servers, and if binlog servers are ahead of other slaves, MHA applies differential binlog events to the new master before recovery. When MHA does non-GTID based (traditional) failover, MHA ignores binlog servers.
Below is an example configuration.
  manager_host$ cat /etc/app1.cnf 
  [server default]
  # mysql user and password
  user=root
  password=mysqlpass
  # working directory on the manager
  manager_workdir=/var/log/masterha/app1
  # manager log file
  manager_log=/var/log/masterha/app1/app1.log
  # working directory on MySQL servers
  remote_workdir=/var/log/masterha/app1
  
  [server1]
  hostname=host1
  [server2]
  hostname=host2  
  [server3]
  hostname=host3
  [binlog1]
  hostname=binlog_host1
  [binlog2]
  hostname=binlog_host2 

REF:semi-synchronous-replication-at-facebook
https://code.google.com/p/mysql-master-ha/wiki/Configuration#Binlog_server

[SHOUG] 1号店Exadata应用

April 3, 2014 Architect, Exadata, hardware, oracle No comments

Download PDF: [SHOUG.LOUISLIU]Exadata在电商的实践

FlashCard Test Detail

March 5, 2014 Architect, hardware No comments

Compare between FIO LSI and VD
Upload on 2014/3/5 by louis liu

Download this PDF