-- ###########################
-- RUNNING clear.sh

  
  /*
  sure mode, no confirmation prompt
  clearing directory INTERNAL_FILES_DIR (../STORAGE/INT) ...
  done
  clearing directory MAX_PERF_FILES_DIR (../STORAGE/MAX_PERF) ...
  done
  clearing directory TRANSAC_FILES_DIR (../STORAGE/TRANSAC) ...
  done
  */

-- ###########################
-- RUNNING doc_bench.sql
--

Taxi benchmark

data model

stop_on_error;


reponse



success

reponse
success

create col_type t_location_id as text;


reponse



success

reponse
success

create col_type t_pickup_location_id as text;


reponse



success

reponse
success

create col_type t_dropoff_location_id as text;


reponse



success

reponse
success

create col_type t_date as text;


reponse



success

reponse
success

create big table trips (
  cab_type_id text,
  vendor_id text,
  pickup_date t_date,
  pickup_time text,
  dropoff_date text,
  dropoff_time text,
  store_and_fwd_flag text,
  rate_code_id text,
  pickup_longitude number,
  pickup_latitude number,
  dropoff_longitude number,
  dropoff_latitude number,
  passenger_count number,
  trip_distance number,
  fare_amount number,
  extra number,
  mta_tax number,
  tip_amount number,
  tolls_amount number,
  ehail_fee number,
  improvement_surcharge number,
  total_amount number,
  payment_type text,
  trip_type text,
  pickup_nyct2010_gid text,
  dropoff_nyct2010_gid text,
  pickup_location_id t_pickup_location_id,
  dropoff_location_id t_dropoff_location_id
);


reponse



success

reponse
success

create merge table pickup_location(
pickup_location_id t_pickup_location_id,
pickup_location_id2 t_location_id
);


reponse



success

reponse
success

create merge table dropoff_location(
dropoff_location_id t_dropoff_location_id,
dropoff_location_id2 t_location_id
);


reponse



success

reponse
success

create merge table calendar(
date t_date,
week_day text,
month_name text,
full_year text
);


reponse



success

reponse
success

create view v_trips as
  select * from trips, pickup_location, dropoff_location, calendar
  where pickup_location.pickup_location_id=trips.pickup_location_id
  and dropoff_location.dropoff_location_id=trips.dropoff_location_id
  and calendar.date=trips.pickup_date
;


reponse



success

reponse
success

pre requisite

You will need around 250 GB of RAM for the full dataset

Disable swap

swapoff -a

Disable THP (=Transparent Huge Page) if activated

On Centos (as root)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
On Redhat (as root)
echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag

download data

Https://www.stormbase.fr/downloads/taxi_small.zip

insert data

set file_separator=';';


reponse



success

reponse
success

insert into pickup_location select * from file('pickup_location.csv');


reponse



success

reponse
success

insert into dropoff_location select * from file('dropoff_location.csv');


reponse



success

reponse
success

insert into calendar select * from file('calendar.csv');


reponse



success

reponse
success

insert into trips select * from file('trips.csv') where rownum<10;


reponse



success

reponse
success

refresh dirty views;


reponse



success

reponse
success

save;


reponse



success

reponse
success

desc table;


table_name column_count line_count has_delete has_update parent_view_hidden



trips 28 9 n n n
pickup_location 2 202 n n n
dropoff_location 2 255 n n n
calendar 4 4 n n n

table_name	column_count	line_count	has_delete	has_update	parent_view_hidden
trips	28	9	n	n	n
pickup_location	2	202	n	n	n
dropoff_location	2	255	n	n	n
calendar	4	4	n	n	n

desc view;


view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count



v_trips 4 9 n n n 10 5 14 19 0 0

view_name	table_count	rel_count	is_dirty	is_mp_dirty	is_partial	first_iline	mp_rare_count	mp_often_count	mp_count	r_online_count	mp_r_online_count
v_trips	4	9	n	n	n	10	5	14	19	0	0

select * from v_trips;


cab_type_id vendor_id pickup_date pickup_time dropoff_date dropoff_time store_and_fwd_flag rate_code_id pickup_longitude pickup_latitude dropoff_longitude dropoff_latitude passenger_count trip_distance fare_amount extra mta_tax tip_amount tolls_amount ehail_fee improvement_surcharge total_amount payment_type trip_type pickup_nyct2010_gid dropoff_nyct2010_gid pickup_location_id dropoff_location_id pickup_location_id pickup_location_id2 dropoff_location_id dropoff_location_id2 date week_day month_name full_year



2 2 20160804 05:00 20160804 05:16 N 1 0 0 0 0 1 2.130 12 0.500 0.500 0 0 0 0.300 13.300 2 1 # # 129 223 129 129 223 223 20160804 Thursday August 2016
2 2 20160804 05:13 20160804 05:14 N 1 0 0 0 0 1 0.120 2.500 0.500 0.500 10 0 0 0.300 13.800 1 1 # # 244 244 244 244 244 244 20160804 Thursday August 2016
2 2 20160804 05:31 20160804 05:45 N 1 0 0 0 0 1 2.440 12 0.500 0.500 0 0 0 0.300 13.300 1 1 # # 181 228 181 181 228 228 20160804 Thursday August 2016
2 2 20160804 05:08 20160804 05:14 N 1 0 0 0 0 1 0.830 6 0.500 0.500 0 0 0 0.300 7.300 2 1 # # 41 166 41 41 166 166 20160804 Thursday August 2016
2 2 20160804 05:46 20160804 06:05 N 1 0 0 0 0 1 5.620 18.500 0.500 0.500 0 0 0 0.300 19.800 2 1 # # 7 164 7 7 164 164 20160804 Thursday August 2016
2 2 20160804 05:59 20160804 05:59 N 5 0 0 0 0 1 0 1 0 0 0 0 0 0 1 1 2 # # 21 21 21 21 21 21 20160804 Thursday August 2016
2 2 20160804 05:19 20160804 05:30 N 1 0 0 0 0 1 2.140 10 0.500 0.500 0 0 0 0.300 11.300 2 1 # # 112 225 112 112 225 225 20160804 Thursday August 2016
2 2 20160804 05:42 20160804 05:50 N 1 0 0 0 0 1 1.340 7.500 0.500 0.500 0 0 0 0.300 8.800 2 1 # # 82 173 82 82 173 173 20160804 Thursday August 2016
2 2 20160804 05:17 20160804 05:21 N 1 0 0 0 0 1 1.420 6 0.500 0.500 1.460 0 0 0.300 8.760 1 1 # # 166 116 166 166 116 116 20160804 Thursday August 2016

full load

--important parameters

../../../40_BENCH/nyc_files/stormbase.conf
MAX_PERF_USE_COMPRESSION:y
CSV_FILES_DIR:/home/postgres/CSV
FILE_SEPARATOR:;

--script

../../../40_BENCH/nyc_files/insert_all.sql
create col_type t_location_id as text;
create col_type t_pickup_location_id as text;
create col_type t_dropoff_location_id as text;
create col_type t_date as text;

create big table trips (
  cab_type_id text,
  vendor_id text,
  pickup_date t_date,
  pickup_time text,
  dropoff_date text,
  dropoff_time text,
  store_and_fwd_flag text,
  rate_code_id text,
  pickup_longitude number,
  pickup_latitude number,
  dropoff_longitude number,
  dropoff_latitude number,
  passenger_count number,
  trip_distance number,
  fare_amount number,
  extra number,
  mta_tax number,
  tip_amount number,
  tolls_amount number,
  ehail_fee number,
  improvement_surcharge number,
  total_amount number,
  payment_type text,
  trip_type text,
  pickup_nyct2010_gid text,
  dropoff_nyct2010_gid text,
  pickup_location_id t_pickup_location_id,
  dropoff_location_id t_dropoff_location_id
);

create merge table pickup_location(
  pickup_location_id t_pickup_location_id,
  pickup_location_id2 t_location_id
);

create merge table dropoff_location(
  dropoff_location_id t_dropoff_location_id,
  dropoff_location_id2 t_location_id
);

create merge table calendar(
  date t_date,
  week_day text, 
  month_name text,
  full_year text
);

create view v_calendar as select * from calendar;
create view v_pickup_location as select * from pickup_location;
create view v_dropoff_location as select * from dropoff_location;

create view v_trips as 
  select * from trips, pickup_location, dropoff_location, calendar
  where pickup_location.pickup_location_id=trips.pickup_location_id
  and dropoff_location.dropoff_location_id=trips.dropoff_location_id
  and calendar.date=trips.pickup_date
;

insert into pickup_location select * from file('pickup_location.csv');
insert into dropoff_location select * from file('dropoff_location.csv');
insert into calendar select * from file('calendar.csv');

insert into trips select * from file('trips_2009.csv');
insert into trips select * from file('trips_2010.csv');
insert into trips select * from file('trips_2011.csv');
insert into trips select * from file('trips_2012.csv');
insert into trips select * from file('trips_2013.csv');
insert into trips select * from file('trips_2014.csv');
insert into trips select * from file('trips_2015.csv');
insert into trips select * from file('trips_2016.csv');
insert into trips select * from file('trips_2017.csv');
insert into trips select * from file('trips_2018.csv');

save;
refresh dirty view;

save;

--logs

../../../40_BENCH/nyc_files/insert_all.log
3319
[2021-01-22 09:40:24] --------- NEW CONNECTION ------------ CACHE SIZE : 0 / 0
[2021-01-22 09:40:24] connecting to localhost : 3319
[2021-01-22 09:40:24]  -> authentication;
[2021-01-22 09:40:24]  <- data ( 2 ms )
[2021-01-22 09:40:24]  -> create col_type t_location_id as text;
[2021-01-22 09:40:24]  <- data ( 101 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> create col_type t_pickup_location_id as text;
[2021-01-22 09:40:24]  <- data ( 100 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> create col_type t_dropoff_location_id as text;
[2021-01-22 09:40:24]  <- data ( 101 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> create col_type t_date as text;
[2021-01-22 09:40:24]  <- data ( 101 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> 
create big table trips (
  cab_type_id text,
  vendor_id text,
  pickup_date t_date,
  pickup_time text,
  dropoff_date text,
  dropoff_time text,
  store_and_fwd_flag text,
  rate_code_id text,
  pickup_longitude number,
  pickup_latitude number,
  dropoff_longitude number,
  dropoff_latitude number,
  passenger_count number,
  trip_distance number,
  fare_amount number,
  extra number,
  mta_tax number,
  tip_amount number,
  tolls_amount number,
  ehail_fee number,
  improvement_surcharge number,
  total_amount number,
  payment_type text,
  trip_type text,
  pickup_nyct2010_gid text,
  dropoff_nyct2010_gid text,
  pickup_location_id t_pickup_location_id,
  dropoff_location_id t_dropoff_location_id
);
[2021-01-22 09:40:24]  <- data ( 175 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> 
create merge table pickup_location(
  pickup_location_id t_pickup_location_id,
  pickup_location_id2 t_location_id
);
[2021-01-22 09:40:24]  <- data ( 102 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> 
create merge table dropoff_location(
  dropoff_location_id t_dropoff_location_id,
  dropoff_location_id2 t_location_id
);
[2021-01-22 09:40:24]  <- data ( 101 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> 
create merge table calendar(
  date t_date,
  week_day text, 
  month_name text,
  full_year text
);
[2021-01-22 09:40:24]  <- data ( 103 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:24]  -> create view v_calendar as select * from calendar;
[2021-01-22 09:40:25]  <- data ( 102 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:25]  -> create view v_pickup_location as select * from pickup_location;
[2021-01-22 09:40:25]  <- data ( 102 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:25]  -> create view v_dropoff_location as select * from dropoff_location;
[2021-01-22 09:40:25]  <- data ( 103 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:25]  -> 
create view v_trips as 
  select * from trips, pickup_location, dropoff_location, calendar
  where pickup_location.pickup_location_id=trips.pickup_location_id
  and dropoff_location.dropoff_location_id=trips.dropoff_location_id
  and calendar.date=trips.pickup_date;
[2021-01-22 09:40:25]  <- data ( 104 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:25]  -> insert into pickup_location select * from file('pickup_location.csv');
[2021-01-22 09:40:25]  <- data ( 102 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:25]  -> insert into dropoff_location select * from file('dropoff_location.csv');
[2021-01-22 09:40:25]  <- data ( 103 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:25]  -> insert into calendar select * from file('calendar.csv');
[2021-01-22 09:40:25]  <- data ( 262 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:40:25]  -> insert into trips select * from file('trips_2009.csv');
[2021-01-22 09:52:10]  <- data ( 704998 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 09:52:10]  -> insert into trips select * from file('trips_2010.csv');
[2021-01-22 10:03:04]  <- data ( 653457 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 10:03:04]  -> insert into trips select * from file('trips_2011.csv');
[2021-01-22 10:16:52]  <- data ( 828521 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 10:16:52]  -> insert into trips select * from file('trips_2012.csv');
[2021-01-22 10:30:13]  <- data ( 801036 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 10:30:13]  -> insert into trips select * from file('trips_2013.csv');
[2021-01-22 10:42:40]  <- data ( 746230 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 10:42:40]  -> insert into trips select * from file('trips_2014.csv');
[2021-01-22 10:55:55]  <- data ( 795400 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 10:55:55]  -> insert into trips select * from file('trips_2015.csv');
[2021-01-22 11:07:49]  <- data ( 714364 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 11:07:49]  -> insert into trips select * from file('trips_2016.csv');
[2021-01-22 11:16:18]  <- data ( 508225 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 11:16:18]  -> insert into trips select * from file('trips_2017.csv');
[2021-01-22 11:21:55]  <- data ( 337340 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 11:21:55]  -> insert into trips select * from file('trips_2018.csv');
[2021-01-22 11:26:57]  <- data ( 301737 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 11:26:57]  -> save;
[2021-01-22 12:04:44]  <- data ( 2266823 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 12:04:44]  -> refresh dirty view;
SIGHUP received (ignored)
[2021-01-22 12:35:21]  <- data ( 1837801 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1
[2021-01-22 12:35:22]  -> save;
[2021-01-22 12:50:19]  <- data ( 897616 ms )
|                                           reponse|
|--------------------------------------------------|
|                                           success|
ROWCOUNT :  1

--size info

../../../40_BENCH/nyc_files/size.log
bash-4.2$ ./size.sh
CSV_FILES_DIR             /home/postgres/CSV                   260782276 KB
INTERNAL_FILES_DIR        ../INT                               197165912 KB
MAX_PERF_FILES_DIR        ../MAX_PERF                           91560016 KB
TMP_FILES_DIR             ../TMP                                       4 KB

-- ###########################
-- RUNNING shutdown.sql
shutdown;

cab_type_id	vendor_id	pickup_date	pickup_time	dropoff_date	dropoff_time	store_and_fwd_flag	rate_code_id	passenger_count	trip_distance	fare_amount	extra	mta_tax	tip_amount	improvement_surcharge	total_amount	payment_type	trip_type	pickup_nyct2010_gid	dropoff_nyct2010_gid	pickup_location_id	dropoff_location_id	pickup_location_id	pickup_location_id2	dropoff_location_id	dropoff_location_id2	date	week_day	month_name	full_year
2	2	20160804	05:00	20160804	05:16	N	1	1	2.130	12	0.500	0.500	0	0.300	13.300	2	1	#	#	129	223	129	129	223	223	20160804	Thursday	August	2016
2	2	20160804	05:13	20160804	05:14	N	1	1	0.120	2.500	0.500	0.500	10	0.300	13.800	1	1	#	#	244	244	244	244	244	244	20160804	Thursday	August	2016
2	2	20160804	05:31	20160804	05:45	N	1	1	2.440	12	0.500	0.500	0	0.300	13.300	1	1	#	#	181	228	181	181	228	228	20160804	Thursday	August	2016
2	2	20160804	05:08	20160804	05:14	N	1	1	0.830	6	0.500	0.500	0	0.300	7.300	2	1	#	#	41	166	41	41	166	166	20160804	Thursday	August	2016
2	2	20160804	05:46	20160804	06:05	N	1	1	5.620	18.500	0.500	0.500	0	0.300	19.800	2	1	#	#	7	164	7	7	164	164	20160804	Thursday	August	2016
2	2	20160804	05:59	20160804	05:59	N	5	1	0	1	0	0	0	0	1	1	2	#	#	21	21	21	21	21	21	20160804	Thursday	August	2016
2	2	20160804	05:19	20160804	05:30	N	1	1	2.140	10	0.500	0.500	0	0.300	11.300	2	1	#	#	112	225	112	112	225	225	20160804	Thursday	August	2016
2	2	20160804	05:42	20160804	05:50	N	1	1	1.340	7.500	0.500	0.500	0	0.300	8.800	2	1	#	#	82	173	82	82	173	173	20160804	Thursday	August	2016
2	2	20160804	05:17	20160804	05:21	N	1	1	1.420	6	0.500	0.500	1.460	0.300	8.760	1	1	#	#	166	116	166	166	116	116	20160804	Thursday	August	2016

< * | *** >

Taxi benchmark

Taxi benchmark

data model

pre requisite

download data

insert data

full load

../../../40_BENCH/nyc_files/stormbase.conf

../../../40_BENCH/nyc_files/insert_all.sql

../../../40_BENCH/nyc_files/insert_all.log

../../../40_BENCH/nyc_files/size.log