Mysql – Is there possible chache query or save it in memory in MySql? Mayby Should I use something other

cacheinnodbmemcachedmemoryMySQL

I have complicated query (some joins and ordering), that I want to execute very often. Results of query can change very rarely.

Query gets words form given language and category sorted by frequency, so it have two parameters – language and category.

I heart about materialized views, or databases in memory, bu I do not have experience enough to consider cons and props in reasonable way.

I want to have possibility execute this query for these parameters, and next not execute it until I decide manually, that it must be refreshed. In practice, all application based on executing this query many times, so I do not know if can I create one view in memory, when database use innodb.

I can change mysql on something else, and innodb too if is it recommended.

Best Answer

Let's try the Query cache first. Add these to my.cnf, then restart mysql:

query_cache_type = ON
query_cache_size = 50M

If that does not work adequately, we can discuss alternatives.

Decent Tuning of InnoDB would be the key

Buffer Pool (Sized by innodb_buffer_pool_size)

Since InnoDB does not support INSERT DELAYED, using a large InnoDB Buffer Pool is closest thing you can get to INSERT DELAYED. All DML (INSERTs, UPDATEs, and DELETEs) would be cached in the InnoDB Buffer Pool. Transactional information for the Writes are written immediately to the Redo Logs (ib_logfile0, ib_logfile1). The writes that are posted in the Buffer Pool are periodically flushed from memory to disk via ibdata1 (InsertBuffer for Secondary Indexes, Double Write Buffer). The larger the Buffer Pool, the large the amount of INSERTs can be cached. In a system with 8GB or more of RAM, use 75-80% of the RAM as the innodb_buffer_pool_size. In a system with very little RAM, 25% (to accommodate the OS).

CAVEAT : You can set innodb_doublewrite to 0 to speed up writes even more, but at the risk of data integrity. You can also speed things up with setting innodb_flush_method to O_DIRECT to prevent caching InnoDB to the OS.

Redo Logs (Sized by innodb_log_file_size)

By default, the redo logs are named ib_logfile0 and ib_logfile1 and would be 5MB each. The size should be 25% of the innodb_buffer_pool_size. If the redo logs already exist, add the new setting in my.cnf, shutdown mysql, delete them, and restart mysql.

Log Buffer (Sized by innodb_log_buffer_size)

The log buffer holds changes in RAM before flushing them into the redo logs. The default is 8M. The larger the log buffer, the less the Disk I/O. Be careful with very large transactions, as this may slow down COMMITs by milliseconds.

Accessing Multiple CPUs

MySQL 5.5 and the MySQL 5.1 InnoDB Plugin have settings to have InnoDB Storage Engine access multiple CPUs. Here are the options you need to set:

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. It is usually recommended to set for this is (2 X Number of CPUs) + Number of Disks. Last year, I learned firsthand from the Percona NYC Conference that you should set this to 0 in order to alert the InnoDB Storage Engine to find the best number of threads for the environment it is running in.
innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
innodb_read_io_threads (set this to 3000) and innodb_write_io_threads (set this to 7000) (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. Set these to 64. Also, set the innodb_io_capacity to 10000.

Upgrade to MySQL 5.5

If you have MySQL 5.0, upgrade to MySQL 5.5. If you have MySQL 5.1.37 or prior, upgrade to MySQL 5.5. If you have MySQL 5.1.38 or above and want to remain in MySQL 5.1, install the InnoDB Plugin. That way, you can take advantage of all the CPUs for InnoDB.

MySQL RDS Instance eating up memory and swapping

Memory is not instrumented in MySQL until version 5.7 (currently in development), so this does make your question a bit of a guessing game.

I can see from inside InnoDB status, that it doesn't appear to be InnoDB consuming the memory (assuming you collected this from when the problem was occurring):

Total memory allocated 26217103360; in additional pool allocated 0
Dictionary memory allocated 1212141
Buffer pool size   1563519

Total memory: 24.41 GiB (InnoDB needs about 5-10% more than your buffer pool for internal structures) Buffer Pool memory: ~23.85 GiB

I'm going to have to guess that the memory is consumed at the MySQL layer (not InnoDB), and two common causes are intrinsic temporary tables, and very large sort/join buffers.