MySQL Forums
Forum List  »  General

Re: Sending data in processlist
Posted by: Rick James
Date: December 29, 2014 01:16PM

Preliminary analysis...

Version: 5.5.22
16 GB of RAM
You are running on Windows.
You appear to be running entirely (or mostly) InnoDB.

Very important: Increase innodb_buffer_pool_size; evidence:
( innodb_buffer_pool_size / _ram ) = 150M / 16384M = 0.9% -- % of RAM used for InnoDB buffer_pool
( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 189,083 / 1084393 = 17.4% -- Read requests that had to hit disk
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 189,459 / 1084393 = 17.5% -- Read requests that had to hit disk
-- See http://mysql.rjweb.org/doc.php/memory

Very important:
Opened_tables, Opened_files, Opened_table_definitions -- each is about 6/second. This is bogging down MySQL. Based on "Yes I have lots of test#### tables like test1 to test7000", I predict this will be a very serious problem. You have open_files_limit = 7048; I don't know if this is enough, but it might be. You should increase table_open_cache, innodb_open_files, and table_definition_cache. Try 7000; see if mysql will accept such large numbers. (Be sure to increase innodb_buffer_pool_size !) You filled up those caches within 1067 seconds, and are probably thrashing.
Please explain why you need 7000 tables. Usually it is better to have one table with lots of "things" in it rather than have lots of tables, one per "thing", all with the same CREATE TABLE. Increasing the 3 settings might get you going, but the "right" solution may involve a minor design change in the schema.

Much analysis is hindered by:
( Uptime ) = 1,067 -- How long (in seconds) the server has been running.
-- The system has not been up long enough to get reliable suggestions for many of the issues. Fix what you can, then come back with fresh values after the system has been running a several hours.
Even without a useful GLOBAL STATUS, here are a few more comments:

( innodb_log_buffer_size ) = 1M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions.
-- Adjust innodb_log_buffer_size.

( log_slow_queries ) = OFF -- Whether to log slow queries. (Before 5.1.29, 5.6.1)
( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest turning on the slowlog and using long_query_time = 2. This will provide further information about the naughty queries.

( thread_cache_size ) = 100 -- How many extra processes to keep around
-- 0 is good for Windows

Options: ReplyQuote


Subject
Written By
Posted
December 19, 2014 07:55AM
December 20, 2014 12:03AM
December 20, 2014 05:33AM
December 20, 2014 10:18AM
December 26, 2014 02:23AM
December 26, 2014 11:56AM
December 29, 2014 02:59AM
Re: Sending data in processlist
December 29, 2014 01:16PM
December 30, 2014 07:02AM
December 30, 2014 02:55PM
January 01, 2015 01:55AM
January 01, 2015 08:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.