Reputation
Top tag
Next privilege 125 Rep.
Vote down
Badges
6
Newest
 Commentator
Impact
~20k people reached

  • 0 posts edited
  • 0 helpful flags
  • 8 votes cast
May
13
awarded  Commentator
Apr
12
answered SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
Apr
12
awarded  Student
Apr
11
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
Tx a lot Rick, you've been very helpful. In the least by giving me a new approach to this issue. I feel I have to start digging into parameters now. I've tuned the production machine using my accumulated knowledge and I didn't put that effort into these galera nodes (intentionally) yet. If I find it I'll make sure this info gets noted here. Nothing is really hitting those DB's to answer your Q's. It's 2:30 here now, chances are production is being backed up. It's been stable for 700 days. Perfect to compair with.
Apr
11
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
totally, I'm a command liner, so I work the mysql history, always the same query, but now the 2nd node it's not 5mins anymore. What is interesting is that on the same host, on an exact clone of this node (they are VM's) on the same xen host with galera not activated (cloned b4 that moment), it runs in 0.20 seconds, like the current production node.
Apr
11
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
I Tried this again on a 2nd node but this doesn't work there. It ends up around 1min 2 secs -twice- in a row. I cannot reproduce it.
Apr
11
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
this is insane. I switch the query cache back off and tried this query again and now this thing flies like on production. All I tried were QC ON, down to 40s . Then QC OFF and now I get : 950 rows in set (0.50 sec). This makes no sense. I looked at this for weeks and it always did the 5 minute run.
Apr
11
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
I tried switching the cache on (to the production values) on 1 node and now it takes about 40 seconds, just to test.
Apr
11
revised SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
table sizes update
Apr
11
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
I'll update the Q with the sizes. I recall that I read QC was better to be off on Galera. I have plenty of MariaDB years behind me but not much Galera. quite funny that it does runs a lot faster now. I would love to keep it on now :)
Apr
10
revised SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
add query cache feedback
Apr
10
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
It probably is bigger than that when all the fields come back. I'm sure the distinct doesn't help but I tried stripping and rewriting without much success as well. I might just bump up the cache just to test this. Maybe the table open cache can be upped too a bit, there are plenty of tables and there is a jenkins running against staging from time to time. But production is like 50 times more busy, so that should compensate.
Apr
10
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
rewritten as BETWEEN query on the date would probably speed both up. Personally I never write those greater / smaller than on date fields ever. But the query design I can only suggest and advice de coders which I always do.
Apr
10
comment SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
The difference is just time, production moves in time, but I can assure you that this happened right after import too. Anyway, the number of rows should not affect a query in that way at all, we are talking 5 minute difference here. It's not like there are millions more. I don't think the data is the cause. It's not really doing a lot either cpu/mem wise. the box has lot's of legroom left
Apr
10
revised SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
add cache settings.
Apr
10
revised SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
small cosmetic fix
Apr
10
revised SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
added 4408 characters in body
Apr
10
asked SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible
Oct
20
awarded  Teacher
Jan
12
revised Any better way out of MySQL InnoDB log “in the future”?
added conclusion after fix