Skip to main content
Database Administrators

Return to Question

added 124 characters in body
Source Link
ypercubeᵀᴹ
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

Error from mysql.logmysql.log is as follows,

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.

140720 17:07:42 InnoDB: Initializing buffer pool, size = 512.0M

140720 17:07:42 InnoDB: Completed initialization of buffer pool

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

140720 17:07:42 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

140720 17:07:42 InnoDB: Started; log sequence number 2 2605701970

140720 17:07:42 [Note] Event Scheduler: Loaded 0 events

140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.1.73-0ubuntu0.10.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.
140720 17:07:42  InnoDB: Initializing buffer pool, size = 512.0M
140720 17:07:42  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140720 17:07:42  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140720 17:07:42  InnoDB: Started; log sequence number 2 2605701970
140720 17:07:42 [Note] Event Scheduler: Loaded 0 events
140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-0ubuntu0.10.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Our server:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000

RAM : 1Gb

Hdd : 50Gb

OS Ubuntu 10.04.4 LTS

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000  
RAM : 1Gb  
Hdd : 50Gb  
OS Ubuntu 10.04.4 LTS

my.cnfmy.cnf :

max_connections = 250

thread_cache_size = 8

query_cache_limit = 1M

query_cache_size = 16M

query_cache_type = 1

key_buffer = 16M

read_buffer_size = 128K

join_buffer_size = 128K

connect_timeout = 10

max_connect_errors = 10

max_allowed_packet = 16M

tmp_table_size = 16M

innodb_buffer_pool_size=512M

innodb_additional_mem_pool_size=40M

innodb_log_buffer_size=40M

innodb_thread_concurrency=2

innodb_flush_log_at_trx_commit=1

max_connections = 250
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
key_buffer = 16M
read_buffer_size = 128K
join_buffer_size = 128K
connect_timeout = 10
max_connect_errors = 10
max_allowed_packet = 16M
tmp_table_size = 16M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=40M
innodb_log_buffer_size=40M
innodb_thread_concurrency=2
innodb_flush_log_at_trx_commit=1

dmesg:

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB

Regards,

Ridda.

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB

Error from mysql.log is as follows,

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.

140720 17:07:42 InnoDB: Initializing buffer pool, size = 512.0M

140720 17:07:42 InnoDB: Completed initialization of buffer pool

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

140720 17:07:42 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

140720 17:07:42 InnoDB: Started; log sequence number 2 2605701970

140720 17:07:42 [Note] Event Scheduler: Loaded 0 events

140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.1.73-0ubuntu0.10.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

Our server:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000

RAM : 1Gb

Hdd : 50Gb

OS Ubuntu 10.04.4 LTS

my.cnf :

max_connections = 250

thread_cache_size = 8

query_cache_limit = 1M

query_cache_size = 16M

query_cache_type = 1

key_buffer = 16M

read_buffer_size = 128K

join_buffer_size = 128K

connect_timeout = 10

max_connect_errors = 10

max_allowed_packet = 16M

tmp_table_size = 16M

innodb_buffer_pool_size=512M

innodb_additional_mem_pool_size=40M

innodb_log_buffer_size=40M

innodb_thread_concurrency=2

innodb_flush_log_at_trx_commit=1

dmesg:

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB

Regards,

Ridda.

Error from mysql.log is as follows,

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.
140720 17:07:42  InnoDB: Initializing buffer pool, size = 512.0M
140720 17:07:42  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140720 17:07:42  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140720 17:07:42  InnoDB: Started; log sequence number 2 2605701970
140720 17:07:42 [Note] Event Scheduler: Loaded 0 events
140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-0ubuntu0.10.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Our server:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000  
RAM : 1Gb  
Hdd : 50Gb  
OS Ubuntu 10.04.4 LTS

my.cnf :

max_connections = 250
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
key_buffer = 16M
read_buffer_size = 128K
join_buffer_size = 128K
connect_timeout = 10
max_connect_errors = 10
max_allowed_packet = 16M
tmp_table_size = 16M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=40M
innodb_log_buffer_size=40M
innodb_thread_concurrency=2
innodb_flush_log_at_trx_commit=1

dmesg:

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB
Source Link
user44157
user44157
  • 1

MySQL crashing randomly

We have a sever running a website Joomla 2.5. We are facing frequent mysql crashes that occur when website has medium visitor load like a few thousands. It appears that mysql consumes more memory and is killed by OOM killer. Currently if I look for memory consumption for mysql using top command, it says that mysql process is consuming 17% of memory. Normally the server shows upto 45% of total memory used. But when mysql crashes, system memory is recorded to be between 70% to 95% or sometimes 100%.

I have a feeling that its mysql memory settings (I think we have set huge values for different variables) which cause it to consume that much memory at peak traffic hours. Or it is the myisam tables in Joomla 2.5 that are causing it to crash due to locking of tables. Please suggest your thoughts.

Error from mysql.log is as follows,

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.

140720 17:07:42 InnoDB: Initializing buffer pool, size = 512.0M

140720 17:07:42 InnoDB: Completed initialization of buffer pool

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

140720 17:07:42 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

140720 17:07:42 InnoDB: Started; log sequence number 2 2605701970

140720 17:07:42 [Note] Event Scheduler: Loaded 0 events

140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.1.73-0ubuntu0.10.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

Our server:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000

RAM : 1Gb

Hdd : 50Gb

OS Ubuntu 10.04.4 LTS

my.cnf :

max_connections = 250

thread_cache_size = 8

query_cache_limit = 1M

query_cache_size = 16M

query_cache_type = 1

key_buffer = 16M

read_buffer_size = 128K

join_buffer_size = 128K

connect_timeout = 10

max_connect_errors = 10

max_allowed_packet = 16M

tmp_table_size = 16M

innodb_buffer_pool_size=512M

innodb_additional_mem_pool_size=40M

innodb_log_buffer_size=40M

innodb_thread_concurrency=2

innodb_flush_log_at_trx_commit=1

dmesg:

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB

Regards,

Ridda.