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