ClickHouse/dbms/benchmark/memsql/instructions.txt

Note: column store in MemSQL was introduced in Feb 2014.

http://www.memsql.com/download/
http://docs.memsql.com/docs/latest/setup/setup_onprem.html
wget http://download.memsql.com/8d9f4c4d99a547baa40ba097b171bd15/memsql-3.2.x86_64.deb
scp memsql-3.2.x86_64.deb example05e:~
ssh example05e
sudo dpkg -i memsql-3.2.x86_64.deb

sudo mkdir /opt/memsql-data/
sudo cp -r /var/lib/memsql/data/* /opt/memsql-data/
sudo rm -rf /var/lib/memsql/data
sudo ln -s /opt/memsql-data /var/lib/memsql/data
sudo chown -R memsql /opt/memsql-data
sudo chown -R memsql /var/lib/memsql/data

sudo service memsql start
mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> "

CREATE DATABASE test;
USE test;

CREATE TABLE hits_10m
(
    WatchID BIGINT,
    JavaEnable SMALLINT,
    Title VARCHAR(1400),
    GoodEvent SMALLINT,
    EventTime TIMESTAMP,
    EventDate DATE,
    CounterID BIGINT,
    ClientIP BIGINT,
    RegionID BIGINT,
    UserID BIGINT,
    CounterClass TINYINT,
    OS SMALLINT,
    UserAgent SMALLINT,
    URL VARCHAR(7800),
    Referer VARCHAR(3125),
    Refresh TINYINT,
    RefererCategoryID INT,
    RefererRegionID BIGINT,
    URLCategoryID INT,
    URLRegionID BIGINT,
    ResolutionWidth INT,
    ResolutionHeight INT,
    ResolutionDepth SMALLINT,
    FlashMajor SMALLINT,
    FlashMinor SMALLINT,
    FlashMinor2 VARCHAR(256),
    NetMajor SMALLINT,
    NetMinor SMALLINT,
    UserAgentMajor INT,
    UserAgentMinor CHAR(2),
    CookieEnable SMALLINT,
    JavascriptEnable SMALLINT,
    IsMobile SMALLINT,
    MobilePhone SMALLINT,
    MobilePhoneModel VARCHAR(80),
    Params VARCHAR(2925),
    IPNetworkID BIGINT,
    TraficSourceID SMALLINT,
    SearchEngineID INT,
    SearchPhrase VARCHAR(2008),
    AdvEngineID SMALLINT,
    IsArtifical SMALLINT,
    WindowClientWidth INT,
    WindowClientHeight INT,
    ClientTimeZone INTEGER,
    ClientEventTime TIMESTAMP,
    SilverlightVersion1 SMALLINT,
    SilverlightVersion2 SMALLINT,
    SilverlightVersion3 BIGINT,
    SilverlightVersion4 INT,
    PageCharset VARCHAR(80),
    CodeVersion BIGINT,
    IsLink SMALLINT,
    IsDownload SMALLINT,
    IsNotBounce SMALLINT,
    FUniqID BIGINT,
    OriginalURL VARCHAR(8181),
    HID BIGINT,
    IsOldCounter SMALLINT,
    IsEvent SMALLINT,
    IsParameter SMALLINT,
    DontCountHits SMALLINT,
    WithHash SMALLINT,
    HitColor CHAR(1),
    LocalEventTime TIMESTAMP,
    Age SMALLINT,
    Sex SMALLINT,
    Income SMALLINT,
    Interests INT,
    Robotness SMALLINT,
    RemoteIP BIGINT,
    WindowName INT,
    OpenerName INT,
    HistoryLength SMALLINT,
    BrowserLanguage CHAR(2),
    BrowserCountry CHAR(2),
    SocialNetwork VARCHAR(128),
    SocialAction VARCHAR(128),
    HTTPError INT,
    SendTiming BIGINT,
    DNSTiming BIGINT,
    ConnectTiming BIGINT,
    ResponseStartTiming BIGINT,
    ResponseEndTiming BIGINT,
    FetchTiming BIGINT,
    SocialSourceNetworkID SMALLINT,
    SocialSourcePage VARCHAR(256),
    ParamPrice BIGINT,
    ParamOrderID VARCHAR(80),
    ParamCurrency CHAR(3),
    ParamCurrencyID INT,
    OpenstatServiceName VARCHAR(80),
    OpenstatCampaignID VARCHAR(512),
    OpenstatAdID VARCHAR(80),
    OpenstatSourceID VARCHAR(256),
    UTMSource VARCHAR(256),
    UTMMedium VARCHAR(256),
    UTMCampaign VARCHAR(407),
    UTMContent VARCHAR(256),
    UTMTerm VARCHAR(437),
    FromTag VARCHAR(428),
    HasGCLID SMALLINT,
    RefererHash BIGINT,
    URLHash BIGINT,
    CLID BIGINT,
    INDEX ColumnStoreIndex USING CLUSTERED COLUMNSTORE (CounterID, EventDate, UserID, EventTime)
);

Table creation takes about 15 seconds.

LOAD DATA INFILE '/opt/dumps/hits_10m_corrected.tsv' INTO TABLE hits_10m;

12 min 24.51 sec

13422 rows/sec.

data size: 1 613 773 528 bytes.