Skip navigation links

MySQL Forums :: Performance :: Poor performance of MySQL 5.5


Advanced Search

Re: Poor performance of MySQL 5.5
Posted by: Rick James ()
Date: June 03, 2011 09:33AM

> processors are working at 100% for 1-2 second when the data on the tables are modified
Here's the likely cause:
> query_cache_size = 150M
Set that to 0 and set query_cache_type = OFF

The "query cache" acquires global mutex(es) all too frequently. A global mutex effectively disables all but one core. You have lots of cores; they are sitting there waiting for a turn.

One query can use only one core. (InnoDB has some 'helper' threads that do some extra work.)

5.1 performance stops increasing and starts decreasing at 4 to 8 cores. 5.6 should be increase until about 16 cores, then decrease. HT is a double hit -- it moves you past the peak efficiency, and it slows down each core. A pair of HT cores in one CPU will not do as much work as two single threads, one per CPU. (Or something like that. -- The technology keeps changing, and I have researched your particular CPU architecture.)

> about 300 queries per second, with 80 simultaneous connections
I often see servers that 'busy'. Yet when I do SHOW PROCESSLIST, I rarely see more than one query running. This is because simple queries run so fast that the vanish before I can see them. (At the other extreme I have seen 9 queries run in parallel for more than a week.) Neither qps, nor "simultaneous connections" is a good metric for how busy the server is. A better metric is the "typical number of running threads". Do SHOW PROCESSLIST, then ignore 'Sleep'. You don't need (and can't use) more cores than that number.

Whenever a write occurs to a table, _all_ entries for that table need to be removed from the Query cache. This process is (unfortunately) linear with the size of the QC. 150MB is a lot to scan through on every write!

If it turns out that you can effectively use the QC (most production systems can't), then I recommend no more than 40MB and use DEMAND, plus put SQL_CACHE or SQL_NO_CACHE in each SELECT so that DEMAND knows which to do.

> innodb_buffer_pool_instances = 2
> innodb_buffer_pool_size = 700M
The manual recommends not having 'instances' smaller than 1G.

If you are using InnoDB, then this is much to small for a 16GB machine (unless that's how small your data is):
> innodb_buffer_pool_size = 700M

More tips:
http://mysql.rjweb.org/doc.php/memory

I see that you have the slowlog turned on. Use mysqldumpslow to find out what are the naughtiest queries. Let's discuss them.

Options: ReplyQuote


Subject Views Written By Posted
Poor performance of MySQL 5.5 8104 Matteo Canella 05/23/2011 04:43AM
Re: Poor performance of MySQL 5.5 2962 Rick James 05/24/2011 08:14AM
Re: Poor performance of MySQL 5.5 3180 Matteo Canella 05/25/2011 04:10AM
Re: Poor performance of MySQL 5.5 3315 Rick James 05/25/2011 10:40AM
Re: Poor performance of MySQL 5.5 2152 Merlotti Nicola 05/31/2011 07:15AM
Re: Poor performance of MySQL 5.5 1883 Rick James 05/31/2011 09:03AM
Re: Poor performance of MySQL 5.5 1689 Matteo Canella 05/31/2011 11:15AM
Re: Poor performance of MySQL 5.5 2198 Matteo Canella 06/03/2011 01:44AM
Re: Poor performance of MySQL 5.5 1373 Matteo Canella 06/03/2011 02:20AM
Re: Poor performance of MySQL 5.5 2247 Rick James 06/03/2011 09:33AM
Re: Poor performance of MySQL 5.5 1705 Nicola Merlotti 06/03/2011 11:25AM
Re: Poor performance of MySQL 5.5 2572 Matteo Canella 06/09/2011 05:36AM
Re: Poor performance of MySQL 5.5 1503 Nicola Merlotti 06/03/2011 11:38AM
Re: Poor performance of MySQL 5.5 1340 Rick James 06/03/2011 07:13PM
Re: Poor performance of MySQL 5.5 1228 Vladislav Vaintroub 06/11/2011 07:30PM
Re: Poor performance of MySQL 5.5 1120 Vladislav Vaintroub 06/11/2011 07:32PM
Re: Poor performance of MySQL 5.5 1422 Matteo Canella 06/12/2011 10:09AM
Re: Poor performance of MySQL 5.5 1176 Jerry Richart 06/15/2011 01:08PM


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.