Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Incomplete
-
Affects Version/s: 10.0.17
-
Fix Version/s: N/A
-
Component/s: Query Cache, Storage Engine - InnoDB, Views
-
Labels:
-
Environment:Debian 7 x86_64
Description
I had only one request which is 200* slower on MariaDB than Mysql. The others thousands of requests are in the same line or quicker with MariaDB.
I can change the DB server by MariaDB and Mysql and reproduce it each time on Debian 7 on several servers (Virtual or production servers).
I can not send the full database but only the request. It is store on a view. In the example attached, there is no performance issue with 'erp_view_supplyneeds_base', a SELECT take around 0.4 sec and then is well stored in cache, but only with 'erp_view_supplyneeds_global' on wich a SELECT take around 30 sec on MariaDB and is never stored in cache.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I'm full agree with you but I can not change the request, I just host it... And we gone back to Mysql. I report it here because it seems that there is a real bug behind this problem.
The "request" is erp_view_supplyneeds_global. I assume the bug come from the huge amount of SUM(). Just a SELECT * FROM `erp_view_supplyneeds_global` WHERE `product_id` = 1; took 30 sec, instead of 0,5 sec on Mysql.
To help, I will try next week to reproduce the problem on a clean DB and send it to you.
A comparative compare of EXPLAIN request_query on mariadb and mysql (which version?) would help.
To help, I will try next week to reproduce the problem on a clean DB and send it to you.
Thank you.
private uploads are there if required: https://mariadb.com/kb/en/meta/ftp/
There was no feedback for a month, I'm closing it.
Feel free to add a comment or reopen the issue if you have more information to provide.
So the "request" is erp_view_supplyneeds_global?
What queries are you doing on these view (this is usually bad for performance anyway)?
You haven't included your table structures. SHOW INDEXES FROM
{table}for all table would also help.
Can you include EXPLAIN
{query}for both views? And on the queries you are using?
The larger query might be exceeding query_cache_limit size and therefore isn't cached.
Honestly these queries are horrible and quite hard to read. use language features.
IF(x > y, x-y, 0) is the same as MAX(x-y,0)
if(x,x,0) is same as COALESCE(x,0)
If you can simplify and include them in a readable presentation that would be good too.