Table of Contents
Abstract
This article tries to show how to increase performance of registrar with database without large code modifications and how can be performance influenced by used data.
Tests done in this article don't cover all parts of registrar/proxy and thus some suggestions for performance improvements can cause problems in other situations. Use them with care.
This text will be updated in the future according new test results and observations...
Tests were done with SER Ottendorf (before RC1, it was version from March 2007) compiled without DBG_QM_MALLOC (but in test with default tables and numeric UID there was no significant difference between SER compiled with DBG_QM_MALLOC and without, probably due to slowness of DB operations).
MySQL server 5.0.32-7etch1 was running on the same machine. It used default config file (included dir at the end of this file contained only one file setting old_passwords option which should not have impact on performace).
Because of uselessness of db_mode 2 shown elsewhere I used registrar in db_mode 1 (write through). In this mode is data into database written immediately after change in memory (i.e. within processing REGISTER request).
Each test is composed from scenarios shown in Table 1, “Used scenarios”. Each of them tries to measure specific part of configuration.
Table 1. Used scenarios
| name | description |
|---|---|
| save_only |
Called save without doing anything else (no UID assignment, no attributes, ...). In this case is username (+1429xxx) stored as UID. |
| save_uid | Called save with $t.uid set to value @msg[Uid]. Uid header is sent by sipp. |
| uri2uid_only | After lookup domain and lookup user is called sl_reply. Nothing more... |
| load_attrs_only | Called load_attrs with $t.uid set to value @msg[Uid] and then sl_reply. Uid header is sent by sipp. |
| uri2uid_with_attrs | Does lookup domain, lookup user, load attrs and sl_reply. |
| registrar_no_attrs | Does lookup domain, lookup user and save. No attributes are read. |
| full | Does lookup domain, lookup user, load attrs and save. It is simple fully working registrar. |
Domains were always the same - there were few domains in domain table. Domain used in test was “example.com”.
Usernames were always the same they were generated sequentialy in the form of telephone numbers: +14290000001 - +14291000000.
User attributes were always the same - three numbers (tc_barred, tc_pop, tc_subs_class).
There were two variants of UIDs - sequential numbers and randomly generated strings.
It was done 2 millions of iterations, registered contacts were in the form of <sip:username@testing_machine>. This means that in first run phase (first million) was each contact registered and in second phase (second million) was this contact refreshed. It is visible from graphs that second part is different processing than the first one.
Here is used SIPP scenario.
Before each test was database recreated (NOT before each test scenario!):
./ser_mysql.sh drop ./ser_mysql.sh create mysql -user -pheslo ser < domains.sql mysql -user -pheslo ser < choosen_datafile.sql
Following tests were done with default table settings (database creation script without modifications) - they differ only in stored data.
UIDs were 0-999999. Data was loaded from datafile 1m_ex.sql. Sipp used datafile 1m_ex.sipp.
UIDs were randomly generated strings with 6-32 characters. Data was loaded from datafile 1m_random_uid.sql. Sipp used datafile 1m_random_uid.sipp.
Table 2. Results for default table settings
| test scenario | throughput [requests per second] | |
|---|---|---|
| sequential UIDs | random UIDs | |
| save_only | 1536 | 1531 |
| save_uid | 1557 | 1083 |
| uri2uid_only | 3277 | 3268 |
| load_attrs_only | 2611 | 2951 |
| uri2uid_with_attrs | 1606 | 1665 |
| registrar_no_attrs | 1466 | 1027 |
| full | 980 | 793 |
Some questions appear here:
Why is load_attrs slower than lookup_domain + lookup_user in first test?
The answer might be that MySQL has problems with indexed string column which values are sequential numbers. This supposition seems to be confirmed by results given with randomized UIDs.
Why user_attrs doesn't have index according to UID only? If I'm not mistaken this table is read from avp_db module only and the search is performed according to UID.
Are both indexes in location table really needed?
For this question I have partial answer - the UID+contact key is needed because of UPDATE operation running when reregistering contact. But what about the second one?
I have tried to remove both of them, but reregistering was with huge amount of data not working...
How is the performance dependent on order of registrations? The tests here used registering users from +14290000001 to +14291000000 in this order - this may be the reason of some MySQL problems... (TODO)
Following tests try to show how to speed up things...
I did following modifications in default DB creation script to show the impact on performance:
Changed UID type in database from string to number. Needs change in uri_db module (and for reloading data from DB in usrloc too, but I didn't need this change).
user_attrs
uri_attrs (not used in tests)
location
uri
numeric UIDs and changed indexes
Indexes were changed like in case above and numeric UIDs were used.
Note that I'm comparing througput only because
used SIPP doesn't correctly store response times into output file
response times are closely bound with throughput so the comparison could show similar influence (TODO)
One more note to CPU graphs: I don't know why but top sometimes measures very strange values of CPU usage - really more than the machine has. ;-) [TODO - try to eliminate]
Table 3. Sumary
| test scenario | throughput [request per sec] | note | |||||
|---|---|---|---|---|---|---|---|
| default tables | changed indexes | numeric UIDs | numeric UIDs, changed indexes | ||||
| sequential UIDs | random UIDs | sequential UIDs | random UIDs | ||||
| save_only | 1536 | 1531 | 2321 | 2310 | — | — | Scenario is not using uid - no differences expected according data. Removed location index according contact shows 51% improvement. |
| save_uid | 1557 | 1083 | 2402 | 1509 | 1600 | 2429 | Randomized UID with default tables shows 30% decrease - computing indexes according randomized values is probably slower than for sequential. It is interesting to compare CPU utilization in both cases. Removed location index according contact shows 54 resp. 39% increase. This index has shown to be useful in cases when one user has lots of contacts but this is rather unusual case I guess. |
| uri2uid_only | 3277 | 3268 | 4494 | 4482 | 3267 | 4474 | Index change brings 37% increase. |
| load_attrs_only | 2611 | 2951 | 2801 | 3433 | 2674 | 4148 | Index with random data is generated slower than for sequential ones, but for read-only members like attributes it helps - here it shows 13% throughput increase. The index change brings 7 resp. 16% increase here. Numeric UIDs with changed indexes helped a lot here - 59% increase against default table settings. Other scenarios didn't show such high difference. |
| uri2uid_with_attrs | 1606 | 1665 | 1946 | 2128 | 1623 | 2512 | |
| registrar_no_attrs | 1466 | 1027 | 2217 | 1408 | 1505 | 2239 | |
| full | 980 | 793 | 1266 | 1006 | 994 | 1481 | Note that final throughput with random UIDs is 19% lower than with sequential numbers. |
Some things has shown during testing:
There were generated very large log files in /var/log/mysql. Changing log settings of mysql can speed up things too (TODO).
I have tried changes in code without touching DB schema.
I tried to use pthread locking with sequential resp. random UIDs instead of the default one because the default one highly consumes CPU power in some scenarios. (Note that results from this test might be architecture dependent.)
It seems that for pure save() call it really helps (47% throughput increase with sequential UIDs, 28% with random), but together with other operations like reading attributes or UID it results into similar throughput like with the default locking strategy.