Re: HAProxy proxying JDBC requests two a percona cluster
Hi! > If it's not too much trouble maybe you could send a short message to haproxy > list describing how you fixed the problem. This might help someone else who > reads the list archives. Yes, Jarno. You're absolutely right. The main problem of this was the enormous default idle timeout of Hybris - 50 minutes. Once I configure all timeouts (timeout connect, timeout client, timeout server. Though I'm actually not sure, if I really needed ALL timeouts) to reflect that, everything worked fine. Thanks again for the help! Kind regards Dennis -- Dennis Plöger Systemadministrator und Berater -- Besuchen Sie uns auf unseren nächsten Veranstaltungen: - J.Boye Web- und Intranetkonferenz, 04.-06.11.2014, Aarhus - konaktiva, 11.11.2014, Halle 3 B / Stand G4 (Westfalenhallen), Dortmund -- getit - Gesellschaft für Technologie- und Informationstransfer mbH Emil-Figge-Straße 76-80 44227 Dortmund Tel: +49.231.9742.7846 Fax: +49.231.9742.356 http://www.getit.de Amtsgericht Dortmund, HRB-Nr. 26548 Geschäftsführung: Dr. Thomas Krämerkämper (Vorsitzender) Christoph Hecker Dietmar Müller Leonardo Musso
Re: HAProxy proxying JDBC requests two a percona cluster
Hello! > Oh. Thanks for mentioning that page (I somehow never got there). I now saw, > that > the connections are dropped with "cD" - meaning, that HAproxy thought, the > server would be down. Getting back on that. I understand, that "D" means that HAProxy thinks, the server is down. Shouldn't hatop show something then that indicates a downtime on the server? Because hatop shows nothing in that direction. All's up, no downtime. Kind regards Dennis -- Dennis Plöger Systemadministrator und Berater -- Besuchen Sie uns auf unseren nächsten Veranstaltungen: - konaktiva, 11.11.2014, Halle 3 B / Stand G4 (Westfalenhallen), Dortmund -- getit - Gesellschaft für Technologie- und Informationstransfer mbH Emil-Figge-Straße 76-80 44227 Dortmund Tel: +49.231.9742.355 Fax: +49.231.9742.356 http://www.getit.de Amtsgericht Dortmund, HRB-Nr. 26548 Geschäftsführung: Dr. Thomas Krämerkämper (Vorsitzender) Christoph Hecker
Re: HAProxy proxying JDBC requests two a percona cluster
Hi! > Maybe try with "no option dontlognull" ? > Are all connections in logs logged with "normal --" session state ? > http://cbonte.github.io/haproxy-dconv/configuration-1.5.html#8.5 Oh. Thanks for mentioning that page (I somehow never got there). I now saw, that the connections are dropped with "cD" - meaning, that HAproxy thought, the server would be down. Could that be something in conjunction with the Percona-check on port 9200? Kind regards Dennis -- Dennis Plöger Systemadministrator und Berater -- Besuchen Sie uns auf unseren nächsten Veranstaltungen: - konaktiva, 11.11.2014, Halle 3 B / Stand G4 (Westfalenhallen), Dortmund -- getit - Gesellschaft für Technologie- und Informationstransfer mbH Emil-Figge-Straße 76-80 44227 Dortmund Tel: +49.231.9742.355 Fax: +49.231.9742.356 http://www.getit.de Amtsgericht Dortmund, HRB-Nr. 26548 Geschäftsführung: Dr. Thomas Krämerkämper (Vorsitzender) Christoph Hecker
Re: HAProxy proxying JDBC requests two a percona cluster
Hi, On Wed, Oct 15, Dennis Plöger | getit GmbH wrote: > Hi! > > > Hmm... I just understood, that we're probably talking about different things > > here. As you can see in my configuration, I haven't specify a maxconn value. > > From the configuration documentation I didn't understand, what the default > > value is. I just updated my configuration based on this tutorial and will > > try > > again: > > http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/haproxy.html > > Same error (nothing in the logs however). My haproxy.cfg now looks like this: > option dontlognull Are you using any kind of connection pool on hybis/jdbc ? (Hybris opens/keeps open multiple connections and reuses the open connection). Does the connection pool do any connection checking (periodically checks that the connection is still alive) ? (Maybe pool opens connections that sit idle for too long -> haproxy closes idle connection (50s?)) Maybe try with "no option dontlognull" ? Are all connections in logs logged with "normal --" session state ? http://cbonte.github.io/haproxy-dconv/configuration-1.5.html#8.5 -Jarno > retries 3 > option redispatch > maxconn 2000 > timeout connect 5 > timeout client 5 > timeout server 5 > > listen mysql-cluster 127.0.0.1:3306 > mode tcp > balance roundrobin > option httpchk > server shopclusterdb1 10.10.42.82:3306 check port 9200 inter 12000 > rise 3 fall 3 > server shopclusterdb2 10.10.42.83:3306 check port 9200 inter 12000 > rise 3 fall 3 backup > > Kind regards > > Dennis > > -- > Dennis Plöger > Systemadministrator und Berater > > -- > Besuchen Sie uns auf unseren nächsten Veranstaltungen: > - konaktiva, 11.11.2014, Halle 3 B / Stand G4 (Westfalenhallen), Dortmund > -- > > getit - Gesellschaft für Technologie- und Informationstransfer mbH > Emil-Figge-Straße 76-80 > 44227 Dortmund > > Tel: +49.231.9742.355 > Fax: +49.231.9742.356 > http://www.getit.de > > Amtsgericht Dortmund, HRB-Nr. 26548 > > Geschäftsführung: > Dr. Thomas Krämerkämper (Vorsitzender) > Christoph Hecker -- Jarno Huuskonen
Re: HAProxy proxying JDBC requests two a percona cluster
Hi! > Hmm... I just understood, that we're probably talking about different things > here. As you can see in my configuration, I haven't specify a maxconn value. > From the configuration documentation I didn't understand, what the default > value is. I just updated my configuration based on this tutorial and will try > again: > http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/haproxy.html Same error (nothing in the logs however). My haproxy.cfg now looks like this: global log /dev/log local1 log /dev/log local0 debug chroot /var/lib/haproxy maxconn 4096 user haproxy group haproxy daemon stats socket /var/run/haproxy.sock mode 600 level admin stats timeout 2m defaults log global modehttp option tcplog option dontlognull retries 3 option redispatch maxconn 2000 timeout connect 5 timeout client 5 timeout server 5 listen mysql-cluster 127.0.0.1:3306 mode tcp balance roundrobin option httpchk server shopclusterdb1 10.10.42.82:3306 check port 9200 inter 12000 rise 3 fall 3 server shopclusterdb2 10.10.42.83:3306 check port 9200 inter 12000 rise 3 fall 3 backup Kind regards Dennis -- Dennis Plöger Systemadministrator und Berater -- Besuchen Sie uns auf unseren nächsten Veranstaltungen: - konaktiva, 11.11.2014, Halle 3 B / Stand G4 (Westfalenhallen), Dortmund -- getit - Gesellschaft für Technologie- und Informationstransfer mbH Emil-Figge-Straße 76-80 44227 Dortmund Tel: +49.231.9742.355 Fax: +49.231.9742.356 http://www.getit.de Amtsgericht Dortmund, HRB-Nr. 26548 Geschäftsführung: Dr. Thomas Krämerkämper (Vorsitzender) Christoph Hecker
Re: HAProxy proxying JDBC requests two a percona cluster
Hi! Me again. > Kinda (see my haproxy.cfg below). You're right. I will check out the logging > options and optimize logging. I just checked the configuration options again and saw, that I'm already logging enough (I think). However, no errors are logged. >> What kind of server / client timeouts do you use in haproxy or are >> you reaching maxconn etc. limits ? > > I don't think, I reach maxconn, because the maximum connections HATop shows > are > 23 and my limit is around 2000. Hmm... I just understood, that we're probably talking about different things here. As you can see in my configuration, I haven't specify a maxconn value. From the configuration documentation I didn't understand, what the default value is. I just updated my configuration based on this tutorial and will try again: http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/haproxy.html Kind regards Dennis
Re: HAProxy proxying JDBC requests two a percona cluster
Hi! Thanks for answering. > (You might consider running garbd arbitrator on a third server (or a > third node), because if the two pxc nodes loose connection between each other > -> split brain and both nodes won't work > http://www.percona.com/blog/2012/07/25/percona-xtradb-cluster-failure-scenarios-with-only-2-nodes/) Yes, I am already planning this. > Do you get any errors if you use direct jdbc connection from hybris to > your PXC nodes (no haproxy in between) ? No. Because of this and my tests with the Active/Passive-setup, I already ruled out the Percona cluster. > Have you enabled logging in haproxy and do you get any errors in > haproxy logs ? Kinda (see my haproxy.cfg below). You're right. I will check out the logging options and optimize logging. > What kind of server / client timeouts do you use in haproxy or are > you reaching maxconn etc. limits ? I don't think, I reach maxconn, because the maximum connections HATop shows are 23 and my limit is around 2000. Here's my haproxy.cfg btw: global log /dev/loglocal0 log /dev/loglocal1 notice chroot /var/lib/haproxy user haproxy group haproxy daemon stats socket /var/run/haproxy.sock mode 600 level admin stats timeout 2m defaults log global modehttp option tcplog option dontlognull retries 3 option redispatch timeout connect 5 timeout client 5 timeout server 5 errorfile 400 /etc/haproxy/errors/400.http errorfile 403 /etc/haproxy/errors/403.http errorfile 408 /etc/haproxy/errors/408.http errorfile 500 /etc/haproxy/errors/500.http errorfile 502 /etc/haproxy/errors/502.http errorfile 503 /etc/haproxy/errors/503.http errorfile 504 /etc/haproxy/errors/504.http listen mysql-cluster 127.0.0.1:3306 mode tcp balance roundrobin option httpchk server shopclusterdb1 10.10.42.82:3306 check port 9200 inter 12000 rise 3 fall 3 server shopclusterdb2 10.10.42.83:3306 check port 9200 inter 12000 rise 3 fall 3 backup Kind regards Dennis -- Dennis Plöger Systemadministrator und Berater -- Besuchen Sie uns auf unseren nächsten Veranstaltungen: - HartmanEVENT 2014, 09.10.2014, Utrecht, Niederlande -- getit - Gesellschaft für Technologie- und Informationstransfer mbH Emil-Figge-Straße 76-80 44227 Dortmund Tel: +49.231.9742.355 Fax: +49.231.9742.356 http://www.getit.de Amtsgericht Dortmund, HRB-Nr. 26548 Geschäftsführung: Dr. Thomas Krämerkämper (Vorsitzender) Christoph Hecker
Re: HAProxy proxying JDBC requests two a percona cluster
Hi, On Tue, Oct 14, Dennis Plöger | getit GmbH wrote: > Hi there! > > I'm currently trying to create a completely high available setup for the > Hybris platform (http://hybris.com/). Hybris is a Spring-based framework for > e-commerce sites, that is running on Tomcat and connecting via a JDBC pool to > different databases, MySQL being one. > > So I've set up two MySQL nodes using a Percona cluster and HAProxy on the > application server node to proxy requests to one or both of the database > nodes. (I've tried Active/Active and Active/Passive setups) (You might consider running garbd arbitrator on a third server (or a third node), because if the two pxc nodes loose connection between each other -> split brain and both nodes won't work http://www.percona.com/blog/2012/07/25/percona-xtradb-cluster-failure-scenarios-with-only-2-nodes/) > Everything works quite fine, but during the high-load initialization process, > the JDBC connections in the pool somehow seem to timeout or loose connection > to the databse. These exceptions pop up: Do you get any errors if you use direct jdbc connection from hybris to your PXC nodes (no haproxy in between) ? Have you enabled logging in haproxy and do you get any errors in haproxy logs ? > INFO | jvm 1| main| 2014/10/14 15:59:45.362 | ... 4 more > INFO | jvm 1| main| 2014/10/14 15:59:45.362 | Caused by: > java.io.EOFException: Can not read response from server. Expected to read 4 > bytes, read 0 bytes before connection was unexpectedly lost. > INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at > com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914) > INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at > com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3337) > INFO | jvm 1| main| 2014/10/14 15:59:45.362 | ... 35 more What kind of server / client timeouts do you use in haproxy or are you reaching maxconn etc. limits ? -Jarno > I've been using version 1.4 from the Ubuntu repository and already updated to > current 1.5, in which these exceptions come at a later point during the > intialization process, however they still exist. > > Can anybody help me here? How can I better debug this? Do you need any more > data to help? > > Thank you in advance. > > Kind regards > Dennis > -- Jarno Huuskonen
HAProxy proxying JDBC requests two a percona cluster
Hi there! I'm currently trying to create a completely high available setup for the Hybris platform (http://hybris.com/). Hybris is a Spring-based framework for e-commerce sites, that is running on Tomcat and connecting via a JDBC pool to different databases, MySQL being one. So I've set up two MySQL nodes using a Percona cluster and HAProxy on the application server node to proxy requests to one or both of the database nodes. (I've tried Active/Active and Active/Passive setups) Everything works quite fine, but during the high-load initialization process, the JDBC connections in the pool somehow seem to timeout or loose connection to the databse. These exceptions pop up: INFO | jvm 1| main| 2014/10/14 15:59:45.361 | The last packet successfully received from the server was 59.601 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago. INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at sun.reflect.GeneratedConstructorAccessor430.newInstance(Unknown Source) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at java.lang.reflect.Constructor.newInstance(Constructor.java:526) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3427) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3327) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4842) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at de.hybris.platform.jdbcwrapper.ConnectionImpl.doSetAutoCommit(ConnectionImpl.java:431) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at de.hybris.platform.jdbcwrapper.ConnectionImpl.setTxBoundUserTA(ConnectionImpl.java:228) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at de.hybris.platform.tx.Transaction.bindConnection(Transaction.java:578) INFO | jvm 1| main| 2014/10/14 15:59:45.361 | at de.hybris.platform.tx.Transaction.beginOuter(Transaction.java:538) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.tx.Transaction.begin(Transaction.java:486) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.tx.Transaction.execute(Transaction.java:1190) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.tx.Transaction.execute(Transaction.java:1160) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.jalo.Item.setAllAttributes(Item.java:2059) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.jalo.Item.setAllAttributes(Item.java:2034) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.storeAttributes(ItemModelConverter.java:1402) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.save(ItemModelConverter.java:683) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.servicelayer.internal.model.impl.wrapper.ModelWrapper.save(ModelWrapper.java:336) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.servicelayer.internal.model.impl.ResolvingModelPersister.saveOthers(ResolvingModelPersister.java:64) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.servicelayer.internal.model.impl.ResolvingModelPersister.persist(ResolvingModelPersister.java:49) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.saveViaJalo(DefaultModelService.java:1059) INFO | jvm 1| main| 2014/10/14 15:59:45.362 | at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.doJaloPersistence
