Memcache vs. Database for simple selects

db.c

/**
 * related blogpost at http://sidekick.windforwings.com/2012/04/memcache-vs-database-for-simple-selects.html
 */

#include <mysql.h>
#include <stdio.h>
#include <string.h>

int main(int argc, char **argv) {
        MYSQL *conn;
        MYSQL_RES *res;
        MYSQL_ROW row;
        char *server = "localhost";
        char *user = "root";
        char *password = ""; /* set me first */
        char *database = "test";
        int loopIndex = 0;
        int rowIndex = 0;
        int maxLoops = 10;
        int maxRecs = 10;

        fprintf(stderr, "argc=%d\n", argc);
        if(argc > 2) {
                maxLoops = atoi(argv[1]);
                maxRecs = atoi(argv[2]);
                fprintf(stderr, "%s,%s after atoi %d,%d\n", argv[1], argv[2], maxLoops, maxRecs);
        }

        conn = mysql_init(NULL);

        /* Connect to database */
        if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
                fprintf(stderr, "%s\n", mysql_error(conn));
                exit(1);
        }


        for(loopIndex=0; loopIndex < maxLoops; loopIndex++) {
                fprintf(stderr, "loopindex=%d\n", loopIndex);
                for(rowIndex=0; rowIndex < maxRecs; rowIndex++) {
                        char query[1024];

                        //fprintf(stderr, "rowindex=%d\n", rowIndex);
                        sprintf(query, "select * from LOAD_TEST where str_key='key%d'", rowIndex);

                        //if((rowIndex % 3000) == 0) fprintf(stderr, "%s\n", query);
                        if(mysql_query(conn, query)){
                                fprintf(stderr, "mysql_query error %s\n", mysql_error(conn));
                                exit(1);
                        }
                        res = mysql_use_result(conn);

                        while ((row = mysql_fetch_row(res)) != NULL) {
                                if((rowIndex % 3000) == 0) {
                                        printf("%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s\n", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10]);
                                }
                        }
                        mysql_free_result(res);
                }
        }

        /* close connection */
        mysql_close(conn);
}

gen_insert.sh

# related blogpost at http://sidekick.windforwings.com/2012/04/memcache-vs-database-for-simple-selects.html

echo "USE test;"

echo "SET autocommit = 0;"

for i in {0..150000}
do
   if !(( $i % 100 )); then
        if (( i > 0)); then
                echo "COMMIT;";
        fi
        echo "BEGIN;";
   fi
   echo "INSERT INTO LOAD_TEST VALUES('key${i}', 'str1${i}', 'str2${i}', 'str3${i}', 'str4${i}', 'str5${i}', 'str6${i}', 'str7${i}', 'str8${i}', 'str9${i}', 'str10${i}');"
done
echo "COMMIT;"

make_bin.sh

# related blogpost at http://sidekick.windforwings.com/2012/04/memcache-vs-database-for-simple-selects.html

export MSQL_CFLAGS="-I${MSQL_HOME}/include  -g -Os -arch i386 -fno-common -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL"

export MSQL_LIBS="-L${MSQL_HOME}/lib -lmysqlclient -lz -lm -lmygcc"

export MC_CFLAGS="-g -Os -fno-common"
export MC_LIBS="-lmemcached"

gcc -o db ${MSQL_CFLAGS} db.c ${MSQL_LIBS}
gcc -o mc ${MC_CFLAGS} mc.c ${MC_LIBS}

make_tables.sql

USE test;

DROP TABLE IF EXISTS LOAD_TEST;

CREATE TABLE IF NOT EXISTS LOAD_TEST (
        str_key CHAR(32) PRIMARY KEY,
        str1 CHAR(32),
        str2 CHAR(32),
        str3 CHAR(32),
        str4 CHAR(32),
        str5 CHAR(32),
        str6 CHAR(32),
        str7 CHAR(32),
        str8 CHAR(32),
        str9 CHAR(32),
        str10 CHAR(32)
)
ENGINE = MyISAM;

mc.c

/**
 * related blogpost at http://sidekick.windforwings.com/2012/04/memcache-vs-database-for-simple-selects.html
 */

#include <libmemcached/memcached.h>
#include <stdio.h>
#include <string.h>

typedef struct {
        char str_key[32];
        char str1[32];
        char str2[32];
        char str3[32];
        char str4[32];
        char str5[32];
        char str6[32];
        char str7[32];
        char str8[32];
        char str9[32];
        char str10[32];
} _rec;

int main(int argc, char **argv) {
        int loopIndex = 0;
        int rowIndex = 0;
        int maxLoops = 10;
        int maxRecs = 10;
        memcached_st * mc_conn;
        char * mc_conf = "--SERVER=localhost";

        fprintf(stderr, "argc=%d\n", argc);
        if(argc > 2) {
                maxLoops = atoi(argv[1]);
                maxRecs = atoi(argv[2]);
                fprintf(stderr, "%s,%s after atoi %d,%d\n", argv[1], argv[2], maxLoops, maxRecs);
        }

        mc_conn = memcached(mc_conf, strlen(mc_conf));
        if(NULL == mc_conn) {
                fprintf(stderr, "memcache connect failed for %s\n", mc_conf);
                exit(1);
        }

        for(loopIndex=0; loopIndex < maxLoops; loopIndex++) {
                fprintf(stderr, "loopindex=%d\n", loopIndex);
                for(rowIndex=0; rowIndex < maxRecs; rowIndex++) {
                        char key[80];
                        uint32_t flags;
                        memcached_return_t error;
                        size_t value_length;
                        _rec record;

                        sprintf(key, "key%d", rowIndex);
                        char value[512];
                        char * result = memcached_get(mc_conn, key, strlen(key), &value_length, &flags, &error);
                        /*
                        if(error != MEMCACHED_SUCCESS) {
                                fprintf(stderr, "memcache get failed : %s\n",  memcached_strerror(mc_conn, error));
                                exit(1);
                        }
                        */
                        if(NULL == result) {
                                /* no record. put it */
                                sprintf(record.str_key, "key%d", rowIndex);
                                sprintf(record.str1, "str%d0", rowIndex);
                                sprintf(record.str2, "str%d0", rowIndex*2);
                                sprintf(record.str3, "str%d0", rowIndex*3);
                                sprintf(record.str4, "str%d0", rowIndex*4);
                                sprintf(record.str5, "str%d0", rowIndex*5);
                                sprintf(record.str6, "str%d0", rowIndex*6);
                                sprintf(record.str7, "str%d0", rowIndex*7);
                                sprintf(record.str8, "str%d0", rowIndex*8);
                                sprintf(record.str9, "str%d0", rowIndex*9);
                                sprintf(record.str10, "str%d0", rowIndex*10);
                                error = memcached_set(mc_conn, record.str_key, strlen(record.str_key), (char *)&record, sizeof(record), 0, 0);
                                if(error != MEMCACHED_SUCCESS) {
                                        fprintf(stderr, "memcache put failed : %s\n",  memcached_strerror(mc_conn, error));
                                        exit(1);
                                }
                        }
                        else {
                                /* have record. print it */
                                memcpy(&record, result, sizeof(record));
                        }
                        if((rowIndex % 3000) == 0) {
                                fprintf(stderr, "%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s\n", record.str_key, record.str1, record.str2, record.str3, record.str4, record.str5, record.str6, record.str7, record.str8, record.str9, record.str10);
                        }
                }
        }

        /* close connection */
        memcached_free(mc_conn);
}