MySQL InnoDB-ის მორგება 1-8 GB სერვერებისთვის

MySQL სერვერზე 1-დან 8 GB მეხსიერებით შედეგის უმეტეს ნაწილს სამი პარამეტრი წყვეტს: innodb_buffer_pool_size, max_connections და innodb_redo_log_capacity. MySQL 8.4-ზე კიდევ ორია, რომლებზეც პატარა სერვერები ბორძიკობენ, დიდები კი ვერც ამჩნევენ: temptable_max_ram, რომლის ახალი ნაგულისხმევი არასოდეს არის 1 GB-ზე ნაკლები, და binary log, რომელიც ნაგულისხმევად ჩართულია და 30 დღის ისტორიას ინახავს. 1-2 GB სერვერზე buffer pool დააყენე მეხსიერების დაახლოებით ნახევარზე, მის ზემოთ 60-70%-ზე, კავშირები ათეულებში შეინარჩუნე და არა ასეულებში, redo log ასეულ მეგაბაიტებში აირჩიე, მეხსიერებაში არსებულ დროებით ცხრილებს ზღვარი დაუწესე და binary log-ის შენახვის ვადა შეამოკლე. დანარჩენი ყველაფერი ან ნაგულისხმევად კარგადაა, ან ერთ დაკარგულ index-ზე ნაკლები ღირს.

პატარა მანქანაზე ეს უფრო მნიშვნელოვანია ჩავარდნის ხასიათის გამო. დიდი, ოდნავ არასწორად კონფიგურირებული სერვერი უბრალოდ იმაზე ნელია, ვიდრე შეიძლებოდა. პატარა, არასწორად კონფიგურირებული კი მეხსიერებას ამოწურავს, ხოლო მეხსიერების ამოწურვა შენელება არ არის: kernel პროცესს აჩერებს. პატარა MySQL სერვერის მორგება ძირითადად ფიქსირებული მეხსიერების ბიუჯეტის მიზანმიმართულად ხარჯვაა.

რას ვარაუდობს ნაგულისხმევი მნიშვნელობები#

MySQL-ის ნაგულისხმევი მნიშვნელობები შერჩეულია იმისთვის, რომ ყველგან ჩაირთოს, და არა იმისთვის, რომ ყველგან კარგად იმუშაოს. რამდენიმე მათგანი 8.4-ში შეიცვალა, რაც ღირს იცოდე, თუ 8.0-ისთვის დაწერილ რჩევებს მიჰყვები.

buffer pool-ის ნაგულისხმევი 128 MB ყველაზე ნათელი მაგალითია მნიშვნელობისა, რომელიც არავინ უნდა დატოვოს. 4 GB სერვერზე ის მეხსიერების 90%-ს ოპერაციული სისტემის ქეშს უტოვებს, რომელსაც InnoDB ისედაც გვერდს უვლის, რადგან 8.4 Linux-ზე ნაგულისხმევად innodb_flush_method=O_DIRECT-ს იყენებს. მონაცემები, რომლებიც buffer pool-ში არ ეტევა, დისკიდან იკითხება, ხოლო მეხსიერება, რომელშიც იხდი, უსაქმოდ დგას.

query cache, ძველი მორგების რჩევების დიდი ნაწილის საგანი, MySQL 8.0-ში ამოიღეს. თუ გზამკვლევი query_cache_size-ის დაყენებას გეუბნება, ის 5.7-ისთვის დაიწერა და სერვერი ამ ხაზით კონფიგურაციაში ჩართვაზე უარს იტყვის.

მეხსიერების ბიუჯეტი#

წარმოიდგინე MySQL-ის მეხსიერება სამ ნაწილად: რაც ერთხელ გამოიყოფა (buffer pool, log buffer, performance schema), რისი აღებაც თითოეულ კავშირს შეუძლია query-ის შესრულებისას, და რაც ოპერაციულ სისტემას სჭირდება. უარესი შემთხვევა დაახლოებით ასეთია:

buffer pool+ log buffer (64 MB in 8.4)+ performance_schema (often 100-250 MB)+ max_connections x (per-thread buffers + thread stack)+ in-memory temporary tables+ a margin for the OS and everything else

თითო კავშირის ბუფერები ნაგულისხმევად პატარაა - sort_buffer_size 256 KB, join_buffer_size 256 KB, read_buffer_size 128 KB, read_rnd_buffer_size 256 KB, thread_stack 1 MB - და მხოლოდ მაშინ გამოიყოფა, როცა query-ს სჭირდება. ეს უმოქმედო კავშირს იაფს ხდის, დაკავებულს კი რამდენიმე მეგაბაიტად. საფრთხე მათი გლობალურად აწევაა: sort_buffer_size = 64M უწყინარად გამოიყურება, სანამ ორმოცდაათი კავშირი ერთდროულად არ დაიწყებს დალაგებას. აწიე ისინი session-ზე იმ query-სთვის, რომელსაც სჭირდება:

SET SESSION sort_buffer_size = 16 * 1024 * 1024;SELECT ... ORDER BY ...;   -- the one report that sorts a lot

იმის სანახავად, სად მიდის მეხსიერება სინამდვილეში გაშვებულ სერვერზე, sys სქემა performance schema-ს მეხსიერების ინსტრუმენტებს აჯამებს:

SELECT event_name, current_allocFROM sys.memory_global_by_current_bytesLIMIT 10;SELECT * FROM sys.memory_global_total;

innodb_buffer_pool_size#

buffer pool ცხრილისა და index-ის გვერდებს ქეშავს. hit მეხსიერებიდან წაკითხვაა; miss - დისკიდან. სპეციალურად ბაზისთვის განკუთვნილ სერვერზე ეს მეხსიერების ყველაზე დიდი მომხმარებელია, და ჩვეული რჩევა „RAM-ის 70-80%“ დიდ მანქანას ვარაუდობს, სადაც დარჩენილი 20% რამდენიმე გიგაბაიტია. პატარაზე ზემოთ ჩამოთვლილი ფიქსირებული ხარჯები უფრო დიდ წილს ჭამს, ამიტომ პროცენტი იკლებს.

ეს საწყისი წერტილებია და არა ჭეშმარიტებები. buffer pool-ზე პატარა ბაზას უფრო დიდი არ სჭირდება - შეამოწმე, რამდენი მონაცემი გაქვს სინამდვილეში:

SELECT table_schema,       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mbFROM information_schema.tablesGROUP BY table_schemaORDER BY size_mb DESC;

თუ მთელი ბაზა 300 MB-ია, 1 GB buffer pool მას სრულად იტევს და დანარჩენი ფუჭად იკარგება. თუ 4 GB სერვერზე 20 GB-ია, მნიშვნელობა აქვს, ეტევა თუ არა სამუშაო სიმრავლე - ხაზები, რომლებსაც ჩვეულებრივ საათში ეხებიან. ამას miss-ების მაჩვენებელი გეტყვის:

SELECT  (SELECT variable_value FROM performance_schema.global_status   WHERE variable_name = 'Innodb_buffer_pool_reads') AS disk_reads,  (SELECT variable_value FROM performance_schema.global_status   WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS logical_reads;

disk_reads გაყოფილი logical_reads-ზე გახურებულ სერვერზე 1%-ზე საგრძნობლად ნაკლები უნდა იყოს. აიღე ორი ნიმუში ერთი საათის შუალედით და შეადარე სხვაობები, და არა ჯამები გაშვების მომენტიდან. დატვირთულ სისტემაზე მუდმივად მაღალი თანაფარდობა პატიოსანი სიგნალია, რომ სამუშაო სიმრავლე არ ეტევა და ან index აკლია, ან გეგმა ძალიან პატარაა.

buffer pool-ის ზომის შეცვლა 8.x-ში online შეიძლება. ზომა მრგვალდება innodb_buffer_pool_chunk_size-ის (128 MB) ჯერადამდე, გამრავლებული innodb_buffer_pool_instances-ზე, რაც 8.4-ში 1-ია 1 GB-იანი ან უფრო პატარა pool-ებისთვის, ხოლო მის ზემოთ ზომისა და CPU-ების რაოდენობიდან გამოითვლება:

SET PERSIST innodb_buffer_pool_size = 2560 * 1024 * 1024;SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

ზომის შეცვლას ცოტა დრო სჭირდება და მოკლე ხნით ბლოკავს წვდომას გადასატან გვერდებზე; ეს წყნარ დროს გააკეთე.

innodb_dedicated_server=ON MySQL-ს ეუბნება, რომ buffer pool-ის (მეხსიერების 50% 1-დან 4 GB-მდე, 75% მის ზემოთ) და redo log-ის ზომა თავად განსაზღვროს. ის ზომას იმ მეხსიერებიდან ადგენს, რომელსაც აღმოაჩენს, და დოკუმენტაცია არ გპირდება, რომ აღმოჩენა კონტეინერის ლიმიტს გაითვალისწინებს, ამიტომ კონტეინერში მნიშვნელობები ცხადად დააყენე.

innodb_redo_log_capacity#

InnoDB-ის მონაცემების ყოველი ცვლილება ჯერ redo log-ში იწერება და მოგვიანებით, checkpoint-ებზე, მონაცემთა ფაილებში გადადის. ზედმეტად პატარა redo log ხშირ checkpoint-ებს აიძულებს, რაც ნიშნავს ჩაწერის ტალღებს და შეფერხებებს, როცა ჩაწერით დატვირთული მომენტი მას ავსებს. ზედმეტად დიდი დისკს ფლანგავს და crash-ის შემდეგ აღდგენას ახანგრძლივებს.

MySQL 8.0.30-დან redo log-ის ზომას ერთი ცვლადი, innodb_redo_log_capacity განსაზღვრავს, და ის დინამიკურია. ძველი წყვილი innodb_log_file_size და innodb_log_files_in_group მოძველებულია; თუ ისინი დაყენებულია, ხოლო innodb_redo_log_capacity - არა, სერვერი ტევადობას მათგან გამოიყვანს, მაგრამ ახალმა კონფიგურაციამ ერთი პარამეტრი უნდა გამოიყენოს. redo ფაილები data დირექტორიის შიგნით #innodb_redo-ში ცხოვრობს.

SET PERSIST innodb_redo_log_capacity = 512 * 1024 * 1024;

ნაგულისხმევი 100 MB მსუბუქი ჩაწერის დატვირთვის გარდა ყველაფრისთვის ძალიან პატარაა. ზომის განსაზღვრის სასარგებლო გზაა გაზომო, რამდენ redo-ს წერს შენი პიკური საათი:

SELECT variable_value INTO @a FROM performance_schema.global_statusWHERE variable_name = 'Innodb_redo_log_current_lsn';DO SLEEP(60);SELECT ROUND((variable_value - @a) / 1024 / 1024, 1) AS redo_mb_per_minuteFROM performance_schema.global_statusWHERE variable_name = 'Innodb_redo_log_current_lsn';

ეს დატვირთულ დროს გაუშვი. ტევადობა, რომელიც პიკური ჩაწერის 30-დან 60 წუთამდე იტევს, გულუხვია. პატარა დისკზე ზღვარი დაუწესე: 2 GB redo 10 GB volume-ზე შენი სივრცის მეხუთედია. ზემოთ მოცემული ცხრილი ამ ფარგლებში რჩება.

კავშირები, thread-ები და timeout-ები#

ყოველი კავშირი thread-ია საკუთარი stack-ითა და ბუფერებით. ერთ- ან ორ-vCPU-იან სერვერს ერთდროულად რამდენიმე query-ზე მეტის გაშვება არ შეუძლია; ამის მიღმა კავშირები ელოდებიან და ამ დროს მეხსიერება უჭირავთ. max_connections = 151 1 GB სერვერზე დაპირებაა, რომლის შესრულებაც მეხსიერებას არ შეუძლია, თუ ყველა ერთდროულად დაკავდება.

• ჯერ აპლიკაციის pool-ების ზომა განსაზღვრე. ათი კავშირი თითო აპლიკაციის პროცესზე ვებ დატვირთვების უმეტესობას ფარავს.
• შეკრიბე ყოველი პროცესი, worker და cron ამოცანა, რომელიც უკავშირდება. ეს ჯამი, პლუს მარაგი, არის max_connections.
• MySQL ლიმიტის მიღმა ერთ დამატებით კავშირს ინახავს CONNECTION_ADMIN-ის მქონე ანგარიშისთვის, ასე რომ root-ს „Too many connections“ ინციდენტის გამოსაძიებლად შესვლა მაინც შეუძლია.
• thread_cache_size (ნაგულისხმევად ავტომატურად ზომავს) thread-ებს ხელახლა გამოსაყენებლად ინახავს; დატოვე.

SET PERSIST max_connections = 60;SHOW GLOBAL STATUS LIKE 'Max_used_connections';SHOW GLOBAL STATUS LIKE 'Threads_connected';

Max_used_connections გაშვების მომენტიდან მაქსიმალური ნიშნულია, რაც გეუბნება, ახლოსაა თუ არა ლიმიტი მიღწევასთან. wait_timeout (28800 წამი) უმოქმედო კავშირებს რვა საათის შემდეგ ხურავს; მისი 600-მდე ან მსგავსამდე დაწევა აბრუნებს კავშირებს, რომლებიც აპლიკაციებმა გახსნეს და მიატოვეს, მაგრამ დარწმუნდი, რომ შენი pool კავშირებს ამაზე სწრაფად განაახლებს. პატარა ლიმიტებისა და pool-ების სრული არგუმენტი MySQL-ის კავშირების ლიმიტებსა და pooling-შია.

დროებითი ცხრილები და 8.4-ის temptable ნაგულისხმევი#

query-ები GROUP BY-ით, DISTINCT-ით, UNION-ით, derived ცხრილებით ან გარკვეული დალაგებებით შიდა დროებით ცხრილებს აგებენ. MySQL 8 მათ მეხსიერებაში TempTable ძრავით ინახავს ჯამში temptable_max_ram-მდე, ხოლო მის მიღმა დისკზე არსებულ InnoDB დროებით ცხრილებში იღვრება.

8.4-ში ნაგულისხმევი temptable_max_ram ჯამური მეხსიერების 3% გახდა, მაგრამ არასოდეს 1 GB-ზე ნაკლები და არასოდეს 4 GB-ზე მეტი. 1 GB ან 2 GB სერვერზე ეს ქვედა ზღვარი ნიშნავს, რომ დროებით ცხრილებს უფლება აქვთ, მთელი გეგმის ან მისი ნახევრის ტოლი მეხსიერება გამოიყენონ. მაშინ ერთ ცუდ ანგარიშის query-ს შეუძლია სერვერი ლიმიტს გადააცილოს. დაუწესე ზღვარი იმაზე, რისი შეწოვაც შენს ბიუჯეტს შეუძლია:

SET PERSIST temptable_max_ram = 64 * 1024 * 1024;   -- 1 GB serverSET PERSIST tmp_table_size = 32 * 1024 * 1024;

დისკზე დაღვრა უფრო ნელია, მაგრამ გადასატანი; მეხსიერების ამოწურვა არც ერთია და არც მეორე. query-ები, რომლებიც დიდ დროებით ცხრილებს აგებენ, EXPLAIN-ში Using temporary-თ არის მონიშნული, და ისინი, როგორც წესი, იგივეა, რაც slow query log-ში ჩნდება. SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable%' აჩვენებს, რამდენი უჭირავს ძრავას ახლა.

სანდოობა, flush და binary log#

innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_flush_method = O_DIRECTinnodb_io_capacity = 10000

innodb_flush_log_at_trx_commit = 1 redo log-ს ყოველ commit-ზე დისკზე flush-ავს, ასე რომ commit-ირებული ტრანზაქცია crash-ს გადაურჩება. მისი 2-ზე დაყენება commit-ზე წერს, მაგრამ flush-ს წამში ერთხელ აკეთებს, რაც ჩაწერით დატვირთული სამუშაოსთვის რეალური აჩქარებაა და ნიშნავს, რომ მანქანის crash-მა (და არა მხოლოდ MySQL-ისამ) შეიძლება commit-ების დაახლოებით ერთი წამი დაკარგოს. ბაზის დაზიანება მას არ შეუძლია. ეს მას გონივრულს ხდის ლოგირებისა და ანალიტიკისთვის, და არასწორს შეკვეთებისა და გადახდებისთვის. sync_binlog იგივე გაცვლაა binary log-ისთვის.

innodb_io_capacity InnoDB-ს ეუბნება, რამდენად სწრაფად შეუძლია ფონურად flush. 8.0-ის ნაგულისხმევი 200 მბრუნავი დისკებისთვის დაიწერა და NVMe-სთვის ძალიან დაბალია; თუ 8.0-ს იყენებ, დაახლოებით 2000-მდე ასწიე. 8.4-ის ნაგულისხმევი 10000 სწრაფ flash-ს ვარაუდობს, რაც NVMe სწორედ არის.

binary log პატარა დისკზე ცალკე წინადადებას იმსახურებს. ის 8.0-დან ნაგულისხმევად ჩართულია, ყოველ ცვლილებას რეპლიკაციისა და დროის კონკრეტულ მომენტზე აღდგენისთვის ინიშნავს და 30 დღეს ინახავს. ჩაწერით დატვირთულ ბაზაზე 10 ან 20 GB volume-ით binary log-ები შეიძლება მონაცემებზე დიდი გახდეს. თუ რეპლიკაციას არ აკეთებ და დროის მომენტზე აღდგენასაც არ იყენებ, შენახვის ვადა შეამოკლე:

SET PERSIST binlog_expire_logs_seconds = 259200;   -- 3 daysSHOW BINARY LOGS;PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

binary logging-ის მთლიანად გამორთვას (skip-log-bin ან disable-log-bin) კონფიგურაციის ფაილში გაშვების ოფცია სჭირდება და არა SET PERSIST. expire_logs_days, ძველი პარამეტრი დღეებში, 8.4-ში ამოიღეს.

პარამეტრების გამოყენება: SET PERSIST და საიდან მოდის მნიშვნელობები#

MySQL 8-ში სერვერის მოსარგებად my.cnf-ის რედაქტირება იშვიათად გჭირდება. SET PERSIST დინამიკურ ცვლადს ახლავე ცვლის და მას data დირექტორიაში mysqld-auto.cnf-ში ინიშნავს, რომელიც გაშვებისას ჩვეულებრივი option ფაილების შემდეგ იკითხება, ასე რომ ცვლილება restart-ს გადაურჩება. მას SYSTEM_VARIABLES_ADMIN სჭირდება, რაც root ანგარიშს აქვს.

-- Where does each value come from?SELECT v.variable_name, v.variable_source, v.set_time, g.variable_valueFROM performance_schema.variables_info vJOIN performance_schema.global_variables g USING (variable_name)WHERE v.variable_name IN ('innodb_buffer_pool_size', 'max_connections',                          'innodb_redo_log_capacity', 'temptable_max_ram');-- Everything persisted so farSELECT * FROM performance_schema.persisted_variables;-- Undo a persisted setting (takes effect at next restart)RESET PERSIST temptable_max_ram;

variable_source აჩვენებს COMPILED-ს (ჩაშენებული ნაგულისხმევი), GLOBAL-ს ან SERVER-ს (option ფაილი), PERSISTED-ს ან DYNAMIC-ს (გაშვებისას დაყენებული). ცვლადები, რომლებიც გაშვებისას ვერ იცვლება, მაგალითად performance_schema ან innodb_buffer_pool_chunk_size, იღებს SET PERSIST_ONLY-ს, რომელიც მნიშვნელობას შემდეგი გაშვებისთვის ინიშნავს გამოყენების გარეშე; ამას დამატებითი PERSIST_RO_VARIABLES_ADMIN უფლება სჭირდება.

RE:NODE-ზე შენი MySQL სერვერის root პაროლს იღებ, ასე რომ SET PERSIST ზემოთ ჩამოთვლილი ყველაფრისთვის ხელმისაწვდომია. ერთდროულად ერთი პარამეტრი შეცვალე და ნებისმიერ restart-მდე backup გააკეთე - აღდგენა ღილაკია, ხოლო backup სლოტები ყველა MySQL გეგმას მოჰყვება.

ჯერ გაზომე, მერე მოარგე#

კონფიგურაცია პროცენტებს აბრუნებს. დაკარგული index სიდიდის რიგებს ჯდება. ამ პოსტში რაიმეს შეცვლამდე ერთი დღით ჩართე slow query log და ნახე, რას დაიჭერს, ხოლო ყველაზე ცუდი query-ის გეგმა წაიკითხე EXPLAIN-ითა და EXPLAIN ANALYZE-ით. მეხსიერების გრაფიკს ადევნე თვალი და არა საშუალოებს - სერვერის დატვირთვის გრაფიკის კითხვა განმარტავს, რატომ არის პიკი ის რიცხვი, რომელიც გკლავს. და თუ გაზომვები ამბობს, რომ სამუშაო სიმრავლე უბრალოდ არ ეტევა, პატიოსანი პასუხი უფრო დიდი გეგმაა: როდის გაიუმჯობესო შენი გეგმა. PostgreSQL-ის მომხმარებლები იგივე მსჯელობას იქ გამოყენებულს იპოვიან PostgreSQL-ის მორგებაში პატარა სერვერებისთვის.

FAQ#

რამდენი უნდა იყოს innodb_buffer_pool_size 2 GB სერვერზე?

დაახლოებით 1 GB, რაც ადგილს უტოვებს კავშირებს, დროებით ცხრილებს, performance schema-სა და ოპერაციულ სისტემას. თუ შენი მონაცემები ამაზე პატარაა, buffer pool მონაცემებს პლუს ცოტა ზრდის მარაგს შეუსაბამე და მეხსიერება თავისუფალი დატოვე.

ჯერ კიდევ მჭირდება innodb_log_file_size?

არა. MySQL 8.0.30-დან redo log-ის ზომას innodb_redo_log_capacity განსაზღვრავს, რომლის შეცვლაც გაშვებისას შეიძლება. innodb_log_file_size და innodb_log_files_in_group მოძველებულია და მხოლოდ მაშინ გამოიყენება, თუ ახალი ცვლადი დაყენებული არ არის.

უნდა გამოვრთო performance_schema მეხსიერების დასაზოგად?

1 GB სერვერზე ამით შესამჩნევი ნაჭრის დაზოგვა შეიძლება, და ეს გაშვების პარამეტრია, ამიტომ restart სჭირდება. ფასი ისაა, რომ კარგავ sys სქემის view-ებს, statement digest-ებსა და მეხსიერების აღრიცხვას, რომლებიც ამ პოსტის დანარჩენ ნაწილს გაზომვადს ხდის. ჩართული დატოვე, თუ მეხსიერება ერთადერთი არ არის, რაც შენსა და უფრო პატარა გეგმას შორის დგას.

რატომ იყენებს MySQL buffer pool-ზე მეტ მეხსიერებას?

იმიტომ, რომ buffer pool მხოლოდ ყველაზე დიდი ნაჭერია. კავშირები, დროებითი ცხრილები, log buffer, performance schema, ცხრილების ქეში და სერვერის საკუთარი კოდი ყველა ამატებს. sys.memory_global_total ინსტრუმენტირებულ ჯამს აჩვენებს; კონტეინერის მეხსიერების გრაფიკი - რეალურს.

კარგი იდეაა innodb_dedicated_server პატარა გეგმაზე?

კონტეინერის შიგნით - არა. ის buffer pool-ის ზომას იმ მეხსიერებიდან ადგენს, რომელსაც აღმოაჩენს, და ეს შეიძლება არ იყოს ის ლიმიტი, რომელსაც კონტეინერი აწესებს. buffer pool-ისა და redo log-ის ტევადობა ცხადად დააყენე.