0

For some reason my server has been finicky lately and for the life of me I can't figure out why. I monitor the memory usage by running

shell# watch -n 5 free -m

without running any activities memory used is anywhere from 4-5 GB. Once I run a query it starts increasing all the way up to 35 GB. For example, see output below

Every 5.0s: free -m                                     Fri Mar 13 10:17:28 2015

             total       used       free     shared    buffers     cached
Mem:         36196      34429       1767          0        124       3172
-/+ buffers/cache:      31132       5064
Swap:        24563          0      24563

the query I am running is

SELECT DISTINCT(c1),c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12
FROM db1.tb1
WHERE NOT EXISTS (SELECT * FROM db2.tb2 WHERE db2.tb2.c1 = db1.tb1.c1)
and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N'
GROUP BY c1
LIMIT 5000

All columns on both tables are indexed. Normally this query only takes me a couple of minutes. But last couple days, it has been taking 4+ hours

The db1.t1 has 360 columns and 204 million records. So it is quite large It uses the InnoDB engine

Ubuntu v. 12.04

MySQL 5.5

I have 36 GB RAM installed

4 core Intel Xeon processor 2.93 GHz with hyper threading enabled.

Below is my my.cnf file (im only going to post the un-commented portions of the file)

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql    
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr    
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 50.253.189.57
key_buffer      = 5G
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 50
myisam-recover         = BACKUP
max_connections        = 500
table_cache            = 3200
thread_concurrency     = 10
bulk_insert_buffer_size = 1G
join_buffer_size = 500M
sort_buffer_size = 5G
query_cache_limit   = 0
query_cache_size        = 0
open-files-limit = 65535
tmp_table_size = 3G
max_heap_table_size = 1G
table_definition_cache = 1024
table_open_cache = 2048
thread_cache_size = 50
max_connect_errors = 1000000
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 26G
innodb_additional_mem_pool_size = 1G
innodb_buffer_pool_instances = 2
innodb_log_buffer_size = 1G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[isamchk]
key_buffer      = 16
Improve this question
5
  • Why is there a parenthesis around c1 in: SELECT DISTINCT (c1) ...? Do you know it's redundant? DISTINCT is not a function. – ypercubeᵀᴹ Mar 13 '15 at 17:16
  • More important, do you know that selecting c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12 which are not included in the GROUP BY (only c1 is) means that you may get indeterminate results for all columns (except c1)? – ypercubeᵀᴹ Mar 13 '15 at 17:18
  • No i didnt know it was redundant, thanks for the heads up. and yes i know about the GROUP BY because i only want unique records for c1, and when i dont put that as the group by i then have to go dedupe c1 in excel anyway. – Bill Mar 13 '15 at 18:19
  • And since you have GROUP BY c1, the DISTINCT is redundant as well. You can remove it, too. Each row will have a distinct c1 value, so no need to use distinct. So, you either keep GROUP BY c1 and remove DISTINCT (which means you don't mind indeterminate results). Or you remove the GROUP BY and keep the DISTINCT (which will give you determinate results but a different query than you have now - possibly more than 1 row with same c1). – ypercubeᵀᴹ Mar 13 '15 at 18:24
  • okay that is very helpful, thank you for that input – Bill Mar 13 '15 at 18:28
1

All columns on both tables are indexed.

Enough said. That is not the right way to go about indexing.

WHERE db2.tb2.c1 = db1.tb1.c1

db2.tb2 needs INDEX(c1) -- Keep in mind that a PRIMARY KEY is a UNIQUE key is an INDEX, so do not redundantly add INDEX if you already have PRIMARY KEY(c1).

and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N'

Needs a compound index; recommending adding these three, since there are no clues of which would be best:

INDEX(c6, c9)
INDEX(c6, c10)
INDEX(c6, c12)

At the same time, remove INDEX(c6) because it is now redundant. For further discussion, Cookbook on making an INDEX, given a SELECT.

tmp_table_size = 3G
max_heap_table_size = 1G

Very dangerous. Are you swapping? Swapping is much worse on performance than lowering some of the settings. Each SELECT that needs a tmp table will allocate 1GB; if you have lots of such queries running at the same time, you will rapidly chew up RAM. Lower both of those to their defaults.

key_buffer = 5G

That is for MyISAM indexes. If you are not using MyISAM, set it to 20M. InnoDB is the preferred engine; 26G (out of 36GB) is reasonable if all of your tables are InnoDB.

There may be more suggestions; see how far you get with these.

Edit

DISTINCT(c1),c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12

May not mean what you think -- DISTINCT is not a function, and the parens are irrelevant. That de-dups the entire set of 12 expressions. Also, it is probably irrelevant, unless you actually have duplicate rows.

Is c1 your PRIMARY KEY? Hence UNIQUE? So the GROUP BY is also irrelevant. Removing it may help speed.

Improve this answer
9
  • Another possibility for the index would be (c6,c1) (which can used for group by) and - independent - including all the columns in the index, eg.: (c6, c1, c9, c10, c12) . – ypercubeᵀᴹ Mar 13 '15 at 17:21
  • Including all 12 columns in the SELECT would be a "covering index"; stopping at the 5 in the index in unlikely to help at all. – Rick James Mar 13 '15 at 17:54
  • OKay thanks for all of the feedback. first thing should i be keeping the exists or not exists statement? – Bill Mar 13 '15 at 18:24
  • c1 isnt primary key, but i need only that collumn to be unique records. – Bill Mar 13 '15 at 18:25
  • And no i am not swapping which i was not sure if that was odd or not. – Bill Mar 13 '15 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.