ฉันขอแนะนำให้คุณอ่านบันทึกรายงานต้นปี 2016 ของ Vladimir Sitnikov “PostgreSQL และ JDBC กำลังบีบข้อมูลทั้งหมดออก”
สวัสดีตอนบ่าย ฉันชื่อวลาดิมีร์ ซิตนิคอฟ ฉันทำงานให้กับ NetCracker มาเป็นเวลา 10 ปีแล้ว และฉันก็เน้นเรื่องประสิทธิภาพการทำงานเป็นส่วนใหญ่ ทุกอย่างที่เกี่ยวข้องกับ Java ทุกอย่างที่เกี่ยวข้องกับ SQL คือสิ่งที่ฉันชอบ
และวันนี้ผมจะพูดถึงสิ่งที่เราพบในบริษัทเมื่อเราเริ่มใช้ PostgreSQL เป็นเซิร์ฟเวอร์ฐานข้อมูล และเราทำงานกับ Java เป็นส่วนใหญ่ แต่สิ่งที่ฉันจะบอกคุณวันนี้ไม่ใช่แค่เกี่ยวกับ Java เท่านั้น ตามที่แสดงให้เห็นในทางปฏิบัติ สิ่งนี้เกิดขึ้นในภาษาอื่นด้วย
พวกเราจะพูด:
- เกี่ยวกับการสุ่มตัวอย่างข้อมูล
- เกี่ยวกับการบันทึกข้อมูล
- และยังเกี่ยวกับประสิทธิภาพด้วย
- และเกี่ยวกับคราดใต้น้ำที่ถูกฝังอยู่ที่นั่น
เริ่มต้นด้วยคำถามง่ายๆ เราเลือกหนึ่งแถวจากตารางตามคีย์หลัก
ฐานข้อมูลอยู่บนโฮสต์เดียวกัน และการทำฟาร์มทั้งหมดนี้ใช้เวลา 20 มิลลิวินาที
20 มิลลิวินาทีนี้ถือว่ามาก หากคุณมีคำขอดังกล่าว 100 รายการ คุณจะใช้เวลาต่อวินาทีในการเลื่อนดูคำขอเหล่านี้ กล่าวคือ เรากำลังเสียเวลา
เราไม่ชอบที่จะทำสิ่งนี้ และดูว่าฐานเสนออะไรให้เราสำหรับสิ่งนี้ ฐานข้อมูลเสนอทางเลือกสองทางให้เราในการดำเนินการค้นหา
ตัวเลือกแรกคือคำของ่ายๆ มีอะไรดีเกี่ยวกับเรื่องนี้? ความจริงที่ว่าเรารับมันและส่งไปและไม่มีอะไรเพิ่มเติม
ฐานข้อมูลยังมีการสืบค้นขั้นสูงซึ่งซับซ้อนกว่า แต่ใช้งานได้ดีกว่า คุณสามารถส่งคำขอการแยกวิเคราะห์ การดำเนินการ การเชื่อมโยงตัวแปร ฯลฯ แยกต่างหากได้
ข้อความค้นหาแบบขยายพิเศษเป็นสิ่งที่เราจะไม่กล่าวถึงในรายงานปัจจุบัน บางทีเราอาจต้องการบางสิ่งบางอย่างจากฐานข้อมูลและมีสิ่งที่ปรารถนาเกิดขึ้นในรูปแบบใดรูปแบบหนึ่งนั่นคือสิ่งที่เราต้องการ แต่มันเป็นไปไม่ได้ในขณะนี้และในปีหน้า ดังนั้นเราจึงบันทึกมันไว้และเราจะเขย่าคนหลักๆ
และสิ่งที่เราทำได้คือแบบสอบถามง่ายๆ และแบบสอบถามแบบขยาย
แต่ละแนวทางมีความพิเศษอย่างไร?
แบบสอบถามธรรมดาเหมาะสำหรับการดำเนินการเพียงครั้งเดียว เสร็จแล้วลืมเลย และปัญหาคือมันไม่รองรับรูปแบบข้อมูลไบนารี่ กล่าวคือ ไม่เหมาะกับระบบประสิทธิภาพสูงบางระบบ
แบบสอบถามแบบขยาย - ช่วยให้คุณประหยัดเวลาในการแยกวิเคราะห์ นี่คือสิ่งที่เราทำและเริ่มใช้ สิ่งนี้ช่วยเราได้จริงๆ ไม่เพียงประหยัดในการแยกวิเคราะห์เท่านั้น มีการประหยัดในการถ่ายโอนข้อมูล การถ่ายโอนข้อมูลในรูปแบบไบนารี่มีประสิทธิภาพมากกว่ามาก
เรามาฝึกกันต่อ นี่คือลักษณะของแอปพลิเคชันทั่วไป อาจเป็น Java เป็นต้น
เราสร้างคำสั่ง ดำเนินการตามคำสั่ง สร้างปิด. นี่ผิดตรงไหน? อะไรคือปัญหา? ไม่มีปัญหา. นี่คือสิ่งที่กล่าวไว้ในหนังสือทุกเล่ม ควรจะเขียนแบบนี้ครับ หากคุณต้องการประสิทธิภาพสูงสุด เขียนแบบนี้
แต่การปฏิบัติแสดงให้เห็นว่าสิ่งนี้ไม่ได้ผล ทำไม เพราะเรามีวิธีการ "ปิด" และเมื่อเราทำเช่นนี้ จากมุมมองของฐานข้อมูล ปรากฎว่ามันเหมือนกับคนสูบบุหรี่ทำงานกับฐานข้อมูล เราพูดว่า "PARSE EXECUTE DEALLOCATE"
เหตุใดจึงมีการสร้างและขนถ่ายข้อความพิเศษทั้งหมดนี้ ไม่มีใครต้องการพวกเขา แต่สิ่งที่มักจะเกิดขึ้นในPreparedStatementsก็คือเมื่อเราปิดพวกมัน พวกมันจะปิดทุกอย่างในฐานข้อมูล นี่ไม่ใช่สิ่งที่เราต้องการ
เราต้องการทำงานร่วมกับฐานเช่นเดียวกับคนที่มีสุขภาพแข็งแรง เราได้รับและเตรียมคำแถลงของเราครั้งหนึ่ง จากนั้นเราก็ดำเนินการหลายครั้ง ในความเป็นจริง หลายครั้ง - นี่เป็นครั้งเดียวในชีวิตของแอปพลิเคชัน - แอปพลิเคชันเหล่านั้นถูกแยกวิเคราะห์แล้ว และเราใช้รหัสคำสั่งเดียวกันกับ REST ที่ต่างกัน นี่คือเป้าหมายของเรา
เราจะบรรลุเป้าหมายนี้ได้อย่างไร?
ง่ายมาก - ไม่จำเป็นต้องปิดงบ เราเขียนดังนี้: "เตรียม" "ดำเนินการ"
ถ้าเราเปิดตัวอะไรแบบนี้ ก็ชัดเจนว่าจะมีบางอย่างล้นอยู่ที่ไหนสักแห่ง หากไม่ชัดเจนคุณสามารถลองใส่ได้ มาเขียนเกณฑ์มาตรฐานที่ใช้วิธีการง่ายๆ นี้กัน สร้างคำสั่ง เราเปิดใช้งานกับไดรเวอร์บางเวอร์ชันและพบว่ามันขัดข้องค่อนข้างเร็วโดยสูญเสียหน่วยความจำทั้งหมดที่มี
เป็นที่ชัดเจนว่าข้อผิดพลาดดังกล่าวแก้ไขได้ง่าย ฉันจะไม่พูดถึงพวกเขา แต่ฉันจะบอกว่าเวอร์ชันใหม่ทำงานได้เร็วกว่ามาก วิธีการนี้โง่แต่ก็ยัง
ทำงานอย่างไรให้ถูกต้อง? เราต้องทำอะไรเพื่อสิ่งนี้?
ในความเป็นจริง แอปพลิเคชันจะปิดงบเสมอ ในหนังสือทุกเล่มเขาบอกให้ปิด ไม่อย่างนั้นหน่วยความจำจะรั่ว
และ PostgreSQL ไม่ทราบวิธีแคชข้อความค้นหา จำเป็นที่แต่ละเซสชันจะสร้างแคชนี้ขึ้นมาเอง
และเราไม่ต้องการเสียเวลาในการแยกวิเคราะห์เช่นกัน
และตามปกติเรามีสองทางเลือก
ตัวเลือกแรกคือเรารับมันแล้วบอกว่ามารวมทุกอย่างไว้ใน PgSQL มีแคชอยู่ที่นั่น มันแคชทุกอย่าง มันจะออกมาดี เราเห็นสิ่งนี้ เรามีคำขอ 100500 รายการ ไม่ทำงาน, ไม่เป็นผล. เราไม่ตกลงที่จะเปลี่ยนคำขอเป็นขั้นตอนด้วยตนเอง ไม่ไม่.
เรามีทางเลือกที่สอง - เอาไปตัดเอง เราเปิดแหล่งที่มาและเริ่มตัด เราเห็นแล้วเห็น ปรากฎว่าการทำนั้นไม่ยากนัก
สิ่งนี้ปรากฏในเดือนสิงหาคม 2015 ขณะนี้มีเวอร์ชันที่ทันสมัยมากขึ้น และทุกอย่างยอดเยี่ยมมาก มันทำงานได้ดีมากโดยที่เราไม่ต้องเปลี่ยนแปลงอะไรในแอปพลิเคชัน และเรายังหยุดคิดไปในทิศทางของ PgSQL ด้วยซ้ำ นั่นก็เพียงพอแล้วสำหรับเราที่จะลดต้นทุนค่าโสหุ้ยทั้งหมดให้เกือบเป็นศูนย์
ดังนั้น คำสั่งที่จัดเตรียมโดยเซิร์ฟเวอร์จะถูกเปิดใช้งานในการดำเนินการครั้งที่ 5 เพื่อหลีกเลี่ยงการสูญเสียหน่วยความจำในฐานข้อมูลในแต่ละคำขอแบบครั้งเดียว
คุณอาจถามว่า – ตัวเลขอยู่ที่ไหน? คุณได้อะไร? และฉันจะไม่ให้ตัวเลขที่นี่เพราะแต่ละคำขอมีของตัวเอง
การสืบค้นของเรานั้นใช้เวลาประมาณ 20 มิลลิวินาทีในการแยกวิเคราะห์การสืบค้น OLTP ใช้เวลาดำเนินการ 0,5 มิลลิวินาที และ 20 มิลลิวินาทีสำหรับการแยกวิเคราะห์ คำขอ – ข้อความ 10 KiB, แผน 170 บรรทัด นี่คือคำขอ OLTP มันขอ 1, 5, 10 บรรทัด บางครั้งก็มากกว่านั้น
แต่เราไม่ต้องการเสียเวลา 20 มิลลิวินาทีเลย เราลดมันลงเหลือ 0. ทุกอย่างดีมาก
คุณจะเอาอะไรไปจากที่นี่? หากคุณมี Java คุณจะใช้ไดรเวอร์เวอร์ชันทันสมัยและชื่นชมยินดี
หากคุณพูดภาษาอื่น ลองคิดดูว่า คุณอาจต้องการสิ่งนี้ด้วยหรือไม่ เพราะจากมุมมองของภาษาสุดท้าย เช่น ถ้า PL 8 หรือคุณมี LibPQ ก็ไม่ชัดเจนสำหรับคุณว่าคุณไม่ได้ใช้เวลาไปกับการดำเนินการ การแยกวิเคราะห์ และนี่เป็นสิ่งที่ควรค่าแก่การตรวจสอบ ยังไง? ทุกอย่างฟรี
ยกเว้นว่ามีข้อผิดพลาดและลักษณะเฉพาะบางประการ และเราจะพูดถึงพวกเขาตอนนี้ ส่วนใหญ่จะเกี่ยวกับโบราณคดีอุตสาหกรรม เกี่ยวกับสิ่งที่เราค้นพบ สิ่งที่เราเจอ
หากคำขอถูกสร้างขึ้นแบบไดนามิก มันเกิดขึ้น. มีคนนำสายมาต่อเข้าด้วยกัน ทำให้เกิดแบบสอบถาม SQL
ทำไมเขาถึงแย่? แย่เลยเพราะแต่ละครั้งเราจะลงเอยด้วยสายที่แตกต่างกัน
และจำเป็นต้องอ่าน hashCode ของสตริงที่แตกต่างกันนี้อีกครั้ง นี่เป็นงานของ CPU จริงๆ - การค้นหาข้อความคำขอแบบยาวในแฮชที่มีอยู่นั้นไม่ใช่เรื่องง่าย ดังนั้นข้อสรุปจึงง่าย - อย่าสร้างคำขอ เก็บไว้ในตัวแปรเดียว และชื่นชมยินดี
ปัญหาต่อไป. ชนิดข้อมูลมีความสำคัญ มี ORM ที่บอกว่าไม่สำคัญว่าจะมี NULL แบบไหน ก็ขอให้มีบ้าง ถ้าเป็น Int เราก็พูดว่า setInt และถ้าเป็น NULL ก็ปล่อยให้มันเป็น VARCHAR เสมอ และมันจะสร้างความแตกต่างอะไรในท้ายที่สุดว่ามีค่า NULL อะไรบ้าง? ฐานข้อมูลจะเข้าใจทุกอย่างเอง และภาพนี้ใช้ไม่ได้
ในทางปฏิบัติฐานข้อมูลไม่สนใจเลย หากคุณพูดครั้งแรกว่านี่คือตัวเลข และครั้งที่สองที่คุณบอกว่าเป็น VARCHAR คุณจะไม่สามารถใช้คำสั่งที่เตรียมโดยเซิร์ฟเวอร์ซ้ำได้ และในกรณีนี้ เราต้องสร้างคำสั่งของเราใหม่
หากคุณกำลังดำเนินการค้นหาเดียวกัน ตรวจสอบให้แน่ใจว่าชนิดข้อมูลในคอลัมน์ของคุณไม่สับสน คุณต้องระวังค่า NULL นี่เป็นข้อผิดพลาดทั่วไปที่เราได้รับหลังจากที่เราเริ่มใช้ PreparationStatements
โอเค เปิดแล้ว บางทีพวกเขาอาจจะเอาคนขับไป และผลผลิตก็ลดลง สิ่งที่เลวร้าย
สิ่งนี้เกิดขึ้นได้อย่างไร? นี่เป็นจุดบกพร่องหรือคุณสมบัติหรือไม่? ขออภัย ไม่สามารถเข้าใจได้ว่านี่คือจุดบกพร่องหรือคุณลักษณะ แต่มีสถานการณ์สมมติที่ง่ายมากสำหรับการจำลองปัญหานี้ เธอซุ่มโจมตีเราโดยไม่คาดคิดโดยสิ้นเชิง และประกอบด้วยการสุ่มตัวอย่างจากตารางเดียว แน่นอนว่าเรามีคำขอดังกล่าวมากกว่านี้ ตามกฎแล้วจะมีตารางสองหรือสามตารางรวมอยู่ด้วย แต่มีสถานการณ์การเล่นเช่นนี้ นำเวอร์ชันใดก็ได้จากฐานข้อมูลของคุณมาเล่น
ประเด็นก็คือเรามีสองคอลัมน์ ซึ่งแต่ละคอลัมน์ได้รับการจัดทำดัชนีแล้ว มีหนึ่งล้านแถวในหนึ่งคอลัมน์ NULL และคอลัมน์ที่สองมีเพียง 20 บรรทัด เมื่อเราดำเนินการโดยไม่มีตัวแปรที่ถูกผูกไว้ ทุกอย่างจะทำงานได้ดี
หากเราเริ่มดำเนินการด้วยตัวแปรที่ถูกผูกไว้ เช่น เราดำเนินการคำสั่ง "?" หรือ “$1” สำหรับคำขอของเรา สุดท้ายแล้วเราจะได้อะไร?
การดำเนินการครั้งแรกเป็นไปตามที่คาดไว้ อันที่สองเร็วขึ้นเล็กน้อย มีบางอย่างถูกแคชไว้ สาม,สี่,ห้า. จากนั้นปัง - และอะไรทำนองนั้น และสิ่งเลวร้ายที่สุดคือสิ่งนี้จะเกิดขึ้นในการประหารชีวิตครั้งที่หก ใครจะรู้ว่าจำเป็นต้องดำเนินการหกครั้งพอดีจึงจะเข้าใจว่าแผนการดำเนินการที่แท้จริงคืออะไร
ใครเป็นคนผิด? เกิดอะไรขึ้น ฐานข้อมูลประกอบด้วยการปรับให้เหมาะสม และดูเหมือนว่าจะได้รับการปรับให้เหมาะสมสำหรับกรณีทั่วไป และด้วยเหตุนี้ เมื่อถึงจุดหนึ่ง เธอก็เปลี่ยนไปใช้แผนทั่วไป ซึ่งน่าเสียดายที่อาจกลายเป็นแตกต่างออกไป มันอาจจะกลายเป็นสิ่งเดียวกันหรืออาจจะแตกต่างออกไป และมีค่าเกณฑ์บางประเภทที่นำไปสู่พฤติกรรมนี้
คุณสามารถทำอะไรเกี่ยวกับเรื่องนี้? แน่นอนว่าการคาดเดาอะไรได้ยากกว่าที่นี่ มีวิธีแก้ไขง่ายๆ ที่เราใช้ นี่คือ +0, OFFSET 0 แน่นอนว่าคุณคงรู้จักวิธีแก้ปัญหาดังกล่าว เราเพียงแค่รับมันและเพิ่ม "+0" ให้กับคำขอและทุกอย่างเรียบร้อยดี ฉันจะแสดงให้คุณดูในภายหลัง
และมีตัวเลือกอื่น - ดูแผนให้ละเอียดยิ่งขึ้น นักพัฒนาไม่เพียงต้องเขียนคำขอเท่านั้น แต่ยังต้องพูดว่า “อธิบายการวิเคราะห์” 6 ครั้งด้วย ถ้าเป็น 5 มันจะไม่ทำงาน
และมีตัวเลือกที่สาม - เขียนจดหมายถึงแฮกเกอร์ pgsql ฉันเขียนไว้ว่ายังไม่ชัดเจนว่านี่เป็นข้อบกพร่องหรือคุณลักษณะ
ขณะที่เรากำลังคิดว่านี่คือจุดบกพร่องหรือคุณลักษณะ เรามาแก้ไขกันดีกว่า มารับคำขอของเราและเพิ่ม "+0" ทุกอย่างปกติดี. สองสัญลักษณ์และคุณไม่จำเป็นต้องคิดว่ามันเป็นอย่างไรหรือเป็นอย่างไร ง่ายมาก. เราเพียงแต่ห้ามไม่ให้ฐานข้อมูลใช้ดัชนีในคอลัมน์นี้ เราไม่มีดัชนีในคอลัมน์ "+0" เพียงเท่านี้ ฐานข้อมูลก็ไม่ได้ใช้ดัชนีนั้น ทุกอย่างเรียบร้อยดี
นี่คือกฎข้อที่ 6 อธิบาย ในเวอร์ชันปัจจุบัน คุณต้องทำ 6 ครั้งหากคุณมีตัวแปรที่ถูกผูกไว้ หากคุณไม่มีตัวแปรที่ถูกผูกไว้ นี่คือสิ่งที่เราทำ และท้ายที่สุดแล้ว คำขอนี้ก็ล้มเหลวอย่างแน่นอน มันไม่ใช่เรื่องยุ่งยาก
ดูเหมือนว่าเป็นไปได้มากแค่ไหน? จุดบกพร่องที่นี่ จุดบกพร่องที่นั่น จริงๆ แล้วแมลงมีอยู่ทุกที่
มาดูกันดีกว่า ตัวอย่างเช่น เรามีสองสคีมา โครงการ A พร้อมตาราง S และแผนภาพ B พร้อมตาราง S แบบสอบถาม – เลือกข้อมูลจากตาราง เราจะได้อะไรในกรณีนี้? เราจะมีข้อผิดพลาด เราจะมีทั้งหมดข้างต้น กฎคือ - มีข้อบกพร่องอยู่ทุกหนทุกแห่ง เราจะมีทุกสิ่งที่กล่าวมาข้างต้น
ตอนนี้คำถามคือ: “ทำไม?” ดูเหมือนว่าจะมีเอกสารประกอบว่าถ้าเรามีสคีมา ก็จะมีตัวแปร "search_path" ที่บอกเราว่าจะค้นหาตารางได้ที่ไหน ดูเหมือนว่าจะมีตัวแปร
อะไรคือปัญหา? ปัญหาคือว่าคำสั่งที่เซิร์ฟเวอร์เตรียมไว้ไม่สงสัยว่า search_path สามารถเปลี่ยนแปลงได้โดยใครบางคน ค่านี้ยังคงเป็นค่าคงที่สำหรับฐานข้อมูล และบางส่วนอาจไม่ได้ความหมายใหม่
แน่นอนว่าสิ่งนี้ขึ้นอยู่กับเวอร์ชันที่คุณกำลังทดสอบ ขึ้นอยู่กับว่าตารางของคุณแตกต่างกันมากน้อยเพียงใด และเวอร์ชัน 9.1 จะดำเนินการตามคำขอเก่าเท่านั้น เวอร์ชันใหม่อาจตรวจพบข้อบกพร่องและบอกคุณว่าคุณมีข้อบกพร่อง
จะรักษาได้อย่างไร? มีสูตรง่ายๆ อย่าทำนะ ไม่จำเป็นต้องเปลี่ยน search_path ในขณะที่แอปพลิเคชันกำลังทำงาน หากคุณเปลี่ยนแปลง ควรสร้างการเชื่อมต่อใหม่จะดีกว่า
คุณสามารถหารือได้ เช่น เปิด อภิปราย เพิ่ม บางทีเราอาจโน้มน้าวนักพัฒนาฐานข้อมูลได้ว่าเมื่อมีคนเปลี่ยนค่า ฐานข้อมูลควรบอกลูกค้าเกี่ยวกับสิ่งนี้: “ดูสิ ค่าของคุณได้รับการอัปเดตที่นี่ บางทีคุณอาจต้องรีเซ็ตข้อความสั่งและสร้างมันขึ้นมาใหม่” ขณะนี้ฐานข้อมูลทำงานเป็นความลับและไม่รายงานในทางใดทางหนึ่งว่าข้อความมีการเปลี่ยนแปลงที่ใดที่หนึ่งภายใน
และฉันจะเน้นย้ำอีกครั้ง - นี่คือสิ่งที่ไม่ปกติสำหรับ Java เราจะเห็นสิ่งเดียวกันใน PL/pgSQL แบบตัวต่อตัว แต่จะมีการสืบพันธุ์ที่นั่น
ลองเลือกข้อมูลเพิ่มเติมดู เราเลือกและเลือก เรามีตารางที่มีหนึ่งล้านแถว แต่ละบรรทัดเป็นกิโลไบต์ ข้อมูลประมาณหนึ่งกิกะไบต์ และเรามีหน่วยความจำที่ใช้งานได้ในเครื่อง Java ขนาด 128 เมกะไบต์
ตามที่แนะนำในหนังสือทุกเล่ม เราใช้การประมวลผลแบบสตรีม นั่นคือเราเปิด resultSet และอ่านข้อมูลจากที่นั่นทีละน้อย มันจะได้ผลไหม? มันจะหลุดจากความทรงจำมั้ย? อ่านสักนิดจะได้ไหม? ไว้วางใจในฐานข้อมูล ไว้วางใจใน Postgres กันเถอะ เราไม่เชื่อมัน เราจะหลุด OutOFMemory หรือไม่? ใครประสบปัญหา OutOfMemory หลังจากนั้นใครจัดการแก้ไขได้? มีคนแก้ไขได้สำเร็จ
ถ้าคุณมีล้านแถว คุณไม่สามารถเลือกเพียงอย่างเดียวได้ ต้องมีออฟเซ็ต/ลิมิต ใครคือตัวเลือกนี้? และใครบ้างที่สนับสนุนการเล่นแบบ autoCommit?
ตามปกติตัวเลือกที่ไม่คาดคิดที่สุดจะกลายเป็นสิ่งที่ถูกต้อง และถ้าคุณปิด autoCommit กะทันหันก็จะช่วยได้ ทำไมเป็นอย่างนั้น? วิทยาศาสตร์ไม่รู้เรื่องนี้
แต่ตามค่าเริ่มต้น ไคลเอนต์ทั้งหมดที่เชื่อมต่อกับฐานข้อมูล Postgres จะดึงข้อมูลทั้งหมด PgJDBC ก็ไม่มีข้อยกเว้นในเรื่องนี้ โดยจะเลือกแถวทั้งหมด
ธีม FetchSize มีการเปลี่ยนแปลง กล่าวคือ คุณสามารถพูดได้ในระดับของคำสั่งแยกต่างหากว่า โปรดเลือกข้อมูลเป็น 10, 50 แต่การดำเนินการนี้จะไม่ได้ผลจนกว่าคุณจะปิดการดำเนินการอัตโนมัติ ปิด autoCommit - มันเริ่มทำงาน
แต่การใส่โค้ดและตั้งค่า setFetchSize ทุกที่นั้นไม่สะดวก ดังนั้นเราจึงทำการตั้งค่าที่จะระบุค่าเริ่มต้นสำหรับการเชื่อมต่อทั้งหมด
นั่นคือสิ่งที่เรากล่าวว่า พารามิเตอร์ได้รับการกำหนดค่าแล้ว แล้วเราได้อะไร? หากเราเลือกจำนวนเล็กน้อย เช่น หากเราเลือกครั้งละ 10 แถว เราก็จะมีต้นทุนค่าโสหุ้ยจำนวนมาก ดังนั้นควรตั้งค่านี้ไว้ที่ประมาณร้อย
ตามหลักการแล้ว คุณยังคงต้องเรียนรู้วิธีจำกัดจำนวนไบต์ แต่สูตรก็คือ: ตั้งค่า defaultRowFetchSize เป็นมากกว่าหนึ่งร้อยและมีความสุข
มาดูการแทรกข้อมูลกันดีกว่า การแทรกทำได้ง่ายกว่ามีตัวเลือกต่างๆ ตัวอย่างเช่น INSERT, VALUES นี่เป็นตัวเลือกที่ดี คุณสามารถพูดว่า "INSERT SELECT" ในทางปฏิบัติก็เป็นสิ่งเดียวกัน ไม่มีความแตกต่างในด้านประสิทธิภาพ
หนังสือบอกว่าคุณต้องดำเนินการคำสั่ง Batch หนังสือบอกว่าคุณสามารถดำเนินการคำสั่งที่ซับซ้อนมากขึ้นโดยใช้วงเล็บหลายอัน และ Postgres ก็มีฟีเจอร์ที่ยอดเยี่ยม คุณสามารถทำ COPY ได้ กล่าวคือ ทำได้เร็วขึ้น
หากคุณวัดผล คุณจะสามารถค้นพบสิ่งที่น่าสนใจได้อีกครั้ง เราต้องการให้สิ่งนี้ทำงานอย่างไร? เราไม่ต้องการแยกวิเคราะห์และไม่รันคำสั่งที่ไม่จำเป็น
ในทางปฏิบัติ TCP ไม่อนุญาตให้เราทำสิ่งนี้ หากไคลเอนต์ยุ่งอยู่กับการส่งคำขอ ฐานข้อมูลจะไม่อ่านคำขอในการพยายามส่งคำตอบถึงเรา ผลลัพธ์สุดท้ายคือไคลเอนต์รอให้ฐานข้อมูลอ่านคำขอ และฐานข้อมูลก็รอให้ไคลเอนต์อ่านการตอบกลับ
ดังนั้นไคลเอ็นต์จึงถูกบังคับให้ส่งแพ็กเก็ตการซิงโครไนซ์เป็นระยะ การโต้ตอบทางเครือข่ายเพิ่มเติม เสียเวลามากขึ้น
และยิ่งเราเพิ่มเข้าไปมากเท่าไหร่ก็ยิ่งแย่ลงเท่านั้น คนขับค่อนข้างมองโลกในแง่ร้ายและเพิ่มบ่อยครั้ง ประมาณทุกๆ 200 บรรทัด ขึ้นอยู่กับขนาดของเส้น เป็นต้น
มันเกิดขึ้นที่คุณแก้ไขเพียงบรรทัดเดียวและทุกอย่างจะเร็วขึ้น 10 เท่า มันเกิดขึ้น. ทำไม ตามปกติแล้ว ค่าคงที่เช่นนี้ได้ถูกใช้ไปที่ไหนสักแห่งแล้ว และค่า "128" หมายถึงไม่ใช้การแบทช์
เป็นเรื่องดีที่ไม่รวมอยู่ในเวอร์ชันอย่างเป็นทางการ ค้นพบก่อนที่จะเริ่มการเปิดตัว ความหมายทั้งหมดที่ฉันให้นั้นขึ้นอยู่กับเวอร์ชันสมัยใหม่
มาลองใส่กันดู เราวัด InsertBatch อย่างง่าย เราวัด InsertBatch หลายครั้ง กล่าวคือ สิ่งเดียวกัน แต่มีหลายค่า ย้ายหากิน ไม่ใช่ทุกคนที่จะทำสิ่งนี้ได้ แต่เป็นการเคลื่อนไหวที่ง่ายดาย ง่ายกว่า COPY มาก
คุณสามารถทำสำเนา
และคุณสามารถทำได้บนโครงสร้าง ประกาศประเภทเริ่มต้นของผู้ใช้ อาร์เรย์ผ่าน และ INSERT ลงในตารางโดยตรง
หากคุณเปิดลิงก์: pgjdbc/ubenchmsrk/InsertBatch.java แสดงว่าโค้ดนี้อยู่บน GitHub คุณสามารถดูคำขอที่สร้างขึ้นโดยเฉพาะได้ที่นั่น มันไม่สำคัญ
เราเปิดตัว. และสิ่งแรกที่เราตระหนักก็คือ การไม่ใช้แบทช์นั้นเป็นไปไม่ได้เลย ตัวเลือกการแบทช์ทั้งหมดเป็นศูนย์ กล่าวคือ เวลาดำเนินการแทบจะเป็นศูนย์เมื่อเทียบกับการดำเนินการครั้งเดียว
เราใส่ข้อมูล มันเป็นตารางที่เรียบง่ายมาก สามคอลัมน์ แล้วเราเห็นอะไรที่นี่? เราเห็นว่าตัวเลือกทั้งสามนี้เปรียบเทียบได้คร่าวๆ และแน่นอนว่า COPY นั้นดีกว่า
นี่คือเมื่อเราใส่ชิ้นส่วน เมื่อเราบอกว่าค่า VALUES หนึ่งค่า ค่า VALUES สองค่า ค่า VALUES สามค่า หรือเราระบุ 10 ค่าโดยคั่นด้วยเครื่องหมายจุลภาค ตอนนี้เป็นเพียงแนวนอน 1, 2, 4, 128 จะเห็นได้ว่า Batch Insert ซึ่งวาดด้วยสีน้ำเงินทำให้รู้สึกดีขึ้นมาก นั่นคือ เมื่อคุณแทรกทีละรายการ หรือแม้กระทั่งเมื่อคุณแทรกสี่รายการในแต่ละครั้ง มันจะดีเป็นสองเท่า เพียงเพราะเราอัดแน่นเข้าไปใน VALUES เพิ่มขึ้นอีกเล็กน้อย การดำเนินการ EXECUTE น้อยลง
การใช้ COPY กับปริมาณน้อยนั้นไม่น่าเป็นไปได้อย่างยิ่ง ฉันไม่ได้วาดสองอันแรกด้วยซ้ำ พวกเขาไปสวรรค์ นั่นคือตัวเลขสีเขียวเหล่านี้สำหรับ COPY
ควรใช้ COPY เมื่อคุณมีข้อมูลอย่างน้อยหนึ่งร้อยแถว ค่าใช้จ่ายในการเปิดการเชื่อมต่อนี้มีขนาดใหญ่ และบอกตามตรงว่าฉันไม่ได้เจาะไปในทิศทางนี้ ฉันปรับแบตช์ให้เหมาะสม แต่ไม่ใช่ COPY
เราจะทำอย่างไรต่อไป? เราลองสวมแล้ว เราเข้าใจดีว่าเราจำเป็นต้องใช้โครงสร้างอย่างใดอย่างหนึ่งหรือการกระทำอันชาญฉลาดที่รวมความหมายหลายประการเข้าด้วยกัน
คุณควรนำอะไรไปจากรายงานของวันนี้?
- ReadyStatement คือทุกสิ่งทุกอย่างของเรา สิ่งนี้ให้ผลผลิตมากมาย มันทำให้เกิดความล้มเหลวครั้งใหญ่ในครีม
- และคุณต้องทำ Explain ANALYZE 6 ครั้ง
- และเราจำเป็นต้องเจือจาง OFFSET 0 และเทคนิคเช่น +0 เพื่อแก้ไขเปอร์เซ็นต์ที่เหลือของข้อความค้นหาที่เป็นปัญหาของเรา
ที่มา: will.com