[Serusers] MySQL-Problem (was: LCR question)

[Stefan Prelle]

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
Specialist VoIP Systems
Broadnet AG
Weidestrasse 112a
22083 Hamburg
Fon +49-40-668610-0
Fax +49-40-668610-530
mailto:s.prelle at broadnet.de

Die in dieser E-Mail enthaltenen Informationen sind vertraulich und nur
für den oder die Empfänger bestimmt. Die Broadnet AG übernimmt keine
Haftung für den Inhalt dieser E-Mail. Versand und Empfang von E-Mails
dienen der Beschleunigung der Kommunikation und der Vorbereitung von
Geschäftsabschlüssen bzw. der Begleitung und Unterstützung unserer
Kundenbetreuung. Auf dem Weg vom Absender zum Empfänger können E-Mails
mitgelesen, verfälscht und verspätet zugestellt werden. Aus diesen
Gründen heraus können Verträge mit der Broadnet AG auf diesem Wege nicht
rechtswirksam abgeschlossen werden. Willenserklärungen können per E-mail
ebenfalls nicht rechtswirksam gegenüber der Broadnet AG abgegeben
werden. Sie werden erst nach einer schriftlichen Bestätigung wirksam.
Jegliche Haftung für Ansprüche, die aufgrund der Kommunikation per
E-Mail begründet werden könnten, sind ausgeschlossen, soweit der
Haftungsausschluß gesetzlich zulässig ist. 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: proc.sql
Type: text/x-sql
Size: 772 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20051117/defaab53/attachment.bin>