
µ¼ÂÛ
UberµÄÔçÆÚ¼Ü¹¹ÓÉÒ»¸öµ¥Ìåºó¶ËÓ¦ÓóÌÐò¹¹³É£¬¸ÃÓ¦ÓÃÓÉPython±àд£¬PythonʹÓÃPostgresÒÔʵÏÖÊý¾Ý³Ö¾Ã»¯¡£×ÔÄÇʱÆð£¬Uber¼Ü¹¹ÒÑ·¢Éú¾Þ±ä£¬Öð²½×ª»¯ÎªÎ¢·þÎñģʽºÍеÄÊý¾Ýƽ̨¡£ÌرðÊÇÔÚ֮ǰһЩʹÓÃPostgresµÄ°¸ÀýÖУ¬ÏÖÔÚÔò¸ÄÓÃSchemaless£¨Ò»¸ö»ùÓÚMySQLµÄÈ«ÐÂÊý¾Ý¿â·ÖƬ£©¡£±¾ÎĽ«Ì½Ë÷PostgresµÄȱÏÝ£¬½âÊÍÇ¨ÒÆµ½MySQLµÄ»ù´¡ÉϹ¹½¨SchemalessºÍÆäËüºó¶Ë·þÎñµÄÔÒò¡£
PostgresµÄ¼Ü¹¹
PostgresÓкܶà¾ÖÏÞÐÔ£º
дÈë¼Ü¹¹µÍЧ
Êý¾Ý¸´ÖƵÍЧ
±íË𻵵ÄÎÊÌâ
Ôã¸âµÄ´Ó¿âMVCCÖ§³Ö
а汾¸üÐÂÄѶÈÉý¼¶
ÏÂÎĽ«·ÖÎöPostgresµÄ±í±íʾ·¨ºÍ´ÅÅÌÉϵÄË÷ÒýÊý¾Ý£¬Öصã¶Ô±ÈMySQLͨ¹ýÆäInnoDB´æ´¢ÒýÇæ³ÊÏÖÏàͬÊý¾ÝµÄ·½·¨£¬ÒÔ̽Ë÷ÉÏÊöȱÏÝ¡£×¢Ò⣺±¾ÎÄÉæ¼°µÄ·ÖÎöÖ÷Òª»ùÓھɰæPostgres
9.2ϵÁС£ ÖÚËùÖÜÖª£¬±¾ÎÄÂÛÊöµÄÄÚ²¿¼Ü¹¹ÔÚз¢²¼µÄPostgresÖÐûÓÐÌ«´ó±ä¸ü¡£ÊÂʵÉÏ£¬ÖÁÉÙ×ÔPostgres
8.3µÄ·¢²¼¿ªÊ¼£¨¾à½ñ½üÊ®Ä꣩£¬Postgres 9.2ÖдÅÅÌÉϱíʾ·¨µÄ»ù´¡Éè¼Æ¾ÍһֱûÓÐ×ö³öÏÔÖøµ÷Õû¡£
´ÅÅÌÉϵÄÊý¾Ý¸ñʽ
¹ØÏµÊý¾Ý¿â±ØÐëÖ´ÐÐÏÂÁйؼüÈÎÎñ£º
Ö§³Ö²åÈë/¸üÐÂ/ɾ³ý¹¦ÄÜ
Ö§³Öschema±ä¸ü¹¦ÄÜ
ʵÏÖÒ»¸ö¶à°æ±¾²¢·¢¿ØÖÆ£¨MVCC£©»úÖÆ£¬´Ùʹ²»Í¬µÄÁ¬½Ó¶ÔÆäËù´¦ÀíµÄÊý¾ÝÉú³ÉÒ»ÖÂÐÔµÄÊÂÎñÊÓͼ
˼¿¼ÆäËùÓÐÌØÐÔÈçºÎÐͬÔË×÷ÊÇÉè¼ÆÊý¾Ý¿âÔÚ´ÅÅÌÉϳÊÏÖÊý¾ÝµÄ»ù´¡¡£
PostgresµÄÒ»ÏîºËÐÄÉè¼ÆÊÇÐÐÊý¾Ý¹Ì¶¨¡£¸Ã¹Ì¶¨ÐÐÔÚPostgresÓÃÓïÖÐÓÖÃû¡°Ôª×飨tuple£©¡±¡£ÔÚPostgresÖУ¬Ôª×éÓÖͨ¹ýctid»ñµÃΨһ±êʶ¡£´Ó¸ÅÄîÉϽ²£¬ctid´ú±íÔª×éÔÚ´ÅÅÌÉϵÄλÖã¨ÀýÈçÎïÀí´ÅÅÌÆ«ÒÆ£©¡£¶à¸öctid¿ÉÄÜÄܹ»ÃèÊöÒ»¸öµ¥ÐУ¨ÀýÈç¶à¸öÐа汾ΪÁËMVCCµÄÄ¿µÄ¶ø´æÔÚʱ£¬»òÊǾɰ汾ÐÐδ¾autovacuum½ø³Ì»ØÊÕʱ£©¡£Ò»×éÔª×éµÄ×éÖ¯¼¯ºÏ¹¹³É±í£¬±í±¾Éí°üº¬Ë÷Òý£¬Ë÷Òý¾×éºÏ¹¹³ÉÊý¾Ý½á¹¹£¨Í¨³£ÊÇB-tree½á¹¹£©£¬´Ó¶ø½«Ë÷Òý×Ö¶ÎÓ³Éäµ½ctidµÄÓÐÐ§ÔØºÉ¡£
ͨ³£Çé¿öÏ£¬ÕâЩctidÊÇÃæÏòÓû§Í¸Ã÷µÄ£¬µ«Á˽âÆäÔËÐз½Ê½ÓÐÖúÓÚÀí½âPostgres±íÔÚ´ÅÅÌÉϵıí¼Ü¹¹¡£ÈôÒª²é¿´Ðеĵ±Ç°ctid£¬Ôò¿ÉÏòWHERE×Ó¾äÖеÄÀ¸Ä¿ÁбíÖÐÌí¼Ó¡°ctid¡±£º
uber@[local] uber=> SELECT ctid, * FROM my_table LIMIT 1;
-[ RECORD 1 ]--------+------------------------------
ctid | (0,1)
...other fields here... |
ΪÇó²¼¾Öϸ½Ú£¬ÏÈÒÔÒ»¸ö¼òµ¥µÄÓû§±íΪÀý¡£UberÕë¶Ôÿ¸öÓû§ÉèÖÃÁË×Ô¶¯µÝÔöµÄÓû§IDÖ÷¼ü¡¢Óû§ÐÕÃûºÍ³öÉúÄê·Ý¡£Í¬Ê±Uber»¹ÉèÖÃÁËÒ»¸ö»ùÓÚÓû§È«Ãû£¨°üÀ¨ÃûºÍÐÕ£©µÄ¸´ºÏ¶þ¼¶Ë÷Òý£¬ºÍÁíÒ»¸ö»ùÓÚÓû§³öÉúÄê·ÝµÄ¶þ¼¶Ë÷Òý¡£ÓÃÒÔ´´½¨¸Ã±íµÄDDLÈçÏ£º
CREATE TABLE users (
id SERIAL,
first TEXT,
last TEXT,
birth_year INTEGER,
PRIMARY KEY (id)
);
CREATE INDEX ix_users_first_last ON users (first,
last);
CREATE INDEX ix_users_birth_year ON users (birth_year); |
×¢Òâ¸Ã¶¨ÒåÖеÄÈý¸öË÷Òý£ºÖ÷¼üË÷ÒýºÍÁ½¸ö¶þ¼¶Ë÷Òý¡£
ΪÇóÀýÖ¤£¬½«ÒÔÏÂÃæµÄ±í¸ñÊý¾ÝÕ¹¿ªÂÛÊö£¬±íÖÐÊý¾Ý¾ùÓÉÀúÊ·ÉÏÆÄ¾ßÓ°ÏìÁ¦µÄÊýѧ¼Ò¹¹³É£º

ÈçǰËùÊö£¬±íÖÐÿһÐÐÒþº¬Ò»¸öΨһÇÒ²»¹«¿ªµÄctid¡£Òò´Ë£¬±íµÄÄÚ²¿±íʾÈçÏ£º

ÉèÖÃÖ÷¼üË÷Òý£¨Ó³ÉäIDµ½ctid£©£º

B-tree½á¹¹µÄÉèÖûùÓÚid×ֶΣ¬ÇÒÆäÿ¸ö½Úµã¶¼±£´æctidÖµ¡£ÔÚÕâ¸ö°¸ÀýÖÐÐèҪעÒâµÄÊÇ£¬ÓÉÓÚʹÓÃ×Ô¶¯µÝÔöid£¬B-treeÖеÄ×Ö¶Î˳ÐòÓÐʱ»áºÍ±íÖÐ˳ÐòÏàͬ£¬µ«ÊÇÒ²²»Ò»¶¨Èç´Ë¡£
¶þ¼¶Ë÷Òý±Ë´Ë¶¼ºÜÏàËÆ£»Ö÷Òª²îÒìÔÚÓÚ×ֶδ洢˳Ðò£¬¶ø×Ö¶ÎÔÚB-treeÖбØÐëÒÔ×Öµä˳ÐòÅŲ¼¡££¨first£¬last£©Ë÷Òý´ÓÃû¿ªÊ¼°´×Öĸ±í˳Ðò×ÔÉ϶øÏÂÅÅÁС£

ͬÑù£¬birth_year£¨³öÉúÄê·Ý£©¾Û¼¯Ë÷ÒýÒÔÉýÐòÅÅÁУº

×ÛÉÏËùÊö£¬²»Í¬ÓÚ×Ô¶¯µÝÔöÖ÷¼üµÄ°¸Àý£¬ÔÚÉÏÃæµÄÇ龳ϣ¬¸÷¸ö¶þ¼¶Ë÷ÒýÖеÄctid×ֶζ¼²»Êǰ´×Öĸ±í˳ÐòÉýÐòÅŲ¼µÄ¡£
¼ÙÉèÐèÒª¸üÐÂÒ»Ìõ±í¼Ç¼£¬±ÈÈ罫al-Khw¨¡rizm¨©µÄ³öÉúÄê·Ý×ֶθüÐÂΪÁíÒ»¸öÔ¤¹ÀÖµ770CE¡£ÈçǰËùÊö£¬ÐÐÔª×éÊǹ̶¨µÄ£¬Òò´Ë£¬ÎªÁ˸üмǼ£¬ÐèÒªÏò±íÖÐÌí¼ÓÒ»¸öÐÂÔª×é¡£¸ÃÐÂÔª×éÓÐÒ»¸öеķǹ«¿ªctid£¬³ÆÖ®ÎªI¡£PostgresÐèÒªÄܹ»Çø·ÖIÉϵÄÐÂÔª×éºÍDÉϵľÉÔª×é¡£ÔÚÄÚ²¿£¬Postgres½«Ò»¸ö°æ±¾×ֶκÍÖ¸Ïòǰһ¸öÔª×飨Èç¹ûÓеϰ£©µÄÖ¸Õë´æÓÚ¸÷¸öÔª×é¡£¾Ý´Ë£¬±íµÄнṹÈçÏ£º

Ö»Òªal-Khw¨¡rizm¨©´æÔÚÁ½¸öÐа汾£¬ÔòË÷Òý±ØÐë°üº¬ÕâÁ½¸öÐеÄÌõÄ¿¡£ÎªÇó¼ò½à£¬Uber´Ë´¦É¾³ýÁËÖ÷¼üË÷Òý£¬Ö»ÏÔʾ¶þ¼¶Ë÷Òý£º


´Ë´¦½«¾É°æÏÔʾΪºìÉ«£¬Ð°æÎªÂÌÉ«¡£ÔÚÄÚ²¿£¬Postgreͨ¹ýÁíÒ»¸ö×ֶα£´æÐа汾£¬ÒÔÅж¨ÄÄÒ»¸öÊÇ×îÐÂÔª×é¡£¸ÃÐÂÔö×ֶΰïÖúÊý¾Ý¿â¾ö¶¨ÈÃÄÄÒ»¸öÐÐÔª×é·þÎñÓÚÒ»¸öÊÂÎñ£¬¸ÃÊÂÎñ¿ÉÄܲ»±»ÔÊÐí²é¿´×îÐÂÐа汾¡£

PostgresÏ£¬Ö÷Ë÷ÒýºÍ¶þ¼¶Ë÷Òý¶¼Ö±Ö¸´ÅÅÌÉϵÄÔª×éÆ«ÒÆ¡£ÈôÒ»¸öÔª×éµÄλÖ÷¢Éú¸Ä±ä£¬Ôò±ØÐë¸üÐÂÈ«²¿Ë÷Òý¡£
¸´ÖÆ
ÔÚ±íÖвåÈëÐÂÐÐʱ£¬ÈôÖ§³ÖÁ÷¸´ÖÆ£¬PostgresÔòÐè¶ÔÆä½øÐи´ÖÆ¡£ÎªÇó¹ÊÕÏÐÞ¸´£¬Êý¾Ý¿âÒÑÆôÓÃÒ»¸öԤдʽÈÕÖ¾£¨WAL£©£¬ÒÔʵÏÖÁ½½×¶ÎÌá½»¡£¼´Ê¹²»Ö§³ÖÁ÷¸´ÖÆ£¬Êý¾Ý¿âÒ²±ØÐëÆôÓÃWAL£¬ÒòΪWALLÖ§³ÖACIDÖеÄÔ×ÓÐԺͳ־ÃÐÔÁ½·½Ãæ¡£
ÈôÊý¾Ý¿â·¢ÉúÍ»·¢Òì³££¨±ÈÈçͻȻ¶Ïµç£©£¬¿Éͨ¹ý¿¼ÂÇ·¢ÉúµÄÊÂÇéÀ´Àí½âWAL¡£Êý¾Ý¿â¼Æ»®¶Ô±íºÍË÷ÒýÔÚ´ÅÅÌÉϵÄÄÚÈÝ×ö³ö¸ü¸Ä£¬¶øWAL±íʾÕâЩ¸ü¸ÄµÄ·ÖÀà»ã×Ü¡£PostgresµÄºǫ́³ÌÐò³õ´ÎÆô¶¯Ê±£¬½ø³Ì½«¸Ã·ÖÀà»ã×ÜÄÚµÄÊý¾ÝºÍ´ÅÅÌÉϵÄʵ¼ÊÊý¾Ý½øÐжԱȡ£Èô»ã×ÜÖаüº¬´ÅÅÌÉÏδ·´Ó¦µÄÊý¾Ý£¬ÔòÊý¾Ý¿âÐÞÕýÔª×é»òË÷ÒýÊý¾Ý£¬ÒÔ·´Ó¦WALԤдµÄÊý¾Ý¡£Ëæºó¹ö»Ø²¿·ÖÊý¾Ý£¬ÕâЩÊý¾Ý³öÏÖÓÚWAL£¬µ«À´×Ô²¿·ÖÓ¦ÓÃÊÂÎñ£¨Ö¸Î´Ìá½»ÊÂÎñ£©¡£
Postgres½«Ö÷Êý¾Ý¿âÉϵÄWAL·¢ËÍÖÁ´Ó¿â£¬ÒÔʵÏÖÁ÷¸´ÖÆ¡£Ã¿Ò»¸ö´Ó¿â¶¼ÔÚÒÔ¹ÊÕÏÐÞ¸´µÄ·½Ê½¸ßЧÔËÐУ¬ÒÔÓ¦¶Ô¹ÊÕϺóÆô¶¯µÄ·½Ê½£¬²»¶ÏʵʩWAL¸üС£Á÷¸´ÖƺÍʵ¼Ê¹ÊÕÏÐÞ¸´Ö®¼äµÄΨһ²»Í¬ÔÚÓÚ£ºÖ´ÐÐÁ÷WALʱ£¬´¦ÓÚ¡°Èȱ¸·Ý¡±Ä£Ê½µÄ´Ó¿âÖ§³Ö¶ÁÈ¡²éѯ£»¶øÊµ¼Ê´¦ÓÚ¹ÊÕÏÐÞ¸´Ä£Ê½µÄPostgresÊý¾Ý¿âÍùÍù²»Ö§³Ö²éѯ£¬Ö±µ½Êý¾Ý¿âʵÀýÍê³É±ÀÀ£»Ö¸´½ø³Ì¡£
ÓÉÓÚWALÊÇרΪ±ÀÀ£»Ö¸´¶øÉè¼ÆµÄ£¬¹Ê°üº¬´ÅÅÌÉϸüеĵײãÐÅÏ¢¡£¶øWALµÄÄÚÈÝλÓÚÐÐÔª×éºÍÆä´ÅÅÌÆ«ÒÆ£¨ÀýÈçÐÐctid£©ÔÚ´ÅÅÌÉϵÄʵ¼Ê±íʾ²ã¡£ÈôÔÚ´Ó¿âÍêȫͬ²½Ê±ÔÝÍ£PostgresµÄÖ÷Êý¾Ý¿âºÍ´Ó¿â£¬Ôò´Ó¿â´ÅÅÌÉϵÄʵ¼ÊÄÚÈÝÇ¡ºÃºÍÖ÷Êý¾Ý¿âÉϵÄÄÚÈÝÍêȫƥÅä¡£Òò´Ë£¬ÈôÓëÖ÷Êý¾Ý¿âÁ¬½Ó³¬Ê±£¬rsyncÕâÀ๤¾ßÄܹ»ÐÞ¸´Ë𻵸±±¾¡£
PostgresÉè¼ÆµÄ¿Ó
ÔÚUberµÄÓ¦Óùý³ÌÖУ¬PostgresµÄÉè¼Æµ¼ÖµÍЧÎÊÌâµÄͬʱ£¬»¹¸øUberÊý¾ÝÔì³ÉÕϰ¡£
дÈë·Å´ó
ÔÚÆäËüÇé¿öÏÂPostgresÉè¼ÆµÄÊ×ÒªÎÊÌâÊÇдÈë·Å´ó¡£Ð´Èë·Å´óͨ³£´úÖ¸Êý¾ÝдÈëSSD´ÅÅ̵ÄÎÊÌ⣺һ¸öСµÄÂß¼¸üУ¨Ð´È뼸¸ö×Ö½Ú£¨byte£©£©±»·Å´óÁË£¬´Ó¶øÔö¼ÓÁ˸üÐÂתÈëÎïÀí²ãµÄ´ú¼Û¡£PostgresÒ²³öÏÖÁËÏàͬµÄÎÊÌâ¡£ÔÚÇ°ÃæµÄÀý×ÓÖУ¬Èô¶Ôal-Khw¨¡rizm¨©µÄ³öÉúÄê·Ý½øÐÐСµÄÂß¼¸üУ¬ÔòÖÁÉÙÐèÒªÕ¹¿ªËÄÏîÎïÀí¸üУº
Ïò±í¿Õ¼äдÈëеÄÐÐÔª×飻
¸üÐÂÖ÷¼üË÷ÒýÒÔÏòÐÂÔª×éÖмÓÈëÒ»Ìõ¼Ç¼£»
¸üУ¨first£¬last£©Ë÷ÒýÒÔÏòÐÂÔª×éÖмÓÈëÒ»Ìõ¼Ç¼£»
¸üÐÂbirth_yearË÷ÒýÒÔÏòÐÂÔª×éÖмÓÈëÒ»Ìõ¼Ç¼¡£
ʵ¼ÊÉÏ£¬ÕâËÄÏî¸üнö·´Ó³ÁËÖ÷Òª±í¿Õ¼äµÄдÈ룻ÆäÖÐÿһÏîдÈë¶¼ÐèÒª·´Ó³ÓÚWAL£¬Òò´Ë´ÅÅÌÉϵÄ×ÜдÈëÁ¿¸ü´óÁË¡£
´Ë´¦ÖµµÃ×¢ÒâµÄÊǵڶþºÍµÚÈýÏî¸üС£¸üÐÂal-Khw¨¡rizm¨©µÄ³öÉúÄê·Ýʱ£¬Êµ¼ÊÉϲ¢·Ç¸ü¸ÄÖ÷¼ü£¬Ò²²»ÊǸıäÆäÃûºÍÐÕ¡£È»¶ø£¬ÕâЩË÷ÒýÈÔÒªÒÀ¾ÝÊý¾Ý¿âÖÐÐмǼд´½¨µÄÐÐÔª×é½øÐиüС£Õë¶ÔÓµÓдóÁ¿¶þ¼¶Ë÷ÒýµÄ±í£¬ÕâЩ¶àÓàµÄ²½Ö轫µ¼ÖµÍЧ¡£ÀýÈ磬ÏÖÓÐÒ»±í£¬ÆäÉ϶¨ÒåÁËÊ®¶þÌõË÷Òý£¬ÔòÆä¸üÐÂÖÁÒ»¸ö½öº¸ÇÒ»ÌõË÷ÒýµÄ×Ö¶ÎÒ²±ØÐëҪתÖÁÈ«²¿Ê®¶þÌõË÷ÒýÒÔ·´Ó¦ÐÂÐеÄctid¡£
¸´ÖÆ
´Ë´¦µÄдÈë·Å´óÎÊÌâ×ÔȻתÖÁ¸´ÖƲ㣬ÒòΪ¸´ÖÆ·¢ÉúÔÚ´ÅÅÌÉϵĸü¸Ä²ã¼¶¡£²»¸´ÖÆÐ¡µÄÂß¼¼Ç¼£¨Èç¡°½«ctid
D¾à½ñµÄ³öÉúÄê·Ý¸ü¸ÄΪ770¡±£©£¬Êý¾Ý¿âת¶øÐ´³öÉÏÊöËÄÏî²Ù×÷µÄWAL¼Ç¼£¬¸Ã¼Ç¼ͨ¹ýÍøÂçͬ²½¡£Òò´Ë£¬Ð´ÈëÀ©´óÎÊÌâҲת»¯Îª¸´ÖÆÀ©´óÎÊÌ⣬ÇÒPostgres¸´ÖÆÊý¾ÝÁ÷ѸËÙ±äµÃ¼«Îª·±Ëö£¬´Ó¶ø¿ÉÄÜ»á´óÁ¿Õ¼ÓÃÆµ¿í¡£
ÈôPostgres¸´ÖƽöÔÚÒ»¸öÊý¾ÝÖÐÐÄÄÚ·¢Éú£¬Ôò¸´ÖÆÆµ¿í¿ÉÄܲ»»á¹¹³ÉÎÊÌâ¡£ÏÖ´úÍøÂçÉ豸ºÍ½»»»Æ÷¿ÉÖ§³Ö´óÁ¿Æµ¿í£¬ÇҺܶàÖ÷»úÌṩÉÌÌṩÃâ·Ñ»òÁ®¼ÛµÄÄÚ²¿Êý¾ÝÖÐÐÄÆµ¿í¡£È»¶ø£¬Èô¸´ÖƱØÐë·¢ÉúÓÚÁ½¸öÊý¾ÝÖÐÐÄÖ®¼ä£¬ÎÊÌâÔò»áѸËÙÀ©´ó¡£ÀýÈ磬Uber×î³õʹÓÃÎ÷º£°¶Ö÷»úÍйܿռäµÄÎïÀí·þÎñÆ÷¡£³öÓÚ±ÀÀ£»Ö¸´µÄÄ¿µÄ£¬Ïò¶þ¼¶¶«º£°¶Ö÷»úÍйܿռäÌí¼Ó·þÎñÆ÷¡£ÔÚ¸ÃÉè¼ÆÖУ¬Î÷²¿Êý¾ÝÖÐÐÄÓÐÒ»¸öÖ÷Êý¾Ý¿âPostgresʵÀý£¨ºÍ´ÓÊý¾Ý¿â£©£¬¶«²¿Êý¾ÝÖÐÐÄÓÐÒ»¸ö¸±±¾¼¯¡£
¼¶Áª¸´Öƽ«ÄÚ²¿Êý¾ÝÖÐÐÄÆµ¿íÐèÇóÏÞÖÆÔÚÖ÷Êý¾Ý¿âºÍµ¥Ò»¸±±¾¼äµÄËùÐèµÄÁ¿£¬¼´Ê¹¶þ¼¶Êý¾ÝÖÐÐÄÓкܶั±¾¡£È»¶ø£¬Postgres¸±±¾ÐÒéÈßÓàÈÔÈ»»áµ¼ÖÂʹÓôóÁ¿Ë÷ÒýµÄÊý¾Ý¿â²úÉú¾ÞÁ¿Êý¾Ý¡£¹ºÂò¸ßƵ¿í¿ç¹úÁ¬½Ó»°·ÑºÜ¸ß£¬¶øÇÒ¼´Ê¹×ʽð²»ÊÇÎÊÌ⣬ҲºÜÄÑ»ñµÃÒ»¸öºÍ±¾µØÄÚ²¿Á¬½ÓÒ»ÑùµÄ¿ç¹úÍøÂçÁ¬½Ó¡£Í¬Ê±Æµ¿íÎÊÌâÒ²´øÀ´ÁËWALµµ°¸ÎÊÌâ¡£³ýÁËÒª½«ËùÓÐWAL¸üдÓÎ÷º£°¶·¢Ë͵½¶«º£°¶£¬»¹Òª½«ËùÓÐWAL´æÈëÒ»¸öÎļþ´æ´¢Æ÷web·þÎñ£¬Õâ²»½öÊÇΪ¹ÊÕÏ·¢ÉúºóµÄÊý¾Ý¿â»Ö¸´Ìṩ¶îÍâ±£ÕÏ£¬Í¬Ê±´æ´¢WALÒ²´ÓÊý¾Ý¿â¿ìÕÕÖÐÌáȡи±±¾¡£ÔÚÔçÆÚͨП߷å½×¶Î£¬´æ´¢web·þÎñµÄƵ¿íËٶȲ»¹»¿ì£¬ÒÔÖÁÓÚ²»×ãÒÔ¸ÏÉÏWAL±»Ð´ÈëµÄËÙ¶È¡£
Êý¾ÝËð»µ
ÔÚÒ»´Î³£¹æÖ÷Êý¾Ý¿âÀ©Èݹý³ÌÖУ¬UberÔâÓöÁËPostgres 9.2µÄbug¡£´Ó¿â°´Ê±¼ä˳ÐòÇл»³ö´í£¬µ¼Ö²¿·ÖWAL¼Ç¼ÎóÓá£ÓÉÓÚÕâ¸öbug£¬²¿·ÖÓ¦±»°æ±¾¿ØÖÆ»úÖÆ±ê¼ÇΪ·Ç»îÔ¾µÄ¼Ç¼£¬Êµ¼ÊÉÏδ±»±ê¼ÇΪ·Ç»îÔ¾¡£
ÏÂÃæµÄ²éѯ²ûÊÍÁ˸ÃbugÊÇÈçºÎÓ°ÏìÓû§±íµÄ£º
SELECT * FROM users WHERE id = 4;
¸Ã²éѯ·µ»ØÁ½Ìõ¼Ç¼£º³õʼal-Khw¨¡rizm¨©ÐеÄ780 CE³öÉúÄê·Ý£¬¼ÓÉÏÐÂal-Khw¨¡rizm¨©ÐеÄ770
CE³öÉúÄê·Ý¡£ÈôÏëÏòWHEREÁбíÖÐÌí¼Óctid£¬ÔòÁ½¸ö·µ»Ø¼Ç¼½«¾ßÓв»Í¬µÄctidÖµ£¬»á²úÉúÁ½¸ö½ØÈ»²»Í¬µÄÐÐÔª×é¡£
ÊÜһЩÔÒòÓ°Ï죬Õâ¸öÎÊÌ⼫Ϊ¼¬ÊÖ¡£Ê×ÏÈ£¬ºÜÄÑÈ·ÈÏÓжàÉÙÐлáÊܵ½Ó°Ï죻Êý¾Ý¿â·µ»ØµÄÖØ¸´½á¹ûµ¼Ö´óÁ¿Ó¦ÓÃÂß¼³ö´í¡£Uber×îÖÕÌí¼Ó·ÀÓùÐÔ±à³ÌÓï¾äÒÔ¼ì²âÓÐÕâÀàÎÊÌâµÄ±íµÄÇé¾³¡£ÒòΪ¸ÃbugÓ°ÏìËùÓзþÎñÆ÷£¬Òò´Ë´Ó¿âʵÀý²»Í¬Ôò±ÀÀ£Ðв»Í¬£¬Ò²¾ÍÊÇ˵ÔÚͬһ¸ö¸±±¾ÉÏXÐпÉÄÜÊÇ»µµÄ£¬¶øYÐÐÓÖÊǺõ쬵«ÁíÒ»¸ö¸±±¾µÄXÐпÉÄÜÊǺõ쬶øYÐÐÈ´ÊÇ»µµÄ¡£Êµ¼ÊÉÏ£¬²¢²»ÄÜÈ·¶¨°üº¬±ÀÀ£Êý¾ÝµÄ¸±±¾Êý£¬ÒÔ¼°¸ÃÎÊÌâÊÇ·ñÓ°Ïìµ½ÁËÖ÷Êý¾Ý¿â¡£
×ÛÉÏËùÊö£¬¸ÃÎÊÌâ½öÌåÏÖÔÚÿ¸öÊý¾Ý¿âÖеöÐÐÉÏ£¬µ«ÊÇÈ´ÆÄÁîÈËÓÇÂÇ£¬ÒòΪ¸´ÖÆ·¢ÉúÔÚÎïÀí²ã£¬ËùÒÔ×îÖÕ¿ÉÄܵ¼ÖÂÊý¾Ý¿âË÷ÒýÍêÈ«±ÀÀ£¡£B-treeµÄÒ»¸öÖØÒª·½Ãæ¾ÍÊDZØÐ붨ÆÚ¶ÔÆä½øÐÐÖØÐÂÆ½ºâ£¬ÇҸòÙ×÷»á³¹µ×¸Ä±ätreeµÄ½á¹¹£¬sub-trees±»ÒÆÍùеĴÅÅÌλÖá£Èô´íÎóÊý¾Ý±»×ªÒÆ£¬¿ÉÄܻᵼÖÂtreeµÄ´ó²¿·ÖÍêȫʧЧ¡£
×îÖÕ£¬UberÄܹ»×·²¶Êµ¼Êbug£¬²¢ÓÃËüÀ´È·¶¨ÐÂÉý¼¶µÄÖ÷Êý¾Ý¿â²»°üº¬ÈκÎÊÜËðÐС£Í¨¹ýÖØÐÂͬ²½È«²¿Ö÷Êý¾Ý¿âµÄпìÕÕ£¬ÒÔ½â¾ö¸±±¾±ÀÀ£ÎÊÌ⣬Õâ¸ö½ø³ÌºÜÀ§ÄÑ£»UberÒ»´ÎÖ»ÄÜ´Ó¸ºÔؾùºâ³ØÖÐÌáÈ¡ÓÐÏÞÁ¿µÄ¸±±¾¡£
ÔâÓöµÄÎÊÌâ½öÓ°ÏìÁËPostgres 9.2µÄÌØ¶¨·¢²¼£¬²¢ÇÒÒѾ±»½â¾öºÜ³¤Ê±¼äÁË¡£µ«ÈÔÈ»»áµ£ÐÄÕâÀàbug»áÔٴη¢Éú¡£Ò»µ©·¢ÏÖÕâÖÖÐÔÖʵÄbug£¬Ò»¸öаæµÄPostgres¿ÉÒÔËæÊ±·¢²¼£¬¶øÇÒÓÉÓÚ¸´ÖƵŤ×÷·½Ê½£¬¸ÃÎÊÌâ¿ÉÄÜ»áͬ²½ÖÁ¸´ÖƲãµÄËùÓÐÊý¾Ý¿â¡£
´Ó¿âMVCC
PostgresûÓÐÕæÕýµÄ¸´ÖÆMVCCÖ§³Ö¡£´Ó¿âÔËÓÃWAL¸üÐÂÕâÒ»µãµ¼ÖÂÆä¾ßÓÐÒ»¸ö´ÅÅÌÉÏÊý¾ÝµÄ¸±±¾£¬ÔÚÈκθø¶¨µÄʱ¼äµã¶¼ºÍÖ÷Êý¾Ý¿âÏàͬ¡£
PostgresÐèÒª±£³ÖMVCC¾ÉÐа汾µÄ¸±±¾¡£ÈôÁ÷¸´Öưüº¬Ò»¸ö¿ª·ÅÊÂÎñ£¬ÔòÔÚÓ°Ï쵽ͨ¹ý¸ÃÊÂÎñ±£³Ö¿ª·ÅµÄÐÐʱ£¬Êý¾Ý¿â¸üÐÂ»á±»Ëø¶¨¡£ÔÚÕâÖÖÇ龳ϣ¬PostgresÖÐÖ¹WALÓ¦ÓÃỊ̈߳¬Ö±µ½ÊÂÎñÖÕÖ¹¡£ÓÉÓÚ´Ó¿â½ÏÖ®Ö÷Êý¾Ý¿â¿ÉÄÜ»áÑÏÖØÖͺó£¬ËùÒÔÈôÊÂÎñºÄʱ¹ý³¤Ôò»á²úÉúÎÊÌâ¡£Òò´Ë£¬PostgresÔÚÕâÀàÇé¾°ÏÂÉèÖó¬Ê±£ºÈôÒ»ÏîÊÂÎñËø¶¨WALÓ¦ÓÃÒ»¶Îʱ¼ä£¬ÔòPostgres½«kill¸ÃÊÂÎñ¡£
Õâ¸öÉè¼ÆÒâζ×Å´Ó¿âÕý³£»á±ÈÖ÷Êý¾Ý¿âÑÓ³Ù¼¸Ã룬Òò´ËºÜÈÝÒ×±àд´úÂ룬´Ó¶øµ¼ÖÂɱËÀÊÂÎñ¡£Ò»Ð©Ó¦Óÿª·¢Õß±àдµÄ´úÂëÖУ¬ÊÂÎñµÄÆðʼµã½ÏΪģºý£¬¶ÔÓÚËûÃǶøÑÔ£¬Õâ¸öÎÊÌâµÄ¿ÉÄܲ»ÄÇôÃ÷ÏÔ¡£ÀýÈ磬¼ÙÉèÒ»¸ö¿ª·¢ÕßÓÐһЩ´úÂ룬¶øÕâЩ´úÂë±ØÐëÏòÓû§Óʼþ·¢ËÍÊվݡ£»ùÓÚÆä±àд·½Ê½£¬¸Ã´úÂë°µº¬Ò»¸öÊý¾Ý¿âÊÂÎñ£¬ÕâÒ»ÊÂÎñ±£³Ö¿ª·Å״̬£¬Ö±ÖÁÓʼþ·¢ËÍÍê³É¡£ËäȻִÐÐÎÞ¹Ø×èÈûI/Oʱ£¬´úÂë°üº¬¿ª·ÅʽÊý¾Ý¿âÊÂÎñ²¢²»Í×µ±£¬µ«ÊÂʵÊÇ´ó¶àÊý¹¤³Ìʦ¶¼²»ÊÇÊý¾Ý¿âר¼Ò£¬ËùÒÔÍùÍù¿ÉÄܲ»ÄÜÀí½âÕâ¸öÎÊÌâ£¬ÌØ±ðÊÇÔÚʹÓÃORMµÄʱºò£¬ÀàËÆÓÚ¿ª·ÅÊÂÎñ£¬Ëü»áÄ£ºýµ×²ãϸ½Ú¡£
Postgres¸üÐÂ
ÒòΪ¸´ÖƼǼÔÚÎïÀí²ãÖ´ÐУ¬ËùÒÔ²»¿ÉÄܸ´ÖÆPostgres²»Í¬Í¨Óð汾¼äµÄÊý¾Ý¡£Ò»¸öÔËÐÐPostgres
9.3µÄÖ÷Êý¾Ý¿â£¬ÎÞ·¨¸´ÖÆÒ»¸öÔËÐÐPostgres 9.2µÄ´Ó¿â£»£¨·´Ö®ÒàÈ»£©Ò»¸öÔËÐÐPostgres
9.2µÄÖ÷Êý¾Ý¿âÒ²ÎÞ·¨¸´ÖÆÒ»¸öÔËÐÐPostgres 9.3µÄ´Ó¿â¡£
ÔÚ´ÓÒ»¸öPostgres GA¸üе½ÁíÒ»¸öµÄ¹ý³ÌÖУ¬×ñÑÒÔϲ½Ö裺
¹Ø±ÕÖ÷Êý¾Ý¿â£»
ÔÚÖ÷Êý¾Ý¿âÉÏÔËÐС¯pg_upgrade¡¯Ö¸ÁËü½«ÔÚÊʵ±Î»ÖøüÐÂÖ÷Êý¾Ý¿âÊý¾Ý¡£Õâ¶ÔÓÚÒ»¸ö´óÐÍÊý¾Ý¿â¶øÑÔ£¬ÍùÍùÒª»¨ºÃ¶à¸öСʱ£¬ÇÒÔڸýø³Ì½øÐеĹý³ÌÖУ¬²»Ö§³ÖÈκÎͨÐÅ£»
ÔÙ´ÎÆô¶¯Ö÷Êý¾Ý¿â£»
´´½¨Ö÷Êý¾Ý¿âпìÕÕ¡£¸Ã²½ÖèÍêÈ«¸´ÖÆÖ÷Êý¾Ý¿âÄÚµÄËùÓÐÊý¾Ý£¬Òò´ËÒ»¸ö´óÐÍÊý¾Ý¿âÔÚ´ËÒ²Òª»¨·ÑÊý¸öСʱ£»
ÇåÀíÿ¸ö´Ó¿â£¬ÐÞ¸´Ö÷Êý¾Ý¿âµ½´Ó¿âµÄпìÕÕ£»
»Ö¸´Ã¿¸ö´Ó¿âÖÁ¸´ÖƲ㣬Óë´Ëͬʱ£¬µÈ´ý´Ó¿âÍêÈ«»ñÈ¡Ö÷Êý¾Ý¿âµÄÈ«²¿¸üС£
UberÍŶӴÓPostgres 9.1¿ªÊ¼£¬³É¹¦ÊµÏÖÁ˸üÐÂÖÁPostgres 9.2µÄ½ø³Ì¡£È»¶ø£¬¸Ã½ø³ÌºÄʱ¹ý³¤£¬Òò¶øÎÞ·¨Ö§³ÖÔÙ´ÎÖ´ÐС£Postgres
9.3ÍÆ³öºó£¬UberµÄ·¢Õ¹´ÙʹÆäÊý¾Ý¼¯´ó·ùÔö£¬Òò´Ë¸üйý³Ì½«ºÄʱ¸ü¾Ã¡£³öÓÚÕâ¸öÔÒò£¬Ö®Ç°ÒÅÁôµÄPostgresʵÀýÖ±ÖÁ½ñÈÕÈÔÔÚÔËÐÐPostgres
9.2£¬¾¡¹ÜĿǰPostgres GAÒѾ·¢²¼µ½9.5°æ±¾ÁË¡£
ÈôÔËÐÐPostgres 9.4»òÊǸüеİ汾£¬Ôò¿ÉÒÔÔËÓÃpglogicalÕâÑùµÄ¹¦ÄÜ£¬¸Ã¹¦ÄÜʵÏÖÁËPostgresµÄÒ»¸öÂß¼¸´ÖƲ㡣ÔËÓÃpglogical£¬Ôò¿É¸´ÖƲ»Í¬Postgres°æ±¾¼äµÄÊý¾Ý£¬ÕâÒâζ×Å¿ÉÒÔÔÚÎÞÐèÔâÓöÖØ´ó¹ÊÕϵÄǰÌáÏ£¬½øÐиüвÙ×÷£¨È罫Postgres
9.4¸üе½9.5°æ±¾£©¡£Í¬Ê±£¬¸ÃÐÔÄÜÒ²ÈÔÈ»´æÔÚÎÊÌ⣬ÒòΪËü²¢Î´¼¯³Éµ½PostgresµÄÖ÷¸ÉÊ÷£¬ÇÒpglogical±¾ÉíÒ²ÈÔ·ÇÔËÐоɰæPostgresµÄÑ¡Ïî¡£
MySQLµÄ¼Ü¹¹
³ýÁ˽éÉÜPostgresµÄÏÞÖÆ£¬»¹Òª²ûÊöһ϶ÔÓÚ½üÆÚµÄUber Engineering´æ´¢ÏîÄ¿£¨ÈçSchemaless£©¶øÑÔ£¬ÒÔMySQLΪ¹¤¾ßµÄÖØÒªÒâÒå¡£»á·¢ÏÖÔںܶàÇé¿öÏ£¬MySQL¸üΪÊÊÓá£ÎªÁËÁ˽âÆäÖеIJîÒ죬UberÍŶӼì²âÁËMySQLµÄ¼Ü¹¹ÒÔ¼°ÆäÓëPostgres¼Ü¹¹¼äµÄ²î±ð¡£ÍŶÓÌØ±ð·ÖÎöÁËMySQLÊÇÈçºÎÓëInnoDBµÄ´æ´¢ÒýÇæÏàºÏ×÷µÄ¡£Õë¶ÔUber£¬ÆäÍŶӲ»½öʹÓõ½ÁËInnoDB£¬µ«Ëü¿ÉÄÜÊÇ×îÊÜ»¶ÓµÄMySQL´æ´¢ÒýÇæ¡£
InnoDB´ÅÅÌÉϵıíʾ·¨
ÀàËÆÓÚPostgres£¬InnoDBÖ§³Ö¸ß¼¶¹¦ÄÜ£¬ÈçMVCCºÍ¿É±äÊý¾Ý¡£¹ØÓÚInnoDBÔÚ´ÅÅÌÉϸñʽµÄÏ꾡ÌÖÂÛ£¬²»ÊôÓÚ±¾Îĵķ¶³ë£»±¾ÎĽ«×ÅÑÛÓÚÆäÓëPostgres¶þÕß¼äµÄºËÐIJîÒì¡£
ÆäÖÐ×îÖØÒªµÄ¼Ü¹¹Çø±ðÔÚÓÚ£ºPostgresÖ±½Ó½«Ë÷Òý¼Ç¼ӳÉäµ½´ÅÅÌÉϵÄλÖ㬶øInnoDBÔò±£ÓÐÒ»¸ö¶þ¼¶½á¹¹¡£InnoDB¶þ¼¶Ë÷Òý¼Ç¼ָÕëÖ¸ÏòÖ÷¼üÖµ£¬¶ø²»ÊÇÏñPostgresÖÐctidµÄ²Ù×÷£¬Ö¸ÕëÖ¸Ïò´ÅÅÌÉÏÐеÄλÖá£Èç´Ë£¬MySQLÖеĶþ¼¶Ë÷ÒýÁ¬½ÓË÷Òý¼üºÍÖ÷¼ü¡£

ÈôÒª»ùÓÚ£¨first£¬last£©Ë÷ÒýÖ´ÐÐÒ»ÏîË÷Òý²éÕÒ£¬Êµ¼ÊÉÏÐèÒªÍê³ÉÁ½Ïî²éÕÒ¡£µÚÒ»ÏîËÑË÷±í²¢ÕÒ³öÒ»Ìõ¼Ç¼µÄÖ÷¼ü¡£Ò»µ©ÕÒµ½Ö÷¼ü£¬µÚ¶þÏî²éѯÆô¶¯£¬¿ªÊ¼ËÑË÷Ö÷¼üË÷ÒýÒÔÕÒ³ö´ÅÅÌÉÏÐеÄλÖá£
¸ÃÉè¼Æ±íÃ÷ÔÚÖ´ÐеڶþÏî¼ü²éÕÒµÄʱºò£¬¶ÔPostgresÔì³ÉÇá΢µÄ¸ºÃæÓ°Ï죬ÒòΪInnoDBÒª½øÐÐÁ½´ÎË÷Òý²éÕÒ£¬¶øPostgres½öÐèÒ»´Î¡£È»¶ø£¬ÓÉÓÚÊDZê×¼»¯Êý¾Ý£¬ÐиüнöÐè¸üÐÂÄÇÐ©ÕæÕýÒòΪÐиüжø²úÉú±ä¸üµÄË÷Òý¼Ç¼¡£´ËÍ⣬InnoDBÍùÍùÔÚÊʵ±µÄλÖýøÐÐÐиüС£Èô¾ÉÊÂÎñÐèÒªÒýÓÃÒ»¸öÐÐÒÔʵÏÖMVCC£¬ÔòMySQL½«¾ÉÐи´ÖƵ½Ò»¸öÃûΪ»Ø¹ö¶ÎµÄÌØÊâÇøÓò¡£
ÏÂÃæ¼ÌÐø¸ú½ø¸üÐÂal-Khw¨¡rizm¨©³öÉúÄê·Ýʱ·¢ÉúµÄÇé¿ö¡£ÈôÓпռ䣬idΪ4µÄÐÐÖеijöÉúÄê·Ý×Ö¶ÎÔÚÊʵ±Î»ÖøüУ¨Êµ¼ÊÉÏ£¬¸Ã¸üÐÂ×ÜÊÇÔÚÊʵ±Î»Ö÷¢Éú£¬ÒòΪ³öÉúÄê·Ýʱһ¸öÕ¼¾Ý¶¨¶î¿Õ¼äµÄInteger£©¡£³öÉúÄê·ÝË÷ÒýÒ²ÔÚÊʵ±Î»ÖøüУ¬ÒÔ·´Ó³ÐÂÊý¾Ý¡£¾ÉÐÐÊý¾Ý±»¸´ÖƵ½»Ø¹ö¶Î¡£Ö÷¼üË÷ÒýÎÞÐè¸üУ¬£¨Ãû£¬ÐÕ£©Ë÷ÒýÒ²ÎÞÐè¸üС£Èô±íÉϰüº¬´óÁ¿Ë÷Òý£¬Ò²Ö»ÐèÒª¸üмìË÷Éæ¼°birth_year×ֶεÄÄDz¿·ÖË÷Òý¡£Òò´Ë£¬¼ÙÉèÏÖÓÐÉæ¼°ÀàËÆÓÚsignup_date¡¢last_login_timeµÈ×ֶεÄË÷Òý£¬ÔòÎÞÐè¶ÔÆä½øÐиüУ¬·´Ö®£¬ÔÚPostgresÖÐÔò±ØÐë¶ÔÆä¸üС£
ͬʱ£¬¸ÃÉè¼Æ»¹ÄÜÌá¸ßÊý¾Ýɨ³ý£¨vacuuming£©ºÍѹËõ²Ù×÷µÄЧÂÊ¡£ËùÓа´Ðè´ýɨ³ýµÄÐж¼¿ÉÖ±½ÓÔڻعö¶ÎÖлñÈ¡¡£Ïà½Ï¶øÑÔ£¬PostgresµÄautovacuum£¨×Ô¶¯É¨³ý£©½ø³ÌÔò±ØÐëÒª½øÐÐÈ«±íɨÃèÀ´È·ÈÏɾ³ýÐС£

MySQLÆôÓÃÒ»¸ö¶îÍâµÄ¼ä½Ó²ã£º¶þ¼¶Ë÷Òý¼Ç¼ָÏòÖ÷¼ü¼Ç¼£¬ÇÒÖ÷¼ü±¾Éí±£´æ´ÅÅÌÉϵÄÐÐλÖá£ÈôÐÐÆ«ÒÆ·¢Éú±ä¸ü£¬Ö»Ðè¸üÐÂÖ÷Ë÷Òý¡£
¸´ÖÆ
MySQLÖ§³Ö¶àÖÖ¸´ÖÆÄ£Ê½£º
»ùÓÚÓï¾ä£¨statement£©µÄ¸´ÖÆ£º¸´ÖÆÂß¼SQLÓï¾ä£¨ÀýÈ磬°´×ÖÃæ¸´ÖÆÓï¾äÎÄ×ÖÈ磺UPDATE users
SET birth_year=770 WHERE id = 4£©£»
»ùÓÚÐеĸ´ÖÆ£º¸´ÖƱä¸üµÄÐмǼ£»
»ìºÏ¸´ÖÆÄ£Ê½£º»ìºÏÉÏÊöÁ½ÖÖģʽ¡£
¶ÔÓÚÕâЩģʽ»¹ÓжàÖÖȨºâ¡£»ùÓÚÓï¾äµÄ¸´ÖÆÍùÍù×îΪ¼ò½à£¬µ«¿ÉÄÜ»áÐèÒª´Ó¿âÔËÓø߼۵ÄÓï¾äÀ´¸üÐÂÉÙÁ¿Êý¾Ý¡£´ËÍ⣬»ùÓÚÐеĸ´ÖÆ£¬ÀàËÆÓÚPostgres
WAL¸´ÖÆ£¬Ïà¶Ô·±Ëö£¬µ«Æä½á¹ûÔ¤¼ûÐÔ¸üÇ¿£¬ÇÒÓÐÖúÓÚ´Ó¿â¸ßЧ¸üС£
ÔÚMySQLÖУ¬Ö»ÓÐÖ÷Ë÷Òý¾ßÓÐÖ¸ÕëÖ¸Ïò´ÅÅÌÉϵÄÐÐÆ«ÒÆ¡£ÕâÔÚÉæ¼°¸´ÖÆÊ±£¬²úÉúÁËÒ»¸öÖØÒª½á¹û¡£MySQL¸´ÖÆÁ÷Ö»ÐèÒª°üº¬Âß¼¸üе½ÐеÄÐÅÏ¢¡£¸´ÖƸüÐÂÊôÓÚÕâÒ»Àà¡°½«ÐеÄʱ¼ä´Á´ÓT_1¸Äµ½T_2¡±¡£ÕâЩÓï¾äµÄ½á¹ûΪ´Ó¿â×Ô¶¯ÍƶÏÐèÒª±ä¸üµÄË÷Òý¡£
¶Ô±È֮ϣ¬Postgres¸´ÖÆÁ÷°üº¬ÎïÀí±ä¸ü£¬ÀýÈç¡°´ÅÅÌÉÏÆ«ÒÆ8,382,491£¬Ð´Èë×Ö½ÚXYZ¡±¡£PostgresÏ£¬´ÅÅ̵Äÿһ´ÎÎïÀí±ä¸ü¶¼ÐèÄÉÈëWALÁ÷¡£Ð¡µÄÂß¼±ä»¯£¨Èç¸üÐÂÒ»¸öʱ¼ä´Á£©ÐèÒª¶à¸ö´ÅÅÌÉϵıä¸ü£ºPostgres±ØÐë²åÈëÐÂÔª×é²¢¸üÐÂËùÓÐÖ¸Ïò¸ÃÔª×éµÄË÷Òý¡£Èç´Ë£¬WALÁ÷Öн«ÊäÈë´óÁ¿±ä¸ü¡£¸ÃÉè¼Æ²îÒì±íÃ÷MySQL¸´ÖƵĶþ½øÖÆÈÕÖ¾±ÈPostgres
WALÁ÷Òª¼ò½àºÜ¶à¡£
ÿ¸ö¸´ÖÆÁ÷µÄÔËÐз½Ê½¶ÔÓÚMVCCºÍ´Ó¿âµÄºÏ×÷·½Ê½Ò²ÓÐ×ÅÖØÒªÒâÒå¡£ÓÉÓÚMySQL¸´ÖÆÁ÷ÓÐÂß¼¸üУ¬´Ó¿â¿É¾ßÓÐÕæÕýµÄMVCCÓïÒ壻Òò´Ë¶ÁÈ¡´Ó¿â²éѯ²»»áËø¶¨¸´ÖÆÁ÷¡£Ïà½Ï¶øÑÔ£¬Postgres
WALÁ÷°üº¬´ÅÅÌÉϵÄÎïÀí±ä¸ü£¬Òò´ËPostgres´Ó¿â²»ÄÜÔËÓÃÓë¶ÁÈ¡²éѯÏà³åÍ»µÄ¸´ÖƸüУ¬¹Ê¶øÎÞ·¨ÊµÏÖMVCC¡£
MySQLµÄ¸´ÖƼܹ¹±íÃ÷£¬Èôbugsȷʵµ¼ÖÂÁ˱í±ÀÀ££¬Ôò¸ÃÎÊÌⲻ̫¿ÉÄܵ¼ÖÂÔÖÄÑÐԵĹÊÕÏ¡£¸´ÖÆ·¢ÉúÔÚÂß¼²ã£¬Òò´ËÔÙÆ½ºâB-treeÕâÀà²Ù×÷²»¿ÉÄÜÒý·¢Ë÷Òý±ÀÀ£¡£Ò»¸öµäÐ͵ÄMySQL¸´ÖÆÎÊÌâÍùÍùÊÇÓï¾ä±»Ìø¹ý£¨»òȱÉÙ´ÎÊý¡¢ÔËÐÐÁ½´Î£©µÄÇé¿ö¡£Õâ¿ÉÄܻᵼÖÂÊý¾Ýȱʧ»òʧЧ£¬µ«²»»áµ¼ÖÂÊý¾Ý¿âå´»ú¡£
×îÖÕ£¬MySQLµÄ¸´ÖƼܹ¹¼ò»¯Á˲»Í¬MySQL°æ±¾¼äµÄ¸´ÖÆ¡£Èô¸´ÖƸñʽ±ä»¯£¬ÔòMySQL½öÔö¼ÓÆä°æ±¾£¬Õâ¶ÔMySQL¶øÑÔ²¢²»³£¼û¡£MySQLµÄÂß¼¸´ÖƸñʽҲ±íÃ÷´æ´¢ÒýÇæ²ãÄÚ´ÅÅÌÉϵıä¸ü²»»áÓ°Ïì¸´ÖÆ¸ñʽ¡£Ö´ÐÐMySQL¸üеij£Ó÷½Ê½ÊÇÒ»´Î¸üÐÂÒ»¸ö´Ó¿â£¬ÇÒÒ»µ«¸üÐÂËùÓдӿ⣬ÔòÆäÖÐÒ»¸ö´Ó¿â½«±äΪеÄÖ÷Êý¾Ý¿â¡£Õâ¿ÉÒÔÔÚÁã¹ÊÕÏÏÂÍê³É£¬Í¬Ê±Ò²¼ò»¯Á˱£³ÖMySQL×îÐÂ״̬µÄ²Ù×÷¡£
ÆäËüMySQLÉè¼ÆÓÅÊÆ
So far, we¡¯ve focused on the on-disk architecture for
Postgres and MySQL. Some other important aspects of
MySQL¡¯s architecture cause it to perform significantly
better than Postgres, as well.
½ØÖ¹Ä¿Ç°£¬±¾ÎÄÖ÷Òª¹Ø×¢PostgresºÍMySQL´ÅÅÌÉϵļܹ¹¡£¶øMySQL¼Ü¹¹»¹ÓÐÒ»²¿·ÖÒªµãʹ֮ÔÚÔËÐÐÉÏÓÅÓÚPostgres¡£
»º³å³Ø
Ê×ÏÈ£¬»º´æÔÚÕâÁ½¸öÊý¾Ý¿âÖеÄÔËÐз½Ê½²»Í¬¡£PostgresΪÄÚ²¿¸ßËÙ»º´æÅäÖÃÄڴ棬µ«½ÏÖ®»úÆ÷×ÜÄÚ´æÁ¿£¬ÕâЩ¸ßËÙ»º´æÍùÍù½ÏС¡£ÎªÁËÌáÉýÐÔÄÜ£¬PostgresÖ§³ÖÄÚºËͨ¹ýÒ³Ãæ»º´æ×Ô¶¯´æ´¢×î½ü·ÃÎÊ´ÅÅ̵ÄÊý¾Ý¡£ÀýÈ磬Uber×î´óµÄPostgres´Ó¿â¾ßÓÐ768
GB¿ÉÓÃÄڴ棬µ«Êµ¼ÊÉÏÆäÖÐÖ»ÓÐÔ¼25 GB±»PostgresµÄRSSÄÚ´æÕ¼Óá£Õâ¸øLinuxÒ³Ãæ»º´æÒÅÁôÁ˳¬¹ý700
GBµÄ¿ÕÏÐÄÚ´æ¡£
¸ÃÉè¼ÆµÄÎÊÌâÔÚÓÚ£¬ÊÂʵÉÏ£¬½ÏÖ®·ÃÎÊRSSÄڴ棬ͨ¹ýÒ³Ãæ»º´æ·ÃÎÊÊý¾ÝºÄ×ʸü¸ß¡£Õë¶Ô´ÅÅÌÖеÄÊý¾Ý²éѯ£¬Postgres·¢²¼ÁËlseek(2)ºÍread(2)ϵͳµ÷ÓÃÒÔ¶¨Î»Êý¾Ý¡£´Ë¶þÕß¶¼»áÒý·¢ÉÏÏÂÎÄÇл»£¨context
switch£©£¬Õâ±È·ÃÎÊÖ÷ÄÚ´æÊý¾ÝµÄ»¨·Ñ»¹Òª¸ü¸ß¡£Êµ¼ÊÉÏ£¬ÔÚÕâÒ»µãÉÏ£¬PostgresÉõÖÁ»¹Ã»ÓÐÍêÈ«ÓÅ»¯£ºÆäδʹÓÃpread(2)ϵͳµ÷Óã¬pread(2)½«seek
+ read²Ù×÷ºÏ²¢µ½Ò»¸öϵͳµ÷ÓÃÖ®ÖС£
ͨ¹ý±È½Ï£¬InnoDB´æ´¢ÒýÇæÊµÏÖÁË×ÔÉíInnoDB»º³å³ØÖеÄLRU¡£ÕâÔÚÂß¼ÉϺÍLinuxÒ³Ãæ»º´æÏàËÆ£¬µ«ËüʵÏÖÓÚÓû§¿Õ¼ä¡£ËäÈ»±ÈPostgresµÄÉè¼Æ¸ü¸´ÔÓ£¬µ«InnoDB»º³å³ØÉè¼ÆÒ²ÓµÓÐһЩÄÜÍ»³öµÄÓÅÊÆ£º
¿ÉʵÏÖÒ»¸öÓû§LRUÉè¼Æ¡£ÀýÈ磬Äܹ»¼ì²â³öÓÐËðLRUµÄ¶ñÒâ·ÃÎÊģʽ£¬²¢×èÖ¹ÆäÔì³É¸ü½øÒ»²½µÄΣº¦£»
¼¸ºõ²»»áµ¼ÖÂÉÏÏÂÎÄÇл»¡£Í¨¹ýInnoDB»º³å³Ø·ÃÎʵ½µÄÊý¾Ý²»ÒªÇóÈκÎÓû§/Äں˵ÄÉÏÏÂÎÄÇл»¡£×îÔã¸âµÄÇé¿öιýÓÚ·¢ÉúTLB
miss£¨TLBʧ°Ü£©£¬ÕâÏà¶Ô¶øÑÔÒ²½ÏΪ±ãÒË£¬ÇÒ¿Éͨ¹ý´óÄÚ´æÒ³Ê¹Ëðʧ×îС»¯¡£
Á¬½Ó¹ÜÀí
ͨ¹ý¶Ôÿ¸öÁ¬½ÓÉú³ÉÒ»¸öỊ̈߳¨thread-per-connection£©£¬MySQLʵÏÖ²¢·¢Á¬½Ó¡£ÕâÖÖÁ¬½ÓÕ¼ÓÃÏà¶Ô½ÏµÍ£»Ã¿¸öÏ̶߳¼Õ¼ÓÃÕ»¿Õ¼äÄڴ棬¼ÓÉϲ¿·ÖÅäÖõ½¶ÑÄÚ´æÁ¬½ÓÌØ¶¨£¨connection-specific£©»º³åÇøµÄ¡£½«MySQLÀ©Õ¹µ½10,000»òÊÇÕâÑùµÄ²¢ÐÐÁ¬½ÓÊǺܳ£¼ûµÄ£¬Êµ¼ÊÉÏ£¬Èç½ñUberµÄMySQLʵÀý¸ü½Ó½üÕâ¸öÁ¬½ÓÊý¡£
µ«Postgres²ÉÓÃÒ»¸öÿ¸öÁ¬½ÓÒ»¸ö½ø³Ì£¨process-per-connection£©Éè¼Æ¡£³öÓë¶à·½ÃæµÄÔÒò£¬¸ÃÉè¼ÆºÄ×ʱÈÒ»¸öthread-per-connectionÉè¼Æ¸ß³öºÜ¶à¡£½ÏÖ®Éú³ÉÒ»¸öÐÂỊ̈߳¬ÅÉÉúÒ»¸öнø³Ì»áÕ¼¾Ý¸ü¶àÄÚ´æ¡£´ËÍ⣬½ø³Ì¼äµÄIPC±ÈÏ̼߳äµÄIPCºÄ×ʸü¶à¡£Postgres
9.2ʹÓÃSystem V IPCΪIPCµÄÔÓ¶ø²»ÊÇʹÓÃÏß³ÌʱµÄÇáÁ¿¼¶futexes¡£ÒòΪͨ³£ÔÚ futexÎÞ¾ºÕùµÄÇé¿öÏ£¬ÎÞÐèÉÏÏÂÎÄÇл»£¬ËùÒÔfutexes±ÈSystem
V IPCËٶȸü¿ì¡£
PostgresÉè¼ÆµÄÄÚ´æºÍIPCÕ¼ÓÃÖ®Í⣬PostgresËÆºõÔÚÖ§³Ö¸ßÁ¬½ÓÊýÁ¿·½ÃæµÄ´¦ÀíÄÜÁ¦½Ï²î£¬¼´Ê¹ÊÇÔÚ¿ÉÓÃÄÚ´æ³ä×ãµÄÇé¿öÏ¡£¶øÔÚÀ©Õ¹PostgresÖÁÊý°Ù¸ö»îÔ¾Á¬½ÓµÄ¹ý³ÌÖУ¬UberÍŶÓÔâÓöÁËÖØ´óÎÊÌâ¡£ËäÈ»¸Ã¹Ù·½ÎĵµÎ´¸ø³öÃ÷È·ÔÒò£¬µ«µÄÈ·¸ø³öÁËÇ¿ÁҵĽ¨Ò飺ѡÓýø³ÌÍâÁ¬½Ó³Ø»úÖÆÒÔÀ©Õ¹PostgresÁ¬½ÓÊýÁ¿¡£ÏàÓ¦µØ£¬ÔÚÔËÓÃpgbouncerºÏ²¢Á¬½ÓºÍPostgres·½Ã棬UberÍŶÓ×ÜÌåÉÏ»ñµÃÁ˳ɹ¦¡£È»¶ø£¬Å¼¶ûºó¶Ë·þÎñ»¹ÊÇ»á³öÏÖÓ¦ÓÃbug£¬´Ó¶øµ¼Ö¿ªÆô¶àÓÚÓ¦ÓзþÎñµÄ»îÔ¾Á¬½Ó£¨ÍùÍùÊÇ¡°idle
in transaction¡±Á¬½Ó£©£¬ÕâЩbugsÓֻᵼÖ¹ÊÕÏÀ©ÕÅ¡£
½áÂÛ
ÔÚUberÔçÆÚ£¬PostgresÊÊÓ¦·¢Õ¹ÐèÇ󣬵«ÊÇËæ×ÅPostgresµÄÀ©Õ¹ºÍUberµÄ·¢Õ¹£¬UberÍŶÓÖ𽥿ªÊ¼ÔâÓöһЩ¼¬ÊÖµÄÎÊÌâ¡£Èç½ñ£¬Uber»¹±£ÓÐһЩÒÅÁôPostgresʵÀý£¬µ«´óÁ¿Êý¾Ý¿âÊÇ»ùÓÚMySQL£¨ÍùÍùÓõ½Schemaless²ã£©¹¹½¨£¬»òÊÇ£¨ÔÚijЩÇé¿öÏ£©»ùÓÚNoSQLÊý¾Ý¿â£¬±ÈÈçCassandra¡£UberÍŶÓÈç½ñ¶ÔMySQL½ÏΪÂúÒ⣬δÀ´½«»á¸üиü¶à²©ÎÄ£¬ÒÔ½âÊÍÆäÕë¶ÔUberµÄ¸ü¶àÏȽøÓ¦Óá£
|