Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Incomplete
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Hello there
Yesterday we tried to migrate from MariaDB 5.2 to 5.3 on one of our servers. We have noticed very huge growth of CPU usage by mysqld processes. The following are 2 pictures showing what happened. Upgrade has been done at about 22:00, used default optimizer_switch settings.
http://clip2net.com/clip/m115425/1331798690-dbprousercpu1-22kb.png
http://clip2net.com/clip/m115425/1331798721-dbprousercpu2-22kb.png
After changing optimizer_switch to index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off (similar to 5.2 values) in some cases CPU usage decreases but not always.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: 5.2 vs 5.3 CPU usage. Is it normal?
Hi Anton,
my educated guess is that MariaDB-5.3 introduces some optimizer feature that adds a penalty for at least some queries that you (or your customers) are running.
By looking at the optimizer_switch, it seems the only change between your "good" settings and the MariaDB 5.3 defaults is engine_condition_pushdown. Default is "on", you set it to "off".
While we know of some workload where ICP leads to performance loss, it has always been in the range of few percent, never as much as your cpu graph suggests.
The only other optimizer features that you have turned on are for the index merge algorithms. Those are quite mature and as far as I know there was no changes to those between MariaDB 5.2 and 5.3.
Since you mention the slow log - do you happen to have examples of slow queries? What we would need are example queries and table structure/data where turning ICP on shows a significant performance loss.
You also mention that the new optimizer flags don't always work. So you might have queries in the slow log with 5.3 that were never slow with 5.2? Such examples would be very helpful too.
Re: 5.2 vs 5.3 CPU usage. Is it normal?
Hi Axel
The optimizer_switch options shown above are the options we turned on/off after we found the problem with CPU usage. These are not the options we used to run with from the beginning. At first we used default set of options (no tuning via my.cnf at all). Then I tried to find a way to reduce CPU usage and the shown options are what I came to. But after reading this bug tracker I found that some switches may affect not only performance, but may produce wrong result sets as well. That's why we decided to downgrade back to 5.2.
I will try to find some examples of difference in slow queries and if succeed I will post examples here.
Re: 5.2 vs 5.3 CPU usage. Is it normal?
Hi Anton,
> The optimizer_switch options shown above are the options we turned on/off
> after we found the problem with CPU usage. These are not the options we
> used to run with from the beginning.
Yes, I'm aware of that. And as I said, those options differ from the default in just one point: engine_condition_pushdown
> I will try to find some examples of difference in slow queries and if succeed
> I will post examples here.
Excellent. Please don't forget to also show us the structure of the underlying tables (SHOW CREATE TABLE ...). If you have any data that you want to keep private, then use our write-only FTP server: http://kb.askmonty.org/en/ftp
Re: 5.2 vs 5.3 CPU usage. Is it normal?
Hi Axel,
After examining our slow query logs I found that normally only about 10-15 customers have something in a daily slow log. During the day we tried 5.3, amount of customers with something in a slow log grew from 10-15 to over 100. I subtracted from the list those who has slow logs daily and finally I've got 103 log files for those whom slow logs were created with 5.3 and never with 5.2.
Then, I've got top 50 most frequent queries using mysqldumpslow and this list is uploaded to your ftp server as ticket-955776-top50-slows.log. Please take a look at it and let me know which queries are interesting for you and for which of them you want to see CREATE TABLE statements and sample data.
Launchpad bug id: 955776
dbprousercpu1.png
LPexportBug955776_dbprousercpu1.png