Changes between Version 1 and Version 2 of db/mtgs/mysql2014


Ignore:
Timestamp:
04/10/2014 01:31:00 PM (5 years ago)
Author:
danielw
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • db/mtgs/mysql2014

    v1 v2  
    5656 
    5757== 1.6 What Dropbox wants from mysqld == 
    58 -- better single-threaded performance 
    59 -- better replication 
    60 -- online schema changes 
    61 -- stored procedures 
    62 -- faster recovery 
    63 -- dta fragmentation 
     58- better single-threaded performance 
     59- better replication 
     60- online schema changes 
     61- stored procedures 
     62- faster recovery 
     63- dta fragmentation 
    6464 
    6565== 1.7 Dropbox future == 
    66 -- Shift whole backend to database as a service (put service layer 
    67 around entire db layer, not just parts) 
    68 -- Eliminate use of monolithic db 
    69 -- Enhance db ops 
    70 -- Introduce compression, improve performance 
     66- Shift whole backend to database as a service (put service layer around entire db layer, not just parts) 
     67- Eliminate use of monolithic db 
     68- Enhance db ops 
     69- Introduce compression, improve performance 
    7170 
    7271Believe "database as a service" common framework was a huge win. 
     
    8382 
    8483Numbers you should know: 
    85 Mem access: 0.1us 
    86 SSD read: 150us 
    87 Datacenter round-trip-time: 500us 
    88 Disk seek: 10ms 
    89 California-Europe RTT: 150ms 
    90 Blink of eye: 150us 
     84- Mem access: 0.1us 
     85- SSD read: 150us 
     86- Datacenter round-trip-time: 500us 
     87- Disk seek: 10ms 
     88- California-Europe RTT: 150ms 
     89- Blink of eye: 150us 
    9190 
    9291== 2.3 Goal of new client == 
     
    104103 
    105104== 2.5 Rough approach == 
    106 -- Extend libmysqlclient: non-blocking sockets 
    107 -- API indicates read/write 
    108 -- file descriptor (fd) exposed, so you can use it for select/poll/epoll 
    109 -- client lib internally uses state machines to keep track of what's 
    110 going on. 
    111 -- Same API for async and sync, to allow gradual migration. Some code 
    112 is more fragile and tricky to port to async. 
     105- Extend libmysqlclient: non-blocking sockets 
     106- API indicates read/write 
     107- file descriptor (fd) exposed, so you can use it for select/poll/epoll 
     108- client lib internally uses state machines to keep track of what's going on. 
     109- Same API for async and sync, to allow gradual migration. Some code is more fragile and tricky to port to async. 
    113110 
    114111== 2.6 Implementation notes == 
    115 -- mysqlclient changes are invasive 
    116 -- mysql test suite was useful for verifying correctness and catching 
    117 bugs. 
    118 -- partial deployment was very helpful 
    119 -- Can toggle async-sync on a per-call-site basis 
    120 -- tight monitoring during rollout, watch for errors after enabling 
    121 each feature. 
     112- mysqlclient changes are invasive 
     113- mysql test suite was useful for verifying correctness and catching bugs. 
     114- partial deployment was very helpful 
     115- Can toggle async-sync on a per-call-site basis 
     116- tight monitoring during rollout, watch for errors after enabling each feature. 
    122117 
    123118== 2.7 Final notes == 
    124119Try in webscalesql.org 
    125120python api: github/chipturner 
     121 
    126122FB's PHP is a little non-standard: using HHVM and Hack (an enhancement 
    127123of PHP that adds static types, generics, lambdas, etc.) 
     
    149145 
    150146== 3.3 What is the right way? == 
    151 NoSQL -- NoProblem: doc-level storage, no joining to retrieve whole 
    152 record 
    153 RDBMS -- tuples, relations make data dist more complicated 
     147- NoSQL -- NoProblem: doc-level storage, no joining to retrieve whole record 
     148- RDBMS -- tuples, relations make data dist more complicated 
    154149 
    155150Right way depends on data itself, user habits, application 
     
    196191 
    197192Example tools: 
    198 Percona TCP playback : replay TCP stream 
    199 pt-stalk : collects data upon trigger. "its god-like in its 
    200 capability" 
     193- Percona TCP playback : replay TCP stream 
     194- pt-stalk : collects data upon trigger. "its god-like in its capability" 
    201195 
    202196works on older, non-Percona mysqld as well. 
     
    236230== 5.4 Sharding == 
    237231Three questions to ask when deciding to shard: 
    238 1. How am I going to shard (hash, range, list)? 
    239 2. How do I add shards, remove shards, or split shards? 
    240 3. How do I move rows from one shard to another. 
     232- 1. How am I going to shard (hash, range, list)? 
     233- 2. How do I add shards, remove shards, or split shards? 
     234- 3. How do I move rows from one shard to another. 
    241235 
    242236== 5.5 Cloud/in-house == 
     
    254248 
    255249Everyone in session doing bare-metal mysqld 
    256 -- VM imposes too many context switches, also have to deal with 2 I/O 
    257 schedulers. 
    258 -- Use "noop" or "deadline" I/O scheduler. Don't use "cfq" ("it 
    259 sucks") 
    260 -- But recommend "MySQL Sandbox" for running multiple mysqld on single 
    261 node. Much easier than figuring out how to isolate them manually. 
     250- VM imposes too many context switches, also have to deal with 2 I/O schedulers. 
     251- Use "noop" or "deadline" I/O scheduler. Don't use "cfq" ("it sucks") 
     252- But recommend "MySQL Sandbox" for running multiple mysqld on single node. Much easier than figuring out how to isolate them manually. 
    262253 
    263254Rick James (yahoo guy): 20% overhead from using VM. If you want to 
     
    296287(Continuent) 
    297288== MySQL scale? == 
    298 R: Of course the relational model scales, contrast with 
    299 Stonebraker 
    300 N: Hardware has enabled scale 
    301 P: Misconception--search for silver bullet is misguided. Scale 
    302 always takes special care, planning, and effort, although the 
    303 hardware/storage tech makes small/medium scales more painless and fun. 
    304 P: used to be bottlenecked by storage I/O, but SSD is not so fast that 
    305 mysql can't keep up. So software work is needed to exploit faster 
    306 storage. More work is needed in both single query execution speed and 
    307 parallel execution. 
    308  
    309 R: Mysql is being used as always-on appliance/mainframe 
     289- R: Of course the relational model scales, contrast with Stonebraker 
     290- N: Hardware has enabled scale 
     291- P: Misconception--search for silver bullet is misguided. Scale always takes special care, planning, and effort, although the hardware/storage tech makes small/medium scales more painless and fun. 
     292- P: used to be bottlenecked by storage I/O, but SSD is not so fast that mysql can't keep up. So software work is needed to exploit faster storage. More work is needed in both single query execution speed and parallel execution. 
     293 
     294- R: Mysql is being used as always-on appliance/mainframe 
    310295 
    311296== Growth of mysql == 
    312 P: Mysql is unstoppable; but need more embracing of flash technology: 
    313 see lots of misconception. Need better networking, 1Gbps is not 
     297- P: Mysql is unstoppable; but need more embracing of flash technology: see lots of misconception. Need better networking, 1Gbps is not 
    314298enough. Also, should run latest software (mysqld, OS). 
    315 R: Want database automation in OpenStack like RDS on Amazon 
    316 N: System needs to be balanced. Also need predictability. Need 
    317 predictable impacs from failure--otherwise can't scale. 
     299- R: Want database automation in OpenStack like RDS on Amazon 
     300- N: System needs to be balanced. Also need predictability. Need predictable impacs from failure--otherwise can't scale. 
    318301 
    319302== How has 5.6 impacted? == 
    320 R: Panic attack because 5.6 changed binlog; customers wanted 
    321 performacne improvement from 5.6 from first day of availability. 
    322 N: Customers want to make sure that hardware works with 5.6. 
    323 P: It's taken some time to upgrade. 5.5 is working well for 
    324 people--MySQL is mature, so people are comfortable iwth it. 
     303- R: Panic attack because 5.6 changed binlog; customers wanted performance improvement from 5.6 from first day of availability. 
     304- N: Customers want to make sure that hardware works with 5.6. 
     305- P: It's taken some time to upgrade. 5.5 is working well for people--MySQL is mature, so people are comfortable iwth it. 
    325306 
    326307== 5.7? == 
    327 N: Anything improving concurrency is good for flash. 
    328 P: We see that Oracle has a closed dev process, but are pleasantly 
    329 surprised with new features; Oracle accepted suggestions from MySQL 
    330 Connect; Oracle takes community patches. Excited about GIS features, 
    331 pluggable parser. 
    332 R: We appreciate the HA features, schema changes, rolling upgrades, 
    333 improvements in replication; global tid. 
     308- N: Anything improving concurrency is good for flash. 
     309- P: We see that Oracle has a closed dev process, but are pleasantly surprised with new features; Oracle accepted suggestions from MySQL 
     310Connect; Oracle takes community patches. Excited about GIS features, pluggable parser. 
     311- R: We appreciate the HA features, schema changes, rolling upgrades, improvements in replication; global tid. 
    334312 
    335313== In 2-3 years, what changes would help the community? == 
    336 P: Have seen a great reduction in hostility in MySQL community. Really 
    337 need to see it continue to be more accepting and 
    338 welcoming. Competition has been great. 
    339 R: Not much change is needed: community is respectful and yet 
    340 competitive. But High Availability needs to be improved--amazed at how 
    341 much comes out-of-the-box in MongoDB. Switched to Apache licensing to 
    342 be more accessible. 
     314- P: Have seen a great reduction in hostility in MySQL community. Really need to see it continue to be more accepting and welcoming. Competition has been great. 
     315- R: Not much change is needed: community is respectful and yet competitive. But High Availability needs to be improved--amazed at how 
     316much comes out-of-the-box in MongoDB. Switched to Apache licensing to be more accessible. 
    343317 
    344318== Impact of !OpenStack on operating MySQL at scale? == 
    345 R: Although AWS is big in public cloud, still a lot of resistance, and 
    346 OpenStack is good there. OpenStack needs: Cinder block storage doesn't 
    347 have provisioned IOPS 
    348 N: Usability is still much needed for OpenStack 
    349 P: OpenStack still has a long way to go; community likes open-source 
    350 if it's good enough. Cinder block storage is not good enough, but can 
    351 use local high-performance storage. 
     319- R: Although AWS is big in public cloud, still a lot of resistance, and OpenStack is good there. OpenStack needs: Cinder block storage doesn't have provisioned IOPS 
     320- N: Usability is still much needed for OpenStack 
     321- P: OpenStack still has a long way to go; community likes open-source if it's good enough. Cinder block storage is not good enough, but can use local high-performance storage. 
    352322 
    353323== Competition among forks? == 
    354 P: Closer you are to customers, easier to understand problems and 
    355 implement solutions quickly. Lots of collaboration among engineers at 
    356 competitors. Webscalesql is vendor-neutral--this is exciting. 
     324- P: Closer you are to customers, easier to understand problems and implement solutions quickly. Lots of collaboration among engineers at competitors. Webscalesql is vendor-neutral--this is exciting. 
    357325 
    358326== Final thoughts on scale? == 
    359 N: Efficiency, predictability, manageability: Improvements in any of 
    360 these help scalability. 
    361 P: Hardware keeps getting better at the single node; software gets 
    362 better at scaling, and scaling people. Maturity--MySQL has been used 
     327- N: Efficiency, predictability, manageability: Improvements in any of these help scalability. 
     328- P: Hardware keeps getting better at the single node; software gets better at scaling, and scaling people. Maturity--MySQL has been used 
    363329at extreme scale for 5-10 years already. 
    364 R: Improvements in the last two MySQL releases have been amazing, so 
    365 don't be afraid to upgrade. Automation has gotten much better, very 
    366 important. 
     330- R: Improvements in the last two MySQL releases have been amazing, so don't be afraid to upgrade. Automation has gotten much better, very important. 
    367331 
    368332= 7 Building Data Warehouse with Hadoop and MySQL= 
     
    370334 
    371335Columnar file formats: 
    372 RCFILE 
    373 Parquet 
    374 ORC 
     336- RCFILE 
     337- Parquet 
     338- ORC 
    375339 
    376340Beats music streaming arch: 
     
    389353 
    390354== 8.1 Cassandra storage engine == 
    391 -- flexible schema, dynamic columns 
    392 -- several mariadb nodes --> cassandra ring 
    393 -- auto-replicated fault-tolerant tables 
     355- flexible schema, dynamic columns 
     356- several mariadb nodes --> cassandra ring 
     357- auto-replicated fault-tolerant tables 
    394358 
    395359== 8.2 Connect storage engine == 
    396 -- "CSV & federated" 
    397 -- CSV, BIN, FMT, INI 
    398 -- ODBC, MySQL 
    399 -- one row per key-valuye pair 
    400 -- can also append 
    401 -- remote mysql query results. 
    402 -- table transformations: XCOL, PIVOT 
     360- "CSV & federated" 
     361- CSV, BIN, FMT, INI 
     362- ODBC, MySQL 
     363- one row per key-valuye pair 
     364- can also append 
     365- remote mysql query results. 
     366- table transformations: XCOL, PIVOT 
    403367 
    404368== 8.3 OQGraph v3 == 
    405 create links table(edges) 
    406 create graph table 
    407 Now can find shortest path, all reachable nodes. 
     369- create links table(edges) 
     370- create graph table 
     371- Now can find shortest path, all reachable nodes. 
    408372 
    409373== 8.4 Sequence table engine == 
     
    412376== 8.5 Spider storage engine == 
    413377Federates multiple mysql nodes together. 
    414 Can push down parts of query to spider nodes, but not in the current 
    415 release. 
     378 
     379Can push down parts of query to spider nodes, but not in the current release. 
    416380 
    417381= 9 Lightnings =