Anope IRC Services

Please login or register.

Login with username, password and session length
Advanced search  

News:

Pages: [1]   Go Down

Author Topic: SQL Support  (Read 9075 times)

0 Members and 1 Guest are viewing this topic.

lavinpj1

  • Guest
SQL Support
« on: November 01, 2006, 06:34:39 PM »

Don't worry, I'm not going to ask for the full SQL support that is planned for Phase 3. What I would like however is better support for SQL server downtime. I update MySQL whenever a new version comes out. I am a bit of a cheater and use Debian's apt-get update. This stops the MySQL for about 30 secs while it updates it then it restarts it again. In this period Anope has tried to write to the database again and in turn crashes. It'd be nice if it handled this slightly better. To me, sitting and doing nothing would be a good place to start.

~Phil~
Logged

katsklaw

  • Guest
(No subject)
« Reply #1 on: November 01, 2006, 07:54:58 PM »

try disabling sql in Anope before upgrading mysql. /operserv set sql off when the upgrade is complete, turn it back on. /operserv set sql on
Logged

lavinpj1

  • Guest
(No subject)
« Reply #2 on: November 01, 2006, 08:02:48 PM »

That is one option. It would be nice though if some error handling routines were added to stop this all together. This would benifit those who update servers using their relevant package tool via a crontab.

~Phil~
Logged

katsklaw

  • Guest
(No subject)
« Reply #3 on: November 02, 2006, 10:23:11 PM »

It's nicer if we don't waste resources detecting if sql is running or not and just rely on the humans that are supposedly running services to just turn the bloody thing off and on.
Logged

lavinpj1

  • Guest
(No subject)
« Reply #4 on: November 04, 2006, 05:10:33 PM »

Ok, say you have mysql hosted elsewhere and there is a routing problem between the services server and the mysql server. Are the services roots expected to anticipate this routing issue and disable sql before anope crashes?
Logged

katsklaw

  • Guest
(No subject)
« Reply #5 on: November 04, 2006, 06:03:14 PM »

Quote
Originally posted by lavinpj1
Ok, say you have mysql hosted elsewhere and there is a routing problem between the services server and the mysql server. Are the services roots expected to anticipate this routing issue and disable sql before anope crashes?


Would you want the occasional crash, as configured now, or would you like the lag and hang time waiting for services sql check routines to timeout that are trying to determine if sql is running or not every 5 minutes (default UpdateTimeout)? Basiclly either hangs once and crashes do to lack of availability or it hangs every 5 minutes because it's checking it's connection and there happens to be some latency between services and it's data. Either way Anope will choke a bit. It chokes because it can't connect or it chokes because it's checking if it can connect.

Also, running remote sql db isn't a very good idea and your scenario is the perfect reason why. Latency is a fact of the internet, it applies to everything .. not just IRC and it's a fact that can't be coded around. The only thing we can do is make sure that the important stuff, such as db access is as local as possible and remove the variables that we can.

Best solution is to use sql on localhost.

[Edited on 4-11-2006 by katsklaw]
Logged

Dave Robson

  • Team
  • *
  • Offline Offline
  • Posts: 357
(No subject)
« Reply #6 on: November 04, 2006, 06:27:43 PM »

In fairness we really shouldnt crash - ever if we can avoid it :)
Logged

lavinpj1

  • Guest
(No subject)
« Reply #7 on: November 04, 2006, 08:23:43 PM »

That is my thinking Rob. My Anope has been known to go badly wrong in instances where it crashes due to my module writing errors. This has resulted in loss of all/parts of the db. On my local module test server this doesn't matter however on larger networks I can see it being catastrophic if databases were lost.

I am not a very accomplished C coder however I do use MySQL with php a lot. PHP has a function, mysql_ping, which checks if the connection to mysql is still active. I wrote a script to test this concept. The script is as follows...

connect to database
wait 30 secs
make query
show results

This takes 30.001686 seconds to parse.

If I change it to this...

connect to database
wait 30 secs
mysql_ping function
make query if mysql ping function returned true
show results

This takes 30.001921 seconds to parse

If I refresh a few times with each they waiver just around that point. This shows that in PHP there is little extra time taken to do a mysql_ping.

The point of the 30 second time was so that I could stop mysql in another test. Once I stop mysql after a connect in the waiting period, the script tells me that mysql isn't there anymore and doesn't do the query. The time for this script is roughly the same as the times when mysql is on, if not slightly faster.

Now, PHP is written in C. Anope is written in C. Why can Anope not do a similar check to that performed by PHP when the mysql_ping function is called. I don't understand why this would cause a huge lag.

~Phil~

[Edited on 4-11-2006 by lavinpj1]
Logged

djGrrr

  • Anope User
  • Offline Offline
  • Posts: 51
    • http://www.p2p-network.net/
(No subject)
« Reply #8 on: November 04, 2006, 09:07:01 PM »

Quote
I don't understand why this would cause a huge lag.


It can't cause any lag, let alone huge lag

[Edited on 4-11-2006 by djGrrr]
Logged
P2P-NET Network Staff

Jan Milants

  • Team
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 1372
(No subject)
« Reply #9 on: November 04, 2006, 09:50:51 PM »

actually, if i understand the implementation of mysql in anope correctly it very well can...

as far as i know, and i will assume this is true, anope is single threaded.
and i think that also includes the mysql implementation. which means that if anope pings the mysql server, it has to wait for a reply, and make all other stuff wait as well. so it doesn't really make a lot of difference whether we ping first or whether we send the query right away... in both cases we have to wait.

but what i just said is only true if it doesn't use seperate threads to handle the db updating...

it still shouldn't crash though imho... if a connection fails, it will still cause loads of lagggg (mysql should run locally for this reason) but dunno why that should mean it has to crash...i guess it s because of a timeout or so while anope waits for a reply but that s only a quess.
i don't use mysql because it is a cause of huge lag... also don't use sql modules for that reason... it s just not worth it. even if anope wouldn't crash, an sql query is wayy slower then looking it up in the memory. no decently sized network can afford that kind of delay, imo it stalls to much even while sql works normally...

i think the same goes if you use anope's build in smtp client... it the smtp client fails, it stalls everything

[Edited on 4-11-2006 by Viper]
Logged
If you like me donate coins to 1FBmZVT4J8WAUMHKqpWhgNVj3XXnRN1cCk :)

katsklaw

  • Guest
(No subject)
« Reply #10 on: November 04, 2006, 09:54:26 PM »

Quote
Originally posted by djGrrr
Quote
I don't understand why this would cause a huge lag.


It can't cause any lag, let alone huge lag
 


It most certainly can cause a huge lag. IF you are waiting for an simple ACK packet to return from a source that is experiencing heavy loads or is being DDoS'ed you could be waiting several minutes if ever to get it. Your request could ever well timeout after several seconds if not a few minutes and I's say that a few minutes is definately lag.

This is exacty what happens when a server gets DDoS'ed and splits from the network. Ask DALnet who lost 100,000 users over the course of a few months due to heavy, ongoing DDoS attacks for weeks on end if waiting for return packets can cause lag. Same thing with ping timeouts, the server sends you a ping packet, if your client never gets it or the server never gets your reply packet, the server will likely close your connection.
Logged

lavinpj1

  • Guest
(No subject)
« Reply #11 on: November 04, 2006, 10:11:59 PM »

As was said by Rob, Anope shouldn't crash if it can be helped. This certainly can be helped - even if it means putting another directive in the config which decides whether Anope should check for the existance of a mysql server before each query-set or not but face it crashing if mysql isn't there.

However, surely the query sends packets just as the check would do and I assume, as was said before, crashes because it reaches a timeout period. Would it be sensible to have a timeout period for the check, say 1-3 secs (clearly the devs know better than I do) and then just continue as normal without making any queries if this check fails.

~Phil~

[Edited on 4-11-2006 by lavinpj1]
Logged

lavinpj1

  • Guest
(No subject)
« Reply #12 on: November 05, 2006, 11:47:42 AM »

Oh, and maybe send a notice to svsadmins and put a message in the log channel about the failure of the sql so that the necessary people can go fix it/disable SQL for a brief period. And hey, if you wanted to take it really far, put another directive in the config to decide whether or not to auto disable SQL on server failure.

~Phil~
Logged

djGrrr

  • Anope User
  • Offline Offline
  • Posts: 51
    • http://www.p2p-network.net/
(No subject)
« Reply #13 on: November 05, 2006, 07:05:28 PM »

Quote
Originally posted by katsklaw
Quote
Originally posted by djGrrr
Quote
I don't understand why this would cause a huge lag.


It can't cause any lag, let alone huge lag
 


It most certainly can cause a huge lag. IF you are waiting for an simple ACK packet to return from a source that is experiencing heavy loads or is being DDoS'ed you could be waiting several minutes if ever to get it. Your request could ever well timeout after several seconds if not a few minutes and I's say that a few minutes is definately lag.

This is exacty what happens when a server gets DDoS'ed and splits from the network. Ask DALnet who lost 100,000 users over the course of a few months due to heavy, ongoing DDoS attacks for weeks on end if waiting for return packets can cause lag. Same thing with ping timeouts, the server sends you a ping packet, if your client never gets it or the server never gets your reply packet, the server will likely close your connection.


no, if you have to do a query anyways, it would not make any bit of difference, because u would have to wait for the query to finish which under the said conditions would do the same as waiting for the ping. (also this ping could be limited to like 500ms timeout or less, then assume that mysql is useless and disable until the ping is better)

but what was asked about was when shutting down mysql, which actually closes the connection, which is VERY easy to detect, and makes it easy avoid trying to write to mysql until it is restarted and anope reconnects.

And I don't see the significance of all that Dalnet DDoS stuff, my network has been hit by an 8gbit ddos attacks about 2 months ago, taking out over half of the servers, and it didn't cause any mysql lag.... Anyone who is trying to run anope with a mysql server across the net is a moron anyways.

[Edited on 5-11-2006 by djGrrr]
Logged
P2P-NET Network Staff
Pages: [1]   Go Up