Odoo Performance Drop
Based upon odoo.com performance measurements in March 2023 the Odoo-driver of Invantive’s UniversalSQL has been configured to use a page size of 1500. For more details see:
However, somewhere between March and July, and probably between March and May, the performance of the Odoo API (and user interface) on odoo.com have considerably dropped and remained since then at a lower speed.
This was used to validate the original default page size chosen in March 2023, using the same approach, and discover whether consistent API performance changes require a consistently different page size.
No effort was made to analyze the impact of the fair use rate limit, given that Odoo has not yet established this and we have chosen to limit API-calls to an arbitrary 60 per minute.
Tests were now executed on Odoo 16.2, whereas in March on 16.0.
Analysis of Impact on best Default Page Size
An analysis was made to establish a first default setting of page size using the table event with over 9.390 calendar appointments on odoo.com. A number of queries were executed for each page size tested on a fast HP-workstation with a fast low-latency Internet connection:
set use-http-disk-cache false
set use-http-memory-cache false
set requested-page-size x
select count(*)
from ( select 1 c
from event
limit 9390
)
select avg(cast(duration_ms as decimal))
from SessionIos@DataDictionary
where call_safe_name like '%event'
and id between z1 and z2
The results were listed in the table below. Results include processing time by the Invantive SQL driver, but excludes row handling and other high level SQL-pipeline activities; actual API response times can be a little less. Retrieval of individual pages was measured not to be related to the page number, so the table below only lists individual page response times of the Odoo external API.
| Page Size | Individual Page I/O (ms) | Actual I/O (ms) | Throttled Throughput (rows/second) | I/O Throughput (rows/second) | Previous Throttled Throughput (rows/second ) |
|---|---|---|---|---|---|
| 10 | 46 | 42912 | 218 | 10 | 10 |
| 100 | 205 | 19295 | 486 | 130 | 100 |
| 500 | 1198 | 22764 | 412 | 347 | 500 |
| 1000 | 2303 | 23037 | 407 | 347 | 724 |
| 1500 | 3354 | 23479 | 399 | 347 | N/A |
| 2000 | 4552 | 22760 | 412 | 361 | N/A |
| 2500 | 5987 | 23948 | 392 | 335 | N/A |
| 10000 | 4129 | 45422 | 206 | 361 | 774 |
The table clearly displays that since March 2023, the performance of the Odoo API has considerably dropped for this test case. It seems to level off now at around 400 rows per second, whereas previously performance well exceed 700 rows per second.
Also, the increase of page size seems no longer to have an effect beyond 100 rows per page, whereas previously a significantly larger page size would benefit the performance. However, with 100 rows per page, the self-chosen rate limiter of 1 API call per second reduces performance. With 500 rows per page, the rate limiter no longer applies.
Conclusion
It seems that odoo.com environments are being throttled by new software code or explicitly in terms of Odoo API performance since somewhere after March 2023.
For Invantive’s UniversalSQL-driver on Odoo, the default page size has been decreased from 1500 to 500 to reflect the limited API performance starting release 23.0.21.