select ends with killed in mysql

-
I ran a query in mysql and it hangs for a while and then I get killed as a response.


mysql> select * from usagehistory;
Killed
SHELL:~$
SHELL:~$ date
Thu Mar 14 06:26:17 UTC 2019
SHELL:~$

Reason

The mysql process/client itself runs out of memory and is terminated by the OS. I do not have a good view of the implementation, but it appears that mysql is trying something like pulling the whole table into memory to do the select *.

You can use the --quick mysql option to not buffer the results in memory.

I also found out that the data in table is around 2 GB and Memory in my jump-box is around 1 GB.

Two more proofs that I see in my jump box (where mysql is run) system is:

 1. I see OOM messages in syslog.

root@ip-XXX-XXX-XXX-XXX:~# grep mysql /var/log/syslog | less
Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301028] mysql invoked oom-killer: gfp_mask=0x24201ca, order=0, oom_score_adj=0
Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301032] mysql cpuset=/ mems_allowed=0
Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301038] CPU: 0 PID: 25553 Comm: mysql Not tainted 4.4.0-1074-aws #84-Ubuntu
Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301237] [25553]  1000 25553   235060   210811     434       3        0             0 mysql
Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301251] Out of memory: Kill process 25553 (mysql) score 832 or sacrifice child
Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.306445] Killed process 25553 (mysql) total-vm:940240kB, anon-rss:842532kB, file-rss:712kB
(END)

Press :q to exit.

2. I see free memory going down when I execute the query. I use free -m command. Another command that can be used is vmstat.


SHELL:~# free -m
              total        used        free      shared  buff/cache   available
Mem:            990          75         818           8          96         783
Swap:             0           0           0
SHELL:~#
SHELL:~# free -m
              total        used        free      shared  buff/cache   available
Mem:            990         400         494           8          96         458
Swap:             0           0           0
SHELL:~#
SHELL:~# free -m
              total        used        free      shared  buff/cache   available
Mem:            990         669         224           8          96         189
Swap:             0           0           0
SHELL:~#
SHELL:~# free -m
              total        used        free      shared  buff/cache   available
Mem:            990         885          52           8          52           7
Swap:             0           0           0

NOTE: After getting the Killed response from mysql, the free memory comes back again.


SHELL:~# free -m
              total        used        free      shared  buff/cache   available
Mem:            990          75         860           8          53         804
Swap:             0           0           0    
SHELL:~#

Some References 

MySQL Query with LARGE number of records gets Killed 

Mysql client ran out of memory 

Debug out-of-memory with /var/log/messages 

What To Do When MySQL Runs Out of Memory: Troubleshooting Guide 

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more