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
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! 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
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! 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! 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! 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
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
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
