mysql Out of memory 에러 관련
글쓴이: sangjin0822 / 작성시간: 금, 2011/12/30 - 2:39오후
안녕하세요.
mysql 5.1.55 버전을 사용하고 있습니다.
메모리는 8G로 사용 중인데, 최근 5일동안 매일 오후 시간에 아래와 같은 로그와 함께 mysql이 비정상적으로 작동합니다.
mysql을 재구동하면 바로 정상적으로 이용이 가능하지만, 근본적인 원인을 찾아 해결하려 합니다.
고수님의 도움 좀 부탁드립니다.
참고로 innodb 엔진을 사용하고 있습니다.
- mysql error log
111230 13:03:20 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677708 bytes) 111230 13:03:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:24 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:24 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:26 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1675044 bytes) 111230 13:03:32 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1675264 bytes) 111230 13:03:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1675044 bytes) 111230 13:03:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1419572 bytes) 111230 13:03:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1611904 bytes) 111230 13:03:35 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1454024 bytes) 111230 13:03:35 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1454024 bytes) 111230 13:03:35 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:37 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677456 bytes) 111230 13:03:37 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677720 bytes) 111230 13:03:38 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1454024 bytes) 111230 13:03:38 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1454024 bytes) 111230 13:03:40 [Note] /usr/sbin/mysqld: Normal shutdown 111230 13:03:40 [Note] Event Scheduler: Purging the queue. 0 events 111230 13:03:42 InnoDB: Starting shutdown... 111230 13:03:46 InnoDB: Shutdown completed; log sequence number 7 129834023 111230 13:03:46 [Note] /usr/sbin/mysqld: Shutdown complete 111230 13:03:46 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended 111230 13:03:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 111230 13:03:49 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 111230 13:03:49 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. 111230 13:03:49 [Note] Plugin 'FEDERATED' is disabled. 111230 13:03:49 InnoDB: Initializing buffer pool, size = 2.0G 111230 13:03:50 InnoDB: Completed initialization of buffer pool 111230 13:03:50 InnoDB: Started; log sequence number 7 129834023 111230 13:03:50 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode. 111230 13:03:50 [Note] Event Scheduler: Loaded 0 events 111230 13:03:50 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.55-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 111230 13:04:25 mysqld_safe A mysqld process already exists
- my.cnf
# Example MySQL config file for very large systems. # # This is for a large system with memory of 1G-2G where the system runs mainly # MySQL. # # MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # <a href="http://dev.mysql.com/doc/mysql/en/option-files.html # #" rel="nofollow">http://dev.mysql.com/doc/mysql/en/option-files.html # #</a> In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock default-character-set=euckr # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 default-character-set=euckr init_connect=SET names euckr skip-name-resolve max_connections = 300 max_connect_errors = 1000 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin binlog_cache_size = 1M max_binlog_size = 100M expire_logs_days = 7 # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted #server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # binary logging - not required for slaves, but recommended #log-bin=mysql-bin # # binary logging format - mixed recommended #binlog_format=mixed # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_data_file_path = ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:2G;ibdata6:2G;ibdata7:2G;ibdata8:2G;ibdata9:2G;ibdata10:2G;ibdata11:2G;ibdata12:2G;ibdata13:2G;ibdata14:2G;ibdata15:2G;ibdata16:2G;ibdata17:2G;ibdata18:2G;ibdata19:2G;ibdata20:2G;ibdata21:2G;ibdata22:2G;ibdata23:2G;ibdata24:2G;ibdata25:2G;ibdata26:2G;ibdata27:2G;ibdata28:2G;ibdata29:2G;ibdata30:2G;ibdata31:2G;ibdata32:2G;ibdata33:2G;ibdata34:2G;ibdata35:2G;ibdata36:2G;ibdata37:2G;ibdata38:2G;ibdata39:2G;ibdata40:2G;ibdata41:2G;ibdata42:2G;ibdata43:2G;ibdata44:2G;ibdata45:2G;ibdata46:2G;ibdata47:2G;ibdata48:2G;ibdata49:2G;ibdata50:2G;ibdata51:2G;ibdata52:2G;ibdata53:2G;ibdata54:2G;ibdata55:2G;ibdata56:2G;ibdata57:2G;ibdata58:2G;ibdata59:2G;ibdata60:2G;ibdata61:2G;ibdata62:2G;ibdata63:2G;ibdata64:2G;ibdata65:2G;ibdata66:2G;ibdata67:2G;ibdata68:2G;ibdata69:2G;ibdata70:2G;ibdata71:2G;ibdata72:2G;ibdata73:2G;ibdata74:2G;ibdata75:2G;ibdata76:2G;ibdata77:2G;ibdata78:2G;ibdata79:2G;ibdata80:2G;ibdata81:2G;ibdata82:2G;ibdata83:2G;ibdata84:2G;ibdata85:2G;ibdata86:2G;ibdata87:2G;ibdata88:2G;ibdata89:2G;ibdata90:2G;ibdata91:2G;ibdata92:2G;ibdata93:2G;ibdata94:2G;ibdata95:2G;ibdata96:2G;ibdata97:2G;ibdata98:2G;ibdata99:100M:autoextend innodb_log_group_home_dir = /var/lib/mysql # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 2048M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 512M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M default-character-set=euckr [geshifilter-mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
Forums:
흠.
뭔가 엄청 무거운 쿼리가 실행될것입니다.
slow query 관련만 찾아서 넣어주셔도 원인은 금방 찾을 것 같습니다.
innodb 를 사용한다면 왠만해서는 무조건 64bit 가 좋습니다.
innodb_buffer_pool_size = 2048M 라고 설정한것은 x86 이기 때문인가요?
--------------- 절취선 ------------------------
하늘은 스스로 삽질하는 자를 삽으로 팬다.
http://glay.pe.kr
새해 복 많이 받으세요.
glay님 답변 주셔서 감사합니다.
네 CentOS 5.5 32비트 운영체제입니다.
innodb_buffer_pool_size는 실 메모리의 8% ~ 50%로 설정하는 것이 좋다고 들었습니다.
실 메모리가 8G 이기 때문에 25% 정도인 2G가 적당하지 않나 싶어서요.
답변 주신대로 slow query log를 설정해서 파악해 봐야겠네요.
glay님 새해 복 많이 받으시기 바랍니다~ ^^
흠
제가본건 두가지 유형.
위에서 말씀하신데로 무진장 무거원 쿼리(서브쿼리 와장창..)가 돌아가서 Lock Table 걸려 쿼리가 밀린경우구요.
(꼬임 방지인지 순차 적용이더군요 =ㅅ=;; 그래서 앞에서 쿼리 안들어가고 뻐팅기면 뒤에 줄 슨다는..)
아니면 SQL인젝션 공격인 경우도 같은 문제가 발생하기도 합니다.
만약 슬로우쿼리가 발생한다면 where 절에 있는 테이블의 필드에 index 걸어주는 방법으로 어느정도 해결이 될거구요.
그래도 안되면 쿼리 뜻어 고쳐야 하는 문제가 발생합니다.
인젝션 공격이면.. 오픈된 게시판 다 닫으시는게 방책 =ㅅ=;;
.
32비트에선 한 프로세스가 쓸수있는 최대mem이 2긱으로 알고있습니다.
pae를 사용해도 2긱으로 알고있는데
64비트를 가시면 해결이되죠
...
댓글을 수정할줄 모르겠어서 댓댓글로 남깁니다.
innodb_buffer_pool_size을 1536MB로 줄이면 32비트에서도 oom은 나오지 않을거라 생각합니다.
innodb_buffer_pool_size
저희 회사에서도 한 분이 32비트 OS에 innodb_buffer_pool_size 를 2G 로 잡은 경우,
위와 같이 out of memory 에러가 발생하였습니다.
이 사이즈를 1G 로 줄여주니, 문제가 사라졌습니다.
daebo01 님 말씀처럼 사이즈를 줄여주시면 일단은 해결이 될 듯하고요~
근본적인 해결책을 위해서는 64비트 OS로 재설치 해주시는 것이 답이 될 것입니다.
취미개발자 '해피가이'
[ http://gauryan.blogspot.com/ ]