Hi all,
I'm testing the LCR module and I am having problems with its performance.
In my setup I use a call generator (sipp) to place 50 calls, 10 calls each second - which works like a charm without any retransmissions when NOT using LCR functionality (no load_gws()).
As soon as I add this code in my route_block if (!load_gws()) { sl_send_reply("500", "Server Internal Error - Cannot load gateways"); return; }; I am having huge performance problems. The OpenSER drops to effectivly 1 CPS. My lcr table contains only 20 entries and I have just 2 gateways configured, so this shouldn't be a problem.
I guess I made a big mistake somewhere, since this can't be the intended behaviour of the LCR module.
Can somebody help me?
Regards, Stefan
Stefan Prelle writes:
I am having huge performance problems. The OpenSER drops to effectivly 1 CPS. My lcr table contains only 20 entries and I have just 2 gateways configured, so this shouldn't be a problem.
currently load_gws() makes a complex mysql query, but only one mysql query per second seems very slow to me. load_gws could be rewritten totally in c, but so far nobody has had time to do so.
-- juha
Hi all,
Am Samstag, den 12.11.2005, 15:48 +0200 schrieb Juha Heinanen:
I am having huge performance problems. The OpenSER drops to effectivly 1 CPS. My lcr table contains only 20 entries and I have just 2 gateways configured, so this shouldn't be a problem.
currently load_gws() makes a complex mysql query, but only one mysql query per second seems very slow to me. load_gws could be rewritten totally in c, but so far nobody has had time to do so.
I came a log way here and now I'm stuck again.
1. I moved to another machine, since I suspected some more basic problems to be the cause of my timing problems. That indeed led to an increased and smooth performance.
2. Then I inserted 1 Million routes into the "lcr" table, which resulted in a query time of 10 seconds. So I created an index for lcr.prefix - reducing the processing time to 1 seconds.
3. Since 1 second per Call is way too much to achieve the targeted 200+ CPS, I changed the SQL string in the LCR module from a LIKE comparision to an exact match comparison. The result was very satisfying (MySQL CLI said 0.00 seconds per request)
4. The lcr table will contain prefixes, so I needed a mechanism to perform multiple exact match requests, while shortening the dialed number to compare. Because my C/C++ is basically just enough for "Hello World", I switched to MySQL 5 and used stored procedures. The stored procedure I used is attached (proc.sql). I changed the SQL query to use this procedure.
5. Without modifications the mysql module isn't able to use MySQL stored procedures. So I modified the flags for the MySQL connection setup from if (!mysql_real_connect(..., id->port, 0, 0)) { to if (!mysql_real_connect(..., id->port, 0, CLIENT_MULTI_STATEMENTS)) {
Now everything seems fine. Call processing is smooth, although there are a million entries in the lcr-table and 200+ CPS doesn't seem to be a problem. But this doesn't last. I made a test with 200 CPS and after 5 seconds or so all calls are rejected due to a SQL problem in load_gws() which produces the following error message: 0(1346) submit_query: MySQL server has gone away 0(1346) db_raw_query: Error while submitting query 0(1346) load_gws(): Failed to query accept data 0(1346) submit_query: MySQL server has gone away 0(1346) db_raw_query: Error while submitting query 0(1346) load_gws(): Failed to query accept data 0(1346) submit_query: MySQL server has gone away
The MySQL server is still up and running. As soon as I restart the (Open)SER I can work again ... for about 5 seconds (or 1000 calls).
And that is my current situation. Any ideas?
Regards, Stefan
Stefan Prelle writes:
- Then I inserted 1 Million routes into the "lcr" table, which resulted in a query time of 10 seconds. So I created an index for lcr.prefix - reducing the processing time to 1 seconds.
stefan,
one million "routes" in lcr table is a very big number. when i wrote lcr module, i had in mind usage where lcr table would hold a rather small number of prefixes, such as for different countries and area codes. i never thought that the table would hold individual phone numbers.
Since 1 second per Call is way too much to achieve the targeted 200+ CPS, I changed the SQL string in the LCR module from a LIKE comparision to an exact match comparison. The result was very satisfying (MySQL CLI said 0.00 seconds per request)
The lcr table will contain prefixes, so I needed a mechanism to perform multiple exact match requests, while shortening the dialed number to compare. Because my C/C++ is basically just enough for "Hello World", I switched to MySQL 5 and used stored procedures. The stored procedure I used is attached (proc.sql). I changed the SQL query to use this procedure.
are you saying that LIKE operation is much slower that a many exact matches? if so, it looks like a bug in mysql's LIKE implementation. have you asked mysql list, why LIKE takes so long?
-- juha
Hi Juha,
Am Samstag, den 26.11.2005, 17:17 +0200 schrieb Juha Heinanen:
are you saying that LIKE operation is much slower that a many exact matches? if so, it looks like a bug in mysql's LIKE implementation. have you asked mysql list, why LIKE takes so long?
It definetly is. Try generating a million entries and use the MySQL command line interface to measure the time for the operation. A like operation takes about one second, a exact match (if there is an index for that column) less than 10 ms.
As far as I know the LIKE operation cannot make use of an table index and thus is a lot slower. And no, I haven't contacted the mysql list, since I'm not that surprised by that fact.
Regards, Stefan
On 26-11-2005 17:17, Juha Heinanen wrote:
Stefan Prelle writes:
- Then I inserted 1 Million routes into the "lcr" table, which resulted in a query time of 10 seconds. So I created an index for lcr.prefix - reducing the processing time to 1 seconds.
stefan,
Since 1 second per Call is way too much to achieve the targeted 200+ CPS, I changed the SQL string in the LCR module from a LIKE comparision to an exact match comparison. The result was very satisfying (MySQL CLI said 0.00 seconds per request)
The lcr table will contain prefixes, so I needed a mechanism to perform multiple exact match requests, while shortening the dialed number to compare. Because my C/C++ is basically just enough for "Hello World", I switched to MySQL 5 and used stored procedures. The stored procedure I used is attached (proc.sql). I changed the SQL query to use this procedure.
are you saying that LIKE operation is much slower that a many exact matches? if so, it looks like a bug in mysql's LIKE implementation. have you asked mysql list, why LIKE takes so long?
Yes, it is much slower than exact match, because mysql cannot make use of indexes in this case. You can prefix the query with "explain", this will tell you how exactly is the query planned and executed. If there is no index that can be used then the query would often result in full table scan.
Jan.
-
Jan Janak -
Juha Heinanen -
Stefan Prelle -
Stefan Prelle