2019年1月24日 星期四

MySQL 維運

查詢連線數

mysqladmin -u root -p -h127.0.0.1 status

Threads 就是連線數
Uptime: 168765  Threads: 3  Questions: 2265  Slow queries: 0  Opens: 394  

Flush tables: 2  Open tables: 250  Queries per second avg: 0.013

mysql>show full processlist

+----+-----------------+-----------------+------+---------+--------+------------------------+-----------------------+
| Id | User            | Host            | db   | Command | Time   | State                  | Info                  |
+----+-----------------+-----------------+------+---------+--------+------------------------+-----------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon  | 169192 | Waiting on empty queue | NULL                  |
| 16 | root            | localhost:56512 | live | Sleep   |  23923 |                        | NULL                  |
| 18 | root            | localhost       | NULL | Query   |      0 | starting               | show full processlist |
+----+-----------------+-----------------+------+---------+--------+------------------------+-----------------------+

2019年1月23日 星期三

Linux command ps

ps(process status)

說明:用來顯示執行緒的資訊

支援多種options:
UNIX options
BSD options
GNU long options

使用概念

  • 選擇process,simple selection / selection by list
  • 輸出格式控制(format)

Simple selection 詳細參考man ps

 a      Lift the BSD-style "only yourself" restriction, 
             which is imposed upon the set of all processes when some BSD-style (without "-") 
             options are used or when the ps personality setting is BSD-like.  
             The set of processes selected in this manner is in addition to the set of processes selected by other means.  
             An alternate description is that this option causes ps to list all processes with a terminal (tty), 
             or to list all processes when used together with the x option.

       -A     Select all processes.  Identical to -e.

       -a     Select all processes except both session leaders (see getsid(2)) and processes not associated with a terminal.

       -d     Select all processes except session leaders.

       --deselect
              Select all processes except those that fulfill the specified conditions (negates the selection).  
              Identical to -N.

       -e     Select all processes.  Identical to -A.

       g      Really all, even session leaders.  This flag is obsolete and may be discontinued in a future release.  
              It is normally implied by the a flag, and is only
              useful when operating in the sunos4 personality.

       -N     Select all processes except those that fulfill the specified conditions (negates the selection).  
              Identical to --deselect.

       T      Select all processes associated with this terminal.  Identical to the t option without any argument.

       r      Restrict the selection to only running processes.

       x      Lift the BSD-style "must have a tty" restriction, which is imposed upon the set of all processes 
              when some BSD-style (without "-") options are used or
              when the ps personality setting is BSD-like.  The set of processes selected in this manner is in addition 
              to the set of processes selected by other means.
              An alternate description is that this option causes ps to list all processes owned by you (same EUID as ps), 
              or to list all processes when used together
              with the a option.

Selection by list

 ps -p "1 2" -p 3,4
       -123   Identical to --pid 123.
       123    Identical to --pid 123.
-C cmdlist
              Select by command name.  This selects the processes whose executable name is given in cmdlist.
-g grplist
              Select by session OR by effective group name.  Selection by session is specified by many standards, 
              but selection by effective group is the logical behavior that several other operating systems use.  
              This ps will select by session when the list is completely numeric (as sessions are).  Group ID
              numbers will work only when some group names are also specified.  See the -s and --group options.
p pidlist
              Select by process ID.  Identical to -p and --pid.

--sid sesslist
              Select by session ID.  Identical to -s.

-u userlist
              Select by effective user ID (EUID) or name.  This selects the processes 
              whose effective user name or ID is in userlist.

Output control

-f Do full-format listing. 
        This option can be combined with many other UNIX-style options to add additional columns.  
        It also causes the command arguments to be printed.  
        When used with -L, the NLWP (number of threads) and LWP (thread ID) columns will be added.  
        See the c option, the format keyword args, and the format keyword comm.
-F Extra full format.
-j Jobs format
 -o format List user-defined format. 

EXAMPLES

顯示使用者當次登入資訊

[root@li1548-65 live]# ps
  PID TTY          TIME CMD
24539 pts/1    00:00:00 bash
24761 pts/1    00:00:00 ps

列出最耗費記憶體的執行緒

ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%mem | head

-e:輸出所有行程
-o:指定輸出欄位,後面接著所有想要輸出的欄位名稱:
pid:行程 ID(process ID)
ppid:父行程 ID(parent process ID)
cmd:程式名稱
%mem:記憶體使用量(百分比)
%cpu:CPU 使用量(百分比)

--sort 參數則是指定排序的依據欄位,
預設會依照數值由小到大排序,
若要由大到小則在欄位名稱前加負號。
-%mem,就是記憶體使用量從大到小排序。

  PID  PPID CMD                         %MEM %CPU
 2124  1278 /usr/lib/chromium-browser/c 25.0 14.4
 1446  1278 /usr/lib/chromium-browser/c 21.5 15.4
 1253     1 /usr/lib/chromium-browser/c 19.2 13.0
 1328  1278 /usr/lib/chromium-browser/c  7.6  2.9
 1392  1278 /usr/lib/chromium-browser/c  7.6  0.5
  732   669 /usr/bin/X :0 -seat seat0 -  5.7  2.0
 1060     1 /usr/lib/arm-linux-gnueabih  1.8  0.0
 1086   758 pcmanfm --desktop --profile  1.5  0.2
 1085   758 lxpanel --profile LXDE-pi    1.5  0.3

輸出pid,ppid,cpu,mem,起始時間....

ps -eo uname,pid,ppid,nlwp,pcpu,pmem,psr,start_time,tty,time,args

USER   PID  PPID NLWP %CPU %MEM PSR START TT       TIME COMMAND
root     1     0    1  0.0  0.1   1 Feb20 ?    00:00:01 /sbin/init
root     2     0    1  0.0  0.0   0 Feb20 ?    00:00:00 [kthreadd]
root     3     2    1  0.0  0.0   0 Feb20 ?    00:02:23 [ksoftirqd/0]
root     6     2    1  0.0  0.0   0 Feb20 ?    00:00:00 [migration/0]
root     7     2    1  0.0  0.0   1 Feb20 ?    00:00:00 [migration/1]
root     9     2    1  0.1  0.0   1 Feb20 ?    00:13:52 [ksoftirqd/1]


列出記憶體使用

ps aux | grep apache2 | awk '{ total += $6; } END { print total/1024"MB" }'


//寫成function
$ vim .bashrc

function memusage() {
    ps aux | grep "$1" | awk '{ total += $6; } END { print total/1024"MB" }'
}

//用法
memusage apache2  //就可以印出記憶體使用量

列出CPU使用

ps aux | grep apache2 | awk '{ total += $3; } END { print total"%" }'

//寫成function
$ vim .bashrc
function cpuusage() {
    ps aux | grep "$1" | awk '{ total += $3; } END { print total"%" }'
}

cpuusage apache2 # 就可以印出總 CPU 使用量
註:cpu 算法是比較奇怪(因為包含多顆 CPU),不過可以把自己的 CPU 數量 * 100%,再來看這個比例。

Linux process 資源使用查詢

cat /proc/[pid]/limits

本process的資源限制設定

Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             31204                31204                processes 
Max open files            1024                 4096                 files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       31204                31204                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us 

cat /proc/[pid]/statm

180024 1241 1046 35 0 171703 0

Size (pages) 任务虚拟地址空间的大小 VmSize/4
Resident(pages) 应用程序正在使用的物理内存的大小 VmRSS/4
Shared(pages) 共享页数 0
Trs(pages) 程序所拥有的可执行虚拟内存的大小 VmExe/4
Lrs(pages) 被映像到任务的虚拟内存空间的库的大小 VmLib/4
Drs(pages) 程序数据段和用户态的栈的大小 (VmData+ VmStk )4

cat /proc/[pid]/status

本process當前資源使用狀態

Name: cat 
State: R (running) 
SleepAVG: 88% 
Tgid: 5783 
Pid: 5783 
PPid: 5742 
TracerPid: 0 
Uid: 0 0 0 0 
Gid: 0 0 0 0 
FDSize: 256 
Groups: 0 1 2 3 4 6 10 
VmSize: 6588 kB 
VmLck: 0 kB 
VmRSS: 400 kB 
VmData: 144 kB 
VmStk: 2040 kB 
VmExe: 14 kB 
VmLib: 1250 kB 
StaBrk: 0804e000 kB 
Brk: 088df000 kB 
StaStk: bfe03270 kB 
ExecLim: 0804c000 
Threads: 1 
SigPnd: 0000000000000000 
ShdPnd: 0000000000000000 
SigBlk: 0000000000000000 
SigIgn: 0000000000000000 
SigCgt: 0000000000000000 
CapInh: 0000000000000000 
CapPrm: 00000000fffffeff 
CapEff: 00000000fffffeff 

參數解釋

Name 应用程序或命令的名字 
State 任务的状态,运行/睡眠/僵死/ 
SleepAVG 任务的平均等待时间(以nanosecond为单位),交互式任务因为休眠次数多、时间长,它们的 sleep_avg 也会相应地更大一些,所以计算出来的优先级也会相应高一些。 
Tgid 线程组号 
Pid 任务ID 
Ppid 父进程ID 
TracerPid 接收跟踪该进程信息的进程的ID号 
Uid Uid euid suid fsuid 
Gid Gid egid sgid fsgid 
FDSize 當前文件描述符的分配個數,32位元系統將以32倍數遞增,
64位元系統以64倍數遞增。
此數字只會增加不會減少。

列出當前使用的文件
ls -l /proc/`pgrep rsyslogd|grep -v grep`/fd  


Groups 
VmSize(KB) 任务虚拟地址空间的大小 (total_vm-reserved_vm),其中total_vm为进程的地址空间的大小,reserved_vm:进程在预留或特殊的内存间的物理页 
VmLck(KB) 任务已经锁住的物理内存的大小。锁住的物理内存不能交换到硬盘 (locked_vm) 
VmRSS(KB) 应用程序正在使用的物理内存的大小,就是用ps命令的参数rss的值 (rss) 
VmData(KB) 程序数据段的大小(所占虚拟内存的大小),存放初始化了的数据; (total_vm-shared_vm-stack_vm) 
VmStk(KB) 任务在用户态的栈的大小 (stack_vm) 
VmExe(KB) 程序所拥有的可执行虚拟内存的大小,代码段,不包括任务使用的库 (end_code-start_code) 
VmLib(KB) 被映像到任务的虚拟内存空间的库的大小 (exec_lib) 
VmPTE 该进程的所有页表的大小,单位:kb 
Threads 共享使用该信号描述符的任务的个数,在POSIX多线程序应用程序中,线程组中的所有线程使用同一个信号描述符。 
SigQ 待处理信号的个数 
SigPnd 屏蔽位,存储了该线程的待处理信号 
ShdPnd 屏蔽位,存储了该线程组的待处理信号 
SigBlk 存放被阻塞的信号 
SigIgn 存放被忽略的信号 
SigCgt 存放被俘获到的信号 
CapInh Inheritable,能被当前进程执行的程序的继承的能力 
CapPrm Permitted,进程能够使用的能力,可以包含CapEff中没有的能力,这些能力是被进程自己临时放弃的,CapEff是CapPrm的一个子集,进程放弃没有必要的能力有利于提高安全性 

幾個 process 相關的

ls /proc/PID/cmdline : process arguments
ls /proc/PID/cwd : process current working directory (symlink)
ls /proc/PID/exe : path to actual process executable file (symlink)
ls /proc/PID/environ : environment used by process
ls /proc/PID/root : the root path as seen by the process. For most processes this will be a link to / 
        unless the process is running in a chroot jail.
cat /proc/PID/status : basic information about a process including its run state and memory usage.

ls /proc/PID/task : hard links to any tasks that have been started by this (the parent) process.

列出 process fd

//看當前配置的 fd size
cat /proc/pid/status

//列出詳細 fd 內容
sudo ls -la /proc/pid/fd
//計算總數
ls -l /proc/28290/fd | wc -l

sudo lsof -p 28290
sudo lsof -a -p 28290


顯示 socket fd 詳細內容

sudo lsof -i | grep 4654214  //socket fd 
sudo netstat -alep | erep -i 4654214  //socket fd

2019年1月21日 星期一

MySQL 重整及optimize

REPARE TABLE table_name;
OPTIMIZE TABLE table_name;

or

$mysqlcheck -u root -p --auto-repair --optimize --all-databases


INNODB table不直接支援optimize,
而是建立新table再把資料copy過去,然後分析,

所以會顯示:
Table does not support optimize, doing recreate + analyze instead

MySQL 字元集 character set

How to support full Unicode in MySQL databases
Mysql字元集詳解
Mysql utf8mb4說明

character set 字元集

utf8mb4=utf8 most byte 4 是比utf8更完整的字元集

collation 字元排序方式

utf8mb4_unicode_ci 是依據unicode排序
utf8mb4_general_ci 無依據unicode排序,所以排序可能某些字元會不如預期

實作上只要全部統一就好,table跟mysql 系統相同,
用utf8mb4_unicode_ci或utf8mb4_general_ci都可以

檢查字元集指令

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' 
OR Variable_name LIKE 'collation%';

字元集變數

character_set_client | utf8mb4 |
character_set_connection | utf8mb4 |
character_set_database | utf8mb4 |
character_set_filesystem | binary |
character_set_results | utf8mb4 |
character_set_server | utf8mb4 |
character_set_system | utf8 |
character_sets_dir | /usr/share/mysql-8.0/charsets/

變數說明

character_set_client
客户端请求数据的字符集

character_set_connection
从客户端接收到数据,然后传输的字符集

character_set_database
默认数据库的字符集,无论默认数据库如何改变,都是这个字符集;如果没有默认数据库,那就使用 character_set_server指定的字符集,这个变量建议由系统自己管理,不要人为定义。

character_set_filesystem
把os上文件名转化成此字符集,即把 character_set_client转换character_set_filesystem, 默认binary是不做任何转换的

character_set_results
结果集的字符集

character_set_server
数据库服务器的默认字符集

character_set_system
这个值总是utf8,不需要设置,是为存储系统元数据的字符集


設定完整支援utf8mb4

分兩個部分 1 config中設定  2 建立table及建立column時設定

1.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

2.
# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2018年9月27日 星期四

MySQL Datetime and Timestemp

Basic


StackOverflow

Datetime 基本上就是儲存某個時間,通常不會改變。
Timestamp 基本上用來當作戳記,也就是會改變。當你改資料,會一起改timestamp。

Datetime

存入死的日期,就算更改mysql timezone,資料也不會變。

Timestamp

Save:current time zone converted to UTC for storage.
Retrieval: converted from UTC to current time zone for retrieval.
Timestamp存入的是UTC時間,取出後,會根據mysql time zone轉成當地時間。

兩者比較

處理速度大部份時候比datetime快。
timestamp有cache,datetime沒有。
timestamp有max 2038年問題。


Defult Value


官方說明

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0,
  dt DATETIME DEFAULT 0
);

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);

Eg.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

2018年9月24日 星期一

MySQL JOIN

INNER JOIN / JOIN

選取兩個表的交集部分,產生新的結果表。

Eg.

select item.id, item.weapon from item INNER join user on user.id=item.user_id;


LEFT OUTER JOIN

以左表為主,查詢左表中有,但右表中沒有的資料。