[MDEV-6782] mariadb crashed under load Created: 2014-09-24  Updated: 2014-10-31  Due: 2014-10-30  Resolved: 2014-10-31

Status: Closed
Project: MariaDB Server
Component/s: Query Cache
Affects Version/s: 10.0.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: rohan Assignee: Elena Stepanova
Resolution: Incomplete Votes: 0
Labels: None
Environment:

amazon ec2: m1.xlarge pv.instancestore
4 vcpu 15GB memory

CentOS release 6.5
Linux 2.6.32-431.20.5.el6.x86_64 #1 SMP Fri Jul 25 08:34:44 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

Attachments: File my.cnf    

 Description   

mariadb crashed while executing query

Server version: 10.0.12-MariaDB-log MariaDB Server

below are the logs from mysqld.log. I'm attaching /etc/my.cnf
crash logs

140924  6:42:48 [ERROR] 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.
 
 
 
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
 
 
 
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.
 
 
 
Server version: 10.0.12-MariaDB-log
 
key_buffer_size=67108864
 
read_buffer_size=131072
 
max_used_connections=36
 
max_threads=302
 
thread_count=12
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 728914 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x12e31c008
 
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...
 
stack_bottom = 0x7f71433c8d68 thread_stack 0x48000
 
/usr/libexec/mysqld(my_print_stacktrace+0x2e)[0xb72b0e]
 
/usr/libexec/mysqld(handle_fatal_signal+0x4ac)[0x71a57c]
 
/lib64/libpthread.so.0[0x386cc0f710]
 
/usr/libexec/mysqld(_ZN11Query_cache26double_linked_list_excludeEP17Query_cache_blockPS1_+0x0)[0x57c620]
 
/usr/libexec/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_table+0x6c)[0x57d06c]
 
/usr/libexec/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_block+0x83)[0x57d9e3]
 
/usr/libexec/mysqld(_ZN11Query_cache14free_old_queryEv+0x6c)[0x57db0c]
 
/usr/libexec/mysqld(_ZN11Query_cache14allocate_blockEmcm+0x98)[0x57dbb8]
 
/usr/libexec/mysqld(_ZN11Query_cache16write_block_dataEmPhmN17Query_cache_block10block_typeEj+0x5c)[0x57df8c]
 
/usr/libexec/mysqld(_ZN11Query_cache12insert_tableEjPKcP23Query_cache_block_tablejhhPFcP3THDPcjPyEyc+0x7e)[0x57fd2e]
 
/usr/libexec/mysqld[0xb4f066]
 
/usr/libexec/mysqld(_ZN11Query_cache25register_tables_from_listEP3THDP10TABLE_LISTjPP23Query_cache_block_table+0x177)[0x580017]
 
/usr/libexec/mysqld(_ZN11Query_cache19register_all_tablesEP3THDP17Query_cache_blockP10TABLE_LISTj+0x46)[0x5800b6]
 
/usr/libexec/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x442)[0x580572]
 
/usr/libexec/mysqld[0x5ae33f]
 
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x4c56)[0x5b6a06]
 
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x27d)[0x5b89ad]
 
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x19ea)[0x5ba3aa]
 
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xff)[0x67f70f]
 
/usr/libexec/mysqld(handle_one_connection+0x47)[0x67f877]
 
/lib64/libpthread.so.0[0x386cc079d1]
 
/lib64/libc.so.6(clone+0x6d)[0x386c8e8b5d]
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0xf62a0020): select a.keyword as keyword_ivalue, CONCAT(a.search_engine_device_id,'_',a.search_engine_group_id) as search_engine_ivalue, a.domain as domain_ivalue, a.domain_rank as `domain_rank`, a.serp_type as `serp_type`, b.serp_type as `serp_type5`, a.absolute_domain_rank as `absolute_domain_rank`, b.absolute_domain_rank as `absolute_domain_rank7`, CAST(a.value_000 as signed)  as `rank`, CAST(a.value_001 as signed)  as `blended_rank`, static_search_volume.es_es_volume as `search_volume`, CAST(a.value_010 as signed)  as `page_num`, CAST(b.value_010 as signed)  as `page_num4`, CAST(a.value_012 as signed)  as `number_likes_shares`, CAST(a.value_013 as signed)  as `number_tweets`, CAST(a.value_014 as signed)  as `number_plus_one` from
 
keyword_weekly_dataset a
 
 
 
left join keyword_weekly_dataset b on a.account_id=b.account_id and a.search_engine_device_id=b.search_engine_device_id and a.search_engine_group_id=b.search_engine_group_id and a.domain=b.domain and a.keyword_id=b.keyword_id and a.domain_rank=b.domain_rank and a.time=201240 and b.time=201239 and a.time_time_type=b.time_time_type
 
  join account_keyword_searchengines aks
 
on aks.account_id = 3193
 
and aks.keyword_id = `a`.`keyword_id`
 
and aks.collector_id = 55
 
join keywords kw
 
on aks.keyword_id = kw.id
 
 
 
join (
 
SELECT da.domain_id AS domain_id, da.account_id AS account_id, 1 AS main_domain
 
from domains_accounts da
 
where da.account_id = 3193
 
UNION
 
SELECT avc.competitor_id AS domain_id, avc.account_id AS account_id, 0 AS main_domain
 
from account_viewable_competitors avc
 
where avc.account_id = 3193
 
) do_avc on do_avc.domain_id = `a`.`domain`
 
join domains d
 
on d.id = `a`.`domain`
 
 
 
left join google_keyword_volumes as static_search_volume
 
on static_search_volume.keyword_id= a.keyword_id
 
and static_search_volume.yearmonth= 201406
 
join keyword_groups_keywords
 
on keyword_groups_keywords.keyword_id = a.keyword_id
 
and keyword_group_id = '11099'
 
 
 
     where `a`.`search_engine_device_id`="1" and `a`.`search_engine_group_id`="55" and `a`.`domain`=4508 and a.account_id=3193 and a.time_time_type=2 and a.time=201240
 
 
 
Connection ID (thread ID): 916907
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.
 
140924 06:42:57 mysqld_safe Number of processes running now: 0
 
140924 06:42:57 mysqld_safe mysqld restarted
 
2014-09-24 06:42:58 7f79ce464800 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
 
140924  6:42:58 [Note] InnoDB: Using mutexes to ref count buffer pool pages
 
140924  6:42:58 [Note] InnoDB: The InnoDB memory heap is disabled
 
140924  6:42:58 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
140924  6:42:58 [Note] InnoDB: Compressed tables use zlib 1.2.3
 
140924  6:42:58 [Note] InnoDB: Using Linux native AIO
 
140924  6:42:58 [Note] InnoDB: Using CPU crc32 instructions
 
140924  6:42:58 [Note] InnoDB: Initializing buffer pool, size = 9.0G
 
140924  6:42:59 [Note] InnoDB: Completed initialization of buffer pool
 
140924  6:42:59 [Note] InnoDB: Highest supported file format is Barracuda.
 
140924  6:42:59 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1182502737711
 
140924  6:42:59 [Note] InnoDB: Database was not shutdown normally!
 
140924  6:42:59 [Note] InnoDB: Starting crash recovery.
 
140924  6:42:59 [Note] InnoDB: Reading tablespace information from the .ibd files...
 
140924  6:43:23 [Note] InnoDB: Restoring possible half-written data pages
 
140924  6:43:23 [Note] InnoDB: from the doublewrite buffer...
 
InnoDB: Doing recovery: scanned up to log sequence number 1182502738017
 
InnoDB: Last MySQL binlog file position 0 201346893, file name /mnt/mysql/bin_log/bin-log.000013
 
140924  6:43:24 [Note] InnoDB: 128 rollback segment(s) are active.
 
140924  6:43:24 [Note] InnoDB: Waiting for purge to start
 
140924  6:43:24 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.17-65.0 started; log sequence number 1182502738017
 
140924  6:43:24 [Note] Plugin 'FEEDBACK' is disabled.
 
140924  6:43:24 [Note] Recovering after a crash using /mnt/mysql/bin_log/bin-log
 
140924  6:43:24 [Note] Starting crash recovery...
 
140924  6:43:24 [Note] Crash recovery finished.
 
140924  6:43:24 [Note] Server socket created on IP: '0.0.0.0'.
 
140924  6:43:24 [Note] Event Scheduler: Loaded 0 events
 
140924  6:43:24 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--log-basename=#' or '--relay-log=mysqld-relay-bin' to avoid this problem.
 
140924  6:43:24 [Note] /usr/libexec/mysqld: ready for connections.
 
Version: '10.0.12-MariaDB-log'  socket: '/mnt/mysql/mysql.sock'  port: 3306  MariaDB Server
 
140924  6:43:24 [Note] Slave SQL thread initialized, starting replication in log 'bin-log.000252' at position 674682332, relay log './mysqld-relay-bin.000783' position: 674682617
 
140924  6:43:24 [Note] Slave I/O thread: connected to master 'replication@pod3-par0-master.brightedge.com:3306',replication started in log 'bin-log.000252' at position 67468233



 Comments   
Comment by Elena Stepanova [ 2014-10-14 ]

Hi,

Would you be able to provide the dump of the schema? You can upload it to ftp.askmonty.org/private.

If you can't provide the dump due to confidentiality reasons or because it's too big, please paste the output of the following commands for all tables:

SHOW CREATE TABLE ...
 
SHOW INDEX IN ...
 
SHOW TABLE STATUS LIKE ...

Involved tables, if I haven't missed anything:

  • keyword_weekly_dataset
  • account_keyword_searchengines
  • keywords
  • domains_accounts
  • account_viewable_competitors
  • domains
  • google_keyword_volumes
  • keyword_groups_keywords
Comment by rohan [ 2014-10-15 ]

Hi Elena,
Thank you for your response.
The complete schema is too large. I have uploaded the output of the commands for the tables requested to ftp://ftp.askmonty.org/private/mdbtest
please do let me know if you need any other info

  • Rohan
Comment by Elena Stepanova [ 2014-10-16 ]

Hi Rohan,

Thank you for the information.
Is the problem repeatable? If you run the same query again, does it keep crashing?
Could you please also run 

EXPLAIN EXTENDED <the query>;
 
SHOW WARNINGS;

(Probably you don't want to try running the query or EXPLAIN it if it's a production server)

If the crash is not repeatable, could you please run 

SHOW STATUS LIKE 'QCache%';
 
<the query>
 
SHOW STATUS LIKE 'QCache%';

and paste the output of SHOW STATUS commands, and also give me a hint on how many rows the query returns?

Thanks.

Comment by Elena Stepanova [ 2014-10-31 ]

Closing for now as incomplete. If you have more information, please comment to re-open the issue.

Generated at Thu Feb 08 07:14:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.