<< OpenMP를 이용한 병렬 프로그래밍 | Home | SQL과 NoSQL의 장점을 결합한 NewSQL에 대해 >>

MySQL++과 OpenMP를 활용한 MySQL Benchmark 검토

OpenMP를 활용해서 병렬 기능이 추가된 Benchmark 샘플과 별렬 기능 없이 멀티 커넥션만으로 작성한 샘플의 성능 비교를 통해 OpenMP의 가능성을 진단해 보고자 한다.
그리고 OpenMP의 활용처를 찾아보는데 의의가 있다고 볼 수도 있다.

그 외에 File I/O가 많이 일어나는 분산파일 시스템(C/C++ 기반)에도 적용한다면 좋은 효과를 볼 수 있을 거 같다. 잘만 생각해보면 요긴하게 쓰일 놈이다.

참고로 MySQL++은 MySQL의 C API를 wrapper 한 C++ 클라이언트 라이브러리다.

MySQL과 MySQL++ 설치

1) MySQL 설치
> wget http://dev.mysql.com/get/Downloads/MySQL-5.5/
  mysql-5.5.25.tar.gz/from/http://ftp.jaist.ac.jp/pub/mysql/
> tar xvfz mysql-5.5.25.tar.gz
> cd mysql-5.5.25
> cmake -DCMAKE_INSTALL_PREFIX=/database/server/mysql-5.5.25 \
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DMYSQL_DATADIR=/database/data2 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1
> make; sudo make install
> cp support-files/my-large.cnf /etc/mysql-5.5.25/
> mv /etc/mysql-5.5.25/my-large.cnf /etc/mysql-5.5.25/my.cnf
> vi /etc/mysql-5.5.25/my.cnf
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
> chmod 755 scripts/mysql_install_db
> scripts/mysql_install_db --defaults-file=/etc/mysql-5.5.25/my.cnf \
--basedir=/database/server/mysql-5.5.25 --datadir=/database/data2 \
--user=mysql --pid-file=/etc/mysql-5.5.25/mysql.pid --port=3308 \
--socket=/etc/mysql-5.5.25/mysql.sock
> bin/mysqld_safe --defaults-file=/etc/mysql-5.5.25/my.cnf \
--datadir=/database/data2 --user=mysql &
> bin/mysqladmin -u root password 'new-password'
> mysql -uroot -p
mysql> use test;
mysql> CREATE TABLE user(
id bigint(20) NOT NULL AUTO_INCREMENT,
last_name varchar(256) DEFAULT NULL,
first_name varchar(256) DEFAULT NULL,
email varchar(64) DEFAULT NULL,
PRIMARY KEY (id),
KEY USER_INDEX01 (first_name,last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
> grant all on test.* to 'testuser'@'localhost' identified by 'testadmin';

2) MySQL++ 설치
> wget http://www.tangentsoft.net/mysql++/releases/mysql++-3.1.0.tar.gz
> tar xvfz mysql++-3.1.0.tar.gz
> cd mysql++-3.1.0
> ./configure --prefix=/database/server/mysqlplus \
--with-mysql=/database/server/mysql-5.5.25
> make; make install

성능 테스트 케이스별 소스

1) OpenMP 반영 안한 Single Multi Connection Benchmark 샘플
int read_single_use_multi_connect(vector & user)
{

  time_t start = time (NULL);
  // multi connect start
  vector  dbConn(SMALL_MAX_ROW);
  int j = 0;
  for (vector ::iterator it = dbConn.begin(); 
   it != dbConn.end(); ++it)
  {
    cout << "connect ->"<< (j++) << endl;
    *it = new Connection(false);
    (*it)->connect("test", "localhost", "testuser", "testadmin");
    if (!(*it)->ping())
    {
      cout << (*it) -> error() << endl;
      Abort("cannot connect for benchmarking");
    }
  }
  // multi connect end

  UseQueryResult results;
  int success = 0;
  for (int i = 0; i < MAX_ROW; ++i)
  {
    if (!dbConn.at(i % SMALL_MAX_ROW)->ping())
    {
      cout << dbConn.at(i % SMALL_MAX_ROW)->error() << endl;
      Abort( "cannot connect for benchmarking");
    }
    Query query = dbConn.at(i % SMALL_MAX_ROW)->query();
    Row row;
    int key = rand() % MAX_ROW;
    query << "SELECT SQL_NO_CACHE uid, last_name, first_name, email \
      FROM user WHERE uid =" << key + 1;
    results = query.use();
    if (results)
    {
      while (row = results.fetch_row())
      {
        string value;
        row["first_name"].to_string(value);
        if (user.at(key) == value)
          ++success;
      }
    } else {
      cout << "cannot fetch results for key [" << key << "]"<< endl;
      cout << "reason :" << query.error() << endl;
    }
  }

  time_t end = time(NULL);
  cout << "success :"<< success << " in " << MAX_ROW << " = "
   << (double) success / MAX_ROW * 100 << "% Ok." << endl;
  cout << "time =" << (end - start) << " sec" << endl;
  return (0);
}
////////////////////////// Main
int main()
{
  try {
    vector user(MAX_ROW);
    generate_data(user);
    read_single_use_multi_connect(user);
    //read_parallel_use_multi_connect(user);
  } catch (const Exception& er) {
    cerr << "Error: " << er.what() << endl;
    return -1;
  }

  return(0);
}

2) OpenMP 반영한 Parallel Multi Connection Benchmark 샘플
int read_parallel_use_multi_connect(vector & user)
{

  time_t start = time (NULL);
  // multi connect start
  vector  dbConn(SMALL_MAX_ROW);
  int j = 0;
  for (vector ::iterator it = dbConn.begin(); 
   it != dbConn.end(); ++it)
  {
    cout << "connect ->"<< (j++) << endl;
    *it = new Connection(false);
    (*it)->connect("test", "localhost", "testuser", "testadmin");
    if (!(*it)->ping())
    {
      cout << (*it) -> error() << endl;
      Abort("cannot connect for benchmarking");
    }
  }

  // multi connect end

  UseQueryResult results;
  int success = 0;
# pragma omp parallel for shared(dbConn) 
 private(results) reduction (+:success)
  for (int i = 0; i < MAX_ROW; ++i)
  {
    if (!dbConn.at(omp_get_thread_num())->ping())
    {
      cout << dbConn.at(omp_get_thread_num())->error() << endl;
      Abort( "cannot connect for benchmarking");
    }
    Query query = dbConn.at(omp_get_thread_num())->query();
    Row row;
    int key = rand() % MAX_ROW;
    query << "SELECT SQL_NO_CACHE uid, last_name, first_name, email \
     FROM user WHERE uid =" << key + 1;
    results = query.use();
    if (results)
    {
      while (row = results.fetch_row())
      {
        string value;
        row["first_name"].to_string(value);
        if (user.at(key) == value)
          ++success;
      }
    } else {
      cout << "cannot fetch results for key [" << key << "]"<< endl;
      cout << "reason :" << query.error() << endl;
    }
  }

  time_t end = time(NULL);
  cout << "success :"<< success << " in " << MAX_ROW << " = "
   << (double) success / MAX_ROW * 100 << "% Ok." << endl;
  cout << "time =" << (end - start) << " sec" << endl;
  return (0);
}
////////////////////////// Main
int main()
{
  try {
    vector user(MAX_ROW);
    generate_data(user);
    //read_single_use_multi_connect(user);
    read_parallel_use_multi_connect(user);
  } catch (const Exception& er) {
    cerr << "Error: " << er.what() << endl;
    return -1;
  }

  return(0);
}

3) 컴파일
> g++ -D_REENTRANT -D_PTHREADS -Wno-deprecated \
 -I/database/server/mysql-5.5.25/include \
 -I/database/server/mysqlplus/include/mysql++ \
 -DOMP_UNIX -c mysql-benchmark.c -I. -fopenmp 
g++ -o mysql-benchmark mysql-benchmark.o \
-L/database/server/mysql-5.5.25/lib \
-L/database/server/mysqlplus/lib \
-lmysqlpp -lmysqlclient -lnsl -lz -lm -fopenmp

성능 테스트 결과

1) OpenMP 반영 안한 Single Multi Connection Benchmark 테스트 결과
> ./mysql-benchmark
connect ->0
connect ->1
connect ->2
connect ->3
success :20000 in 20000 = 100% Ok.
time =3 sec

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|    20000 |
+----------+

2) OpenMP 반영한 Parallel Multi Connection Benchmark 테스트 결과
> ./mysql-benchmark
connect ->0
connect ->1
connect ->2
connect ->3
success :20000 in 20000 = 100% Ok.
time =0 sec

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|    20000 |
+----------+

요약하자면

조회 쿼리에서 쿼리 캐시 안되게 해서 테스트를 했는데 결과는 OpenMP를 활용한 프로그래밍이 훨씬 빨랐다. ^^ OpenMP 반영시 20,000건 조회시 0초, OpenMP 반영 안했을 경우는 3초가 걸렸다.
CPU수가 늘어나고 데이터량이 커지면 차이는 훨씬 더 날 것으로 보인다. 역시 C언어로 Benchmark 프로그램을 작성할 때는 CRUD 과정에 OpenMP를 활용해서 병렬처리를 추가해 주면 성능 평가가 좀 더 객관적인 자료가 되지 않을까 생각해 보게 된다.
Tags : ,


Avatar: ijal

Re: MySQL++과 OpenMP를 활용한 MySQL Benchmark 검토

would you send to the complete code of the mysql-benchmark?  it always return to error when i compiling it, what kind of header it must be there. i was adding #include <vector> and using namspace std but still doesn't work


Add a comment Send a TrackBack