Fork me on GitHub
#sql
<
2018-06-04
>
jahson05:06:40

Thank you, guys!

jahson05:06:28

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.

jahson05:06:23

Yeah, I saw walkable some time ago and it is on my Try list.

jahson05:06:00

Unfortunately it is mysql in my case.

3Jane07:06:46

@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

seancorfield07:06:39

@lady3janepl I'm curious as to what you would consider a "large set" for MySQL?

jahson07:06:24

@lady3janepl Thanks! AFAIK we have tuned mysql to handle pretty big lists, so it’s not an issue at the moment.

jahson07:06:33

pretty big lists = pretty big lists in WHERE x IN (...)

3Jane07:06:42

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

seancorfield07:06:57

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?

3Jane07:06:25

(the usual disclaimers apply about individual settings, databases, amount of data, and so on)

seancorfield07:06:40

Maybe MySQL is more efficient than PostgreSQL in this case? (would be nice to find something that MySQL is better at than PG 🙂 )

jahson07:06:47

Same stuff

3Jane07:06:17

(also wanted to have less question marks, but was blocked by the fact that we have to use an orm which blocked this option)

3Jane07:06:12

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.

3Jane07:06:39

afaict (again quick google, not used mysql for ages), mysql also performs the conversion to a sequence of or’s

seancorfield07:06:07

It's still super-fast tho'

3Jane07:06:13

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

seancorfield07:06:23

Sometimes MySQL surprises me.

3Jane07:06:29

(in my case it was a large amount of data, not PK, and row almost always didn’t exist)

seancorfield07:06:48

Ah yeah you'd only want to do this on a pk

3Jane07:06:06

you can’t always get what you want 🎶

4
3Jane07:06:50

(I work on a thing that smoothes out the rough edges between product layers)

3Jane07:06:39

(often we’re dependent on a release cycle, capacity, priorities and so on of other teams)

3Jane07:06:27

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 🙂

4
seancorfield07:06:23

Yeah performance with MySQL can be a cliff: everything's great until suddenly it's not.

3Jane07:06:03

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

3Jane07:06:58

(Where sometimes the unpredictable side effect is “still works, slightly degraded”, sometimes “race condition heisenbugs” and sometimes “performance falls off the cliff”)

3Jane07:06:08

roll d6 on random encounters, really

3Jane07:06:23

and yes, afair mysql can be more performant than postgresql, specifically when your operations are read-heavy (or read-exclusive) and use myisam

3Jane07:06:42

Facebook still run on mysql.

3Jane08:06:26

How has working with Percona been for you @seancorfield?

seancorfield22:06:46

@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.

seancorfield22:06:35

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.

3Jane07:06:39

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.

3Jane07:06:46

Thank you 🙂

3Jane08:06:12

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 🙂

mpenet12:06:19

just wondering why not using "SELECT * FROM foo WHERE id = ANY (?);" in postgresql?

mpenet12:06:53

afaik IN is translated into ANY internally anyway (I read that back then, not 100% certain of it)

mpenet12:06:07

then yes, you need to juggle a bit with interop to create an array parameter, but that's quite straightforward

3Jane13:06:35

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)

bja18:06:10

Doctrine as in the php Doctrine? I'm suprised that's still around. Doctrine was the first ORM I made serious money using.

3Jane07:06:07

yeah, still alive, improving design, tightly integrated with Symfony

Nicolas Boskovic19:06:38

crossposting from #clojure

tristefigure19:06:47

man, I've come accross the exact same bug

tristefigure19:06:55

jsut a couple days ago

tristefigure19:06:50

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.

Nicolas Boskovic19:06:30

How did you do it?

tristefigure19:06:23

Hehe, I'm trying to remember.

Nicolas Boskovic19:06:36

Hm, I wonder - if I do something like, idk (if (not= [] result) (println 'insertion ok') nil), might that do it?

Nicolas Boskovic19:06:58

It would "evaluate" the structure, I think

Nicolas Boskovic19:06:20

no, that didn't do it

tristefigure19:06:11

I think doall consumes only the outer vector, and not the inner lazy seq coming from the variouys maps

tristefigure19:06:06

I didn't gave it much thought I just (╯°□°)╯︵ ┻━┻ out of my way

Nicolas Boskovic19:06:39

Well, good news - it works

seancorfield22:06:48

@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?)