From Oracle to MongoDB, real uses cases (FIB - UPC)

Talk given in the Barcelona School of Informatics (FIB).

Real use cases with MongoDB. A huge success story and another not so successful story.

1. Pablo Enfedaque @pablitoev56 FIB - 16.04.2013 Real uses cases at Telefonica Digital From Oracle to MongoDB
2. Introduction • Telefonica PDI. Who? • Personalisation Server. Why? What? The SQL version • Data model and architecture • Integrations, problems and improvements The NoSQL version • Data model and architecture • Performance boost • The bad and the ugly Conclusions • Personal thoughts • Questions Content A success story? • Orchestration framework • Possible solutions
3. Título del capítulo Máximo 3 líneas 01 Introduction
4. 4 Telefónica PDI Telefonica PDI. Who? •  Telefonica §  Fifth largest telecommunications company in the world §  Operations in Europe (7 countries), the United States and Latin America (15 countries) •  Telefonica Digital §  Web and mobile digital contents and services division •  Product Development and Innovation unit §  Formerly Telefonica R&D §  Product & service development, platforms development, research, technology strategy, user experience and deployment & operation §  Around 70 different on going projects at all time. 01
5. 5 Telefonica PDI Personalisation Server. What? •  User profiling system •  Machine learning •  Recommendations •  Customer’s profile storage 01
6. 6 Telefonica PDI Opt-in and profile module. Why? •  Users data, profile and permissions, was scattered across different storages 01 •  Gender •  Film and music preferences IPTV •  Permission to contact by SMS? •  Gender ADSL •  ADDRESS •  Music preferences Music tickets •  ADDRESS •  Permission to contact by SMS? Mobile So you want to know my address… AGAIN?!
7. 7 Telefonica PDI Opt-in and profile module. Why? •  Users data, profile and permissions, was scattered across different storages 01 •  Gender •  Film and music preferences IPTV •  Permission to contact by SMS? •  Gender ADSL •  ADDRESS •  Music preferences Music tickets •  ADDRESS •  Permission to contact by SMS? Mobile
8. 8 Telefonica PDI Opt-in and profile module. Why? •  Provide a module to become master customer’s data storage 01 •  Gender •  Film and music preferences •  Permission to contact by SMS? •  ADDRESS IPTV ADSL Music tickets Mobile
9. 9 Telefonica PDI Opt-in and profile module. What? •  Features: §  Flexible profile definition, classified in services §  Profile sharing options between different services §  Real time REST API §  Supplementary offline batch interface §  Authentication & Authorization §  High availability §  Inexpensive solution & hardware 01
10. 10 Telefonica PDI Services and sharing profile.name profile.surname profile.age profile.gender profile.status ADSL music.preferred music.pop music.rock music.classic music.indie MUSIC Read Read R/W iptv.channel iptv.adult iptv.pack IPTV MOBILE 01
11. 11 Telefonica PDI Real time REST API [ { "update_source": "call_center", "service_id": ”music_tickets", "name": ”music.pop", "value": ”yes", "update_date": "2010-03-12T10:39:32Z" }, { "update_source": "call_center", "service_id": "ADSL", "name": "profile.address", "value": "13 Rue del Percebe, ático 2a", "update_date": "2010-03-12T10:39:32Z" }, { "update_source": "call_center", "service_id": "ADSL", "name": "contact.sms", "value": true, "update_date": "2010-03-12T10:39:32Z" } ] PS auth user which set that value Timestamp of actual value Service “owner” of the attributes 01
12. 12 Telefonica PDI Offline batch interface M|call_center|psoptin_call_center_20100801T..._20100830T....dat|2010-08-01T...|2010-08-30T... H|dashboard|service_user_id|1|update_date|update_source|profile.address|contact.sms … I|ADSL|1234|1|2010-08-29T12:45:23|call_center|13 rue del percebe, ático 2a | true ... D|ADSL|1235|1|2010-08-29T12:45:23|call_center|&|& ... I|ADSL|8910|1|2010-08-29T12:45:23|call_center| | false ... H|music_tickets|service_user_id|0|update_date|update_source | music.pop | music.rock ... I|ADSL|1234|0|2010-08-29T12:45:23|call_center| true | true ... I|ADSL|8910|0|2010-08-29T12:45:23|call_center| true | false ... F|4 PS auth user which set that value Timestamp of actual value Service “owner” of the attributes 01
13. Título del capítulo Máximo 3 líneas 02 The SQL solution
14. 14 Telefonica PDI Data model •  Services defined a set of attributes (their profile), with default value and data type •  Users were registered in services •  Users defined values for some of the services attributes •  Each attribute value had an update date to avoid overwriting newer changes through batch loads Services, users and their profile 02
15. 15 Telefonica PDI Data model •  Services could access attributes declared inside other services •  There were sharing rights for read or read and write •  The user had to be registered in both services Services profile sharing matrix 02
16. 16 Telefonica PDI Data model •  Everything that could be accessed in the PS was a resource •  Roles defined access rights (read or read and write) of resources •  Auth users had roles •  Roles could include other roles Authorization system 02
17. 17 Telefonica PDI Data model •  Multiple IDS: §  Users profile could be accessed with different equivalent IDs depending on the service §  Each user ID was defined by an ID type (phone number, email, portal ID, hash…) and the ID value Bonus features! 02
18. 18 Telefonica PDI High level logical architecture §  Everything running on Red Hat EL 5.4 64 bits 02
19. 19 Telefonica PDI High level logical architecture §  Everything running on Red Hat EL 5.4 64 bits 02
20. 20 Telefonica PDI Integration •  PS replaces all customers profile and permissions DBs •  All systems access this data through PS real time API •  In special cases, some PS-consumers could use the batch interface. •  The same way new services could be added quite easily Planned integration 02
21. 21 Telefonica PDI Integration •  PS replaces all customers profile and permissions DBs •  All systems access this data through PS real time API •  In special cases, some PS-consumers could use the batch interface. •  The same way new services could be added quite easily Planned integration 02
22. 22 Telefonica PDI Integration •  Budget restrictions: adapt all services to use the API was too expensive •  Keep independent systems DBs and synchronize PS through batch •  Use DBs built-in massive extraction feature to generate daily batch files •  However… in most cases those DBs were not able to generate Delta (only changes) extractions §  Provide full daily snapshots! Problems arise 02
23. 23 Telefonica PDI First version performance •  1.8M customers, 180 profile attributes, 6 services •  Sizes §  Tables + indexes size: 65Gb §  30% of the size were indexes •  Batch §  Full DWH customer’s profile import: > 24 hours §  Delta extractions: 4 - 6 hours §  Loads and extractions performance proportional to data size •  API: §  Response time with average traffic: 110ms 02 Ireland
24. Título del capítulo Máximo 3 líneas 03 The SQL solution Second version
25. 25 Telefonica PDI Second version •  New approach: batch processes access directly DB 03 High level logical architecture
26. 26 Telefonica PDI Second version •  Batch processes had to §  Validate  authen,ca,on  and  authoriza,on   §  Verify  user,  service  and  a6ribute  existence   §  Check  equivalent  IDs   §  Validate  sharing  matrix  rights   §  Validate  values  data  type   §  Check  the  update  date  of  the  exis,ng  values   03 Batch processes
27. 27 Telefonica PDI Second version 03 DB Batch processing Our DBAs
28. 28 Telefonica PDI Second version •  Preprocess incoming batch file in BE servers §  Validate format, services and attributes existence and values data types §  Generate intermediate file with structure like target DB table •  Load intermediate file (Oracle’s SQL*Loader) to a temporal table •  Switch DB to “deferred writing”, storing all incoming modifications •  Merge temporal table and final table, checking values update date •  Replace old users attributes values table with merge result •  Apply deferred writing operations 03 New DB-based batch loading process
29. 29 Telefonica PDI Second version •  Generate a temporal DB table with format similar to final batch file. Two loops over users attributes values table required: §  Select format of the table; number and order of columns / attributes §  Fill the new table •  Loop the whole temporal table for final formatting (empty fields…) •  From batch side loop across the whole table (SELECT * FROM …) •  Write each retrieved row as a line in the resulting file 03 New batch extraction process
30. 30 Telefonica PDI Second version performance •  Batch time window: 3:30 hours §  Full DWH load §  Two Delta loads §  Three Delta extractions •  API: §  Ireland requirement: < 500ms 03 Ireland performance requirements
31. 31 Telefonica PDI Second version performance •  1.8M customers, 180 profile attributes, 6 services •  Sizes §  Tables + indexes size: 65Gb §  30% of the size were indexes §  Temporal tables size increases almost exponentially: 15Gb and above §  Intermediate file size: from 700Mb to 7Gb •  Batch §  Full DWH customer’s profile import: 2:30 hours §  Delta extractions: 1:00 hour §  Loads performance worsened quickly (almost exp): 6:00 hours §  Extractions performance proportional to data size §  Concurrent batch processes may halt the DB •  API: §  Response time with average traffic: 80ms §  Response time while loading was unpredictable: >300ms 03 Ireland
32. Título del capítulo Máximo 3 líneas 04 The SQL solution Third version
33. 33 Telefonica PDI Third version 04 Speed up DB Batch processes Our DBAs (again)
34. 34 Telefonica PDI Third version •  Minor preprocessing of incoming batch file in BE servers §  Just validate format §  No intermediate file needed! •  Load validated file (Oracle’s SQL*Loader) to a temporal table •  Loop the temporal table merging the values into final table, checking values update date and data types §  Use several concurrent writing jobs •  Store results on real table, no need to replace! •  No “deferred writing”! 04 New (second) DB-based batch loading process
35. 35 Telefonica PDI Third version •  Optimized loops to generate temporal output table. §  Use several concurrent writing jobs §  We achieved a speed-up of between 1.5 and 2 •  Loop the whole temporal table for final formatting (empty fields…) •  Download and write lines directly inside Oracle’s sqlplus •  No SELECT * FROM … query from Batch side! 04 Enhancements to extraction process
36. 36 Telefonica PDI Our DBAs F**K YEAH Third version performance •  1.8M customers, 180 profile attributes, 6 services •  Sizes §  Tables + indexes size: 65Gb §  30% of the size were indexes §  Temporal tables: 15Gb •  Batch §  Full DWH customer’s profile import: 1:10 hours (vs. 2:30 hours) §  Three Delta extractions: 2:15 hours (vs. 3:00 hours) §  Loads and extractions performance proportional to data size §  Concurrent batch processes not so harmful •  API: §  Response time with average traffic: 110ms §  Response time while loading: 400ms 04 Ireland
37. 37 Telefonica PDI Our DBAs F**K YEAH Third version performance •  25M customers, 150 profile attributes, 15 services •  Sizes §  Tables + indexes size: 700Gb §  40% of the size were indexes •  Batch §  Two Delta imports: < 2:00 hours §  Two Delta extractions: < 2:00 hours §  Loads and extractions performance proportional to data size •  API: §  Response time with average traffic: 90ms 04 United Kingdom
38. 38 Telefonica PDI Our DBAs F**K YEAH Third version performance 04 Ireland 3rd version 2nd version DB size 65Gb + 15Gb (temp) 65Gb + > 15Gb Full DWH load 1:10 hours 2:30 hours Three Delta exports 2:15 hours 3:00 hours Batch stability Stable, linear Unstable, exponential API response time 110ms 110ms API while loading 400ms Unpredictable United Kingdom 3rd version DB size 700Gb Two Delta loads < 2:00 hours Three Delta exports < 2:00 hours API response time 90ms
39. 39 Telefonica PDI Third version performance •  20 database tables •  API: several queries with up to 35 joins and even some unions •  Authorization: 5 joins to validate auth users access •  Batch: §  Load: 1700 lines of PL/SQL §  Extraction: 1200 of PL/SQL 04 DB stats
40. 40 Telefonica PDI Mission completed? 04
41. 41 Telefonica PDI Third version performance • 20M customers, 200 profile attributes, 10 services • Mexico time window: 4:00 hours §  Full DWH load!! §  Additional Delta feeds loads §  At least two Delta extractions 04 Mexico Our DBAs
42. Título del capítulo Máximo 3 líneas 05 The NoSQL solution
43. 43 Telefonica PDI MongoDB Data Model Services and their profile + sharing matrix 05 { _id : 7, service_name : "root", id_type : 1, default_values: false, owned_attribs : [ { attrib_id : 70005, attrib_nane : “marketing.consent", attrib_data_type : 1, attrib_def_value : "no", attrib_status : 1 }, ... ], shared_attribs : [ {attrib_id : 20144, sharing_mode : 0}, ... ] } attrib_id = service_id * 10000 + num attribs + 1 attrib_id = service_id * 10000 + num attribs + 1
44. 44 Telefonica PDI MongoDB Data Model Users and their profile + multiple IDs 05 { _id : "011234" services_list : [ { service_id : 1, reg_date : {"$date" : 1318040693000} }, ... ], user_values : [ { attrib_id : 10140, attrib_value : "Open", update_date : {"$date" : 1317110161000} }, ... ] } Equivalent ID document: { _id : “05abcd" ue : "011234" } _id = “id type” + “user ID” attrib_id = service_id * 10000 + num attribs + 1 _id = “id type” + “user ID”
45. 45 Telefonica PDI MongoDB Data Model Authorization system 05 AUTH USERS COLLECTION: { _id: "admin" auth_pswd: ”XXX", auth_roles: ['PS_ADMIN_ROLE’, …], auth_uris: [ {uri_path: "/**", method: 'R'}, {uri_path: "/stats/**", method: 'RW'}, {uri_path: "/kpis/**", method: ’IMPORT'}, ... ] } RESOURCES COLLECTION: { _id: "admin.**", role_uri: "/**" } ROLES COLLECTION: { _id: 'PS_ADMIN_ROLE', roles_resources: [ { resource_id: "admin.**”, method: 'R' }, { resource_id: "stats.**”, method: 'IMPORT' }, ... ] } Replicate uris (from resources) and methods (from roles)
46. 46 Telefonica PDI MongoDB Data Model •  Only 5 collections •  API: typically 2 accesses (services and users collections) •  Authorization: access only 1 collection to grant access •  Batch: all processing done outside DB 05 DB stats
47. 47 Telefonica PDI MongoDB version §  Everything running on Red Hat EL 6.2 64 bits 05 High level logical architecture
48. 48 Telefonica PDI MongoDB version performance •  1.8M customers, 180 profile attributes, 6 services •  Sizes §  Collections + indexes size: 20Gb (vs. 65Gb) §  < 5% of the size are indexes (vs. 30%) •  Batch §  Full DWH customer’s profile import: 0:12 hours (vs. 1:10 hours) §  Three Delta extractions: 0:40 hours (vs. 2:15 hours) §  Loads and extractions performance proportional to data size §  Concurrent batch processes without performance affection •  API: §  Response time with average traffic: < 10ms (vs. 110ms) §  Response time while loading: the same §  High load (600 TPS) response time while loading: 300ms 05 Ireland (at PDI lab)
49. 49 Telefonica PDI MongoDB version performance •  25M customers, 150 profile attributes, 15 services •  Sizes §  Collections + indexes size: 210Gb (vs. 700Gb) §  < 5% of the size were indexes •  Batch §  Two Delta imports: < 0:40 hours (vs. 2:00 hours) §  Loads and extractions performance proportional to data size 05 United Kingdom (at PDI lab)
50. 50 Telefonica PDI MongoDB version performance •  20M customers, 200 profile attributes, 15 services •  Sizes §  Collections + indexes size: 320Gb §  Indexes size: 1.2Gb •  Batch §  Initial Full import (20M, 40 attributes): 2:00 hours §  Small Full import (20M, 6 attributes): 0:40 hours •  API: §  Response time with average traffic: < 10ms (vs. 90ms) §  Response time while loading: the same §  High load (500 TPS) response time while loading: 270ms 05 Mexico
51. 51 Telefonica PDI Our DBAs MongoDB version performance 04 Ireland NoSQL version SQL version DB size 20Gb 80Gb Full DWH load 0:12 hours 1:10 hours Three Delta exports 0:40 hours 2:15 hours API while loading < 10ms 400ms API 600TPS + loading 300ms Timeout / failure United Kingdom NoSQL version SQL version DB size 210Gb 700Gb Two Delta loads < 0:40hours < 2:00 hours Mexico NoSQL version DB size 320Gb Initial Full load (40 attr) 2:00 hours Daily Full load (6 attr) 0:40 hours API while loading < 10ms API 500TPS + loading 270ms
52. 52 Telefonica PDI Mission completed? 05
53. 53 Telefonica PDI The bad •  Batch load process was too fast §  To keep secondary nodes synched we needed oplog of 16 or 24Gb §  We had to disable journaling for the first migrations •  Labels of documents fields take up disc space §  We reduce them to just 2 chars (“attribute_id” -> “ai”) and saved 12% aprox. •  Respect the unwritten law of at least 70% of size in RAM •  Always one index, but take care with compound indexes, order matters §  You can save one index… or you can have problems §  Put most important key (never nullable) the first one •  DBAs whining and complaining about NoSQL §  “If we had enough RAM for all data, Oracle would outperform MongoDB” 05
54. 54 Telefonica PDI The ugly •  Imagine that once PS is running a user is updated adding lot of new values 05 USER 1 DOC USER 2 DOC … USER N-1 DOC USER N DOC USER 1 DOC WITH NEW VALUES X
55. 55 Telefonica PDI The ugly •  Despite MongoDB padding, sometimes resulting documents are reallocated 05 empty space USER 2 DOC … USER N-1 DOC USER N DOC USER 1 DOC WITH NEW VALUES
56. 56 Telefonica PDI The ugly •  What happens when all users are updated adding new data? 05 USER 1 DOC USER 2 DOC … USER N-1 DOC USER N DOC USER 1 DOC WITH NEW VALUES USER 2 DOC WITH NEW VALUES USER N-1 DOC WITH NEW VALUES USER N DOC WITH NEW VALUES
57. 57 Telefonica PDI The ugly •  Second migration after the PS is already running §  Full import adding 30 new attributes values: 10:00 hours §  Full import adding 150 new attributes values: 40:00 hours •  Increase considerably documents size (in our case adding lots of new values to the users) makes MongoDB rearrange the documents, performing around 5 times slower §  That’s a problem when you are updating more than 10k documents per second •  Solutions? §  Avoid this situation at all cost. Run away! §  Normalize users values; move to a new individual collection §  Prealloc the size with a faux field •  You would waste space! §  Load in new collection, merge and swap, like we did in Oracle. Just kidding… 05
58. Título del capítulo Máximo 3 líneas 06 A success story?
59. 59 Telefonica PDI Backend processes •  Lots of pipelines of independent backend processes §  Although they might access the same resources (for input or output) 06
60. 60 Telefonica PDI Backend processes •  Multiple machines with redundant processes 06
61. 61 Telefonica PDI Orchestration framework •  We implemented our distributed queues system; the Orchestration 06
62. 62 Telefonica PDI Orchestration framework •  Distributed queues system to manage resources / tasks (Backlog Items) §  Each Backlog Items had a type and the URI pointing to the resource •  Each backend process (Agent) consumed and generated certain types §  A Backlog Item could be assigned to several Agents §  Running Agents could block / prevent other Agents execution •  Distributed logic §  Each Agent woke up on a certain periodicity §  Each Agent was independent and agnostic of its pipeline §  Each Agent had the logic to check if it could run §  Each Agent had the logic the retrieve its own available Backlog Items •  High availability and fault tolerance §  Retries §  Heart beat to monitor all Agents status •  Synchronisation thanks to database transactions 06
63. 63 Telefonica PDI Orchestration DB schema 06
64. 64 Telefonica PDI Orchestration DB schema 06 TRANSACTION
65. 65 Telefonica PDI Solutions •  Denormalize and store Assignments inside Agents •  Two phase commits with an additional collection for “transactions” •  Split transactions in two steps; update Agent as running and then update its Assignments 06
66. 66 Telefonica PDI Solutions •  Denormalize and store Assignments inside Agents §  16Mb document size limit! §  Only denormalize active assignments à transaction needed to move them •  Two phase commits with an additional collection for “transactions” §  Create transaction as “pending” with ids of all involved documents §  Update Agent status (and set pending transaction id) §  Update or create each Assignment (and set pending transaction id) §  Update transaction as “comitted” §  Remove pending transaction from the Agent and each Assignment •  Two steps process: update Agent as running and then update its Assignments §  Lots of accesses too §  Recovery more complicated 06
67. 67 Telefonica PDI SQL Physical architecture 06
68. 68 Telefonica PDI SQL Physical architecture §  Read and write from both DB nodes 06
69. 69 Telefonica PDI MongoDB Physical architecture 06
70. 70 Telefonica PDI MongoDB Physical architecture §  Only write to primary node §  Non real time replication to secondary •  Or slow write operations à longer write locks in primary node 06
71. 71 Telefonica PDI Not a success story? •  No previous problems, no need to change •  Not a clear profit •  Too much possible pitfalls or disadvantages •  Too much effort required •  We left Orchestration in a relational database §  MySQL running in both BE servers with master – master replication 06
72. Título del capítulo Máximo 3 líneas 07 Título del capítulo Máximo 3 líneas Conclusions
73. 73 Telefonica PDI Conclusions & personal thoughts •  Awesome performance boost with MongoDB §  But not all use cases fit in a MongoDB / NoSQL solution! •  New technology, different limitations •  Fear of the unknown §  “Young” technology. Mature enough? •  Python + MongoDB + pymongo = fast development §  I mean, really fast •  MongoDB Monitoring Service (MMS) §  10gen people were very helpful •  Lots of new paradigms with the wide variety of NoSQL solutions 07
74. 74 Telefonica PDI http://www.tid.es/es/Carreras/Paginas/Opportunities.aspx http://www.tid.es/es/Carreras/Paginas/TIDCarrerasBecas.aspx
75. 75 Telefonica PDI Questions? 07
77. 77 Telefonica PDI SQL Physical architecture §  Scale horizontally adding more BE or DB servers or disks in the SAN §  Virtualized or physical servers depending on the deployment 0X
78. 78 Telefonica PDI MongoDB Physical architecture §  MongoDB arbiters running on BE servers §  Scale horizontally adding more BE servers or disks in the SAN §  Sharding may already be configured to scale adding more replica sets 0X