Description:
Hi,
MySQL on my master server has crashed and restarted twice during last 16 days.
I have been analyzing and investigating but I could not identify that caused it. It's very critical for us, because causes a downtime in ours infrastructure.
The first time, using mysqltuner I checked that "Maximum possible memory usage" was 24G, too many larger that 12G available by server. Therefore, I changed mysql memory settings and now it's 9.8G but the problem continues.
I send you the specifications of server, my.cnf, error.log and an mysqltuner output.
-- error.log
110119 11:01:30 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=1073741824
read_buffer_size=2097152
max_used_connections=243
max_connections=450
threads_connected=27
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 5656576 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x7fe210cdfc60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x7fe210cdfc60, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x7fe210cdfc60, stack_bottom=0x44ac0000, thread_stack=196608, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x4cf8070 = select r.id as id , getItineraryRouteHeader( r.id) as route , r.reservation_date as reservationDate , MIN( srr.departure_date) as flightDate from Reservations r join ReservationsRequests rr on rr.reservation_id = r.id join FlightsReservationsRequests frr on rr.id = frr.reservation_request_id join SegmentsReservationsRequests srr on srr.segment_reserved_id = frr.segment_reserved_id join EmployeesAirlines ea on ea.id = r.employee_id join TicketsStateAirlines ta on ta.airline_id = ea.airline_id and frr.ticket_state_code = ta.code and ta.show_in_history = "Y" where r.employee_id = '119105' group by r.id having count( *) =( select count( *) from ReservationsRequests rr join FlightsReservationsRequests frr on rr.id = frr.reservation_request_id where rr.reservation_id = r.id) order by r.reservation_date DESC
thd->thread_id=503655405
-- Server Specifications.
4 processes Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
12037 MB of RAM - The server doesn't use swap -
RAID 10 Storage
-- free -m
mysql1:/var/log/mysql# free -m
total used free shared buffers cached
Mem: 12037 11976 61 0 109 9439
-/+ buffers/cache: 2427 9610
Swap: 19077 0 19076
-- my.cnf
[mysqld]
innodb_file_per_table
back_log = 50
max_connections = 450
max_connect_errors = 50
table_cache = 2048
max_allowed_packet = 64M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 6M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 256M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = MYISAM
thread_stack = 192K
tmp_table_size = 64M
log = /var/log/mysql/mysql.log
log_warnings = 1
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 4
log_long_format
key_buffer_size = 1024M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 1M
innodb_log_file_size = 5M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 60
skip-external-locking
log-queries-not-using-indexes
skip-name-resolve
skip-bdb
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
-- MySQLTuner Output
>> MySQLTuner 0.9.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: LEstrada
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[!!] There is a new version of MySQLTuner available
[OK] Currently running supported MySQL version 5.0.51a-24+lenny4-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 17G (Tables: 1756)
[--] Data in InnoDB tables: 7G (Tables: 2150)
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 7m 15s (135M q [1K qps], 36M conn, TX: 86B, RX: 15B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 18.2M per thread and 1.8G global
[OK] Maximum possible memory usage: 9.8G (83% of installed RAM)
[OK] Slow queries: 0% (416K/135M)
[OK] Highest usage of available connections: 75% (340/450)
[OK] Key buffer size / total MyISAM indexes: 1.0G/3.7G
[OK] Key buffer hit rate: 100.0%
[OK] Query cache efficiency: 53.5%
[!!] Query cache prunes per day: 138856
[OK] Sorts requiring temporary tables: 0%
[!!] Joins performed without indexes: 128509
[OK] Temporary tables created on disk: 10%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 1%
[OK] Open file limit used: 13%
[OK] Table locks acquired immediately: 99%
[!!] InnoDB data size / buffer pool: 7.8G/512.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 256M)
join_buffer_size (> 6.0M, or always use indexes with joins)
table_cache (> 2048)
innodb_buffer_pool_size (>= 7G)
How to repeat:
--
Description: Hi, MySQL on my master server has crashed and restarted twice during last 16 days. I have been analyzing and investigating but I could not identify that caused it. It's very critical for us, because causes a downtime in ours infrastructure. The first time, using mysqltuner I checked that "Maximum possible memory usage" was 24G, too many larger that 12G available by server. Therefore, I changed mysql memory settings and now it's 9.8G but the problem continues. I send you the specifications of server, my.cnf, error.log and an mysqltuner output. -- error.log 110119 11:01:30 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1073741824 read_buffer_size=2097152 max_used_connections=243 max_connections=450 threads_connected=27 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 5656576 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x7fe210cdfc60 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x7fe210cdfc60, backtrace may not be correct. Bogus stack limit or frame pointer, fp=0x7fe210cdfc60, stack_bottom=0x44ac0000, thread_stack=196608, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x4cf8070 = select r.id as id , getItineraryRouteHeader( r.id) as route , r.reservation_date as reservationDate , MIN( srr.departure_date) as flightDate from Reservations r join ReservationsRequests rr on rr.reservation_id = r.id join FlightsReservationsRequests frr on rr.id = frr.reservation_request_id join SegmentsReservationsRequests srr on srr.segment_reserved_id = frr.segment_reserved_id join EmployeesAirlines ea on ea.id = r.employee_id join TicketsStateAirlines ta on ta.airline_id = ea.airline_id and frr.ticket_state_code = ta.code and ta.show_in_history = "Y" where r.employee_id = '119105' group by r.id having count( *) =( select count( *) from ReservationsRequests rr join FlightsReservationsRequests frr on rr.id = frr.reservation_request_id where rr.reservation_id = r.id) order by r.reservation_date DESC thd->thread_id=503655405 -- Server Specifications. 4 processes Intel(R) Xeon(R) CPU E5620 @ 2.40GHz 12037 MB of RAM - The server doesn't use swap - RAID 10 Storage -- free -m mysql1:/var/log/mysql# free -m total used free shared buffers cached Mem: 12037 11976 61 0 109 9439 -/+ buffers/cache: 2427 9610 Swap: 19077 0 19076 -- my.cnf [mysqld] innodb_file_per_table back_log = 50 max_connections = 450 max_connect_errors = 50 table_cache = 2048 max_allowed_packet = 64M binlog_cache_size = 1M max_heap_table_size = 64M sort_buffer_size = 8M join_buffer_size = 6M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 256M query_cache_limit = 2M ft_min_word_len = 4 default_table_type = MYISAM thread_stack = 192K tmp_table_size = 64M log = /var/log/mysql/mysql.log log_warnings = 1 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 4 log_long_format key_buffer_size = 1024M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 512M innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 1M innodb_log_file_size = 5M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 60 skip-external-locking log-queries-not-using-indexes skip-name-resolve skip-bdb [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192 socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqldump] quick quote-names max_allowed_packet = 16M -- MySQLTuner Output >> MySQLTuner 0.9.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: LEstrada Please enter your MySQL administrative password: -------- General Statistics -------------------------------------------------- [!!] There is a new version of MySQLTuner available [OK] Currently running supported MySQL version 5.0.51a-24+lenny4-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 17G (Tables: 1756) [--] Data in InnoDB tables: 7G (Tables: 2150) -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 1h 7m 15s (135M q [1K qps], 36M conn, TX: 86B, RX: 15B) [--] Reads / Writes: 97% / 3% [--] Total buffers: 18.2M per thread and 1.8G global [OK] Maximum possible memory usage: 9.8G (83% of installed RAM) [OK] Slow queries: 0% (416K/135M) [OK] Highest usage of available connections: 75% (340/450) [OK] Key buffer size / total MyISAM indexes: 1.0G/3.7G [OK] Key buffer hit rate: 100.0% [OK] Query cache efficiency: 53.5% [!!] Query cache prunes per day: 138856 [OK] Sorts requiring temporary tables: 0% [!!] Joins performed without indexes: 128509 [OK] Temporary tables created on disk: 10% [OK] Thread cache hit rate: 99% [!!] Table cache hit rate: 1% [OK] Open file limit used: 13% [OK] Table locks acquired immediately: 99% [!!] InnoDB data size / buffer pool: 7.8G/512.0M -------- Recommendations ----------------------------------------------------- General recommendations: Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 256M) join_buffer_size (> 6.0M, or always use indexes with joins) table_cache (> 2048) innodb_buffer_pool_size (>= 7G) How to repeat: --