| max_heap_table_size | 25769803776 |
This sounds dangerous. When a SELECT needs to build a temp table, it first tries to build a MEMORY (heap) table in RAM. If that overflows, it converts the temp table to MyISAM and spills to disk. Your setting says to allow 25GB for such a MEMORY table. But you don't have that much room. Recommend changing it to 2000M in my.cnf (my.ini) -- that would be about 2GB, which you do have room for.
If you are actually using most of the 25GB in your query, then the OS is busy paging/swapping -- this can make it very difficult to get anything else done. MySQL is not a happy camper when this happens.
Since you have 32GB, you may as well crank up
| join_buffer_size | 131072 |
to, say, 20M.
InnoDB uses this for caching data and indexes:
| innodb_buffer_pool_size | 16106127360 |
MyISAM uses this for indexes only:
| key_buffer_size | 5368709120 |
I believe it silents wraps modulo 4G.
| thread_cache_size | 0 |
Minor issue -- change it to, say, 5; this might help getting a connection.
133GB -- I can afford to be wrong sometimes. ;)
I see all CHAR, no VARCHAR. Are all the strings fixed length? In InnoDB, there is perhaps no advantage of CHAR over VARCHAR.
WHERE rec_datetime between
cast(concat(left(now(), 11), '10:01:00') as datetime)
and cast(concat(left(now(), 11), '10:15:00') as datetime)
and stock_code = 5 AND order_type IN ('I', 'R', 'C')
ORDER BY rec_datetime;
begs for a compound query -- one of these (I am not sure which):
INDEX (stock_code, order_type, datetime)
INDEX (stock_code, datetime)
I see you have turned off the slowlog:
#log-slow-queries=/var/log/mysqld.slow.log
#long_query_time=1
It can be a useful tool in figuring out slow queries.
You have 5.1; your multiple tables are "partitions"; you are using them in the way best suited for partitioning, namely a sliding set of daily tables. If you changed to PARTITION, you could skip the VIEWs.