Go 서비스에서 DB 커넥션 풀 문제 해결기

time="2025-09-26 17:01:34.744" level=info msg="Primary DB Pool Status - Open: 38, InUse: 36, Idle: 2, WaitCount: 0, WaitDuration: 0s"
time="2025-09-26 17:01:34.744" level=info msg="Total DB Connections: 38"
time="2025-09-26 17:01:34.744" level=info msg="Total DB GetDB() calls: 727"
time="2025-09-26 17:01:34.744" level=warning msg="Primary DB connection pool usage high: 94.7% (36/38 connections in use)"
time="2025-09-26 17:02:04.755" level=info msg="Primary DB Pool Status - Open: 88, InUse: 88, Idle: 0, WaitCount: 0, WaitDuration: 0s"
time="2025-09-26 17:02:04.755" level=info msg="Total DB Connections: 88"
time="2025-09-26 17:02:04.755" level=info msg="Total DB GetDB() calls: 1750"
time="2025-09-26 17:02:04.755" level=warning msg="Primary DB connection pool usage high: 100.0% (88/88 connections in use)"

time="2025-09-26 16:59:12.175" level=info msg="Primary DB Pool Status - Open: 2, InUse: 0, Idle: 2, WaitCount: 0, WaitDuration: 0s"
time="2025-09-26 16:59:12.175" level=info msg="Total DB Connections: 2"
time="2025-09-26 16:59:12.175" level=info msg="Total DB GetDB() calls: 1080"
time="2025-09-26 17:00:02.175" level=info msg="Primary DB Pool Status - Open: 2, InUse: 0, Idle: 2, WaitCount: 0, WaitDuration: 0s"
time="2025-09-26 17:00:06.335" level=info msg="Total DB Connections: 2"
time="2025-09-26 17:00:06.335" level=info msg="Total DB GetDB() calls: 2136"

func StartConnectionPoolMonitoring() {
	enabled, err := config.GetConfigBool("connection_pool_monitoring")
	if err != nil || !enabled {
		log.Info("Connection pool monitoring is disabled")
		return
	}

	interval, err := config.GetConfigInt("connection_pool_monitoring_interval")
	if err != nil || interval <= 0 {
		interval = 10
	}

	log.Infof("Starting connection pool monitoring with %d second interval", interval)

	go func() {
		ticker := time.NewTicker(time.Duration(interval) * time.Second)
		defer ticker.Stop()

		for {
			select {
			case <-ticker.C:
				logConnectionPoolStatus()
			}
		}
	}()
}

import lrh "github.com/gfremex/logrus-rollingfile-hook"

var logger *log.Entry

func init() {
    HookFormatter := new(prefixed.TextFormatter)
    HookFormatter.TimestampFormat = "2006-01-02 15:04:05.000"
    HookFormatter.FullTimestamp = true
    HookFormatter.ForceFormatting = true
    
    hook, _ := lrh.NewTimeBasedRollingFileHook("tbrfh",
        []log.Level{log.DebugLevel, log.InfoLevel, log.WarnLevel, log.ErrorLevel},
        HookFormatter,
        os.Getenv("HOCTROOT")+"/"+"log/svclog_"+time.Now().Format("2006-01-02")+".log"
	)
    
    new_log := log.New()
    
    Formatter := new(log.TextFormatter)
    
    Formatter.TimestampFormat = HookFormatter.TimestampFormat

var logger *logrus.Logger
func init() {
    logger = logrus.New()
	
	logger.SetOutput(io.MultiWriter(
            os.Stdout,
            &lumberjack.Logger{
            Filename:   os.Getenv("HOCTROOT") + "/log/svclog_" + time.Now().Format("2006-01-02") + ".log",
            MaxSize:    100,  // MB
            MaxBackups: 90,   // Number of files to archive
            MaxAge:     90,   // deleted after 90 days
            Compress:   true, // Compression status
        },
    ))
	
    logger.SetFormatter(&logrus.TextFormatter{
        TimestampFormat: "2006-01-02 15:04:05.000",
        FullTimestamp:   true,
        DisableColors:   true,
    })

// ExamCache manages exam record caching with TTL and LRU(Least Recently Used) eviction
// 메모리 기능을 담당할 items, 동시성 이슈를 위해 mutex, 캐시의 지속 시간을 위한 ttl, 주기 적인 메모리 청소를 위한 cleanup
type ExamCache struct 
	items   map[int]*ExamCacheItem
	mutex   sync.RWMutex
	maxSize int
	ttl     time.Duration
	cleanup *time.Ticker
}

// NewExamCache creates a new exam cache with specified TTL and max size
// 캐시 생성
func NewExamCache(ttl time.Duration, maxSize int) *ExamCache {
	cache := &ExamCache{
		items:   make(map[int]*ExamCacheItem),
		maxSize: maxSize,
		ttl:     ttl,
	}

	// Start to clean up goroutine
	cache.cleanup = time.NewTicker(5 * time.Minute)
	go cache.cleanupExpired()
	return cache
}

// Get retrieves an exam record from cache
// 캐시에서 데이터 가져오기
func (c *ExamCache) Get(examID int) (ExamCacheRecord, bool) {
	c.mutex.Lock()
	defer c.mutex.Unlock()

	item, exists := c.items[examID]
	if !exists {
		return ExamCacheRecord{}, false
	}

	// Check if expired
	if time.Now().After(item.ExpireTime) {
		return ExamCacheRecord{}, false
	}

	// Update access time for LRU
	// 데이터가 있을 경우 LRU을 위해 Acess 시간을 업데이트 했습니다.
	item.AccessTime = time.Now()
	return item.Record, true
}

// Set stores an exam record in cache
func (c *ExamCache) Set(examID int, record ExamCacheRecord) {
	c.mutex.Lock()
	defer c.mutex.Unlock()

	now := time.Now()
	c.items[examID] = &ExamCacheItem{
		Record:     record,
		ExpireTime: now.Add(c.ttl), // TTL
		AccessTime: now,            // LRU
	}

	// Evict the oldest if the cache is full
	// 캐시의 크기가 다 찼을 경우 evictOldest를 실행
	if len(c.items) > c.maxSize {
		c.evictOldest()
	}
}

// evictOldest removes the least recently used item
// This function must be called while holding a write lock
// AccessTime 을 기준으로 가장 오래된 데이터 부터 삭제 합니다
func (c *ExamCache) evictOldest() {
	if len(c.items) == 0 {
		return
	}

	var oldestKey int
	var oldestTime time.Time
	first := true

	for key, item := range c.items {
		if first || item.AccessTime.Before(oldestTime) {
			oldestKey = key
			oldestTime = item.AccessTime
			first = false
		}
	}

	delete(c.items, oldestKey)
}

// cleanupExpired removes expired items periodically
// 업데이트 로직에 대처를 전부 해뒀지만 혹시 모르기에 데이터 무결성을 지키위해 
// 이 부분에서는 AccessTime이 아닌 TTL을 기준으로만 데이터를 지우게 해두었습니다.
func (c *ExamCache) cleanupExpired() {
	for range c.cleanup.C {
		c.mutex.Lock()
		now := time.Now()
		for key, item := range c.items {
			if now.After(item.ExpireTime) {
				delete(c.items, key)
			}
		}
		c.mutex.Unlock()
	}
}

// Close stop the cleanup goroutine
func (c *ExamCache) Close() {
if c.cleanup != nil {
c.cleanup.Stop()
}
}

// Invalidate removes specific exam record from cache
// 업데이트시도 이 기능을 사용해서 캐싱에서 지우는 걸로 진행
func (c *ExamCache) Invalidate(examID int) {
c.mutex.Lock()
defer c.mutex.Unlock()
delete(c.items, examID)
}

err := database.GetDB().QueryRow(query,
    &r.Iol_License_Tbl_Serial_id,
    &r.Device_Name,
    &r.Device_Serial_Number,
    r.Serial_Id,
).Err()

_, err := database.GetDB().Exec(query,
    &r.Iol_License_Tbl_Serial_id,
    &r.Device_Name,
    &r.Device_Serial_Number,
    r.Serial_Id,
)