วันอาทิตย์ที่ 24 เมษายน พ.ศ. 2559

update A1 (สรุป การทำงาน MySQL , PostgreSQL ,Sqlite3 on Ubuntu)

จากสัปดาห์ที่แล้วได้ทำการทดลองใช้ PostgreSQL บน Ubuntu แต่ว่าไม่ได้ทดลอง MySQL และ Sqlite3 บน Ubuntu ด้วย ดังนั้นเพื่อความถูกต้องในการเปรียบเทียบประสิทธิภาพ จึงได้ทดลองใหม่ใน ช่วง ศุกร์ เสาร์ อาทิตย์ที่ผ่านมา


เริ่มจากรายละเอียดของคอมพิวเตอร์ที่ใช้ และ ubuntu version สามารถดูได้จาก System setting > Detial

รายละเอียดของคอมพิวเตอร์และUbuntu version ที่ใช้

ต่อไปทำการติดตั้ง PostgreSQL บน Ubuntu คำสั่งหลักๆที่ใช้ ได้กล่าวไว้ในบทความที่แล้ว(try-postgresql-on-linux.html) ซึ่งสามารถศึกษาเพิ่มเติมได้จาก เว็บไซด์ตาม URL ดังนี้ไปนี้นะคะ how-to-install-and-use-postgresql-on-ubuntu-14-04

คำสั่งที่ใช้ทำการติดตั้ง PostgreSQL บน Ubuntu

sudo apt-get update

sudo apt-get install postgresql postgresql-contrib

ต่อไปทำการติดตั้ง Sqlite3 บน Ubuntu นะคะ

sudo apt-get update

sudo apt-get install sqlite3 libsqlite3-dev


ซึ่งได้ข้อมูลมาจากเว็บไซต์ดังต่อไปนี้ how-to-install-sqlite-3-8-2-on-ubuntu-14-04

ต่อไปติดตั้ง MySQL นะคะ ซึ่งใน ubuntu จะใช้ MariaDB 

            เนื่องจาก MySQL ได้ถูกซื้อไปโดยบริษัท Oracle และมีความไม่แน่นอนกับแนวทางการพัฒนา ทำให้หลาย ๆ บริษัทหรือเว็บใหญ่ ๆ ได้ทยอยเปลี่ยนจาก MySQL เป็น MariaDB การที่เปลี่ยนจาก MySQL เป็น MariaDB จะไม่มีผลต่อการใช้งานโปรแกรมเดิมที่มีอยู่ และ Performance ของ MariaDB สามารถทำงานได้เร็วกว่า  MySQL ประมาณ 10% อ้างอิง และ สามารถอ่านเพิ่มเติมได้ที่ tutorial/mariadb-mysql

การติดตั้ง MariaDB
เริ่มจาก run commandline ตามคำสั่งดังต่อไปนี้

sudo apt-get install software-properties-common

sudo apt-key adv --recv-keys --keyserver
hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

sudo add-apt-repository 'deb [arch=amd64,i386]
http://mirrors.neusoft.edu.cn/mariadb/repo/10.1/ubuntu trusty main'

จากนั้นทำการ update และ install โดยใช้คำสั่ง
sudo apt-get update

sudo apt-get install mariadb-server

อ้างอิงจาก downloads.mariadb.org
จากนั้นใช้คำสั่ง sudo service mysql stop เสร็จแล้วทำการ ตั้งค่ารหัศผ่าน โดยใช้คำสั่ง
sudo mysql_install_db

sudo service mysql start

sudo mysql_secure_installation

เสร็จแล้วจะมีหน้าให้เรากรอกรหัศผ่านที่จะใช้ลงไป
โดยสามารถเข้าใช้การบริการของ MySQL ได้จากคำสั่ง
sudo service mysql start

sudo service mysql stop

เป็นคำสั่งที่ใช้หยุดการบริการของ MySQL

หลังจากทำการติดตั้ง MySQL , PostgreSQL และ Sqlite3 เสร็จเรียบร้อยแล้วต่อไปจะเป็นการสร้าง Database

PostgreSQL สามารถสร้าง Database ได้จากคำสั่ง
sudo -u postgres createdb DatabaseName 

เริ่มเข้าใช้งานโดยการระบุ user ที่จะเข้าใช้ก่อนโดยที่ใน PostgreSQL มี user ชื่อ postgre ที่เป็น user ที่ดูแลระบบทั้งหมด
sudo -i -u postgres  

และเข้าไปตั้งค่า password ก่อนจึงจะสามารถใช้านได้ โดยใช้คำสั่ง
psql

ALTER USER "user_name" WITH PASSWORD 'new_password';



จากนั้นเข้าใช้งาน Database ที่สร้างไว้ได้จากคำสั่ง
sudo -u postgres createdb DatabaseName

psql DatabaseName

โดยที่ psql เป็นคำสั่งที่ใช้เรียก command prom เพื่อเข้าใช้ Database ของ PostgreSQL
หลังจากนั้นก็สามารถ create table และ ดูคำสั่ง SQL statement ต่างๆ ได้จาก http://www.tutorialspoint.com/postgresql

Sqlite3 สามารถสร้าง Database ได้จากคำสั่ง
sqlite3 DatabaseName.db 
สามารถ create table และ ดูคำสั่ง SQL statement ต่างๆของ Sqlite ได้จาก http://www.tutorialspoint.com/sqlite

MySQL ก่อนการสร้าง Database เราต้อง login เข้าใช้งาน โดยการระบุ user และ password โดยใช้คำสั่ง
mysql -u root -p  

สามารถสร้าง Database ได้จากคำสั่ง
CREATE DATABASE name_database; 

และเข้าใช้งาน Database ได้จากคำสั่ง
USE name_database; 

สามารถ create table และ ดูคำสั่ง SQL statement ต่างๆของ MySQLได้จาก http://www.tutorialspoint.com/mysql

ทำการ Insert ข้อมูลลงใน Database โดยใช้ Python

PostgreSQL : Python Code ที่ใช้ทำการ Insert

#!/usr/bin/python
import random, string, psycopg2,time
from string import ascii_lowercase
from random import randint
start_time = time.time()
conn = psycopg2.connect(database="trainDB",user="postgres",password="198196",host="127.0.0.1", port="5432")
c = conn.cursor()
size=[100,150,200,250,300,350,400,450,500]
mm=["01","02","03","04","05","06","07","08","09","10","11","12"]
re1=["Yes","No"]
re2=["Unlimit","15kg","30kg","45kg"]
i=3000001
day=25
month=11
year=1908
def twochar(num):
    if num<10:
        cha="0"+str(num)
    else:
        cha=str(num)
    return cha

 def fourchar(no):
    if(no<10):
        out="000"+str(no)
    elif(no<100):
        out="00"+str(no)
    elif(no<1000):
        out='0'+str(no)
    else:
        out=str(no)
    return out

 while i<=6000000:
    if day == 30 and month == 12 and year == 9999:
        day = 0
        month = 0 
        year = 0
    amount = randint(50,50000)
    city = ''.join(random.choice(ascii_lowercase) for i in range(10))
    tname = ''.join(random.choice(ascii_lowercase) for i in range(15))
    rands = randint(0,8)
    y = randint(0,9999)
    ye = fourchar(y)
    ran1 = randint(0,1)
    ran2 = randint(0,3)
    res = "Smoke:"+re1[ran1]+"|WeightLimit:"+re2[ran2]
    m = randint(0,11)
    d = randint(1,30)
    y1 = randint(0,9999)
    m1 = randint(0,11)
    d1 = randint(1,30)
    h1 = randint(0,23)
    i1 = randint(0,59)
    s1 = randint(0,59)
    y2 = randint(0,9999)
    m2 = randint(0,11)
    d2 = randint(1,30)
    h2 = randint(0,23)
    i2 = randint(0,59)
    s2 = randint(0,59)
    if m==1 and d >= 29 :
        d = 28
    if m1==1 and d1 >= 29 :
        d1 = 28
    if m2==1 and d2 >= 29 :
        d2 = 28
    if day>=29 and month==2:
        day=1
        month+=1
    elif day>30:
        day=1
        month+=1
    if month>12:
        month=1
        year+=1
    arr = fourchar(y1)+"-"+mm[m1]+"-"+twochar(d1)+" "+twochar(h1)+":"+twochar(i1)+":"+twochar(s1)
    dep = fourchar(y2)+"-"+mm[m2]+"-"+twochar(d2)+" "+twochar(h2)+":"+twochar(i2)+":"+twochar(s2)
    weekday = ye+"-"+mm[m]+"-"+twochar(d)
    date = fourchar(year)+"-"+twochar(month)+"-"+twochar(day)
    fname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    lname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    aname = fname+" "+lname
    oname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    pname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    state = ''.join(random.choice(ascii_lowercase) for i in range(7))
    rand_name = oname + ' ' + pname
    phone = ''.join(str(randint(0,9)) for i in range(8))
    noaval = randint(0,50)
    total = size[rands]-noaval
    c.execute("insert into RLY_STATION values ('%s','%s','%s','%s');"
%(str(i),city,rand_name,state))
    c.execute("insert into TRAIN_TYPE values ('%s','%s');"
%(tname,str(size[rands])))
    c.execute("insert into CAN_STOP values ('%s','%s');"
                %(str(i),tname))
    c.execute("insert into TRAIN values ('%s','%s','%s');"
%(str(i),str(total),tname))
    c.execute("insert into TRAIN_ROUTE values ('%s','%s','%s','%s','%s');"
%(str(i),str(i),arr,dep,str(i)))
    c.execute("insert into DEST_ROUTE values ('%s','%s');"
%(str(i),weekday))
    c.execute("insert into FARE values ('%s','%s','%s','%s');"
%(str(i),str(i),res,str(amount)))
    c.execute( "insert into ROUTE_INSTANCE values ('%s','%s','%s','%s','%s','%s','%s','%s');"
%(str(i),date,str(noaval),str(i),str(i),str(i),arr,dep))
    c.execute("insert into SEAT values ('%s','%s','%s','%s');"
                %(str(i),str(i),phone,aname))
    print i
    if i%1000000 == 1:
        conn.commit()
    i+=1
    day+=1conn.commit()
conn.close()
print('---%s seconds---'%(time.time()-start_time))

ต้อง install library ของ Python ที่ชื่อ psycopg2  ใช้ทำการเชื่อมต่อกับ PostgreSQL ก่อน โดยใช้คำสั่ง
sudo apt-get install python-psycopg2 

Sqlite3 : Python Code ที่ใช้ทำการ Insert

import random, string, sqlite3,time
from string import ascii_lowercase
from random import randint
start_time = time.time()
conn = sqlite3.connect('train.db')
c = conn.cursor()
size=[100,150,200,250,300,350,400,450,500]
mm=["01","02","03","04","05","06","07","08","09","10","11","12"]
re1=["Yes","No"]
re2=["Unlimit","15kg","30kg","45kg"]
i=3000001
day=25
month=11
year=1908
def twochar(num):
    if num<10:
        cha="0"+str(num)
    else:
        cha=str(num)
    return cha

 def fourchar(no):
    if(no<10):
        out="000"+str(no)
    elif(no<100):
        out="00"+str(no)
    elif(no<1000):
        out='0'+str(no)
    else:
        out=str(no)
    return out

 while i<=6000000:
    if day == 30 and month == 12 and year == 9999:
        day = 0
        month = 0 
        year = 0
    amount = randint(50,50000)
    city = ''.join(random.choice(ascii_lowercase) for i in range(10))
    tname = ''.join(random.choice(ascii_lowercase) for i in range(15))
    rands = randint(0,8)
    y = randint(0,9999)
    ye = fourchar(y)
    ran1 = randint(0,1)
    ran2 = randint(0,3)
    res = "Smoke:"+re1[ran1]+"|WeightLimit:"+re2[ran2]
    m = randint(0,11)
    d = randint(1,30)
    y1 = randint(0,9999)
    m1 = randint(0,11)
    d1 = randint(1,30)
    h1 = randint(0,23)
    i1 = randint(0,59)
    s1 = randint(0,59)
    y2 = randint(0,9999)
    m2 = randint(0,11)
    d2 = randint(1,30)
    h2 = randint(0,23)
    i2 = randint(0,59)
    s2 = randint(0,59)
    if m==1 and d >= 29 :
        d = 28
    if m1==1 and d1 >= 29 :
        d1 = 28
    if m2==1 and d2 >= 29 :
        d2 = 28
    if day>=29 and month==2:
        day=1
        month+=1
    elif day>30:
        day=1
        month+=1
    if month>12:
        month=1
        year+=1
    arr = fourchar(y1)+"-"+mm[m1]+"-"+twochar(d1)+" "+twochar(h1)+":"+twochar(i1)+":"+twochar(s1)
    dep = fourchar(y2)+"-"+mm[m2]+"-"+twochar(d2)+" "+twochar(h2)+":"+twochar(i2)+":"+twochar(s2)
    weekday = ye+"-"+mm[m]+"-"+twochar(d)
    date = fourchar(year)+"-"+twochar(month)+"-"+twochar(day)
    fname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    lname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    aname = fname+" "+lname
    oname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    pname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    state = ''.join(random.choice(ascii_lowercase) for i in range(7))
    rand_name = oname + ' ' + pname
    phone = ''.join(str(randint(0,9)) for i in range(8))
    noaval = randint(0,50)
    total = size[rands]-noaval
    exec_str1 = 'insert into RLY_STATION values ('+str(i)+',"'+city+'","'+rand_name+'","'+state+'")'
    exec_str2 = 'insert into TRAIN_TYPE values ("'+tname+'",'+str(size[rands])+')'
    exec_str3 = 'insert into CAN_STOP values ('+str(i)+',"'+tname+'")'
    exec_str4 = 'insert into TRAIN values ('+str(i)+','+str(total)+',"'+tname+'")'
    exec_str5 = 'insert into TRAIN_ROUTE values ('+str(i)+','+str(i)+',"'+arr+'","'+dep+'",'+str(i)+')'
    exec_str6 = 'insert into DEST_ROUTE values ('+str(i)+',"'+weekday+'")'
    exec_str7 = 'insert into FARE values ('+str(i)+','+str(i)+',"'+res+'",'+str(amount)+')'  
    exec_str8 = 'insert into ROUTE_INSTANCE values ('+str(i)+',"'+date+'",'+str(noaval)+','+str(i)+','+str(i)+','+str(i)+',"'+arr+'","'+dep+'")'
    exec_str9 = 'insert into SEAT values ('+str(i)+','+str(i)+',"'+phone+'","'+aname+'")'
    print "Sqlit3 => ",i
    c.execute(exec_str1)
    c.execute(exec_str2)
    c.execute(exec_str3)
    c.execute(exec_str4)
    c.execute(exec_str5)
    c.execute(exec_str6)
    c.execute(exec_str7)
    c.execute(exec_str9)
    c.execute(exec_str8)
    if i%1000000 == 1:
        conn.commit()
    i+=1
    day+=1
conn.commit()
conn.close()
print('---%s seconds---'%(time.time()-start_time))




MySQL : Python Code ที่ใช้ทำการ Insert

#!/usr/bin/pythonimport random, string, MySQLdb,time
from string import ascii_lowercase
from random import randint
start_time = time.time()
conn = MySQLdb.connect(host= "localhost",user="root",passwd="Saii198196",db="tra_DB")
#conn = MySQLdb.connect(database="tra_DB",user="postgres",password="198196",host="127.0.0.1", port="5432")
c = conn.cursor()
size=[100,150,200,250,300,350,400,450,500]
mm=["01","02","03","04","05","06","07","08","09","10","11","12"]
re1=["Yes","No"]
re2=["Unlimit","15kg","30kg","45kg"]
i=3000001
day=25
month=11
year=1908
def twochar(num):
    if num<10:
        cha="0"+str(num)
    else:
        cha=str(num)
    return cha

 def fourchar(no):
    if(no<10):
        out="000"+str(no)
    elif(no<100):
        out="00"+str(no)
    elif(no<1000):
        out='0'+str(no)
    else:
        out=str(no)
    return out

 while i<=6000000:
    if day == 30 and month == 12 and year == 9999:
        day = 0
        month = 0 
        year = 0
    amount = randint(50,50000)
    city = ''.join(random.choice(ascii_lowercase) for i in range(10))
    tname = ''.join(random.choice(ascii_lowercase) for i in range(15))
    rands = randint(0,8)
    y = randint(0,9999)
    ye = fourchar(y)
    ran1 = randint(0,1)
    ran2 = randint(0,3)
    res = "Smoke:"+re1[ran1]+"|WeightLimit:"+re2[ran2]
    m = randint(0,11)
    d = randint(1,30)
    y1 = randint(0,9999)
    m1 = randint(0,11)
    d1 = randint(1,30)
    h1 = randint(0,23)
    i1 = randint(0,59)
    s1 = randint(0,59)
    y2 = randint(0,9999)
    m2 = randint(0,11)
    d2 = randint(1,30)
    h2 = randint(0,23)
    i2 = randint(0,59)
    s2 = randint(0,59)
    if m==1 and d >= 29 :
        d = 28
    if m1==1 and d1 >= 29 :
        d1 = 28
    if m2==1 and d2 >= 29 :
        d2 = 28
    if day>=29 and month==2:
        day=1
        month+=1
    elif day>30:
        day=1
        month+=1
    if month>12:
        month=1
        year+=1
    arr = fourchar(y1)+"-"+mm[m1]+"-"+twochar(d1)+" "+twochar(h1)+":"+twochar(i1)+":"+twochar(s1)
    dep = fourchar(y2)+"-"+mm[m2]+"-"+twochar(d2)+" "+twochar(h2)+":"+twochar(i2)+":"+twochar(s2)
    weekday = ye+"-"+mm[m]+"-"+twochar(d)
    date = fourchar(year)+"-"+twochar(month)+"-"+twochar(day)
    fname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    lname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    aname = fname+" "+lname
    oname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    pname = ''.join(random.choice(ascii_lowercase) for i in range(8))
    state = ''.join(random.choice(ascii_lowercase) for i in range(7))
    rand_name = oname + ' ' + pname
    phone = ''.join(str(randint(0,9)) for i in range(8))
    noaval = randint(0,50)
    total = size[rands]-noaval
    c.execute("""insert into RLY_STATION values (%s,%s,%s,%s)""",(str(i),city,rand_name,state))
    c.execute("""insert into TRAIN_TYPE values (%s,%s)""",(tname,str(size[rands])))
    c.execute("""insert into CAN_STOP values (%s,%s)""",(str(i),tname))
    c.execute("""insert into TRAIN values (%s,%s,%s)""",(str(i),str(total),tname))
    c.execute("""insert into TRAIN_ROUTE values (%s,%s,%s,%s,%s)""",(str(i),str(i),arr,dep,str(i)))
    c.execute("""insert into DEST_ROUTE values (%s,%s)""",(str(i),weekday))
    c.execute("""insert into FARE values (%s,%s,%s,%s)""",(str(i),str(i),res,str(amount))) 
    c.execute("""insert into ROUTE_INSTANCE values (%s,%s,%s,%s,%s,%s,%s,%s)""",(str(i),date,str(noaval),str(i),str(i),str(i),arr,dep))
    c.execute("""insert into SEAT values (%s,%s,%s,%s)""",(str(i),str(i),phone,aname))
    print i
    if i%1000000 == 1:
        conn.commit()
    i+=1
    day+=1
conn.commit()
conn.close()
print('---%s seconds---'%(time.time()-start_time))

ต้อง install library ของ Python ที่ชื่อ MySQLdb ใช้ทำการเชื่อมต่อกับ MySQL ก่อน โดยใช้คำสั่ง
sudo apt-get install python-mysqldb 

ขนาดของไฟล์ของ MySQL , PostgreSQL และ Sqlite3 โดยการ insert ข้อมูลลงไปอย่างละ 6,000,000 ข้อมูล

PostgreSQL


สามารถดูขนาดของ Database ใน PostgreSQL ได้จาก การใช้คำสั่ง \d+


Sqlite3


MySQL


สามารถดูขนาดของ Database ใน MySQL ได้จาก การใช้คำสั่ง

SELECT table_schema "Database Name", SUM( data_length + index_length)/1024/1024
"Database Size (MB)" FROM information_schema.TABLES where table_schema = 'your Database_Name';

ตารางการเปรียบเทียบขนาดของ MySQL , PostgreSQL และ Sqlite3
PostgreSQL
Sqlite3
MySQL
Size (GB)
4.30
3.4
3.87

สรุป จากตารางขนาดไฟล์ของ Sqlite3 < MySQL < PostgreSQL

 ต่อไปเป็นการ Query และเปรียบเทียบ ประสิทธฺภาพของ MySQL , PostgreSQL และ Sqlite3 บน Ubuntu


ตารางการเปรียบเทียบเวลาที่ใช้ในการ Queryของ MySQL , PostgreSQL และ Sqlite3

Query
ครั้งที่
Time (sec)
PostgreSQL
Sqlite3
MySQL
select * from TRAIN_TYPE where Max_seate = 200 or Max_seate = 500;
1
4.991
70.463
19.13
2
2.766
69.435
12.11
3
1.715
69.236
11.98
4
1.752
69.110
11.36

SELECT TRAIN.Train_name , TRAIN_ROUTE.Route_NO , TRAIN_ROUTE.Scheduleder_time FROM TRAIN INNER JOIN TRAIN_ROUTE ON TRAIN.Train_NO =TRAIN_ROUTE.Train_NO;
1
12.86
638.08
51.39
2
12.64
460.49
49.71
3
33.81
528.63
34.73
4
19.03
348.06
37.27
SELECT Train_name FROM TRAIN WHERE Train_NO <= 10 UNION ALL SELECT Train_name FROM CAN_STOP WHERE Station_ID <= 100 UNION ALL SELECT Train_name FROM TRAIN_TYPE WHERE Max_seate < 50 ORDER BY Train_name;
1
18.02
62.89
16.46
2
14.09
54.23
16.18
3
13.92
45.59
16.34
4
14.15
49.03
17.95
สรุป จากตารางเปรียบเทียบประสิทธิภาพด้านบนจะเห็นได้ว่าเวลาที่ใช้ในการ Query ของ PostgreSQL < MySQL < Sqlite3


เขียนโดย ที่
ป้ายกำกับ:

ไม่มีความคิดเห็น:

แสดงความคิดเห็น

สมัครสมาชิก: ส่งความคิดเห็น (Atom)