碼天狗週刊 第 104 期 @vinta - Recommender System, Apache Spark, Machine Learning, MySQL

碼天狗週刊 第 104 期 @vinta - Recommender System, Apache Spark, Machine Learning, MySQL

本文同步發表於 CodeTengu Weekly - Issue 104

Build a recommender system with Spark: Logistic Regression

前陣子寫了幾篇文章專門在講用 Apache Spark 搭建一個 GitHub repo 的推薦系統,打算寫成一個系列,不過因為身體不適中斷了好一陣子,所以寫著寫著 GitHub 都推出自己的推薦系統了(攤手)。言歸正傳,這篇文章主要是在講用 Logistic Regression 來對推薦結果排序,重點放在特徵工程和 Machine Learning Pipeline,對 LR 演算法本身沒有太多著墨,畢竟它就是個線性模型嘛。

延伸閱讀:

接下來是個沈重的題外話,由衷地建議大家真的要好好注意自己用電腦、用手機的姿勢,因為我最近才被診斷出頸椎椎間盤突出壓迫到神經,這個發作起來真的不是在開玩笑的,手腳又麻又癢又痛,你根本沒辦法專心做任何事,連好好睡一覺都不行。我最近吃了一堆止痛藥和肌肉鬆弛劑,都他媽快變成燕南天了。大家就想像一下有個跳蛋在你的骨頭或是神經的深處,三不五時就震一下,喔,那可一點都不好玩。

Build a recommender system with Spark: Content-based and Elasticsearch

這一篇也是 GitHub 推薦系統系列的文章之一,講的是大家喜聞樂見的 Content-based Recommendation。我原本是打算把 repo 的文本資料轉成 Word2Vec 向量,然後計算各個 repo 之間的相似度(所謂的 Similarity Join),但是要計算這麼多 repo 之間的相似度實在太花時間了,就算用了 Locality Sensitive Hashing 也還是太久又容易 OOM。後來一想,尋找相似或相關物品這件事不就是搜尋引擎在做的嗎,所以後來就直接改用 Elasticsearch 了。用 document id 當作搜尋條件,一個 More Like This query 就解決了,清爽利落。畢竟不需要所有的事情都在 Spark 裡解決。

基于 Spark UI 性能优化与调试 —— 初级篇

在寫程式的時候偶爾會發生「我明明只是加了一行簡單的 code,為什麼整個程式的效能就掉了這麼多」的情形,只因為我們對那行 code 實際上到底做了什麼其實並不清楚。更慘的是你的程式還是跑在一個分散式系統上時。幸好 Spark 提供了一個非常棒的工具:Spark UI。透過 Event Timeline 和 DAG Visualization,你可以看到非常詳細的整個 Spark application 的執行過程,例如某個 job 的某個 stage 的某個 task 做了什麼、花了多少時間和在哪一台機器上執行,甚至能夠精確地定位到是在你的 code 的某個檔案的某一行的某個 function call。真希望所有語言和框架都有這樣方便的工具啊。

不過老實說 DAG Visualization 第一眼看起來真的是讓人眼花撩亂,尤其是當你操作的是 Spark SQL 和 DataFrame,但是 Spark UI 顯示的其實卻是低層的 RDD operations,是需要花一點時間熟悉的。雖然你可能得先對 Executor、RDD、Partition 或 Shuffe 這些東西有點概念。

延伸閱讀:

Google - Machine Learning Glossary

這是 Google 製作的一份機器學習的常見詞彙表,非常實用!

MySQL vs. MariaDB: Reality Check

Percona 製作了一份表格,比較了 MySQL、MariaDB 和 Percona Server for MySQL 之間的異同。對有在評估選用或是跳槽到其中之一的資料庫的人應該很有幫助。不過如果你看完還是不知道該選哪個,黃金法則:你就閉著眼睛挑最多人用的那個就好了。

雖然當年 MariaDB 是打著 "a drop-in replacement for MySQL" 的名號,但是現在都 2017 年了,滄海桑田啊。而且大家對「相容性」這三個字可能都有更現實的認知了,畢竟它們就是由不同的人在不同的時間以不同的方式開發的不同的產品啊。

延伸閱讀:

碼天狗週刊 第 100 期 @vinta - Apache Spark, Scala, Machine Learning, Feature Engineering, MySQL

碼天狗週刊 第 100 期 @vinta - Apache Spark, Scala, Machine Learning, Feature Engineering, MySQL

本文同步發表於 CodeTengu Weekly - Issue 100

Big Data Analysis with Scala and Spark

因為前陣子辭職了(想放個長假吶~),突然多了不少時間,所以決定在打電動之餘,花點時間上幾門 Coursera 的課。然後花了一個禮拜終於完成這門課啦!主題是 Scala 和 Spark,是 Functional Programming in Scala 系列課程的最後一門課(這個系列有一門課的老師就是 Scala 的發明者 Martin Odersky)。之前學東西都習慣看書,這次第一次在 Coursera 完整地上完一門課,老實說是個很棒的體驗啊。尤其是在寫第一個程式作業的時候,上面標註說大概要花 3 小時,結果我寫了一個下午哈哈哈。雖然上手之後,後來的作業其實很快就做完了。但是還是忍不住想抱怨一下:Spark 的 Dataset typed API 寫起來也太麻煩,而且效能還沒有比較好。

題外話,雖然還沒那麼快要開始找工作,不過因為很閒,這陣子新認識了不少工程師同業,交流了很多技術經驗(和業界八卦 XD),感覺挺不錯的啊,所以想說如果各位朋友或公司有興趣,歡迎聯絡我,咱們可以約個時間吃個飯 👍

題外話之二,說到打電動,跟大家分享一下,12 月的時候 PC/PS4/Xbox One 會出「大神 絕景版」,這款遊戲可是 PS2 時代不朽的名作之一啊,如果你還沒有玩過,拜託玩一下。然!後!登登!小島秀夫的傑作之一 Anubis: Z.O.E. 也要重製啦!

Rules of Machine Learning: Best Practices for ML Engineering

不得了啊,這份文件,有在搞機器學習的人,這禮拜讀這篇就夠啦。這份文件的作者是 Google 的 Research Scientist,歸納了 43 條搭建一個 Machine Learning 系統的最佳實踐,而且很多都是從實務和軟體工程角度的經驗總結,這種知識特別寶貴啊。老實說我覺得這也是軟體工程師在這一波 AI 浪潮中可以施力的點,因為任何的機器學習系統或產品,宏觀一點來看,它就是一個軟體工程問題。

Most of the problems you will face are, in fact, engineering problems.

延伸閱讀(RecsysChina 的前輩對這篇文章的評註):

Feature Engineering - Getting most out of data for predictive models

這份簡報很豐富,除了幾乎把我前陣子才讀完的 Mastering Feature Engineering 書裡的內容都講完了之外,也提到很多特別的作法。而且看到最後一頁才發現,原來簡報的作者也讀過這本書啊。

是說我這一陣子實際搗鼓了一番 Feature Engineering 之後的感想,特徵工程根本就是手藝活,講求的其實是創造力啊。

延伸閱讀:

What happens when your application cannot open yet another connection to MySQL

之前工作的時候,要把 Spark 運算完的推薦系統的結果寫回 MySQL 資料庫,結果卻遇到了 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.x.x' (99) 的錯誤,很多人應該都見過類似的錯誤訊息,不過括號裡的數字可能不一樣。在這個例子中,單看文字的描述很容易讓人誤會是 MySQL server 出了什麼差錯,但是其實關鍵在於最後那個括號裡的 OS error code。這篇文章把造成這個錯誤的前因後果說得非常清楚,值得一讀。

簡單說,用 perror 99 可以查到這個 error code 是 Cannot assign requested address 的意思,原來是因為我在寫 Spark 的時候太放肆,不小心在短時間內開了太多的 connection,把 local 的 port 用完了(需要 TIME_WAIT 冷卻時間),所以沒有辦法分配 port 給新的 MySQL connection。解決的辦法:在程式裡重用 MySQL connection 或是限制一下 concurrent 數,再不然就是修改 net.ipv4.tcp_tw_reuse = 1 系統設定。

忍不住提一下,其實 master/slave 架構的 MySQL 還是很罩啊,當初ㄙㄨㄚˋ地一下在幾分鐘內寫了四十幾億筆資料進去,MySQL 跟沒事一樣。原本還ㄏㄧㄠˊ咖稱想用 Cassandra,結果發現在現階段用 MySQL 就頂得住啦。

延伸閱讀:

MySQL system error codes

MySQL system error codes

Print all OS error codes and MySQL error codes using the perror command.

$ for i in {1..190..1}; do perror "$i"; done
OS error code   1:  Operation not permitted
OS error code   2:  No such file or directory
OS error code   3:  No such process
OS error code   4:  Interrupted system call
OS error code   5:  Input/output error
OS error code   6:  No such device or address
OS error code   7:  Argument list too long
OS error code   8:  Exec format error
OS error code   9:  Bad file descriptor
OS error code  10:  No child processes
OS error code  11:  Resource temporarily unavailable
OS error code  12:  Cannot allocate memory
OS error code  13:  Permission denied
OS error code  14:  Bad address
OS error code  15:  Block device required
OS error code  16:  Device or resource busy
OS error code  17:  File exists
OS error code  18:  Invalid cross-device link
OS error code  19:  No such device
OS error code  20:  Not a directory
OS error code  21:  Is a directory
OS error code  22:  Invalid argument
OS error code  23:  Too many open files in system
OS error code  24:  Too many open files
OS error code  25:  Inappropriate ioctl for device
OS error code  26:  Text file busy
OS error code  27:  File too large
OS error code  28:  No space left on device
OS error code  30:  Read-only file system
OS error code  31:  Too many links
OS error code  32:  Broken pipe
OS error code  33:  Numerical argument out of domain
OS error code  34:  Numerical result out of range
OS error code  35:  Resource deadlock avoided
OS error code  36:  File name too long
OS error code  37:  No locks available
OS error code  38:  Function not implemented
OS error code  39:  Directory not empty
OS error code  40:  Too many levels of symbolic links
OS error code  42:  No message of desired type
OS error code  43:  Identifier removed
OS error code  44:  Channel number out of range
OS error code  45:  Level 2 not synchronized
OS error code  46:  Level 3 halted
OS error code  47:  Level 3 reset
OS error code  48:  Link number out of range
OS error code  49:  Protocol driver not attached
OS error code  50:  No CSI structure available
OS error code  51:  Level 2 halted
OS error code  52:  Invalid exchange
OS error code  53:  Invalid request descriptor
OS error code  54:  Exchange full
OS error code  55:  No anode
OS error code  56:  Invalid request code
OS error code  57:  Invalid slot
OS error code  59:  Bad font file format
OS error code  60:  Device not a stream
OS error code  61:  No data available
OS error code  62:  Timer expired
OS error code  63:  Out of streams resources
OS error code  64:  Machine is not on the network
OS error code  65:  Package not installed
OS error code  66:  Object is remote
OS error code  67:  Link has been severed
OS error code  68:  Advertise error
OS error code  69:  Srmount error
OS error code  70:  Communication error on send
OS error code  71:  Protocol error
OS error code  72:  Multihop attempted
OS error code  73:  RFS specific error
OS error code  74:  Bad message
OS error code  75:  Value too large for defined data type
OS error code  76:  Name not unique on network
OS error code  77:  File descriptor in bad state
OS error code  78:  Remote address changed
OS error code  79:  Can not access a needed shared library
OS error code  80:  Accessing a corrupted shared library
OS error code  81:  .lib section in a.out corrupted
OS error code  82:  Attempting to link in too many shared libraries
OS error code  83:  Cannot exec a shared library directly
OS error code  84:  Invalid or incomplete multibyte or wide character
OS error code  85:  Interrupted system call should be restarted
OS error code  86:  Streams pipe error
OS error code  87:  Too many users
OS error code  88:  Socket operation on non-socket
OS error code  89:  Destination address required
OS error code  90:  Message too long
OS error code  91:  Protocol wrong type for socket
OS error code  92:  Protocol not available
OS error code  93:  Protocol not supported
OS error code  94:  Socket type not supported
OS error code  95:  Operation not supported
OS error code  96:  Protocol family not supported
OS error code  97:  Address family not supported by protocol
OS error code  98:  Address already in use
OS error code  99:  Cannot assign requested address
OS error code 100:  Network is down
OS error code 101:  Network is unreachable
OS error code 102:  Network dropped connection on reset
OS error code 103:  Software caused connection abort
OS error code 104:  Connection reset by peer
OS error code 105:  No buffer space available
OS error code 106:  Transport endpoint is already connected
OS error code 107:  Transport endpoint is not connected
OS error code 108:  Cannot send after transport endpoint shutdown
OS error code 109:  Too many references: cannot splice
OS error code 110:  Connection timed out
OS error code 111:  Connection refused
OS error code 112:  Host is down
OS error code 113:  No route to host
OS error code 114:  Operation already in progress
OS error code 115:  Operation now in progress
OS error code 116:  Stale NFS file handle
OS error code 117:  Structure needs cleaning
OS error code 118:  Not a XENIX named type file
OS error code 119:  No XENIX semaphores available
OS error code 120:  Is a named type file
OS error code 121:  Remote I/O error
OS error code 122:  Disk quota exceeded
OS error code 123:  No medium found
OS error code 124:  Wrong medium type
OS error code 125:  Operation canceled
OS error code 126:  Required key not available
OS error code 127:  Key has expired
OS error code 128:  Key has been revoked
OS error code 129:  Key was rejected by service
OS error code 130:  Owner died
OS error code 131:  State not recoverable
OS error code 132:  Operation not possible due to RF-kill
OS error code 133:  Memory page has hardware error
MySQL error code 120: Did not find key on read or update
MySQL error code 121: Duplicate key on write or update
MySQL error code 122: Internal (unspecified) error in handler
MySQL error code 123: Someone has changed the row since it was read (while the table was locked to prevent it)
MySQL error code 124: Wrong index given to function
MySQL error code 125: Undefined handler error 125
MySQL error code 126: Index file is crashed
MySQL error code 127: Record file is crashed
MySQL error code 128: Out of memory in engine
MySQL error code 129: Undefined handler error 129
MySQL error code 130: Incorrect file format
MySQL error code 131: Command not supported by database
MySQL error code 132: Old database file
MySQL error code 126: Index file is crashed
MySQL error code 127: Record-file is crashed
MySQL error code 128: Out of memory
MySQL error code 130: Incorrect file format
MySQL error code 131: Command not supported by database
MySQL error code 132: Old database file
MySQL error code 133: No record read before update
MySQL error code 134: Record was already deleted (or record file crashed)
MySQL error code 135: No more room in record file
MySQL error code 136: No more room in index file
MySQL error code 137: No more records (read after end of file)
MySQL error code 138: Unsupported extension used for table
MySQL error code 139: Too big row
MySQL error code 140: Wrong create options
MySQL error code 141: Duplicate unique key or constraint on write or update
MySQL error code 142: Unknown character set used in table
MySQL error code 143: Conflicting table definitions in sub-tables of MERGE table
MySQL error code 144: Table is crashed and last repair failed
MySQL error code 145: Table was marked as crashed and should be repaired
MySQL error code 146: Lock timed out; Retry transaction
MySQL error code 147: Lock table is full;  Restart program with a larger locktable
MySQL error code 148: Updates are not allowed under a read only transactions
MySQL error code 149: Lock deadlock; Retry transaction
MySQL error code 150: Foreign key constraint is incorrectly formed
MySQL error code 151: Cannot add a child row
MySQL error code 152: Cannot delete a parent row
MySQL error code 153: No savepoint with that name
MySQL error code 154: Non unique key block size
MySQL error code 155: The table does not exist in engine
MySQL error code 156: The table already existed in storage engine
MySQL error code 157: Could not connect to storage engine
MySQL error code 158: Unexpected null pointer found when using spatial index
MySQL error code 159: The table changed in storage engine
MySQL error code 160: There is no partition in table for the given value
MySQL error code 161: Row-based binlogging of row failed
MySQL error code 162: Index needed in foreign key constraint
MySQL error code 163: Upholding foreign key constraints would lead to a duplicate key error in some other table
MySQL error code 164: Table needs to be upgraded before it can be used
MySQL error code 165: Table is read only
MySQL error code 166: Failed to get next auto increment value
MySQL error code 167: Failed to set row auto increment value
MySQL error code 168: Unknown (generic) error from engine
MySQL error code 169: Record is the same
MySQL error code 170: It is not possible to log this statement
MySQL error code 171: The event was corrupt, leading to illegal data being read
MySQL error code 172: The table is of a new format not supported by this version
MySQL error code 173: The event could not be processed no other hanlder error happened
MySQL error code 174: Got a fatal error during initialzaction of handler
MySQL error code 175: File to short; Expected more data in file
MySQL error code 176: Read page with wrong checksum
MySQL error code 177: Too many active concurrent transactions
MySQL error code 178: Record not matching the given partition set
MySQL error code 179: Index column length exceeds limit
MySQL error code 180: Index corrupted
MySQL error code 181: Undo record too big
MySQL error code 182: Invalid InnoDB FTS Doc ID
MySQL error code 183: Table is being used in foreign key check
MySQL error code 184: Tablespace already exists
MySQL error code 185: Too many columns
MySQL error code 186: Row in wrong partition
MySQL error code 187: InnoDB is in read only mode
MySQL error code 188: FTS query exceeds result cache memory limit
MySQL error code 189: Temporary file write failure
MySQL error code 190: Operation not allowed when innodb_forced_recovery > 0
MySQL error code 191: Too many words in a FTS phrase or proximity search
MySQL error code 192: Foreign key cascade delete/update exceeds max depth
MySQL error code 193: Required Create option missing
MySQL error code 194: Out of memory in storage engine
MySQL error code 195: Table corrupted
MySQL error code 196: Query interrupted
MySQL error code 197: Tablespace cannot be accessed
MySQL error code 198: Tablespace is not empty
MySQL error code 199: Incorrect file name
MySQL error code 200: Operation is not allowed
MySQL error code 201: Compute generate value failed

ref:
http://man7.org/linux/man-pages/man3/perror.3.html

Convert utf8 tables to utf8mb4 in MySQL

Convert utf8 tables to utf8mb4 in MySQL

# 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;

SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

# Don’t blindly copy-paste this!
# The exact statement depends on the column type, maximum length, and other properties.
# The above line is just an example for a `VARCHAR` column.

ref:
http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
http://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5
https://mathiasbynens.be/notes/mysql-utf8mb4