Fork me on GitHub
#sql
<
2018-09-24
>
jaihindhreddy06:09:59

How do you guys move old data from production to an archival location? Say data older than 1 year. Is this done in batches, or in a streaming fashion? Are there tools to do this?

donaldball14:09:14

When I used mysql/percona in production, we used partitioned tables for the tables that grew without bound and removed old partitions automatically to a data warehouse.

mark_d21:09:39

Given a connection or db-spec from clojure.java.jdbc, how can I validate that connection with a timeout? For example, I want to attempt to make a connection but if a connection cannot be established within 30 seconds then I want to return an error. I see that prepare-statement has a :timeout but get-connection does not. Clearly if the connection string is invalid or a connection cannot be made (because for example, the system is not on VPN) then the prepare-statement function is unreachable

tanzoniteblack22:09:21

@mark.davidson I do this by using a connection pool library, like https://github.com/brettwooldridge/HikariCP , which has a setting like connectionTimeout that sets this for you

tanzoniteblack22:09:40

There might be other ways to do this, but I believe they're all JDBC driver specific

mark_d22:09:16

Thank you @tanzoniteblack. I’ll take a look at HikariCP or other connection pools.

seancorfield22:09:04

+1 for connection pooling. We use c3p0 in production at work and we've been very happy with that.

seancorfield22:09:18

c3p0 periodically checks the DB is available by running a small query on a test table, so it can rotate bad connections out and stand up new ones.

tanzoniteblack22:09:29

we used to use c3p0 at Yummly, but switched to hikari a few years ago. Does everything we were using c3p0 for, except that it required less configuration to perform well and was simpler for me to understand (coincidentally, I was the one that migrated us)

tanzoniteblack22:09:40

nothing wrong with c3p0 in general, I just found hikaricp easier to use

shaun-mahood22:09:14

If anyone using HikariCP wants to put an example into http://clojure-doc.org/articles/ecosystem/java_jdbc/reusing_connections.html that would be fantastic

tanzoniteblack23:09:25

There's a clojure helper library for hikari that has documentation on how to use it with jdbc