测试sqlite3 插入性能支持多进程

#define _GNU_SOURCE
#include <sched.h>
#include <stdlib.h>
#include <unistd.h>
#include <stdio.h>
#include <assert.h>
#include <pthread.h>
#include <string.h>
#include <stdio.h>
//#include "sqlite3.h"
#include <sqlite3.h>
#include <stdlib.h>
#include <pthread.h>
#include <string.h>
#include<sys/time.h>
#include<unistd.h>
#include <netinet/in.h>
#include <arpa/inet.h>
extern int select_callback(void * data, int col_count, char ** col_values, char ** col_Name);


struct _thread_tmp{
        pthread_t thread;
        int cpu;
};

sqlite3 *db;
int g_count = 250000;

void *thread_func(void *param)
{
    int cpu = *(int *)param;
    cpu_set_t mask;
    cpu_set_t get;
    int num = sysconf(_SC_NPROCESSORS_CONF);
    int j;
    int cpu_real = 0;

    CPU_ZERO(&mask);
    CPU_SET(cpu, &mask);

    if (pthread_setaffinity_np(pthread_self(), sizeof(mask), &mask) < 0) {
        perror("pthread_setaffinity_np");
    }
    CPU_ZERO(&get);
    if (pthread_getaffinity_np(pthread_self(), sizeof(get), &get) < 0) {
        fprintf(stderr, "get thread affinity failed\n");
    }
    for (j = 0; j < num; j++) {
        if (CPU_ISSET(j, &get)) {
            cpu_real = j;
            //printf("thread %d is running in processor %d\n", (int)pthread_self(), j);
        }
    }


    char sql[1024];
    struct  timeval    start;
    struct  timeval end;
    int i;
    int result = 0;
    char *ErrMsg = NULL;
    memset(sql, 0, sizeof(sql));
    gettimeofday(&start,NULL);
    for(i = cpu; i < g_count; i = i+3){
#ifdef STRING
        char IPdotdec[20];
        struct in_addr s;
        s.s_addr = i;
        inet_ntop(AF_INET,(void *)&s,IPdotdec,16);
        snprintf(sql,sizeof(sql),"insert into ct values('%s',%d,'%s',%d)", IPdotdec,i,IPdotdec,i);
#else
        snprintf(sql,sizeof(sql),"insert into ct values(%d,%d,%d,%d)", i, i, i,i);
        //snprintf(sql,sizeof(sql),"insert into ct values(%d)", i);
#endif
        result = sqlite3_exec(db,sql,NULL,NULL,&ErrMsg);
        if(result != SQLITE_OK && SQLITE_BUSY) {
            printf(" err %s\n",sql);
            sqlite3_free(&ErrMsg);
            sqlite3_close(db);
            pthread_exit(NULL);
        }
    }

    gettimeofday(&end,NULL);
    int diff = 1000000 * (end.tv_sec-start.tv_sec)+ end.tv_usec-start.tv_usec;
    printf("thread%d thedifference is %d\n",cpu_real, diff);

    pthread_exit(NULL);

}


int main(int argc, char *argv[])
{
    char *ErrMsg = NULL;
    int result = 0;
    char sql[1024];
    //char *path = "./lwhtest.db";
    char *path = NULL;
    struct  timeval    start;
    struct  timeval end;
    int i;
    cpu_set_t mask;
    CPU_ZERO(&mask);
    CPU_SET(0, &mask);
    if (sched_setaffinity(0, sizeof(mask), &mask) < 0) {
        perror("sched_setaffinity");
    }

#ifdef SIGNAL
    sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
    printf("signal\n");
#else
    printf("Serialized\n");
#endif

    result = sqlite3_open(path,&db);
    if (result != SQLITE_OK) {
        return -1;
    }
    //sqlite3_exec(db, "PRAGMA synchronous = OFF", 0,0,0);
#ifdef NJOURNAL
    sqlite3_exec(db, "PRAGMA journal_mode = OFF", 0, 0 ,0);
    printf("journal off\n");
#else
    printf("journal on\n");
#endif
#ifdef STRING
    snprintf(sql,sizeof(sql),"create table if not exists ct(sip text, sport int, dip text, dport int,primary key(sip))");    /*string*/
    printf("string\n");
#else
    snprintf(sql,sizeof(sql),"create table if not exists ct(sip int, sport int, dip int, dport int,primary key(sip))"); /* int */
    //snprintf(sql,sizeof(sql),"create table if not exists ct(sip int, sport int, dip int, dport int)");
    //snprintf(sql,sizeof(sql),"create table if not exists ct(sip int)");
    //snprintf(sql,sizeof(sql),"create table if not exists ct(sip int,primary key(sip))");
    printf("no string\n");
#endif
    result = sqlite3_exec(db,sql,NULL,NULL,&ErrMsg);
    if(result != SQLITE_OK && SQLITE_BUSY) {
        printf(" err %s\n",sql);
        sqlite3_free(ErrMsg);
        sqlite3_close(db);
        return -1;
    }
    gettimeofday(&start,NULL);
#ifdef THREAD
    struct _thread_tmp t[3];

    for(i =0; i < 3;i++){
        t[i].cpu = i+1;
        if (pthread_create(&t[i].thread, NULL, thread_func, &t[i].cpu) != 0) {
            perror("pthread_create");
        }
    }

    for(i =0; i < 3;i++){
        pthread_join(t[i].thread, NULL);
    }
#else
    for(i = 0; i < g_count; i++){
#ifdef STRING
        char IPdotdec[20];
        struct in_addr s;
        s.s_addr = i;
        inet_ntop(AF_INET,(void *)&s,IPdotdec,16);
        snprintf(sql,sizeof(sql),"insert into ct values('%s',%d,'%s',%d)", IPdotdec,i,IPdotdec,i);
#else
        snprintf(sql,sizeof(sql),"insert into ct values(%d,%d,%d,%d)", i,i,i,i);
#endif
        //snprintf(sql,sizeof(sql),"insert into ct values(%d)", i);
        result = sqlite3_exec(db,sql,NULL,NULL,&ErrMsg);
        if(result != SQLITE_OK && SQLITE_BUSY) {
            printf(" err %s\n",sql);
            sqlite3_free(ErrMsg);
            sqlite3_close(db);
            return -1;
        }
    }
#endif
    gettimeofday(&end,NULL);
    int diff = 1000000 * (end.tv_sec-start.tv_sec)+ end.tv_usec-start.tv_usec;
    printf("thedifference is %d\n",diff);

    memset(sql, 0, sizeof(sql));
    snprintf(sql,sizeof(sql),"select count(*) from ct");
    result = sqlite3_exec(db,sql,select_callback,NULL,&ErrMsg);
    if(result != SQLITE_OK) {
        sqlite3_free(ErrMsg);
        sqlite3_close(db);
        return -1;
    }

    memset(sql, 0, sizeof(sql));
    snprintf(sql,sizeof(sql),"delete from  ct");
    result = sqlite3_exec(db,sql,NULL,NULL,&ErrMsg);
    if(result != SQLITE_OK) {
        sqlite3_free(ErrMsg);
        sqlite3_close(db);
        return -1;
    }

    sqlite3_close(db);

    pthread_exit(NULL);

}

int select_callback(void * data, int col_count, char ** col_values, char ** col_Name)
{
  // 每条记录回调一次该函数,有多少条就回调多少次
  int i;
  for( i=0; i < col_count; i++){
    printf( "count %d\n", atoi(col_values[i]));
  }

  return 0;
}

保存到文件sqlite_memory.c
使用通用makefile
make clean;make string=1 thread=1 signal=1 njournal=1;./sqlite_memory