This might belong in #beginners but... What's the idiomatic way to copy over a table (for backup) from one database to another with constant memory usage (table fits neither in memory nor on disk)?

Maybe this helps http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html#processing-a-result-set-lazily

But.. if you don’t need to do it programmatically, I’d do just something like pg_dump my_first_db -t my_table | psql my_other_db

Assuming postgres

hey y’all…

I got a beginner question, honestly, the jdbc (mysql), how does it work when you use query? open con > open stt > read > close stt > close con??? Or it always maintain 1 connection open?

I’m really having problems with c3p0 or hikari because there’r TOO MUCH DBs to connect, pooling is not working since I cannot do 1 pool for all dbs

I've never seen a real app not use a connection pool like c3p0 or hikari

I am not sure what you mean by too much dbs

@hiredman its an ecommerce, so there’s more than 500 stores, and they are separated dbs

I know what you mean… c3p0 or hikari its really helpful, so mysql does not go crazy

i wonder if you need to rethink your architecture a little bit here

YEah @jgh we are thinking about remove the MySQL, but till there… trying to use clojure to do some stuffs

i mean im not sure mysql is your problem. Is having one application connect to all of your servers directly really the best way to do it?

all servers you mean all dbs?

like.. limit the project, connect from x to y, another connect from a to h

something like it?

I have no idea what you're trying to do so it's hard for me to make suggestions, but I suspect that there's a better way to do whatever it is you're trying to do than have a single clojure app connect to hundreds of databases.

database is sort of an overloaded term, it can mean sort of a namespace within a database server, or it can mean a database server itself, from "500 stores" my guess is your project started as a single store, and to make it multi-tenant database (in the namespace of tables sense) was added for each tenant instead of making the schema multi tenant in a single database

@hiredman its a in-company project, it was like that when I got hired, not much to do, and yeah.. schemas, multi-schemas

db-name

I think if I was in that situation, I might start by creating a pool of connection pools

because, I dunno, it seems like it should be a valid thing to do, but I think in general it is done the otherway, so most off the shelf solutions are going to fight you

I see… pool of connection pool… so would limit the connection pool itself, right?

“limit”

why do you need to limit it?

not limit it.. but not let it goes crazy and outofmemory

atm in having deadlock problem on c3p0

are you sure that is related to connection pooling?

APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!

Well.. I dunno java/c3p0 that well yet

I dont have this problem in dev, just in production

my guess would be you don't have enough connections (either allowed by the database, or made available by your connection pool)

here is the function to connet

connect**

Maybe its ALLL WRONG

such a noob at this, LOL

And the database (mysq) architeture does not help much

Its 1 mysql, with a lot of schemas, to try explain it better

And you have a different user/password for every one of the 500 DBs?

@seancorfield nope, just 1 user/pass

OK, and that user has permissions to read/write every DB schema?

yeah @seancorfield

If that's the case, I would use a single connection pool and use it everywhere for all of the DBs.

In other words, don't create a separate pool for each DB.

and how change db? or use another db, by query? select dbXX.tableXX?

Yes, use the schema name in your queries.

I see… gonna try that out @seancorfield

Thanks

Maybe with that I can even increase the pool siz

That's much more scalable (given the constraints you're already running under).

And, yes, you can have a decent sized pool, since you'll only have one of them for the entire app.

Thanks @seancorfield