Building a database front-end caching framework using memcached, Spring AOP

The various load file modes and time of MySQL and PostgreSQL are assessed on the virtual machine. Because it is a virtual machine on the evaluation, so time only for reference, not too serious, look good.
MySQL Tools:
1. Bring your own Mysqlimport tool.
2. Command line load Data infile ...
3. Use Mysql-connector-python driver to write the script.
PostgreSQL Tools:
1. Pgloader third-party tools.
2. Command line copy ... from ...
3. Use the Python script written by PSYCOPG2.
test table structure:

Mysql> desc t1;+----------+-----------+------+-----+-------------------+-------+| Field    | Type      | Null | Key | Default           | Extra |+----------+-----------+------+-----+-------------------+-------+| ID       | int   | NO   | PRI | NULL              |       | | rank     | int (one)   | NO   |     | NULL              |       | | log_time | timestamp | YES  |     | Current_timestamp |       | +----------+-----------+------+-----+-------------------+-------+3 rows in Set (0.00 sec) mysql> Select COUNT (*) From t1;+----------+| COUNT (*) |+----------+|  1000000 |+----------+1 row in Set (6.80 sec)

To test the CSV file:
T1.csv


MySQL's own loader: (Time 24 wonderful)

mysql> load Data infile '/tmp/t1.csv ' into table T1 fields terminated by ', ' enclosed by ' ' lines terminated by ' \ r \ n ' ; Query OK, 1000000 rows Affected (24.21 sec) records:1000000  deleted:0  skipped:0  warnings:0

MySQL python script: (time 23 seconds)
>>>
Running 23.289 Seconds


MySQL comes with Mysqlimport: (time 23 seconds)

[Email protected] ~]# time Mysqlimport t_girl '/tmp/t1.csv '--fields-terminated-by= ', '--fields-enclosed-by= ' "'-- lines-terminated-by= ' \ r \ n '--use-threads=2-uroot-proott_girl.t1:records:1000000  deleted:0  skipped:0  warnings:0real    0m23.664suser    0m0.016ssys     0m0.037s

PostgreSQL Self Copy: (time 7 seconds)

t_girl=# copy t1 from '/tmp/t1.csv ' with delimiter ', '; COPY 1000000time:7700.332 ms

PSYCOPG2 Drive Copy_to Method: (Time 6 seconds)

[email protected] scripts]# python load_data.py Running 5.969 Seconds.

pgloader Import CSV: (time 33 seconds)

[Email protected] ytt]# pgloader commands.load                          table name       read   imported errors time                        ytt.t1    1000000    1000000          0         33.514s------------------------------  ---------  ---------  - --------  --------------------------------------------  ---------  ---------  ---------  -- ------------Total             import time    1000000    1000000          0         33.514s

Pgloader directly from MySQL pull data: (Time 51 seconds)

[[email protected] ytt]# pgloader commands.mysql                     table name       read   imported     errors            time               fetch Meta Data          2          2          0          0.138s------------------------------  ---------  ---------  ------ ---  --------------                            t1    1000000    1000000          0         51.136s------------------------------  ---------  ---------  ---------  --------------------------------------------  ---------  ---------  ---------  --------------------------------------------  ---------  ---------  - --------  --------------Total             import time    1000000    1000000          0         51.274s

attach commands.load and Commands.mysql

Commands.load:LOAD CSV from     '/tmp/ytt.csv ' with ENCODING UTF-8        (             ID, rank, log_time          )     into Postgresql://t_girl:[email protected]:5432/t_girl?ytt.t1    with Skip Header = 0, fields          optionally enclosed by ' " ', fields          escaped by Backslash-quote, fields          terminated by ', '       SET work_mem to ' MB ', Maintenance_work_mem t O ' MB '; Commands.mysql:LOAD DATABASE from       mysql://python_user:[email protected]:3306/t_girl?t1 to     postgresql:/ /t_girl:[email protected]:5432/t_girl?ytt.t1  with data is SET maintenance_work_mem to ' 64MB ',        Work_ Mem to ' 3MB ',        search_path to ' Ytt '; Pgloader manual: Http://pgloader.io/howto/pgloader.1.html