Re: Slow query, trying to optimize search..
Observations:
Version: 5.6.19-0ubuntu0.14.04.1
32 GB of RAM
Uptime = 09:13:07; some GLOBAL STATUS should be somewhat meaningful.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
( innodb_buffer_pool_size / _ram ) = 27,917,287,424 / 32768M = 81.2% -- % of RAM used for InnoDB buffer_pool
--> That's somewhat high. Be careful not to swap. Recommend innodb_buffer_pool_size = 24G
Reference:
http://mysql.rjweb.org/doc.php/memory
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 56,960 / 106453 = 53.5% -- Write requests that had to hit disk
-- Cache is not very efficient for writes; however, that's only 1 page/second, so there is no real problem.
Still, only 4.1 reads/second and 1.7 writes/sec. An ordinary disk can handle 100/sec (versus 5.8), so you are not I/O bound (at least not during the last 9 hours).
--> This also implies that you don't necessarily need more RAM.
Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size = 60.4%
--> I read this to mean that your working set is smaller than the cache. So, again, more RAM seems not to be needed.
--> Of course, if you expect a lot more data and/or activity, things could change.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 16,290,304 / (33187 / 3600) / 2 / 48M = 0.0176 -- Ratio
--> Very low iblog activity, as if there is not much writing going on. Again, no problem.
innodb_read_io_threads = 64 (ditto for write)
--> These are excessive for the number of cores you have. Recommend 8 each.
( max_connections ) = 2,048 -- Maximum number of connections (threads). Impacts various allocations.
-- If max_connections is too high and various memory settings are high, you could run out of RAM.
max_used_connections = 18
--> Recommend lowering max_connections to a few hundred.
( local_infile ) = ON
-- local_infile = ON is a potential security issue
( query_cache_size ) = 1024M -- Size of QC
--> Too large = too much overhead. Recommend either 0 or no more than 50M.
--> This is possibly a serious issue.
The QC is 95% empty ( Qcache_free_memory / query_cache_size ) -- This may imply that the QC is not very useful.
A variety of other STATUS values (hits/inserts, hits/selects, lomem_prunes, etc) imply that the QC is doing fine.
--> Still, you should lower query_cache_size.
( tmp_table_size ) = 256M -- Limit on size of temp tables used to support a SELECT
--> Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.
( Handler_read_rnd ) = 35,270,406 / 33187 = 1062 /sec -- High if lots of table scans
-- possibly inadequate INDEXes
( Select_scan ) = 71,706 / 33187 = 2.2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 71,706 / 145607 = 49.2% -- % of selects doing full table scan.
-- Add indexes / optimize queries
Questions = 36 queries per second
-- A rather light load.
( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)
-- Suggest ON
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
Slow_queries / Questions = 0.4% -- not a bad number. That is you don't have many slow queries based on the current long_query_time=10
--> After ON and 2 have been in place for a few days, use pt_query_digest to find the "naughtiest" queries.
( Connections ) = 305,145 / 33187 = 9.2 /sec -- Connections
-- Increase wait_timeout; use pooling?
( thread_cache_size ) = 16,384 -- How many extra processes to keep around (Not relevant when using thread pooling)
-- Recommend only 20
More on indexing:
http://mysql.rjweb.org/doc.php/index1
Bottom line (from tuning):
* No more RAM.
* Some tuning needed.
* No performance problems on the whole.
* Adding INDEXes may greatly help individual queries and and further lighten the system load.
Schema critique:
> `title` CHAR(255) NULL DEFAULT NULL,
Unless `title` is always 255 chars long, make it VARCHAR(255). Ditto for other CHAR usages. This will shrink the size of the records, the disk footprint, etc, and have some positive impact on performance.
> NULL DEFAULT NULL
Are all the fields really NULLable? Normally, one uses NOT NULL for most columns.
> AUTO_INCREMENT=14000000
What's up? That's an awfully 'round' number. Or have you inserted exactly 14M rows?
Hmmmm... Is this machine merely in the testing phase, and has not seen a full load of traffic? If so the GLOBAL STATUS values are subject to change. Feel free to ask for another tuning review. Or you could give me the VARIABLES, GLOBAL STATUS, and RAM of the existing server to see what it is like with real traffic.