sqlgrey-users

[Sqlgrey-users] multiple read/write database support patch

[Paul B. H. <he...@ac...>]

I'm setting up sqlgrey with a dual server multi-master mysql replication
configuration. I wanted it to be able to failover between the two database
servers, both of which are read/write. The write remotely, read locally
clustering didn't really seem like it would do what I wanted.

The attached patch adds a new configuration directive, db_hostlist, which
takes a list of one or more database servers (presumably more than one, or
one could just use db_host). If this directive is defined, sqlgrey will
rotate between the listed hosts every time it has to try and reconnect to
the database.

Also, the patch moves the logging of the database error to before the
call to db_unavailable(). For some reason, DBI::errstr lost its value
during the call to db_unavailable(), and with the logging message
afterwards, no error was printed.


-- 
Paul B. Henson  |  (909) 979-6361  |  http://www.csupomona.edu/~henson/
Operating Systems and Network Analyst  |  he...@cs...
California State Polytechnic University  |  Pomona CA 91768

Re: [Sqlgrey-users] multiple read/write database support patch

[Dan F. <da...@ha...>]

Hey Paul.

Thanks for the patch. Its pretty nice and clean :)

Paul B. Henson wrote:
> I'm setting up sqlgrey with a dual server multi-master mysql replication
> configuration. I wanted it to be able to failover between the two database
> servers, both of which are read/write. The write remotely, read locally
> clustering didn't really seem like it would do what I wanted.
>   
Well.. I must admit.. I actually patched the dbclustering DBIx 
component, so you never  write to a read-host. It was what i needed when 
i made the dbclustering support. I added a variable to the module called 
something like READ_HOSTS_NEVER_WRITE (from my memory).

2 questions:
1: Would allowing you to set the READ_HOSTS_NEVER_WRITE = 0 from config 
do the same for you? So ALL hosts defined are read & write hosts and 
switched between in a round robin manner.

2: How scalable is your setup? We currently run 1 write-host and 7 
read-hosts. Can multi-master be used in such a setup? (a little off 
topic i guess;)



> The attached patch adds a new configuration directive, db_hostlist, which
> takes a list of one or more database servers (presumably more than one, or
> one could just use db_host). 
If we need to specify more than 1 "write" hosts, would there be any 
logic in simply allowing multiple hosts to be defined on "db_host" 
instead (comma separated)? Just so we dont need an extra config 
directive to do the job that an existing directive could do.

> Also, the patch moves the logging of the database error to before the
> call to db_unavailable(). For some reason, DBI::errstr lost its value
> during the call to db_unavailable(), and with the logging message
> afterwards, no error was printed.
>   
Im hoping Lionel has the time to double-check this. I dont know much 
about that part of the code.:)




- Dan

Re: [Sqlgrey-users] multiple read/write database support patch

[Lionel B. <lio...@bo...>]

Dan Faerch wrote the following on 03.02.2007 02:26 :
> Hey Paul.
>
> Thanks for the patch. Its pretty nice and clean :)
>
> Paul B. Henson wrote:
>   
> [...]
>   
>> Also, the patch moves the logging of the database error to before the
>> call to db_unavailable(). For some reason, DBI::errstr lost its value
>> during the call to db_unavailable(),

I see. disconnect() probably destroy $DBI:errstr. I didn't pay much
attention to this problem, but the solution seems obvious now :-)

>>  and with the logging message
>> afterwards, no error was printed.
>>   
>>     
> Im hoping Lionel has the time to double-check this. I dont know much 
> about that part of the code.:)
>   

I'm willing to test the patch, but I'd prefer to get one done against
CVS if possible or at least in unified diff format to make it easier to
integrate (the db_unavailable alone would be easy to handle but there is
a piece of code in the rest of the patch I'm not yet very familiar with).

Lionel.

Re: [Sqlgrey-users] multiple read/write database support patch

[Paul B. H. <he...@ac...>]

On Sun, 4 Feb 2007, Lionel Bouton wrote:

> I'm willing to test the patch, but I'd prefer to get one done against CVS
> if possible or at least in unified diff format to make it easier to
> integrate (the db_unavailable alone would be easy to handle but there is
> a piece of code in the rest of the patch I'm not yet very familiar with).

New patch in unified format attached... One minor change, my initial patch
resulted in the first connection being to the second server listed in the
configuration, which was nonintuitive. This patch connects to the first
listed server first.

Dan mentioned possibly changing the semantics of the existing db_host to
provide this functionality, which seemed to make sense, but would take a
bit more work to refactor all of the code currently depending on it.

Thanks...


-- 
Paul B. Henson  |  (909) 979-6361  |  http://www.csupomona.edu/~henson/
Operating Systems and Network Analyst  |  he...@cs...
California State Polytechnic University  |  Pomona CA 91768

[Sqlgrey-users] Performance with Postfix and SQLGrey

[Dave S. <dst...@ma...>]

I am seeing, under the load of about 1,000 Postfix connections per minute, =
SQLGrey slow down. Note the times here in the log below. The email is seen =
by Postfix at 10:44, but not finished and ready to send for about 30 =
seconds. I have about 350 threads open on this server, and they are =
typically all in play. The LA is fairly low (3 to 4).
=20
Is this SQLGrey that's slowing things down? It has to spawn a lot of =
sessions (see the stats above). Or is it our Postgres DB that's slowing =
this down?
=20
Feb 12 12:10:44 ib17 postfix/smtpd[14833]: 5E9FE1D3A481: client=3D{removed}=
[{removed}.99]
Feb 12 12:10:44 ib17 postfix/cleanup[15475]: 5E9FE1D3A481: message-id=3D<7F=
A493658BF13544BCDD9CF41F9BFC01BB5068@{removed}.com>
Feb 12 12:10:45 ib17 postfix/qmgr[14681]: 5E9FE1D3A481: from=3Djsmith@{remo=
ved} ( mailto:from=3Dlsegesvary@removed ).com, size=3D34936, nrcpt=3D2 =
(queue active)
Feb 12 12:11:13 ib17 postfix/smtp[16934]: 5E9FE1D3A481: to=3D<kwilson@{remo=
ved}.com ( mailto:kw...@re... )>, relay=3D{removed}[{removed}.27], =
delay=3D34, status=3Dsent (250 Ok: queued as 27A6A5FB6723)
=20
TIA,
=20
=20
=20
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"




This message has been certified virus-free by MailWise Filter - The real-ti=
me, intelligent, e-mail firewall used to scan inbound and outbound messages =
for SPAM, Viruses and Content. =0A=0A  For more information, please visit: http:=
//www.mailwise.com=0A

Re: [Sqlgrey-users] Performance with Postfix and SQLGrey

[Lionel B. <lio...@bo...>]

Dave Strickler wrote the following on 12.02.2007 18:21 :

Please don't reply to old posts when creating new subjects, it hides
your post in threaded views and make following discussions difficult.

> I am seeing, under the load of about 1,000 Postfix connections per
> minute, SQLGrey slow down. Note the times here in the log below. The
> email is seen by Postfix at 10:44, but not finished and ready to send
> for about 30 seconds. I have about 350 threads open on this server,
> and they are typically all in play. The LA is fairly low (3 to 4).
>  
> Is this SQLGrey that's slowing things down? It has to spawn a lot of
> sessions (see the stats above). Or is it our Postgres DB that's
> slowing this down?
>  
> Feb 12 12:10:44 ib17 postfix/smtpd[14833]: 5E9FE1D3A481:
> client={removed}[{removed}.99]
> Feb 12 12:10:44 ib17 postfix/cleanup[15475]: 5E9FE1D3A481:
> message-id=<7FA493658BF13544BCDD9CF41F9BFC01BB5068@{removed}.com
> <mailto:7FA493658BF13544BCDD9CF41F9BFC01BB5068@%7Bremoved%7D.com>>
> Feb 12 12:10:45 ib17 postfix/qmgr[14681]: 5E9FE1D3A481: from=jsmith
> <mailto:from=jsmith@%7Bremoved%7D.com>@{removed}
> <mailto:from=lsegesvary@%7Bremoved%7D>.com, size=34936, nrcpt=2 (queue
> active)
> Feb 12 12:11:13 ib17 postfix/smtp[16934]: 5E9FE1D3A481:
> to=<kwilson@{removed}.com <mailto:kwilson@%7Bremoved%7D.com>>,
> relay={removed}[{removed}.27], delay=34, status=sent (250 Ok: queued
> as 27A6A5FB6723)

To know which of PostgreSQL or SQLgrey is responsible, you should
monitor at least one. SQLgrey can't really measure the time spent from
receiving a request to the actual response because of the design of
Net::Server::Multiplex, so your best bet is to monitor PostgreSQL.

You can log the queries which take more than 100ms to be completed and
you'll have more informations, like:
- is it always the same type of query (should you try to optimize your
PostgreSQL configuration to optimize this query)?
- does it occur only during short periods (look for other processes
hammering the disk and competing with PostgreSQL for data access or
other databases being used)?

More general monitoring advices: use top and vmstat if you know when
your system becomes unresponsive to get a better view of what's going on.

Oh, and if you don't do it yet: use pg-autovacuum. PostgreSQL needs to
run VACUUM/ANALYZE on busy tables to maintain good performance.

Lionel.

Re: [Sqlgrey-users] multiple read/write database support patch

[Paul B. H. <he...@ac...>]

On Fri, 2 Feb 2007, Dan Faerch wrote:

> 1: Would allowing you to set the READ_HOSTS_NEVER_WRITE = 0 from config
> do the same for you? So ALL hosts defined are read & write hosts and
> switched between in a round robin manner.

I'm not sure, I didn't really look into DBIx. I don't really want to round
robin though; for any given instance of sqlgrey, I want it to use the same
database for all queries until a failure, and only switch to a different
one in the case of such a failure. I'm mostly interested in fault tolerance
at this point, not load balancing.


> 2: How scalable is your setup? We currently run 1 write-host and 7
> read-hosts. Can multi-master be used in such a setup? (a little off
> topic i guess;)

I believe you can scale to higher numbers of multimaster servers, but if I
remember correctly it is not a full mesh but they end up replicating
circularly so the failure of any one breaks the replication chain.


> If we need to specify more than 1 "write" hosts, would there be any
> logic in simply allowing multiple hosts to be defined on "db_host"
> instead (comma separated)? Just so we dont need an extra config
> directive to do the job that an existing directive could do.

That works for me, I just didn't want to change existing functionality.
Given that the degenerative case of setting db_hostlist to just one
instance is exactly the same as using db_host, changing the definition of
that configuration variable to accept a list does seem the simpler thing to
do.


-- 
Paul B. Henson  |  (909) 979-6361  |  http://www.csupomona.edu/~henson/
Operating Systems and Network Analyst  |  he...@cs...
California State Polytechnic University  |  Pomona CA 91768