Mysql和mariaDB性能测试对比

2014-11-25 10:21:22

        在项目规划上选择了mysql进行数据存储处理,不过在功能测试的过程发现mysql的性能并不理想,在数据添加和修改的业务上得到的一个测试结果非常不满足。从网上的一些信息来看mysql的innodb在这方面的确是不理想IO损耗非常大,通过一些参数调整也没看到有比较好的改善。由于缺少相关DBA的支持所以在mysql的选择上感觉可控性不高,所以打算选择其他数据库;经了解mysql有另外一个分支mariadb社区反映其效率有不少改进,所以部署了一份和mysql进行了一个性对测试对比。

测试用例

        测试针对Northwind的数据库进行压力测试,具体测试用例如下:

  • 随机获取订单和相关明细
                int i =  System.Threading.Interlocked.Increment(ref mIndex)%mOrderids.Count;
                int orderid = mOrderids[i];
                (Model.Orders.orderID == orderid).ListFirst<Model.Orders>(cc);
                if (DB == Peanut.DB.DB2 || DB == Peanut.DB.DB3)
                {
                    using (Peanut.DBContext.ChangeTable<Model.OrderDetails>("`Order Details`"))
                    {
                       
                        (Model.OrderDetails.orderID == orderid).List<Model.OrderDetails>(cc);
                    }
                }
                else
                {
                    (Model.OrderDetails.orderID == orderid).List<Model.OrderDetails>(cc);
                }
  • 添加雇员
                string id = Guid.NewGuid().ToString("N");
                Model.Employees item = new Model.Employees();
                item.Address = "gz"+id;
                item.City = "gz";
                item.Country = "cn";
                item.Region = "gd";
                item.Title = "程序员";
                item.BirthDate = DateTime.Now.AddYears(-10);
                item.HireDate = item.BirthDate.AddDays(20) ;
                item.FirstName = "fan";
                item.LastName = "henry";
                item.Notes = id;
                item.Save(cc);
  • 获取客户订单的TOP 10
     int i = System.Threading.Interlocked.Increment(ref mIndex) % mCustomers.Count;
                string customerid = mCustomers[i];
                Peanut.SQL SQL;
                if (DB == Peanut.DB.DB2 || DB == Peanut.DB.DB3)
                {
                    SQL = "select * from northwind.Orders where customerid=@p1 limit 0,10";
                }
                else
                {
                    SQL = "select top 10 * from Orders where customerid=@p1";
                }
                SQL["p1", customerid].List<Model.Orders>(cc);
  • 添加订单和明细(事务)
     Orders order = new Orders();
                    order.EmployeeID = mEmployeesID[mEmployeeIndex % mEmployeesID.Count];
                    order.CustomerID = mCustomersID[mCustomerIndex % mCustomersID.Count];
                    order.ShipVia = mShippers[mShippersIndex % mShippers.Count];
                    order.Freight = 94.5m;
                    order.OrderDate = mOrderDateTime[mOrderDateTimeIndex % mOrderDateTime.Count];
                    order.RequiredDate = order.OrderDate.AddDays(30);
                    order.ShippedDate = order.OrderDate.AddDays(30);
                    order.ShipAddress = "gz ld";
                    order.ShipCity = "gz";
                    order.ShipCountry = "cn";
                    order.ShipName = "sdfsdf";
                    order.ShipPostalCode = "510500";
                    order.ShipRegion = "gd";
                    order.Save(cc);
                    int orderid = sql.GetValue<int>(cc);
                    int items = mDetails[mDetailsIndex % mDetails.Count];
                    int pindex = mProductIndex;
                    for (int i = 0; i < items; i++)
                    {
                        pindex++;
                        Products product = mProducts[pindex % mProducts.Count];
                        OrderDetails detail = new OrderDetails();
                        detail.OrderID = orderid;
                        detail.ProductID = product.ProductID;
                        detail.Quantity = 5;
                        detail.UnitPrice = product.UnitPrice;
                        detail.Discount = 0.8;
                        if (DB == Peanut.DB.DB2 || DB == Peanut.DB.DB3)
                        {
                            using (Peanut.DBContext.ChangeTable<Model.OrderDetails>("`Order Details`"))
                            {
                                detail.Save(cc);
                            }
                        }
                        else
                        {
                            detail.Save(cc);
                        }
                    }
                    cc.Commit();

测试结果

                        


        几测试反映的情况来看在数据查询上两者的处理能力并没有多大的差距,但在数据添加和事务处理上MariaDB有着明显的效率优势。从处理的事务数来看其效率远高于MySQL之前的版本;由于MariaDB是基于MySQL的一个版本分支所以完全兼容原有一些操作的Client API;所以并不需要对程度进行调度即可以使用。而MariaDB现在已经完全被阿里和GOOGLE所使用,所以其成熟度和可靠性也得到了充分的证明。


下载测试代码


关注微信公众号