#! /bin/bash

test_time_ms=100000

#mysql -u m2 -pm2 m2 -e "TRUNCATE TABLE calls;"
#exit

cdr_start=`mysql -u m2 -pm2 m2 -e "SELECT COUNT(*) FROM calls\G;" | grep COUNT | awk -F ' ' '{print $2}'`
echo "CDRs at start:" $cdr_start

echo "Will run CDR inserts for $(($test_time_ms / 1000)) s"

start_time=`date +%s%3N`
echo "Start time: $start_time"

printf "Inserting CDRs "

export MYSQL_PWD=m2

while true; do

    # mysql 5.7 only supports only this import, also works for 5.5
    mysql -u m2 m2 -e "LOAD DATA INFILE '/usr/src/m2/mysql/tests/cdr_insert.csv' 
    INTO TABLE calls
    FIELDS TERMINATED BY ',' ENCLOSED BY '\''
    (calldate, clid, src, dst, duration, billsec, disposition, accountcode, uniqueid, src_device_id, dst_device_id,processed, provider_id, provider_rate, provider_billsec, provider_price, user_id, user_rate, user_billsec, user_price, prefix, server_id, hangupcause, localized_dst, did_provider_id, originator_ip, terminator_ip, real_duration, real_billsec, did_billsec, dst_user_id, src_user_id, pdd, terminated_by, answer_time, end_time)
    ;"

    # mysql 5.5
    #mysql -u m2 -pm2 m2 -e "LOAD DATA INFILE '/usr/src/m2/mysql/tests/cdr_insert.csv_orig' INTO TABLE calls FIELDS TERMINATED BY ',' ENCLOSED BY '\'';"
#    mysql -u m2 -pm2 m2 -e "SET autocommit=0;LOAD DATA INFILE '/usr/src/m2/mysql/tests/cdr_insert.csv' INTO TABLE calls FIELDS TERMINATED BY ',' ENCLOSED BY '\'';COMMIT;"
    printf "."

    end_time=`date +%s%3N`
    time_passed=`expr $end_time - $start_time`

    if [ "$time_passed" -gt $test_time_ms ]; then
	printf " Complete\n"
	break
    fi

done;



end_time=`date +%s%3N`

echo "End time: $end_time"

time_passed=`expr $end_time - $start_time`
echo $time_passed ms

cdr_end=`mysql -u m2 -pm2 m2 -e "SELECT COUNT(*) FROM calls\G;" | grep COUNT | awk -F ' ' '{print $2}'`
echo "CDRs at end:" $cdr_end

cdr_inserted=`expr $cdr_end - $cdr_start`
echo "CDRs inserted: $cdr_inserted"

cdr_per_second=$(($cdr_inserted * 1000 / $time_passed))
echo "CDRs per second inserted: $cdr_per_second"

used_hdd=`df -h | grep sda1 | awk -F ' ' '{print $5}'`
echo "Used HDD: $used_hdd"


# CDRips - CDR inserts per second
# VM on 7200 RPM HDD, m2 installed fully, no optimizations, CDRips 6248/6054/5469/5951/4634, HDD load ~90%, CDRs in DB: 2M-6M
# same VM, added SET autocommit=0; INSERT SQL; COMMIT; on each insert. CDRips: 5571/5394/5699/4621, HDD is a bottleneck, load ~90%, CDRs in DB: 7M
# same VM, removed autocommit, as before. CDRips: 5988/5428/5995/6361/5431
# VM on SSD, clean DB, CDRips: 8957/8732/7883/6446/7406/8357/7998/6922/7995   SSD load ~60%, CPU some cores go to 100% but not always, bottleneck not clear
# same VM on SSD, elasticsearch stopped, CDRips: 8828/8617, clean DB

# new VM on new SSD, Centos 6.7 MySQL 5.7, no m2, insert with column names, less data, CDRips: 21255/21474/22431/22325, clean DB, no load on HDD, load on 1 CPU core!

# not supported by mysql 5.5.62, on 5.7 - no difference
#innodb_io_capacity=1000
#innodb_io_capacity_max=3000
#innodb_flush_neighbors=no # does not work on 5.7 some stupid error
#innodb_log_compressed_pages=no
#binlog_row_image=minimal

# oook, 5.5 and 5.7 insert speed is the same. Difference appears when inserting exact columns or whole columns, this difference is between 22k and 18k, that's fine
# what is not ok is that with full m2 installation, inserts are only 5k and hdd is on 100%, need more tests to find out the cause of this

# another test - fresh m2 full install - CDRips: 24k, wtf?