Tuning EN - История изменений

Uscld2 в 10:29, 12 сентября 2024

2024-09-12T10:29:46Z

← Предыдущая версия		Версия от 10:29, 12 сентября 2024
Строка 15:		Строка 15:
	sudo systemctl restart postgres		sudo systemctl restart postgres

	More information on PostgreSQL optimisation can be found on the Internet. For example~~, at the following links:~~		More information on PostgreSQL optimisation can be found on the Internet. For example [https://wiki.postgresql.org/wiki/Performance_Optimization here].
	* https://wiki.postgresql.org/wiki/Performance_Optimization
	* https://ruhighload.~~com/post/%D0%A2%D1%8E%D0%BD%D0%B8%D0%BD%D0%B3+%D0%B1%D0%B0%D0%B7%D1%8B+Postgres~~

	PostgreSQL's file system architecture is designed to be fast at the expense of file size. PostgreSQL does not waste time trying to update data in the same location on disk as the data being updated. It simply marks the updated data as deleted and adds a new entry to the file. This maximises system performance but makes the files very fragmented. Vacuuming helps to get rid of fragmentation. By default, after PostgreSQL is installed, an automatic vacuuming process is added which monitors the state of the files and performs low-priority defragmentation in parallel with PostgreSQL, if there is no load on the file system. Never disable autovacuuming, otherwise the database files will grow to enormous sizes.		PostgreSQL's file system architecture is designed to be fast at the expense of file size. PostgreSQL does not waste time trying to update data in the same location on disk as the data being updated. It simply marks the updated data as deleted and adds a new entry to the file. This maximises system performance but makes the files very fragmented. Vacuuming helps to get rid of fragmentation. By default, after PostgreSQL is installed, an automatic vacuuming process is added which monitors the state of the files and performs low-priority defragmentation in parallel with PostgreSQL, if there is no load on the file system. Never disable autovacuuming, otherwise the database files will grow to enormous sizes.

N.Skrypnik в 16:15, 23 февраля 2023

2023-02-23T16:15:13Z

← Предыдущая версия		Версия от 16:15, 23 февраля 2023
Строка 24:		Строка 24:

	== PHP-FPM ==		== PHP-FPM ==
	By default, the /etc/php/7.4/fpm/pool.d/www.conf is responsible for setting up the pool		By default, the /etc/php/7.4/fpm/pool.d/www.conf is responsible for setting up the pool.

	This file contains several Process Manager (PM) settings which start with <code>pm</code>		This file contains several Process Manager (PM) settings which start with <code>pm</code>

N.Skrypnik в 16:06, 23 февраля 2023

2023-02-23T16:06:27Z

← Предыдущая версия		Версия от 16:06, 23 февраля 2023
Строка 22:		Строка 22:

	In addition, you may find it useful to know how to start vacuuming manually and the procedure for rebuilding the indexes. These operations can in some cases significantly speed up the database: https://dba.stackexchange.com/questions/135881/postgresql-error-failed-to-re-find-parent-key-in-index		In addition, you may find it useful to know how to start vacuuming manually and the procedure for rebuilding the indexes. These operations can in some cases significantly speed up the database: https://dba.stackexchange.com/questions/135881/postgresql-error-failed-to-re-find-parent-key-in-index

			== PHP-FPM ==
			By default, the /etc/php/7.4/fpm/pool.d/www.conf is responsible for setting up the pool

			This file contains several Process Manager (PM) settings which start with <code>pm</code>

			We recommend using a dynamic pool as there are other services running on the same server. The process manager will then manage the number of php-fpm processes in memory automatically, freeing up resources if they are not needed. This is the default mode of the process manager, but just in case, make sure it is: <code>pm = dynamic</code>

			Next you need to configure the number of php-fpm processes as well as the rest of the settings. Some calculations will need to be done here.

			A single php-fpm process takes up approximately 48MB in RAM (this is only true for USERSIDE). However, if you have too much data in USERSIDE, this value can be slightly higher. The main thing is to use an average value rather than a maximum value. Because not every single query consumes the maximum amount of RAM. To find out how much RAM your PHP-FPM processes are consuming, run the command at the time of normal workload on the WEB-application USERSIDE:

			ps -eo rss,comm,cmd \| grep php-fpm

			As a result, you will get an output consisting of three columns:

			3304 php-fpm7.4 php-fpm: master process (/etc/php/7.4/fpm/php-fpm.conf)
			41596 php-fpm7.4 php-fpm: pool www
			36304 php-fpm7.4 php-fpm: pool www
			38516 php-fpm7.4 php-fpm: pool www

			Here you can see the PHP-FPM master process and three workers - they are created by the master and it is they who process the WEB requests. In this case, three workers consume an average of 38 MB. Remember this value.

			Now we need to find out how much free RAM we have. This should be done after PostgreSQL optimization and preferably at the time of normal workload.
			Perform:

			free -m

			The result will be approximately the following:

			total used free shared buff/cache available
			Mem: 996 147 331 124 516 581

			To calculate the amount of RAM that can be used, use the formula: available = total - (used + buff). In this case, it is 333 MB.

			We could use all this RAM for the php-fpm pool. But it is still advisable to take about 80% of it, no more. In this case, it's about 266 MB.

			Now count the number of workers. One worker consumes an average of 38 MB, and the amount of free memory is 266 MB. This is all very approximate, as these values are far from constant. We just have to divide the amount of free memory by the amount of memory consumed by one worker. We get 266 / 38 = 7. This is the maximum number of workers we can run.

			Specify <code>pm.max_children = 7</code> as the value.

			The next step is to set the minimum and maximum number of wokers waiting to connect. The parameters for this are <code>pm.min_spare_servers</code> and <code>pm.max_spare_servers</code> respectively. The values of these parameters are chosen "by eye" and depend on the actual number of requests, their "severity" and much more. To start with, let's take the minimum number of waiting wokers to be about 25% of the total maximum (max_children), and the maximum to be about 50%.

			pm.min_spare_servers = 2
			pm.max_spare_servers = 4

			Next you need to configure the number of workers that will be running when the php-fpm pool starts. The parameter <code>pm.start_servers</code> is responsible for this and it's value is calculated according to the formula: min_spare_servers + (max_spare_servers - min_spare_servers) / 2. In our case, it is 3

			To summarise, the PHP-FPM pool process manager configuration is as follows:

			pm.max_children = 7
			pm.min_spare_servers = 2
			pm.max_spare_servers = 4
			pm.start_servers = 3

			The following points are important to note here. In these demonstration calculations, data from a server with 1GB of RAM was used. In your case, on a real server, the maximum number of workers may be very large - several hundred. However, you should not use such a large amount of workers if you only have two operators and no one else working with USERSIDE. Extra workers will just uselessly occupy RAM that could have been used by PostgreSQL for beneficial purposes. These guidelines will help to understand what the maximum number of workers can be used to maximize system utilization, but if that number of processes waiting for http-requests has no sense, then there is no need to utilize it - just reduce it to reasonable values.

			We also recommend that you monitor the php-fpm log file for problems with workers.

			tail -f /var/log/php7.4-fpm.log

			Lines like the following might indicate this:

			WARNING: [pool www] server reached pm.max_children setting (5), consider raising it

N.Skrypnik в 16:35, 22 февраля 2023

2023-02-22T16:35:48Z

← Предыдущая версия		Версия от 16:35, 22 февраля 2023
Строка 1:		Строка 1:
	[[Tuning_EN\|en]] \| [[Tuning\|ru]]		[[Tuning_EN\|en]] \| [[Tuning\|ru]]

			= Fine-tuning =

			Many server applications initially come with a basic configuration that allows them to run on the simplest and lowest performance devices. Such systems need to be adapted to the servers and operating systems on which they are used. These actions can significantly (many times or dozens of times) increase the utilisation of server resources and so significantly improve the performance of the system as a whole.

			Although this step is not obligatory, we strongly recommend making this adjustment.

			== PostgreSQL ==
			There are special calculators available for calculating server performance parameters to make such calculations easier. Use one of the calculators to calculate the parameters:
			* https://pgtune.leopard.in.ua/
			* http://pgconfigurator.cybertec.at/

			You need to specify the server specifications, Postgres version, etc. The output will give you a set of recommended parameter values which you should put in the /etc/postgresql/13/main/postgresql.conf file and then reboot PostgreSQL:
			sudo systemctl restart postgres

			More information on PostgreSQL optimisation can be found on the Internet. For example, at the following links:
			* https://wiki.postgresql.org/wiki/Performance_Optimization
			* https://ruhighload.com/post/%D0%A2%D1%8E%D0%BD%D0%B8%D0%BD%D0%B3+%D0%B1%D0%B0%D0%B7%D1%8B+Postgres

			PostgreSQL's file system architecture is designed to be fast at the expense of file size. PostgreSQL does not waste time trying to update data in the same location on disk as the data being updated. It simply marks the updated data as deleted and adds a new entry to the file. This maximises system performance but makes the files very fragmented. Vacuuming helps to get rid of fragmentation. By default, after PostgreSQL is installed, an automatic vacuuming process is added which monitors the state of the files and performs low-priority defragmentation in parallel with PostgreSQL, if there is no load on the file system. Never disable autovacuuming, otherwise the database files will grow to enormous sizes.

			In addition, you may find it useful to know how to start vacuuming manually and the procedure for rebuilding the indexes. These operations can in some cases significantly speed up the database: https://dba.stackexchange.com/questions/135881/postgresql-error-failed-to-re-find-parent-key-in-index

N.Skrypnik: Новая страница: «en | ru»

2023-02-22T13:54:26Z

Новая страница: «en | ru»

Новая страница

[[Tuning_EN|en]] | [[Tuning|ru]]