This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2018-06-04
Channels
- # aleph (24)
- # beginners (60)
- # cider (20)
- # clara (4)
- # cljs-dev (13)
- # cljsrn (4)
- # clojure (66)
- # clojure-italy (32)
- # clojure-nl (4)
- # clojure-serbia (1)
- # clojure-uk (207)
- # clojurescript (115)
- # cursive (3)
- # datomic (36)
- # defnpodcast (1)
- # duct (15)
- # fulcro (14)
- # graphql (8)
- # lein-figwheel (1)
- # leiningen (4)
- # off-topic (140)
- # pedestal (40)
- # portkey (3)
- # reagent (40)
- # remote-jobs (1)
- # ring (11)
- # shadow-cljs (31)
- # spacemacs (6)
- # sql (65)
- # tools-deps (67)
- # yada (1)
I was considering something like HoneySQL but last time I used it I came to conclusion that overhead isn’t worth that. For me, obviously.
@jahson sidenote, performance hit warning: where x in (…) gets very inefficient for large sets of values. Mysql is not my thing, but after a general look around, people seem to recommend FIND_IN_SET
@lady3janepl I'm curious as to what you would consider a "large set" for MySQL?
@lady3janepl Thanks! AFAIK we have tuned mysql to handle pretty big lists, so it’s not an issue at the moment.
I haven’t tried this in Mysql. In postgres, I found it a good tradeoff to switch from in (?, ?, ?, …)
to in (SELECT UNNEST(ARRAY[?, ?, ?...]))
around umpteen ?s
We regularly do PK in
queries up to 10,000 and it's really fast. But we're on Percona so maybe that's why?
(the usual disclaimers apply about individual settings, databases, amount of data, and so on)
Maybe MySQL is more efficient than PostgreSQL in this case? (would be nice to find something that MySQL is better at than PG 🙂 )
(also wanted to have less question marks, but was blocked by the fact that we have to use an orm which blocked this option)
postgres converts where in (? ? ?)
to a sequence of where (x = ?) or (x = ?) or (x = ?)
. There are several ways to work around that, the one I liked the most is where in (select values(…))
- using a subquery.
afaict (again quick google, not used mysql for ages), mysql also performs the conversion to a sequence of or’s
It's still super-fast tho'
The big difference would be: 1) PK vs not PK 2) worst case scenario: whether you’re using the clause to find a row that almost always exists, or almost always doesn’t exist
Sometimes MySQL surprises me.
Ah yeah you'd only want to do this on a pk
(often we’re dependent on a release cycle, capacity, priorities and so on of other teams)
Anyway, that’s why it’s a performance hit warning, and not a categorical “you’re doing it wrong”: in many cases you won’t notice the problem, until you do. Asking about a large number of ??? triggered my spider senses 🙂
Yeah performance with MySQL can be a cliff: everything's great until suddenly it's not.
that makes sense 🙂 tbh I’ve found it to be the case in general: things perform well in conditions they were selected for, and once you cross the boundary you get into Unpredictable Side Effects territory
(Where sometimes the unpredictable side effect is “still works, slightly degraded”, sometimes “race condition heisenbugs” and sometimes “performance falls off the cliff”)
and yes, afair mysql can be more performant than postgresql, specifically when your operations are read-heavy (or read-exclusive) and use myisam
How has working with Percona been for you @seancorfield?
@lady3janepl Really good, to be honest. We have sufficient scale that we're kind of pushing the envelope with MySQL/Percona in some areas and their engineers have been really good at helping track down issues and provide fixes.
As for choosing MySQL in the first place... back in 2009, when we started the rewrite for v2 of our platform, we switched from SQL Server/Windows/IIS to MySQL/Linux/Apache as part of the infrastructure switch because that's where the new team had the expertise. Then we hired a DBA/infrastructure specialist and he just happened to specialize in MySQL/Percona so it continued to be a natural fit. No one on the team at any point had any experience with PostgreSQL so it just wasn't a choice for us.
Oh yeah, preexisting expertise is a very good reason, fine tuned tech > correct flavour of the day with default settings. I’m glad to hear it’s working out and well supported! Will keep Percona in mind as a good alternative.
And what makes it a good match for your business? I like what postgres does but don’t want to lock myself into a box of “I only know of one option”, hearing from people who stress test other systems is really interesting 🙂
afaik IN is translated into ANY internally anyway (I read that back then, not 100% certain of it)
then yes, you need to juggle a bit with interop to create an array parameter, but that's quite straightforward
don’t know about others, in my case the orm 🙂 (Doctrine hardcodes generating a query string/quoting for an array-of-values parameter at such a low level that it’s impossible to override in a reasonable way)
Doctrine as in the php Doctrine? I'm suprised that's still around. Doctrine was the first ORM I made serious money using.
crossposting from #clojure
man, I've come accross the exact same bug
jsut a couple days ago
thought I was going crazy but as I remembered this is called an Heisenbug, i kept calm I and gathered the force to overcome it.
How did you do it?
Hehe, I'm trying to remember.
Hm, I wonder - if I do something like, idk (if (not= [] result) (println 'insertion ok') nil), might that do it?
I think this is because of this : https://github.com/korma/Korma/blob/master/src/korma/db.clj#L277
It would "evaluate" the structure, I think
no, that didn't do it
Ahah !
here it is
interesting
I think doall consumes only the outer vector, and not the inner lazy seq coming from the variouys map
s
I didn't gave it much thought I just (╯°□°)╯︵ ┻━┻ out of my way
Well, good news - it works
Thanks!
@nnbosko You could just use mapv
instead of map
. mapv
is eager (and produces a vector). And you don't need doall
around the vector since vectors already are eager. So
[(mapv insert-a a)
(mapv insert-b b)
(mapv insert-c c)
(mapv insert-d d)]
-- also #(insert-a %)
is the same as just insert-a
(although perhaps you are paraphrasing your code and the actual function is not that simple?)