mercredi 11 décembre 2013

MariaDB world record price per row 0.0000005$ on a single DELL R710

Don't look at an industry benchmark here, it's a real client story.

200 Billion records in a month and it should be transactional but not durable.

For regular workload we use LOAD DATA INFILE into partitioned InnoDB, but here we have estimated 15TB of RAID storage. This is a lot of disks and it can't no more stay inside a single server internal storage.

MariaDB 5.5 come with TokuDB storage engine for compression, but is it possible in the time frame impose by the workload?

We start benchmarking 380G of raw input data files,  6 Billion rows.

First let's check the compression with the dataset.

Great job my TokuDB 1/5, without tuning a single parameter other than durability! well i love you more every day my TokuDB.

My ex InnoDB, 30% compression missed in 8K, very bad compression ratio and slow insertion time. Don't worry InnoDB i still love you in memory :) 

Ok every love affair have a dark side :)

So now you can see that it works for 200 Billions rows because it give 277 hours of processing time at 200K insert/s.

In a month if we impose 12 hours, 6 days a week of processing with full capacity this is 288 hours.

That was very short, getting compression over 200 Billions records and without sharding will be hard.

Fortunately MariaDB 10 have native network partitioning using the spider contribution don't miss that.

vendredi 5 juillet 2013

MariaDB Storage Engine for CCM forum

CCM Benchmark is one of the leading forum provider on the web,  ROI is a major concern for them  and historically MyISAM was used on the forum replication cluster.  Reason is that MyISAM gave better ROI/performance on data that is hardly electable to cache mechanism.

This post is for MySQL users at scale,  if the number of servers or datacenter cost is not an issue for you, better get some more memory or flash storage and ou will found Lucifer server to demonstrate that your investment is not a lost of money or just migrate to Mongo.  

Quoting Damien Mangin, CTO at CCM "I like my data to be small, who want's to get to a post where the question is not popular and have no answer. Despite cleaning we still get more data than what commodity hardware memory can offer and storing all post in memory would be a major waste of money".

Like many other big web players at an other scale, Damien need to scale on disk not because it's good, but because you can catch more with less hardware. Doing this you need to control the cache missed at the level that you found acceptable and that give constant response time for your workload.

What data size do we get  retaining the most popular forum posts ?

TokuDB Fast
InnoDB 8K
TokuDB Small
InnoDB 4K

What hardware do we have ?

PUMA : MariaDB 5.5 InnoDB 32G RAM

|__ LUCIFER : MariaDB 5.5 InnoDB compressed 8K 64G RAM

|__ GERTRUDE : MariaDB 5.5 MyISAM 32G RAM

|__ MYSQL1 : MariaDB 5.5 MyISAM 32G RAM

|__ MYSQL3 : MariaDB 5.5 TokuDB Fast 32G RAM

What are the top 10 queries, response time on each server ?


SELECT categorie, best_answer_id FROM ccmforum_index WHERE id=169328

No surprise here  that table is small and we notice that that TokuDB and InnoDB compression does not affect the response time of the queries.


SELECT id,message FROM ccmforum WHERE id IN(?,?,?,?,?)

In range of 1 to 5000 values in the IN clause.
This table is the big baby that generate RND IOps .

Interesting you get the raison here of why MyISAM is better than InnoDB at equal hardware on disk bound workload.

3 times better is something that matter as the second most frequent query.
We get almost equal performance for MyISAM(mysql1) and TokuDB(mysql3) knowing that TokuDB get all data in RAM and MyISAM 75% ; and InnoDB (puma) uncompressed 50%.


SELECT parentx FROM uforums WHERE module="download" AND info_id=223


SELECT i.categorie,c.resume,c.title,count(i.categorie) AS nbFROM ccmforum_index i INNER JOIN ccmforum_cat c ON i.categorie=c.idWHERE i.parentx IN(32932,213290,2937,15002,13612,10016,154379,116397,79497,31886,4235,5038,5222,84819,81100,36025,8274,162824,10620,21731,12130,123360,232454) AND c.visibilite=0 AND c.acces=0
GROUP BY i.categorieORDER BY nb DESC


SELECT,s.contribs,s.contribs_technique,p.devise,UNIX_TIMESTAMP(m.ts_create) AS date,,p.photo_etag,m.nick,UNIX_TIMESTAMP(s.ts_last_post) AS ts_last_post,p.siteperso AS website,(m.rang+1) AS level,m.contributeur AS contributor,m.blockedFROM commentcamarche.ccmmembres m INNER JOIN ccmforum_stats s ON LEFT JOIN commentcamarche.ccmprofils p ON IN(1191274)


SELECT,i.titre,i.auteur,UNIX_TIMESTAMP( AS date,i.membre,UNIX_TIMESTAMP(i.datex) AS datex,i.etat,i.categorie,i.parentx,i.member_id,i.reponses,i.dernier,i.dernier_membre,i.premier,i.premier_membre,UNIX_TIMESTAMP(i.datex) AS unix_datex,UNIX_TIMESTAMP( AS unix_date,0 AS view,i.appreciation FROM ccmforum_index i   WHERE i.categorie IN (2,105,10,111,108,106,110,109,107) AND i.etat!=0


select sum(count) as cpt from ccmforum_count


SELECT id,nick FROM commentcamarche.ccmmembres WHERE nick="hyxo"


SELECT m.nick,m.mail,m.valid,s2.site_id AS id_site_create,
(m.ts_create) AS ts_create,UNIX_TIMESTAMP(s.ts_last_post) AS ts_last_post,UNIX_TIMESTAMP(p.ts_last_edit) AS ts_last_edit,m.rang+1 AS level,m.contributeur AS contributor,m.following,m.followers,
.signature,p.configuration AS config,p.domaines AS interest_areas,p.devise AS quote,,m.sexe AS gender,m.ville AS city,m.pays AS country,CONCAT(p.anniversaire_annee,'-',p.anniversaire_mois,'-',p.anniversaire_jour) AS birthdate,
.siteperso AS website,m.newsletter AS optin_ccm,m.optin_part,m.`blocked`,m.messagerie AS accept_pm,m.notifications, AS picture,p.photo_etag AS picture_etag,m.domaine AS registration_domain,
.date AS show_date,p.ville AS show_city, p.pays AS show_country, p.anniversaire AS show_birthdate, p.sexe AS show_gender,
.email AS show_mail, LENGTH(p.siteperso) AS show_website,
.job,,d.biography, AS websiteMD,d.twitter,d.facebook,d.linkedin,d.googleplus,d.firstname,d.lastnameFROM   commentcamarche.ccmmembres mLEFT JOIN commentcamarche.ccmprofils p ON = m.idLEFT JOIN commentcamarche.ccmmembres_data d ON = m.idLEFT JOIN ccmforum_stats s ON = m.idINNER JOIN globals.sites s2 ON s2.domain=m.domaineWHERE

Take away 

TokuDB proved identical MyISAM response time but being at least 2 time smaller on disk, we did not check InnoDB compression on 32G should be a more fair test but it was not the point as CCM have a server with memory to cover InnoDB fatness.

We notice that TokuDB like InnoDB does not bring count(*) query faster if data stay in the cache but TokuDB compression does not hurt the performance in all major queries.