Fajri Abdillah

Refactoring Info PTK Application

This application is just an information page, that hold every data about teacher, like salary, teaching time, status, retirement time, their education info, etc. The application itself is not that hard, what make it hard is the data that so big.

I really interested in this project, I bet You know how much cost of sql server, and it can be migrate to redis (thanks antirez). I really don’t know if my way is accepted by the creator itself or by community, but in my opinion, it really solve all my problem.

So, the story begin with the old project itself it using SQL Server 2008 and Vanilla PHP Script, the server down so often, well I don't know which one is wrong, but if this project goes well, it's really cool.

Those data are data that owned by government, like teacher, school, students, etc. My job is to make the refactoring work well. The data size is really big, whole country save here (about 5 million rows).

Here is Old Application Apache Bench result :

[email protected]:/var/www/campur/dokumentasi_infoguru# ab -c 10 -n 10 "http://223.27.144.195:8081/index.php?admin=1&nuptk=9646764665200012&debug=1/"
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 223.27.144.195 (be patient).....done


Server Software:        Apache/2.2.11
Server Hostname:        223.27.144.195
Server Port:            8081

Document Path:          /index.php?admin=1&nuptk=9646764665200012&debug=1/
Document Length:        20244 bytes

Concurrency Level:      10
Time taken for tests:   6.280 seconds
Complete requests:      10
Failed requests:        9
   (Connect: 0, Receive: 0, Length: 9, Exceptions: 0)
Write errors:           0
Total transferred:      206188 bytes
HTML transferred:       202458 bytes
Requests per second:    1.59 [#/sec] (mean)
Time per request:       6279.519 [ms] (mean)
Time per request:       627.952 [ms] (mean, across all concurrent requests)
Transfer rate:          32.07 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        2    3   0.9      3       4
Processing:  5808 6186 140.8   6234    6277
Waiting:     3098 3574 178.4   3619    3716
Total:       5811 6189 140.8   6236    6279

Percentage of the requests served within a certain time (ms)
  50%   6236
  66%   6253
  75%   6275
  80%   6278
  90%   6279
  95%   6279
  98%   6279
  99%   6279
 100%   6279 (longest request)

As we can see it can only hold 1.59 RPS.

Meanwhile, this is New Application result using Redis

[email protected]:/var/www/campur/dokumentasi_infoguru# ab -c 10 -n 10 "http://223.27.144.201:8888/infoguru.php?nuptk=9646764665200012&tgl_lahir=19860314&debug=1/"
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 223.27.144.201 (be patient).....done


Server Software:        Apache/2.2.22
Server Hostname:        223.27.144.201
Server Port:            8888

Document Path:          /infoguru.php?nuptk=9646764665200012&tgl_lahir=19860314&debug=1/
Document Length:        13966 bytes

Concurrency Level:      10
Time taken for tests:   0.080 seconds
Complete requests:      10
Failed requests:        8
   (Connect: 0, Receive: 0, Length: 8, Exceptions: 0)
Write errors:           0
Total transferred:      141562 bytes
HTML transferred:       139652 bytes
Requests per second:    125.46 [#/sec] (mean)
Time per request:       79.704 [ms] (mean)
Time per request:       7.970 [ms] (mean, across all concurrent requests)
Transfer rate:          1734.47 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        4    7   1.5      7       9
Processing:    51   56   5.5     56      70
Waiting:       34   47  10.5     44      67
Total:         58   63   6.2     62      79

Percentage of the requests served within a certain time (ms)
  50%     62
  66%     63
  75%     64
  80%     66
  90%     79
  95%     79
  98%     79
  99%     79
 100%     79 (longest request)

As we can see it improve so much, 125 RPS.

Data Structure

In this project, I use 6 redis instances which has 6 daemon redis in port : 6379, 6380, 6381, 6382, 6383 and 6384.

[email protected]:/var/www/campur/dokumentasi_infoguru# ps -ef | grep redis
root       816     1  0 Sep12 ?        06:08:01 /usr/bin/redis-server /data/r_ptk/redis.conf
root       820     1  0 Sep12 ?        05:55:48 /usr/bin/redis-server /data/r_mengajar/redis.conf
root       826     1  1 Sep12 ?        06:21:10 /usr/bin/redis-server /data/r_index/redis.conf
root       830     1  0 Sep12 ?        05:56:40 /usr/bin/redis-server /data/r_other/redis.conf
root       835     1  0 Sep12 ?        05:49:09 /usr/bin/redis-server /data/r_report/redis.conf
root       839     1  0 Sep12 ?        05:47:57 /usr/bin/redis-server /data/r_tunjangan/redis.conf
root     15885 14667  0 15:36 pts/1    00:00:00 grep redis
[email protected]:/var/www/campur/dokumentasi_infoguru# netstat -ntpl | grep redis
tcp        0      0 0.0.0.0:6379            0.0.0.0:*               LISTEN      816/redis-server
tcp        0      0 0.0.0.0:6380            0.0.0.0:*               LISTEN      820/redis-server
tcp        0      0 0.0.0.0:6381            0.0.0.0:*               LISTEN      826/redis-server
tcp        0      0 0.0.0.0:6382            0.0.0.0:*               LISTEN      830/redis-server
tcp        0      0 0.0.0.0:6383            0.0.0.0:*               LISTEN      835/redis-server
tcp        0      0 0.0.0.0:6384            0.0.0.0:*               LISTEN      839/redis-server

1. Instance Redis PTK

[email protected]:/var/www/campur/dokumentasi_infoguru# redis-cli -p 6379 randomkey
"t_ptk:1560750652210092:lYrOnCQoEeK1nYl5E6RArQ"

  t_ptk    1560750652210092  lYrOnCQoEeK1nYl5E6RArQ
    |             |                   |
    V             V                   V
  table         NUPTK               PTK ID

2. Instance Redis Mengajar

[email protected]:/var/www/campur/dokumentasi_infoguru# redis-cli -p 6380 randomkey       
"t_mengajar:7454760661300033:XVdAgPUuEeCnmfnivLgxCw:2"


  t_mengajar 7454760661300033  XVdAgPUuEeCnmfnivLgxCw 2
    |             |                   |               |
    V             V                   V               V
  table         NUPTK               PTK ID     Jumlah mengajar

3. Instance Redis Index

[email protected]:/var/www/campur/dokumentasi_infoguru# redis-cli -p 6381 randomkey
"login:0538742644200063:19640612"

  login    0538742644200063     19640612
    |             |                |               
    V             V                V               
  table         NUPTK           Password     

4. Instance Redis Rombel & Tunjangan Profesi

[email protected]:/var/www/campur/dokumentasi_infoguru# redis-cli -p 6382 randomkey
"t_rombel:3a7bMKYeEeK-j6G8W8iFbQ:11"

 t_rombel    3a7bMKYeEeK-j6G8W8iFbQ      11
    |                   |                |               
    V                   V                V               
  table            Id sekolah         Jumlah Rombel

[email protected]:/var/www/campur/dokumentasi_infoguru# redis-cli -p 6382 randomkey
"t_tunjangan_profesi_new:4041757661300003:15077532"

5. Instance Redis Report

[email protected]:/var/www/campur/dokumentasi_infoguru# redis-cli -p 6383 randomkey
"t_report:super_duplicate"
[email protected]:/var/www/campur/dokumentasi_infoguru# redis-cli -p 6383 randomkey
"t_report_done:hash"

Challenge

  • Redis Data Type

    Choosing different data type that match in this application is hard, but really challenging.
    I use Hash type to hold all the data.
    Well, this is the hardest part.

  • Never use Search

    Search operation in redis is slow, because redis not suitable for search keys.
    It is Key Value Store

  • Migrating Data to Redis

    This is the hard part, need to query, with so much data.
    Thanks to PropelORM, this job can be done faster.

Year

2013

Stack

  • Ubuntu Server 12.04
  • Apache 2.2
  • PHP 5.4.9
  • Redis 2.6
  • PropelORM 1.6.9
  • Bootstrap v3
  • phpredis
  • 32 Gb Ram, 8 Virtual Core

Screenshoot

Info PTK Old Version  nfz_refactor_1.png

Info PTK New Version  nfz_refactor_2.png

Server Information  nfz_refactor_4.png

Server Information & redis Instance  nfz_refactor_5.png

Lesson learned

Redis is true memcache with steroid. So much data type that I love to use it. And this is my best achievement work at Nufaza.