Advice on tweaking larger server?

[Cintya Takahaschi]

Hi!

I have installed AtoM 2.6 with all dependencies in one (virtual) server with this configuration:

- 16vCPU

- RAM 64GB

- HD 5 TB

- Ubuntu 20.04

(Note: I plan to upgrade to AtoM 2.8 after adjusting this server configurations).

I'm trying to adjust its performance, and made some configurations that made the server faster (pages loading faster, and operations as search:populate A LOT FASTER).

But it seems that there are still configurations I can change or increase for better performance.

htop showed that its consuming of less than 15% of RAM  (~=8GB of 64GB) and barely 10% of each core while running search:populate and (at the same time) running ApacheBench (ab) to send  100.000 requests, with 1000 concurrent connections (ab -n 100000 -c 1000 http://<atom-server_ip>/)

The changes I've made (different than the values on AtoM 2.6's installation manual) are:

On the file /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size = 48G

skip-name-resolve = ON
join_buffer_size = 2M

innodb_redo_log_capacity = 12G
innodb_buffer_pool_instances = 48
thread_cache_size = 10

innodb_flush_method = O_DIRECT

join_buffer_size = 3M

On the file /etc/php/7.2/fpm/pool.d/atom.conf:

pm.max_children = 10000

pm.start_servers = 1000

pm.min_spare_servers = 1000

pm.max_spare_servers = 5000

pm.max_requests = 5000

php_admin_value[memory_limit] = 3G

php_admin_value[opcache.memory_consumption] = 256

php_admin_value[opcache.max_accelerated_files] = 20000

php_admin_value[realpath_cache_size] = 4096K

php_admin_value[realpath_cache_ttl] = 600

Does anyone has any advice or experience tuning a larger server like this for AtoM?

Cintya Takahaschi

[Johan Pieterse]

Hi  Cintya 

See this article on innodb_buffer_pool_size. It seems to big for me.

https://releem.com/docs/mysql-performance-tuning/innodb_buffer_pool_size#:~:text=Configuration%20of%20innodb_buffer_pool_size%20is%20highly,during%20peak%20usage%20and%20maintenance.

join_buffer_size seems fine

https://releem.com/docs/mysql-performance-tuning/join_buffer_size#:~:text=Join_buffer_size%20default%20is%20256KB%2C%20while,of%20max%20connections%2C%20at%20startup.

innodb_redo_log_capacity also seems to big. Do you want to keen 12GB of logs?

You have join_buffer_size = 2M and join_buffer_size = 3M

pm.max_children seems to much. See how to calculate

https://stackoverflow.com/questions/53227355/understand-pm-max-children-tuning

Assigning too much resources is also not making it better.

Regards
Johan Pieterse
AtoM IT Specialist
https://plainsailingisystems.co.za
+27 823371406

[Cintya Takahaschi]

Hi Johan, thanks for your reply!

Actually, I was trying to "force" the server to use more of its resources (16 vCPU / 64GB RAM)...

I saw many places advising "setting innodb_buffer_pool_size as 80% of available RAM", so I thought, since the minimal requirement for AtoM was 7GB, I would "reserve" this amount, and, of 64GB (total of server) - 7GB (required) = 56GB remainng, I would configure 80% (so a little more than 45 GB) as  innodb_buffer_pool_size. Do you think 48G is too much? 

For the other parameters,  I kinda just "blindly" followed MySQLTuner advices ("innodb_redo_log_capacity should be (=12G) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size."). (And also forgot to delete join_buffer_size = 2M from previous test)...

The pm.max_children value was a exaggeration, also trying to "force" the server to use its resources... 

The previous value I had configured was 

pm.max_children = 300

(I found 27M as being the average memory consumed for each php-fpm process with the command:

ps --no-headers -o "rss,cmd" -C php-fpm7.2 | awk '{ sum+=$1 } END { printf ("%d%s\n", sum/NR/1024,"M") }'

as described in https://gist.github.com/holmberd/44fa5c2555139a1a46e01434d3aaa512?permalink_comment_id=3143513

and calculated 8GB = 8192 MB / 27 MB = 303,4, so I rounded this value to 300.)

But since the values showed on htop were so low, and there was no errors in the log, I kept increasing more and more this value (but still the values ​​in htop did not increase much more).

And I got the values 

php_admin_value[opcache.memory_consumption] = 256

php_admin_value[opcache.max_accelerated_files] = 20000

php_admin_value[realpath_cache_size] = 4096K

php_admin_value[realpath_cache_ttl] = 600

from

https://symfony.com/doc/current/performance.html#configure-opcache-for-maximum-performance

Do you think there is any configuration I can do with this server resources to make AtoM "faster" (specially processes such as search:populate and csv:import)? Some configuration of Elasticsearch?

Sorry, I'm just kinda lost in here...

Cintya Takahaschi

[Johan Pieterse]

Good day  Cintya 

I believ  innodb_buffer_pool_size  is too big.

https://www.percona.com/blog/80-ram-tune-innodb_buffer_pool_size/

See this comment in the post

Here is what you should do. First run this query

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine=’InnoDB’) A;
+——-+
| RIBPS |
+——-+
| 8 |
+——-+
1 row in set (4.31 sec)

and set my.cnf setting like this

[mysqld]
innodb_buffer_pool_size=8G

With regards to:

Do you think there is any configuration I can do with this server resources to make AtoM "faster" (specially processes such as search:populate and csv:import)? Some configuration of Elasticsearch?

I am not sure in what version Artifactual stated optimising queries but I know it is in 2.7 and 2.8.

In 2.4 for instance my 8 milioin records took 45 days to run search:populate

In 2.8 it takes 3 days to run.

Perhaps Dan/Artefactual can assist if it was optimised in 2.6.

Johan Pieterse

Records and Archive IT Specialist

Plain Sailing Information Systems

(+27) 082 337-1406

[Cintya Takahaschi]

Hi Johan, thanks for your tips!

> I am not sure in what version Artifactual stated optimising queries but I know it is in 2.7 and 2.8.

> In 2.4 for instance my 8 milioin records took 45 days to run search:populate

> In 2.8 it takes 3 days to run.

> Perhaps Dan/Artefactual can assist if it was optimised in 2.6.

Yes, it seems that AtoM 2.8 really is a lot faster than AtoM 2.6!

What I was planning to do was to import an existing content (database/uploads) from an instance of AtoM 2.6 we have here, tune it to this "new hardware", and then upgrade it to AtoM 2.8, but the search:populate was quite slow, even after the configurations I adjusted - I let it run for 1 h , and,  by the average time taken to populate each document, it would take 78 hours to populate a little more than 1 million documents (1003218 documents).

So I decided to try to install AtoM 2.8 on Ubuntu 24.04 directly, applied these configurations (still based on my old calculations):

On /etc/mysql/mysql.conf.d/mysqld.cnf
innodb_buffer_pool_size = 48G
innodb_flush_method = O_DSYNC
skip-name-resolve = ON

innodb_redo_log_capacity = 12G
innodb_buffer_pool_instances = 48

On /etc/php/7.4/fpm/pool.d/atom.conf
pm.max_children = 240
pm.start_servers = 100
pm.min_spare_servers = 100
pm.max_spare_servers = 150

pm.max_requests = 5000
php_admin_value[memory_limit] = 3G

php_admin_value[apc.enable_cli] = 1

php_admin_value[opcache.memory_consumption] = 256
php_admin_value[opcache.max_accelerated_files] = 20000
php_admin_value[realpath_cache_size] = 4096K
php_admin_value[realpath_cache_ttl] = 600

Then I imported the database/uploads from the "old" server and let  search:populate running overnight.

It took just  5 hours! (Index populated with 1003218 documents in 18033.25 seconds.)

(Unfortunately, I haven't tested with the MySQL and PHP-FPM configurations suggested on the AtoM 2.8 installation manual to compare).

Regarding the query, I got:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

+-------+
| RIBPS |
+-------+
|    11 |
+-------+

So I'm re-configuring these parameters on /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size = 12G
innodb_redo_log_capacity = 3G
innodb_buffer_pool_instances = 12

And I will let search:populate running over this night to see if there is any difference.

You have a pretty big number of records! Do you installed AtoM in just one server, or separated MySQL and Elasticsearch in one server and Nginx and PHP-FPM in another? Do you use Ubuntu?

Cintya Takahaschi

[Johan Pieterse]

Hi  Cintya 

Do you installed AtoM in just one server, or separated MySQL and Elasticsearch in one server and Nginx and PHP-FPM in another? Do you use Ubuntu?

We have MySQL and applications on 2 differnt servers. The application is not very resource intensive, so the DB server has the muscle.

It is actually on SUSE but I believe we will migrate to Ubuntu soon as setting up and maintaing SUSE instance was quite a mission.

How was the populate?

Regards
Johan Pieterse
AtoM IT Specialist
https://plainsailingisystems.co.za
+27 823371406

[Cintya Takahaschi]

Hi Johan, thanks for the reply again!

So, with these parameters on /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size = 12G
innodb_redo_log_capacity = 3G
innodb_buffer_pool_instances = 12

The search:populate took 6,5 hours (Index populated with 1003218 documents in 23583.43 seconds.), 

So it seems that the innodb_buffer_pool_size should be something between the 12G of this test (6,5 hours) and the 48G I configured previoulsy (5 hours).

So I decided to make a new test: apply some parameters advised by ChatGPT. (Some parameters it advised were just wrong - not accepted by the version of the application, or resulted in errors - but most I could apply).

I was lazy enough to not wanting to wait 5 or 6 hours to test every configuration change, so I applied all at the same time (but tested if AtoM was still working on browser after every configuration/restart made).

The question I made to ChatGPT was:

"On an Ubuntu Server 24.04 x64 server with 16 cores and 64GB of RAM, on which Nginx, PHP-FPM 7.4 (using APCU 5.1), MySQL 8, Elasticsearch 5.6 and Memcached are installed, how can I make the maximum server resources be used by these applications so that an application developed in Symfony 1.4 runs as fast as possible?"

And the configurations I changed:

1. On /etc/nginx/nginx.conf:

events {
   worker_connections 1024;
   multi_accept on;
}
http {
   client_body_buffer_size 16K;
   client_header_buffer_size 1k;
   client_max_body_size 8m;
   large_client_header_buffers 2 1k;
   tcp_nodelay on;
   keepalive_timeout 65;
   server_tokens off;
   (...)
   gzip_disable "msie6";
   (...)
}

2. On /etc/php/7.4/fpm/pool.d/atom.conf:

pm.max_children = 128
pm.start_servers = 32
pm.min_spare_servers = 16
pm.max_spare_servers = 32
pm.max_requests = 500

php_admin_value[apc.enable_cli] = 1
php_admin_value[apc.shm_size] = 512M
php_admin_value[apc.ttl] = 7200
php_admin_value[apc.gc_ttl] = 3600

3. On /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16
innodb_redo_log_capacity = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
max_connections = 1000

4. On /etc/elasticsearch/jvm.options:

-Xms31g
-Xmx31g

5. On /etc/sysctl.conf

vm.max_map_count=262144

(Tested before changing with sudo sysctl -w vm.max_map_count=262144)

6. Disabled the memory swap with

sudo swapoff -a

7. On /etc/memcached.conf:

-m 4096

-c 4096

8. On /etc/security/limits.conf:

* soft nofile 65536
* hard nofile 65536

9. On /etc/systemd/system.conf:

DefaultLimitNOFILE=65536

10. On /etc/sysctl.conf:

vm.swappiness=10

The I restarted the server, recriated the nested set, re-generated the slugs and re-indexed with:

sudo php /usr/share/nginx/atom/symfony propel:build-nested-set

sudo php /usr/share/nginx/atom/symfony propel:generate-slugs

sudo php -d memory_limit=-1 /usr/share/nginx/atom/symfony search:populate

sudo php /usr/share/nginx/atom/symfony cc

sudo systemctl restart php7.4-fpm.service

And the results were:

propel:build-nested-set took 0:01:31

propel:generate-slugs took 0:00:21

search:populate took 4:30:09  (Index populated with 1003218 documents in 16205.39 seconds.)

The best results so far! And the navigation/browsing on AtoM seems "faster" (not sure how to "technically" test it, but visually it seems faster).

Think I'm gonna leave like this, at least for now.

Thanks again for all your tips!

Cintya Takahaschi

[Johan Pieterse]

This is a good post with valuable info. Thank you for this.

Johan Pieterse 

AtoM IT Specialist 

+27 0823371406

https://plainsailingisystems.co.za

---

From: ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> on behalf of Cintya Takahaschi <cintya.t...@gmail.com>
Sent: Friday, July 26, 2024 5:12:45 PM
To: AtoM Users <ica-ato...@googlegroups.com>
Subject: [atom-users] Re: Advice on tweaking larger server?

 

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/345b3087-7d1e-48ca-bfb3-9e69e5fc9148n%40googlegroups.com.