Can someone tell me which mysql version provides better performance for magento 1.9.1?
Mysql 5.5 or 5.6? Thanks
databasemagento-1.9MySQLmysql-configperformance
Can someone tell me which mysql version provides better performance for magento 1.9.1?
Mysql 5.5 or 5.6? Thanks
You're getting into a broad, broad world of optimisation here and there certainly isn't a one size fits all approach.
Do you mean the page load time for a single user, or the overall capacity/total concurrency? The two are very distinctly different - and not strictly related. It is entirely possible to have a fast store with limited capacity; or a slow store with lots of capacity.
So when addressing either type of performance:
You have to tackle each independently with their own solutions - especially since each have their own bottlenecks.
Lets make the assumption you are with a competent host that has already configured the other aspects of your server optimally for your store.
Is MySQL the bottleneck
No. Not directly. Its all about latency, in the majority of cases when testing page load time - only the caches will be hit. So the key here is to minimise latency.
But these changes will have such a fractional impact on page load time - where the bottleneck is really elsewhere.
The application is the bottleneck. Not the software. So merely improving core-code or making your template less heavy will have a far more dramatic effect on performance than ANY MySQL configuration change.
What wouldn't we bother with
s1 For separate database servers only. Doesn't apply to local DB servers.
Is MySQL the bottleneck
Maybe. But only once you've nailed your PHP performance and capacity to the point where MySQL is slowing things down. If you've got Varnish and FPC properly configured (don't get us started on how many failed attempts we've seen with either) - then MySQL does become a bottleneck.
So in addition to the above modifications.
What wouldn't we bother with
The last paragraph really leads on to a key area of read and write scalability. Read scaling can be performed fairly infinitely without too much complication with the addition of more and more slaves.
Given Magento's ratio of Reads to Writes is about 0.1% - considering writes shouldn't be much of a concern. That's why I've not bothered mentioning MySQL Cluster and its clever features like auto-sharding (splitting tables off to separate machines).
Hardware is easily the quickest answer when it comes to improvement, so I've deliberately not mentioned it above in both scenarios.
But all the software changes in the world aren't going to make any difference if your underlying hardware is insufficient. That could mean...
Nowadays, there's a really high ceiling on how high you can actually scale on hardware. Lets ignore the myth of infinite scaling "in the cloud" as cloud hardware tends to be fairly mediocre. For example Amazon's flagship models only being 12 Cores @ 3.3GHz. But outside of this, there are some very powerful servers available - our top server has 160 cores and 2TB (yes, Terabytes) of RAM. We've not seen anyone exceed the capabilities of that just yet.
So you've got a massive scope for vertical scaling, before you even need to consider horizontal scaling.
Its worth mentioning that in the pursuit of performance, the bottleneck will always keep moving.
For a stock Magento store.
Turn the caches on. PHP is the bottleneck
Add a backend cache. PHP is the bottleneck
Add a application-level full page cache. PHP is the bottleneck
Add a server-level front-end cache (eg. Varnish). MySQL is the bottleneck
Add an alternative search/layered navigation engine (eg. SOLR/Sphinx). PHP is the bottleneck
Add more application servers. MySQL is the bottleneck
Add a MySQL slave. Front-end cache is the bottleneck
Add more front-end cache servers. PHP is the bottleneck
Add more application servers. SOLR/Sphinx is the bottleneck
Etcetera.
It pretty much becomes a case of rinse-wash repeat. But what is clear to understand is that MySQL certainly isn't the first port of call for optimisation - and really only comes into play when MySQL is consuming more CPU proportionally to PHP - and this ONLY ever happens when both FPC and Varnish are in use and the server(s) are purely processing orders and nothing much else (because everything else is in caches).
Simply adding a MySQL slave because you read us say above that it will help, can cost you performance and reliability on a huge level. A congested network, low spec slave server or even improper settings can cause replication problems that can render your store slower than it was to begin with - or cause synchronisation issues between the Master and Slave.
Example 1 - 300 orders per hour
We've used the following hardware to serve 300 orders per hour; and only at that tipping point - we then felt the need to add a dedicated MySQL server and a local MySQL slave.
1 Server
CPU: 2x Intel E5-4620
RAM: 64GB
HDD: 4x 80k IOP/s SSDs
RAID: Hardware RAID 10
Magento Version: Magento EE
OS: MageStack
During the entire time, load averages remained under 3.00.
Example 2 - 180 orders per hour
Just two days ago, a new client of ours easily soaked up a big traffic spike. Processing 180 orders per hour with a single-server and Magento Community Edition.
1 Server
CPU: 2x Intel E5-4620
RAM: 64GB
HDD: 4x 80k IOP/s SSDs
RAID: Hardware RAID 10
Magento Version: Magento CE
OS: MageStack
Website: Wellgosh.com
During the entire time, load averages remained under 6.00. The load was higher in this scenario and that was down to a couple of factors.
And given the recency of this, we've still got the detailed statistics to give some feedback by means of graphs. These tell an excellent story of how load is distributed amongst the key components of a separated Magento architecture (load balancer, web server, db server etc. - using MageStack).
So from front to back ... the date you want to look at is at 12:00am on 22nd February.
Firewall Packets
Varnish Traffic
Nginx Traffic
MySQL Load
CPU Load
And most importantly, distribution of load
This image really tells it all. And it is that MySQL is certainly not a burden - not yet at least. So our advice, focus your performance concerns elsewhere, unless you are processing more than a few thousand orders per hour.
Making performance changes isn't "one size fits all". It is a case of analysing your current bottlenecks and making subtle changes/adjustments to suit your store and infrastructure.
We do use Percona XtraDB, almost exclusively. We run custom-compiled builds of MySQL that we developed specifically for Magento and had consulted Percona during the process. But it wasn't just performance that influenced this choice.
And much more. So using it over MySQL had other advantages than just performance. In fact - MySQL is and has always been the least of our concerns in the pursuit of performance and stability.
Attributions: wellgosh.com, sonassi.com, iebmedia.com.
I had the same problem. I had to downgrade back to mysql 5.5 for it to work.
I narrowed the problem down to this query when searching by name which returns no results because it doesn't match the word:
select
e.*,
price_index.price,
price_index.final_price,
IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) as minimal_price,
price_index.min_price,
price_index.max_price,
price_index.tier_price,
cat_index.position as cat_index_position
from
catalog_product_entity as e
inner join
catalog_product_index_price as price_index
on (price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0)
inner join
catalog_category_product_index as cat_index on (cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id='2')
where
(e.entity_id IN
(SELECT
t1.entity_id
FROM
catalog_product_entity_varchar AS t1
LEFT JOIN
catalog_product_entity_varchar AS t2
ON t1.entity_id = t2.entity_id
AND t1.attribute_id = t2.attribute_id
AND t2.store_id=1
WHERE
(t1.store_id = 0)
AND
(t1.attribute_id = 60)
AND
(t1.entity_id = price_index.entity_id)
AND
(IF(t2.value_id>0, t2.value, t1.value) LIKE '%gift%')
)
);
Near the end the IF(t2.value_id>0, t2.value, t1.value) doesn't seem to return anything when t2.value_id and t2.value are NULL, if I change it to IF(t1.value_id>0, t1.value, t1.value), it worked.
You can construct the query that is being used for your advanced search by putting:
var_dump($this->getSearchModel()->getProductCollection());
die();
in getResultCount() from app\code\local\Mage\CatalogSearch\Block\Advanced\Result.php
Then looking in the _parts section
Best Answer
As in most cases, it depends quite specifically on the version you're running, but according the Official Magento CE User Guide for v1.9.1, (p.12) it is a System Requirement that you run
This guide also confirms the same point: although it may be POSSIBLE to run Magento 1.9.1 on MySQL versions prior to 5.6, it is recommended to use 5.6
Although there has been some discussion about the specific tuning parameters needed, and the differences you may encounter if running MySQL on Amazon Web Services RDS, the general consensus seems to support that 5.6 will AT LEAST double the performance of 5.5, with Oracle claiming it's 230% faster on read workloads (over 3x). I'm not sure I'd give their marketing literature the same weight as data-driven benchmarks, but there should be no doubt - you should be running MySQL 5.6 if it's at all possible.
You do need to take absolute performance statements with a grain of salt because of the myriad of tuning options available and massive variances possible between environments. But the overwhelming consensus seems to support that you should be running 5.6 if possible with your webhost.
I'd also recommend you take the time to really dig into Ben Lessani's incredibly thorough deep dive into a similar question. He notes that one of the first things you need to do is define "performance" specifically:
There is no easy way to answer this question, not if you want to truly address the issue of performance / scalability and the inevitable tradeoffs that pursuing those objectives may entail.