<div id="readability-page-1" class="page"><div id="SE-5f769c8e-c9aa-403b-a795-1054d8a788c8">
                                    <p id="SE-5402b606-9488-48b5-83c0-565fb767c519"><span id="SE-7d041e85-fd3b-4fdc-8855-d86706f8e5a7">안녕하세요 멍개입니다. </span></p><p id="SE-f09cd98b-cca2-4e95-bec1-457b5bb3f35a"><span id="SE-2b340f87-1cc2-4544-9612-7feb126a9424">​</span></p><p id="SE-e789b114-c40e-4228-9416-20918fc54f0e"><span id="SE-3543359b-4dc0-4111-8c83-58ba7659f77c">최근에 회사에서 프로젝트를 하면서 부하테스트를 진행하였는데 mongodb의 aggregation lookup이 사용된 API에서 병목이 발생을 하였습니다. </span></p><p id="SE-e939ad83-de55-490c-8631-ec2ca46e717b"><span id="SE-6b247513-2fbd-4e18-a268-4f121c5ed2ee">​</span></p><p id="SE-73d2ee90-1d10-4ec6-ade6-acbefb43c0d0"><span id="SE-1d933ec7-0ec0-474c-9410-0a2adeccc0d1">이 당시 개발팀에서는 rdb로 마이그레이션을 하기엔 시간이 부족하여 redis를 기반으로 캐싱 처리하여 해결하였지만, 이를 rdb가 지원하는 join으로 변경하면 어느정도 성능 퍼포먼스 향상이 있을지 궁금하여 2개의 상황을 밴치마킹 해보았습니다.</span></p><p id="SE-8018420f-13c1-4af3-ba85-aa784d1d2e6e"><span id="SE-f1e18c9f-65e8-4b9a-af4d-b8a380a3aa56">​</span></p><p id="SE-572c7edb-a120-455a-85a8-10e4bfe517ea"><span id="SE-1d4d8d71-8411-408e-91a3-bf2ea28c45b8"><b>· 환경구성</b></span></p><p id="SE-4f0744f3-1b92-4f3b-ba99-3ca79673356a"><span id="SE-519e1481-b986-4fb8-b724-f028523fc3ff">2개의 디비 환경은 docker-compose를 기반으로 구축합니다.</span></p>
                            </div><div id="SE-51f4f448-ac0e-4642-9f2a-2385d6463417">
                                    <p id="SE-1625ec7a-7e8d-46e5-b20d-f408948d29c1"><span id="SE-3bebf8d9-e924-4f5c-ba1b-848fb02cc09a"><b>· 컬렉션 / 테이블 생성</b></span></p><p id="SE-092ab436-8ae1-4533-9358-89110512fb61"><span id="SE-e22f6ade-ee96-4efc-9b2b-79438c181b29">테이블(컬렉션)은 a, b, c, d, e, f, g 총 7개이며 g는 f를 참조하고 f는 e를 참조하는 형태로 관계를 가집니다.</span></p>
                            </div><div id="SE-fb7759fd-b721-4745-8dc7-08cab6c28431">
                                    <p>import mongoose from 'mongoose';

const aSchema = new mongoose.Schema({
  name: { type: String, required: true },
  value: { type: Number, required: true },
  createdAt: { type: Date, default: Date.now }
});

const bSchema = new mongoose.Schema({
  aId: { type: mongoose.Schema.Types.ObjectId, ref: 'A', required: true },
  name: { type: String, required: true },
  value: { type: Number, required: true },
  createdAt: { type: Date, default: Date.now }
});

const cSchema = new mongoose.Schema({
  bId: { type: mongoose.Schema.Types.ObjectId, ref: 'B', required: true },
  name: { type: String, required: true },
  value: { type: Number, required: true },
  createdAt: { type: Date, default: Date.now }
});

const dSchema = new mongoose.Schema({
  cId: { type: mongoose.Schema.Types.ObjectId, ref: 'C', required: true },
  name: { type: String, required: true },
  value: { type: Number, required: true },
  createdAt: { type: Date, default: Date.now }
});

const eSchema = new mongoose.Schema({
  dId: { type: mongoose.Schema.Types.ObjectId, ref: 'D', required: true },
  name: { type: String, required: true },
  value: { type: Number, required: true },
  createdAt: { type: Date, default: Date.now }
});

const fSchema = new mongoose.Schema({
  eId: { type: mongoose.Schema.Types.ObjectId, ref: 'E', required: true },
  name: { type: String, required: true },
  value: { type: Number, required: true },
  createdAt: { type: Date, default: Date.now }
});

const gSchema = new mongoose.Schema({
  fId: { type: mongoose.Schema.Types.ObjectId, ref: 'F', required: true },
  name: { type: String, required: true },
  value: { type: Number, required: true },
  createdAt: { type: Date, default: Date.now }
});

export const A = mongoose.model('A', aSchema);
export const B = mongoose.model('B', bSchema);
export const C = mongoose.model('C', cSchema);
export const D = mongoose.model('D', dSchema);
export const E = mongoose.model('E', eSchema);
export const F = mongoose.model('F', fSchema);
export const G = mongoose.model('G', gSchema);</p>
                                </div><div id="SE-60d403f1-cf19-4aa0-bbc0-e80197464d10">
                            <p id="SE-be988b2d-a03f-409f-826a-8e1ac2960b61"><span id="SE-ddf94234-9a83-4698-98c8-bffc9355a0cc">각 테이블(컬렉션)에는 10,000개의 row(document)를 넣어줍니다.</span></p>
                        </div><div id="SE-e19ea335-3b22-4004-85bb-72211055fe25">
                                    <p>import { connectMongoDB, connectPostgreSQL, disconnectMongoDB, disconnectPostgreSQL } from './db-connection.js';
import { A, B, C, D, E, F, G } from './models/mongodb.js';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));

const RECORDS_PER_TABLE = 10000;

async function seedMongoDB() {
  console.log('Starting MongoDB seeding...');
  
  await A.deleteMany({});
  await B.deleteMany({});
  await C.deleteMany({});
  await D.deleteMany({});
  await E.deleteMany({});
  await F.deleteMany({});
  await G.deleteMany({});
  
  const aRecords = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const a = await A.create({
      name: `A_Record_${i}`,
      value: Math.floor(Math.random() * 1000)
    });
    aRecords.push(a);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in collection A`);

  const bRecords = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomA = aRecords[Math.floor(Math.random() * aRecords.length)];
    const b = await B.create({
      aId: randomA._id,
      name: `B_Record_${i}`,
      value: Math.floor(Math.random() * 1000)
    });
    bRecords.push(b);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in collection B`);

  const cRecords = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomB = bRecords[Math.floor(Math.random() * bRecords.length)];
    const c = await C.create({
      bId: randomB._id,
      name: `C_Record_${i}`,
      value: Math.floor(Math.random() * 1000)
    });
    cRecords.push(c);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in collection C`);

  const dRecords = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomC = cRecords[Math.floor(Math.random() * cRecords.length)];
    const d = await D.create({
      cId: randomC._id,
      name: `D_Record_${i}`,
      value: Math.floor(Math.random() * 1000)
    });
    dRecords.push(d);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in collection D`);

  const eRecords = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomD = dRecords[Math.floor(Math.random() * dRecords.length)];
    const e = await E.create({
      dId: randomD._id,
      name: `E_Record_${i}`,
      value: Math.floor(Math.random() * 1000)
    });
    eRecords.push(e);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in collection E`);

  const fRecords = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomE = eRecords[Math.floor(Math.random() * eRecords.length)];
    const f = await F.create({
      eId: randomE._id,
      name: `F_Record_${i}`,
      value: Math.floor(Math.random() * 1000)
    });
    fRecords.push(f);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in collection F`);

  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomF = fRecords[Math.floor(Math.random() * fRecords.length)];
    await G.create({
      fId: randomF._id,
      name: `G_Record_${i}`,
      value: Math.floor(Math.random() * 1000)
    });
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in collection G`);
  
  console.log('MongoDB seeding completed!');
}

async function seedPostgreSQL(pool) {
  console.log('Starting PostgreSQL seeding...');
  
  const sqlSchema = fs.readFileSync(path.join(__dirname, 'models/postgresql.sql'), 'utf-8');
  await pool.query(sqlSchema);
  console.log('PostgreSQL schema created');
  
  await pool.query('DELETE FROM g');
  await pool.query('DELETE FROM f');
  await pool.query('DELETE FROM e');
  await pool.query('DELETE FROM d');
  await pool.query('DELETE FROM c');
  await pool.query('DELETE FROM b');
  await pool.query('DELETE FROM a');
  
  const aIds = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const result = await pool.query(
      'INSERT INTO a (name, value) VALUES ($1, $2) RETURNING id',
      [`A_Record_${i}`, Math.floor(Math.random() * 1000)]
    );
    aIds.push(result.rows[0].id);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in table a`);

  const bIds = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomAId = aIds[Math.floor(Math.random() * aIds.length)];
    const result = await pool.query(
      'INSERT INTO b (a_id, name, value) VALUES ($1, $2, $3) RETURNING id',
      [randomAId, `B_Record_${i}`, Math.floor(Math.random() * 1000)]
    );
    bIds.push(result.rows[0].id);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in table b`);

  const cIds = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomBId = bIds[Math.floor(Math.random() * bIds.length)];
    const result = await pool.query(
      'INSERT INTO c (b_id, name, value) VALUES ($1, $2, $3) RETURNING id',
      [randomBId, `C_Record_${i}`, Math.floor(Math.random() * 1000)]
    );
    cIds.push(result.rows[0].id);
  }
  console.log(`Created ${RECORDS_PER_TABLE} records in table c`);

  const dIds = [];
  for (let i = 0; i &lt; RECORDS_PER_TABLE; i++) {
    const randomCId </p>
                                </div><div id="SE-f0141291-c54b-4ad9-be4d-6795e0220f68">
                                    <p>import { connectMongoDB, connectPostgreSQL, disconnectMongoDB, disconnectPostgreSQL } from './db-connection.js';
import { A } from './models/mongodb.js';

async function benchmarkMongoDB() {
  console.log('\n=== MongoDB Aggregation Lookup Benchmark ===');
  
  const iterations = 100;
  const times = [];
  
  for (let i = 0; i &lt; iterations; i++) {
    const startTime = performance.now();
    
    await A.aggregate([
      {
        $lookup: {
          from: 'bs',
          localField: '_id',
          foreignField: 'aId',
          as: 'b_data'
        }
      },
      {
        $unwind: {
          path: '$b_data',
          preserveNullAndEmptyArrays: false
        }
      },
      {
        $lookup: {
          from: 'cs',
          localField: 'b_data._id',
          foreignField: 'bId',
          as: 'c_data'
        }
      },
      {
        $unwind: {
          path: '$c_data',
          preserveNullAndEmptyArrays: false
        }
      },
      {
        $lookup: {
          from: 'ds',
          localField: 'c_data._id',
          foreignField: 'cId',
          as: 'd_data'
        }
      },
      {
        $unwind: {
          path: '$d_data',
          preserveNullAndEmptyArrays: false
        }
      },
      {
        $lookup: {
          from: 'es',
          localField: 'd_data._id',
          foreignField: 'dId',
          as: 'e_data'
        }
      },
      {
        $unwind: {
          path: '$e_data',
          preserveNullAndEmptyArrays: false
        }
      },
      {
        $lookup: {
          from: 'fs',
          localField: 'e_data._id',
          foreignField: 'eId',
          as: 'f_data'
        }
      },
      {
        $unwind: {
          path: '$f_data',
          preserveNullAndEmptyArrays: false
        }
      },
      {
        $lookup: {
          from: 'gs',
          localField: 'f_data._id',
          foreignField: 'fId',
          as: 'g_data'
        }
      },
      {
        $unwind: {
          path: '$g_data',
          preserveNullAndEmptyArrays: false
        }
      },
      {
        $limit: 100
      }
    ]).exec();
    
    const endTime = performance.now();
    times.push(endTime - startTime);
    
    if ((i + 1) % 10 === 0) {
      console.log(`Progress: ${i + 1}/${iterations} iterations completed`);
    }
  }
  
  return calculateStats(times);
}

async function benchmarkPostgreSQL(pool) {
  console.log('\n=== PostgreSQL JOIN Benchmark ===');
  
  const iterations = 100;
  const times = [];
  
  const query = `
    SELECT 
      a.*, 
      b.name as b_name, b.value as b_value,
      c.name as c_name, c.value as c_value,
      d.name as d_name, d.value as d_value,
      e.name as e_name, e.value as e_value,
      f.name as f_name, f.value as f_value,
      g.name as g_name, g.value as g_value
    FROM a
    INNER JOIN b ON a.id = b.a_id
    INNER JOIN c ON b.id = c.b_id
    INNER JOIN d ON c.id = d.c_id
    INNER JOIN e ON d.id = e.d_id
    INNER JOIN f ON e.id = f.e_id
    INNER JOIN g ON f.id = g.f_id
    LIMIT 100
  `;
  
  for (let i = 0; i &lt; iterations; i++) {
    const startTime = performance.now();
    
    await pool.query(query);
    
    const endTime = performance.now();
    times.push(endTime - startTime);
    
    if ((i + 1) % 10 === 0) {
      console.log(`Progress: ${i + 1}/${iterations} iterations completed`);
    }
  }
  
  return calculateStats(times);
}</p>
                                </div><div id="SE-6ebf6c30-85cf-417d-9a87-9885a6575a6a">
                            <p id="SE-eec69163-6d4e-4892-bbe3-e097e18d4712"><span id="SE-24ebee26-2962-4916-a630-142333886803">해당 코드를 실행하면 다음과 같이 결과를 확인할 수 있습니다.</span></p>
                        </div></div>

삽질하는 멍개씨

안녕하세요 멍개입니다. 최근에 회사에서 프로젝트를 하면서 부하테스트를 진행하였는데 mongodb의 aggregation lookup이 사용된 API에서 병목이 발생을 하였습니다. 이 당시 개발팀에서는 rdb로 마이그레이션을 하기엔 시간이 부족하여 redis를 기반으로 캐싱 처리하여 해결하였지만, 이를 rdb가 지원하는 join으로 변경하면 어느정도 성능 퍼포먼스 향상이 있을지 궁금하여 2개의 상황을 밴치마킹 해보았습니다. · 환경구성 2개의 디비 환경은 docker-compose를 기반으로 구축합니다. · 컬렉션 / 테이블 생성 테이블(컬렉션)은 a, b, c, d, e, f, g 총 7개이며 g는 f를 참조하고 f는 e를 참조하는 형태로 관계를 가집니다. · seeding 각 테이블.......

mongodb의 aggregation과 postgresql의 join 비교하기

댓글