URL gets Connection-Time Out when Multiple API Calls

Hi Everyone ,
I’m Facing this Issue Since last 2 month on Production URL, When We call REST API for Multiple Purchase Receipt Creation some of the Purchase Receipts gets and then URL gets down or says connection time-out (for 10-15 mins) after it starts properly, this happens every time.
nginx logs:

2019/06/19 21:32:32 [error] 7996#7996: *632478 upstream timed out (110: Connection timed out) while connecting to upstream, client: 13.232.52.71, server: ####.#####.com, request: “POST /api/method/custom_app.custom_app.create_pr HTTP/1.1”, upstream: “http://127.0.0.1:8004/api/method/custom_app.custom_app.create_pr”, host: “####.#####.com”
2019/06/19 21:32:32 [error] 7995#7995: *632480 upstream timed out (110: Connection timed out) while connecting to upstream, client: 13.232.52.71, server: ####.#####.com, request: “POST /api/method/custom_app.custom_app.create_pr HTTP/1.1”, upstream: “http://127.0.0.1:8004/api/method/custom_app.custom_app.create_pr”, host: “####.#####.com”
2019/06/19 21:32:32 [error] 7998#7998: *632481 upstream timed out (110: Connection timed out) while connecting to upstream, client: 13.232.52.71, server: ####.#####.com, request: “POST /api/method/custom_app.custom_app.create_pr HTTP/1.1”, upstream: “http://127.0.0.1:8004/api/method/custom_app.custom_app.create_pr”, host: “####.#####.com”

For that, We applied followings scenarios:
1.nginx Tunning up:

  • Increased keep_alive_timeout up to 50%
  • Increased keepalive_ requests up to 50%
  • Decreased max_fails up to 100 %
  • Increased proxy_read_timeout up to 50%

2.Re-Tune Up MySQL Setting:

  • Increased max-allowed-packet to 256M to 800M
  • Increased max_connections to 1000 to 10000 connections
  • Increased open_files_limit to 65535 to 1048576
  • Also, Optimize All Database tables with optimization method MySQL

**3. Re-Indexing Done **:

  • Customized and Custom Doctype

Using the above scenrios we reduce connection time out span from 10-15, but still URL gets down multiple PR API calls to server.

After Checking all logs we found in the slow query log :

Time: 190620 0:07:54
User@Host: [] @ localhost []
Thread_id: 15711 Schema: *********** QC_hit: No
Query_time: 189.036801 Lock_time: 0.000018 Rows_sent: 132640 Rows_examined: 132640
Rows_affected: 0
SET timestamp=1560969474;
select , timestamp(posting_date, posting_time) as “timestamp” from tabStock Ledger Entry
where item_code = ‘### ###’
and warehouse = ‘#######’
and ifnull(is_cancelled, ‘No’)=‘No’
and timestamp(posting_date, posting_time) > timestamp(‘2019-06-17’, ‘21:43:49’) and name!='
*******’
order by timestamp(posting_date, posting_time) asc, name asc
for update

Currently Production Database Size 4.1GB and from Tables Size Calculation Stock Ledger Entry having Size 1273.99 MB

i’m not sure, is this query taking time respond to UI or WEB or is there any issue or reason to connection - time out.

Please Help out on this

@saurabh6790 pls help me on this

check mysql processlist and check load on the server.

How many gunicorn workers you have?

What is the value of your innodb_buffer_pool_size ?

@saurabh6790 Thanks for replying

  1. while connection time-out I checked full processlist from mysql

Full Process List:

MariaDB [############]> show full processlist;
+--------+------------------+-----------+------------------+---------+------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id     | User             | Host      | db               | Command | Time | State                    | Info                                                                                                                                                                                                                                                                                                                                                                                                                        | Progress |
+--------+------------------+-----------+------------------+---------+------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
|      2 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                        |    0.000 |
|      1 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge coordinator | NULL                                                                                                                                                                                                                                                                                                                                                                                                                        |    0.000 |
|      3 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                        |    0.000 |
|      4 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                        |    0.000 |
|      5 | system user      |           | NULL             | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                                                                                                                                                                                                                                                                                                                                                                                        |    0.000 |
| 328214 | root             | localhost | ############# | Query   |    0 | init                     | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                       |    0.000 |
| 328296 | ############# | localhost | ############# | Query   |    2 | Creating sort index      | select *, timestamp(posting_date, posting_time) as "timestamp" from `tabStock Ledger Entry`
		where item_code = 'COW Milk'
		and warehouse = '######### - 5262'
		and ifnull(is_cancelled, 'No')='No'
		and timestamp(posting_date, posting_time) > timestamp('2019-06-17', '21:54:06') and name!='SLE/00157930'
		order by timestamp(posting_date, posting_time) asc, name asc
		 for update |    0.000 |
| 328299 | ############# | localhost | ############# | Query   |    1 | Sending data             | select `current` from `tabSeries` where name='FMCR-1906-' for update                                                                                                                                                                                                                                                                                                                                                        |    0.000 |
+--------+------------------+-----------+------------------+---------+------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+

MariaDB [#############]> show full processlist;
+--------+------------------+-----------+------------------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id     | User             | Host      | db               | Command | Time | State                    | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                               | Progress |
+--------+------------------+-----------+------------------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
|      2 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                               |    0.000 |
|      1 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge coordinator | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                               |    0.000 |
|      3 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                               |    0.000 |
|      4 | system user      |           | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                               |    0.000 |
|      5 | system user      |           | NULL             | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                               |    0.000 |
| 328214 | root             | localhost | ############# | Query   |    0 | init                     | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                              |    0.000 |
| 328301 | ############# | localhost | ############# | Query   |    0 | Creating sort index      | select pr.name, pr.posting_date, pr.posting_time, pr.conversion_rate,
			pr_item.conversion_factor, pr_item.base_price_list_rate, pr_item.discount_percentage,
			pr_item.base_rate
		from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
		where pr.docstatus = 1 and pr_item.item_code = '### #####' and pr.name != 'PREC-1906-**22**!!*' and
			pr.name = pr_item.parent
		order by pr.posting_date desc, pr.posting_time desc, pr.name desc
		limit 1 |    0.000 |
| 328303 | ############# | localhost | ############# | Query   |    0 | Sending data             | select distinct against_voucher_type from `tabGL Entry`                                                                                                                                                                                                                                                                                                                                                                                                            |    0.000 |
+--------+------------------+-----------+------------------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
  1. While collection time - Out Load Average:

1.31 2.65 1.09 1/544 19289

currently Load Average:

0.31 0.65 0.66 1/544 19289

  1. Number of Running Running Gunicorn Workers : 15

@root13F Thanks for Replay
Currently innodb-buffer-pool-size = 5468M