Tuesday, September 8, 2009
Technorati with MySQL
Technorati tags grew from 0 to 100 million in a year.
Technorati has about 10TB of core data in MySQL over about 20 machines and they use replication. With replication they add 100TB and 200 machines more. Currently growing at about 1TB per day in total.
A service oriented architecture to separate physical and logical access is used. Technorati uses commodity hardware and Open Source software.
Scaling Technorati Tags : Launched on Janurary 10 of 2005.
Tags are partitioned by entity (tags and posttags). Tags database is separate. Post information in one set of databases and tags information in another set of databases. A caching layer is also present. Overtime Technorati has blended the use of InnoDB and MyISAM based on use.
Currently around 120 million tags in a single table distributed using replication.
100 million records (tags) is where they start thinking of diving the data in multiple tags.
At this point they are processing half a million post tags a day.
A lot of writes (more than queries).
Release early release often.
Get feedback
Fix it fast
move on
In January 05: 1 master , 1 standby
March 05, 1 master, 1 standby
2 InnoDB query slaves
Jun 05, 1 master 1 standby
3 MyISAM query slaves
Sep 05 1 master 1 standby
6 MyISAM query slaves
Jan 06
1 master, 1 standby
6 MyISAM query slaves
3 MyISAM async count slaves
saving...
MyISAM vs InnoDB.
InnoDB is the right choice for Master-class DBs (data integrity is crucial and write loads are high)
MyISAM right choiice for GROUP BY queries and for read-mostly applications.
Partitioning:
Partition data by various dimensions: Time, Key, Entity, Random key, Finxed vs Variable length.
If you do entity partition (separate posts and tags in different databases) the drawback is that you cannot do joins.
Master database is called cosmos.
Partitioning at Technorati
Technorati uses all of these partitions and also uses some in combionation.
The Cosmos DB is used for the entire blogosphere.
ID based using mod to allocate to shard
Entity based to organize commonly queried lookup tables in one location
Entity based but ID range partitioned.
TIme based for reporting data (hourly or daily). Every web hit is logged. Hourly tables are switched to daily tables. Time based partitioning is really important to Technorati. The hot front end needs to be on better faster hardware.
Examples of Partitioning.
Main data set is sharded by Blog ID with a central sequence generation and map.
Currently running MySQL 4.0 and 4.1. Not ready to jump to 5.0 (being used in a playground).
Replication is ennential to distribute query load and provide for redundancy.
Type of replication used is Master -> Master -> Slave to distribute common lookup tables.
A new slave is bootstrapped using mysqldump.
Technorati has 200+ instances of MySQL and they need to know if they're all happy. To do this they create a management DB and use a heartbeat table and record state in management DB. To this they have put a web front end with sorting and filtering.
MySQL is used to manage meta-data.
Technorati has about 10TB of core data in MySQL over about 20 machines and they use replication. With replication they add 100TB and 200 machines more. Currently growing at about 1TB per day in total.
A service oriented architecture to separate physical and logical access is used. Technorati uses commodity hardware and Open Source software.
Scaling Technorati Tags : Launched on Janurary 10 of 2005.
Tags are partitioned by entity (tags and posttags). Tags database is separate. Post information in one set of databases and tags information in another set of databases. A caching layer is also present. Overtime Technorati has blended the use of InnoDB and MyISAM based on use.
Currently around 120 million tags in a single table distributed using replication.
100 million records (tags) is where they start thinking of diving the data in multiple tags.
At this point they are processing half a million post tags a day.
A lot of writes (more than queries).
Release early release often.
Get feedback
Fix it fast
move on
In January 05: 1 master , 1 standby
March 05, 1 master, 1 standby
2 InnoDB query slaves
Jun 05, 1 master 1 standby
3 MyISAM query slaves
Sep 05 1 master 1 standby
6 MyISAM query slaves
Jan 06
1 master, 1 standby
6 MyISAM query slaves
3 MyISAM async count slaves
saving...
MyISAM vs InnoDB.
InnoDB is the right choice for Master-class DBs (data integrity is crucial and write loads are high)
MyISAM right choiice for GROUP BY queries and for read-mostly applications.
Partitioning:
Partition data by various dimensions: Time, Key, Entity, Random key, Finxed vs Variable length.
If you do entity partition (separate posts and tags in different databases) the drawback is that you cannot do joins.
Master database is called cosmos.
Partitioning at Technorati
Technorati uses all of these partitions and also uses some in combionation.
The Cosmos DB is used for the entire blogosphere.
ID based using mod to allocate to shard
Entity based to organize commonly queried lookup tables in one location
Entity based but ID range partitioned.
TIme based for reporting data (hourly or daily). Every web hit is logged. Hourly tables are switched to daily tables. Time based partitioning is really important to Technorati. The hot front end needs to be on better faster hardware.
Examples of Partitioning.
Main data set is sharded by Blog ID with a central sequence generation and map.
Currently running MySQL 4.0 and 4.1. Not ready to jump to 5.0 (being used in a playground).
Replication is ennential to distribute query load and provide for redundancy.
Type of replication used is Master -> Master -> Slave to distribute common lookup tables.
A new slave is bootstrapped using mysqldump.
Technorati has 200+ instances of MySQL and they need to know if they're all happy. To do this they create a management DB and use a heartbeat table and record state in management DB. To this they have put a web front end with sorting and filtering.
MySQL is used to manage meta-data.
Subscribe to:
Posts (Atom)
