Thanks for the day-long SHOW GLOBAL STATUS.
> 5) Unable to set 8GB innodb_buffer_pool_size after set 8GB the mysql service not started. Error : innobase_buffer_pool_size can't be over 4GB on 32-bit systems
> version_compile_os Win32
Ouch! You have 16GB of RAM, but you cannot use more than 4GB because the OS is 32-bit. Upgrade the OS and the MySQL.
(Meanwhile, you could set innodb_buffer_pool_size=1500M to get some performance gain.)
(Sorry, I should have looked for that last time.)
( Innodb_buffer_pool_reads ) = 14,506,192 / 81617 = 177 /sec -- InnoDB buffer_pool I/O read rate
( innodb_buffer_pool_size / _ram ) = 150M / 16384M = 0.9% -- % of RAM used for InnoDB buffer_pool
--
http://mysql.rjweb.org/doc.php/memory
( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 14,506,192 / 391100730 = 3.7% -- Read requests that had to hit disk
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 30,329,302 / 391100730 = 7.8% -- Read requests that had to hit disk
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((14506192 + 985554) ) / 81617 = 189 /sec -- InnoDB I/O
( Innodb_buffer_pool_read_ahead_evicted ) = 2,825,390 / 81617 = 35 /sec
-- Increase innodb_buffer_pool_size?
( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 441332) / 11585869 = 3.8% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor. Or are you doing "SHOW FIELDS"??
( Qcache_hits / Qcache_inserts ) = 3,173 / 1332920 = 0.00238 -- Hit to insert ratio -- high is good
-- Consider turning off the query cache.
( Qcache_hits / (Qcache_hits + Com_select) ) = 3,173 / (3173 + 1376512) = 0.2% -- Hit ratio -- SELECTs that used QC
-- Consider turning off the query cache.
( Select_scan ) = 128,794 / 81617 = 1.6 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 128,794 / 1376512 = 9.4% -- % of selects doing full table scan. -- Add indexes / optimize queries
( 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 2
( Slow_queries ) = 463,574 / 81617 = 5.7 /sec -- Frequency (Slow queries per sec)
-- Rework slow guys; improve indexes; watch disk space for slow log file
( Slow_queries / Questions ) = 463,574 / 11585869 = 4.0% -- Frequency (% of all queries)
-- Find slow queries; check indexes.
( Max_used_connections / max_connections ) = 1,001 / 1000 = 100.1% -- Peak % of connections
-- increase max_connections and/or decrease wait_timeout
-- Ouch! This issue will probably go away when the rest is resolved, but we will need to check it later.
( Aborted_connects ) = 492,572 / 81617 = 6 /sec -- Could not get a connection, or hacker ?
-- Raise max_connections
( Connections ) = 2,158,022 / 81617 = 26 /sec -- Connections
-- Increase wait_timeout; use pooling?
USE your database before doing SHOW TABLE STATUS; Then show us only a few of the thousands. You have provided info about the tables in information_schema.
My bad... I did not clarify the "session" version "global".
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 10.00000 |
+-----------------+----------+
mysql> SET @@global.long_query_time = 2;
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 10.00000 | -- The "session" value is UNchanged
+-----------------+----------+
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 | -- The "global" value IS changes
+-----------------+----------+
You had successfully changed the value to 2 for all _future_ connections _until_ the next mysql restart.
> But I see in mysql processlist shows 1001 record.
That agrees with max_used_connections. I don't yet see why it is happening.
> take 50 chunk (in every count there are 3/4 select and 2/3 insert query that mean 200 select query and 150 insert query for 50 chunk)
* Let's see the SELECTs.
* Are all 200+150 being done via one connection? Or via 50 connections?
> One more think can I install the mysql 5.6 and see my problem? please suggest.
Do not worry about 5.6 until after getting 64-bit Windows!
You have several issues; they should probably be dealt with in this order:
1. 64-bit OS
2. 64-bit mysql (step 1 is prerequisite)
3. Increase innodb_buffer_pool_size (step 2 is prerequisite)
4. turn off query_cache, decrease long_query_time, etc.
5. Then let's revisit the queries and another SHOW GLOBAL STATUS (plus a new SHOW VARIABLES to reflect the 64-bit version). (step 3 is prerequisite)