All geek questions in one place

MySQL stuck in sorting state

Recently, I have a problem with my site, mysql loading reaches 800%, and most queries get stuck in the "Sort Results" setting.

The problem is that I did a test. The same query with 1 result set, with sorting, took 9.8 seconds and without it takes 0.02 seconds. The request contains conditions and a group.

sql is optimized, the search is done in indexed fields, and everything went fine until two days ago. No road splash, no code change, nothing.

Have you had this problem in the past or have an idea how I can fix it?

thanks

Edit: Request Explanation:

id: 1 select_type: SIMPLE table: m type: range possible_keys: posterid_to_idx,to,poster_id key: posterid_to_idx key_len: 8 ref: NULL rows: 6 Extra: Using where; Using filesort

Again, the same query worked perfectly a couple of days ago.

  `id` int(11) NOT NULL AUTO_INCREMENT, `body` longtext NOT NULL, `poster_id` int(11) NOT NULL, `poster_name` varchar(50) NOT NULL, `to` int(11) NOT NULL, `added` int(11) NOT NULL, `picture` varchar(64) NOT NULL, `folder` int(11) DEFAULT NULL, `read` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `posterid_to_idx` (`poster_id`,`to`), KEY `to` (`to`), KEY `poster_id` (`poster_id`), KEY `msg` (`id`,`poster_id`,`to`), KEY `added` (`added`) ) ENGINE=InnoDB AUTO_INCREMENT=37548617 DEFAULT CHARSET=latin1 SELECT SQL_CALC_FOUND_ROWS m.body, m.id, m.poster_id, m.poster_name, m.to, m.added, m.picture, m.folder FROM messages m WHERE ((m.poster_id = '1885585' OR m.poster_id = '1886341') AND (m.to = '1886341' OR m.to = '1885585')) ORDER BY m.id DESC LIMIT 0, 10

Top pin:

 CPU: 6.0% user, 0.0% nice, 38.8% system, 0.2% interrupt, 55.0% idle Mem: 11G Active, 1400M Inact, 5451M Wired, 87M Cache, 4923M Buf, 29G Free Swap: 8000M Total, 8000M Free
+4
source share
1 answer

So, after many attempts with my.cnf, I fixed the problem:

  • Decreasing request cache value

  • Decreasing the key buffer value

  • Decrease tmp table value

  • Disabled performance circuit

I just tested various configurations and knew about full traffic (about 6 thousand people on the Internet), my mysql remains at 50% load, which is quite simple. No sql or modifications were made simply by incorporating the configuration.

Not sure if this is the answer for someone with the same problem, but my answer is at least.

my.cnf

 query_cache_limit=128M query_cache_size=128M query_cache_type=1 key_buffer_size=128M tmp_table_size=64M performance_schema=off
+1
source

Source: https://habr.com/ru/post/1491961/

More articles:


All Articles