دليل postgresql العملي أساسيات إدارة الذاكرة في قواعد بيانات Postgres

سنتعرف في هذا الفصل على الملفات التي تُخزّن فيها قواعد البيانات في Postgres، وكيف نستعرض المساحات التخزينية التي تحجزها الجداول والفهارس.

مسارات تخزين البيانات

لعله من البديهي أنه لا بد من وجود مكان لتخزين البيانات لأي قواعد بيانات، وهذا المكان هو ملفات موجودة في مكان ما من الخادوم الذي يشغّل عملية postgres.

لمعرفة مكان تخزين البيانات من داخل صدفة psql نستخدم التعليمة التالية:

hsoubguide=# SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
+
       name        |                setting                 
-------------------+----------------------------------------
 config_file       | /var/lib/pgsql/12/data/postgresql.conf
 data_directory    | /var/lib/pgsql/12/data
 external_pid_file | 
 hba_file          | /var/lib/pgsql/12/data/pg_hba.conf
 ident_file        | /var/lib/pgsql/12/data/pg_ident.conf
(5 rows)

تبيّن مخرجات التعليمة السابقة مسارات ملفات الإعدادات، كما تشير إلى المسار الخاص بالبيانات data_directory فإذا انتقلنا إلى هذا المسار واستعرضنا محتوياته سنجد ما يلي:

-bash-4.2$ cd /var/lib/pgsql/12/data
-bash-4.2$ ls -l

total 64
drwx------. 6 postgres postgres    54 Jun 30 00:16 base
-rw-------  1 postgres postgres    30 Aug 14 14:01 current_logfiles
drwx------. 2 postgres postgres  4096 Aug 14 15:23 global
drwx------. 2 postgres postgres   188 Jun 15 00:30 log
drwx------. 2 postgres postgres     6 Jun  5 16:10 pg_commit_ts
drwx------. 2 postgres postgres     6 Jun  5 16:10 pg_dynshmem
-rw-------. 1 postgres postgres  4269 Jun  5 16:10 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Jun  5 16:10 pg_ident.conf
drwx------. 4 postgres postgres    68 Aug 14 14:06 pg_logical
drwx------. 4 postgres postgres    36 Jun  5 16:10 pg_multixact
drwx------. 2 postgres postgres    18 Aug 14 14:01 pg_notify
drwx------. 2 postgres postgres     6 Jun  5 16:10 pg_replslot
drwx------. 2 postgres postgres     6 Jun  5 16:10 pg_serial
drwx------. 2 postgres postgres     6 Jun  5 16:10 pg_snapshots
drwx------. 2 postgres postgres     6 Aug 14 14:01 pg_stat
drwx------. 2 postgres postgres    63 Aug 14 15:28 pg_stat_tmp
drwx------. 2 postgres postgres    18 Jun  5 16:10 pg_subtrans
drwx------. 2 postgres postgres     6 Jun  5 16:10 pg_tblspc
drwx------. 2 postgres postgres     6 Jun  5 16:10 pg_twophase
-rw-------. 1 postgres postgres     3 Jun  5 16:10 PG_VERSION
drwx------. 3 postgres postgres    92 Jun 29 23:21 pg_wal
drwx------. 2 postgres postgres    18 Jun  5 16:10 pg_xact
-rw-------. 1 postgres postgres    88 Jun  5 16:10 postgresql.auto.conf
-rw-------. 1 postgres postgres 26632 Jun  5 16:10 postgresql.conf
-rw-------. 1 postgres postgres    58 Aug 14 14:01 postmaster.opts
-rw-------  1 postgres postgres   102 Aug 14 14:01 postmaster.pid

يمكنك اكتشاف الكثير عن آلية عمل postgres من الداخل في هذا المسار، ولكننا مهتمون بمعرفة مساحتها في الذاكرة، ولذا سنقوم بكتابة الأمر التالي:

-bash-4.2$ du -sh /var/lib/pgsql/12/data

66M /var/lib/pgsql/12/data

يُخبرنا ذلك بأن حجم جميع بيانات postgres هي 66 ميغا بايت.

ولكننا قد نحتاج إلى معرفة حجم قاعدة بيانات أو جدول أو فهرس على حدة، ولذلك توفر Postgres طريقة مناسبة لمعرفة هذه المعلومات عن طريق الاستعلام من داخل صدفة psql أو عن طريق تعليمات الصدفة psql أيضًا.

معرفة حجم قاعدة البيانات

يمكن معرفة حجم قاعدة البيانات بشكل سهل عن طريق التوجيه ‎\l+‎ الذي يعرض قائمة لقواعد البيانات مع أحجامها في العمود Size ;

hsoubguide=# \l+

                                                                    List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description             

------------+----------+----------+-------------+-------------+-----------------------+---------+------------+----------------------------------------
----
 hsoubguide | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |                       | 9353 kB | pg_default | 
 postgres   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |                       | 8201 kB | pg_default | default administrative connection datab
ase
 template0  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +| 8049 kB | pg_default | unmodifiable empty database
            |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +| 8049 kB | pg_default | default template for new databases
            |          |          |             |             | postgres=CTc/postgres |         |            | 
(4 rows)

كما يمكننا تنفيذ الاستعلام التالي للحصول على حجم قاعدة بيانات محددة، وواحدة هذا الحجم هي البايت:

hsoubguide=# SELECT pg_database_size('hsoubguide');

 pg_database_size 
------------------
          9577327
(1 row)

كما يمكننا استخدام التابع pg_size_pretty لإظهار الحجم بواحدة مقروءة للمستخدم مثل kB أو MB كما يلي:

hsoubguide=# SELECT pg_size_pretty(pg_database_size('hsoubguide'));

 pg_size_pretty 
----------------
 9353 kB
(1 row)

معرفة حجم الجدول

يمكن استخدام التعليمة ‎\dt+‎ من صَدَفة psql لتظهر لك كل الجداول مع أحجامها:

hsoubguide=# \dt+

                              List of relations
 Schema |         Name         | Type  |  Owner   |    Size    | Description 
--------+----------------------+-------+----------+------------+-------------
 public | basket_a             | table | postgres | 8192 bytes | 
 public | basket_b             | table | postgres | 8192 bytes | 
 public | departments          | table | postgres | 8192 bytes | 
 public | employee_departments | table | postgres | 8192 bytes | 
 public | employees            | table | postgres | 8192 bytes | 
 public | marks                | table | postgres | 8192 bytes | 
 public | names                | table | postgres | 8192 bytes | 
 public | phones               | table | postgres | 8192 bytes | 
 public | products             | table | postgres | 16 kB      | 
 public | purchase_items       | table | postgres | 328 kB     | 
 public | purchases            | table | postgres | 120 kB     | 
 public | student              | table | postgres | 16 kB      | 
 public | table1               | table | postgres | 8192 bytes | 
 public | table2               | table | postgres | 8192 bytes | 
 public | test_table           | table | postgres | 8192 bytes | 
 public | users                | table | postgres | 16 kB      | 
 public | users2               | table | postgres | 8192 bytes | 
(17 rows)

أو يمكنك استخدام الاستعلام التالي للحصول على حجم جدول محدد:

hsoubguide=# SELECT pg_size_pretty(pg_relation_size('users'));

 pg_size_pretty 
----------------
 8192 bytes
(1 row)

معرفة حجم الفهرس (index)

يمكن تطبيق التعليمة السابقة لمعرفة حجم الفهرس كما يلي:

hsoubguide=# SELECT pg_size_pretty(pg_relation_size('users_pkey'));

 pg_size_pretty 
----------------
 16 kB
(1 row)

قياس حجم الجدول مع الفهارس

تُخزّن الفهارس بمعزل عن الجداول، لكن يمكنك معرفة الحجم الكلي للجدول بالإضافة للفهارس بالتعليمة التالية:

hsoubguide=# SELECT pg_size_pretty(pg_total_relation_size('users'));

 pg_size_pretty 
----------------
 32 kB
(1 row)

ماذا تعني هذه الأرقام؟

عندما تُخبرنا Postgres بأن حجم الجدول 32KB فإن هذا الرقم ليس هو بالفعل حجم البيانات المخزنة فيه، ولكنه الحجم الذي يحجزه الجدول في الذاكرة.

ولفهم ذلك، سنقوم بإنشاء جدول بسيط ومراقبة تغير حجمه مع زيادة البيانات فيه أو نقصها:

hsoubguide=# CREATE TABLE size_calc(msg VARCHAR(255));

CREATE TABLE

حجم الجدول الآن في الذاكرة، هو 0 بايت :

hsoubguide=# SELECT pg_size_pretty(pg_total_relation_size('size_calc'));

 pg_size_pretty 
----------------
 0 bytes
(1 row)

سنقوم بإضافة نص من 64 حرفًا:

hsoubguide=# INSERT INTO size_calc(msg) VALUES ('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ..');

INSERT 0 1

وسنجد أن حجم الجدول قفز إلى 8kB فورًا:

hsoubguide=# SELECT pg_size_pretty(pg_total_relation_size('size_calc'));

 pg_size_pretty 
----------------
 8192 bytes
(1 row)

ولكن ماذا لو قمنا بحذف محتويات الجدول؟

hsoubguide=# SELECT * FROM size_calc;

                               msg                                
------------------------------------------------------------------
 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ..
(1 row)

hsoubguide=# DELETE FROM size_calc;
DELETE 1
hsoubguide=# SELECT * FROM size_calc;
 msg 
-----
(0 rows)

رأينا أن الجدول فارغٌ الآن من المحتوى، ولكن حجمه في الذاكرة لا زال 8kB كما نرى في الاستعلام التالي:

hsoubguide=# SELECT pg_size_pretty(pg_total_relation_size('size_calc'));

 pg_size_pretty 
----------------
 8192 bytes
(1 row)

إلا أنه يمكننا استخدام المكنسة الكهربائية لقواعد البيانات، التعليمة VACUUM FULL التي تنظّف الذاكرة من الملفات الفارغة، أو تلك التي تمددت في وقت ما، ثم لم يعد هناك حاجة إلى حجمها الكبير، كما في المثال التالي:

hsoubguide=# VACUUM FULL;

VACUUM
hsoubguide=# SELECT pg_size_pretty(pg_total_relation_size('size_calc'));
 pg_size_pretty 
----------------
 0 bytes
(1 row)

خلاصة

لا شك أن هذا الفصل قد ساعدك في التعرف إلى كيفية تتبع استخدام الذاكرة في قاعدة بياناتك، وقد أصبح بإمكانك معرفة الحجم الذي تستهلكه الفهارس، والحجم الذي تستهلكه الجداول عمومًا، مما سيساعدك لاحقًا في إدارة ذاكرة التخزين لقاعدة البيانات ككل.

اقرأ أيضًا

• المقال التالي: كيفية إدارة الأداء في قواعد بيانات Postgres
• المقال السابق: إدارة النسخ الاحتياطي في قواعد بيانات Postgres
• النسخة الكاملة من كتاب الدليل العملي إلى قواعد بيانات PostgreSQL