Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Locked thread
Tiny Bug Child
Sep 11, 2004

Avoid Symmetry, Allow Complexity, Introduce Terror

qhat posted:

it's un loving believable the amount of hoops you have to jump through to get that DB to work well enough to be considered production ready. but hey i guess if you hate stored procedures and replication because you see those features as just unnecessary and for spergs only, then go loving nuts

you can do both of those things w MySQL and it's not hard

Adbot
ADBOT LOVES YOU

Captain Foo
May 11, 2004

we vibin'
we slidin'
we breathin'
we dyin'

Tiny Bug Child posted:

you can do both of those things w MySQL and it's not hard

Your expertise is in hard dicks not hard dbs

qhat
Jul 6, 2015


Tiny Bug Child posted:

you can do both of those things w MySQL and it's not hard

i already said you can do them but the way they are implemented in mysql is near totally worthless for production systems

Tiny Bug Child
Sep 11, 2004

Avoid Symmetry, Allow Complexity, Introduce Terror
our server guy set up replication so idk too much about it other than how to stop/start it when i gently caress it up but what's wrong with mysql stored procs. i use them p much every day and have never had a problem

DONT THREAD ON ME
Oct 1, 2002

by Nyc_Tattoo
Floss Finder

qhat posted:

i already said you can do them but the way they are implemented in mysql is near totally worthless for production systems

why is that? just wondering because we have a thing in maria db and it sucks rear end and we want to go to post gres but we wont be able to get `platinum support` from our massive ops team so we're sorta stuck with mysql unless we want to support the db ourselves (we do not)

i was going to try to make the db situation better with sprocs but if that's not an option ugh

refleks
Nov 21, 2006



can someone explain to me what the gently caress an olap cube is. please keep in mind im a non-technical woman or childe that doesnt touch computers for a living.

distortion park
Apr 25, 2011


refleks posted:

can someone explain to me what the gently caress an olap cube is. please keep in mind im a non-technical woman or childe that doesnt touch computers for a living.

it's like map of temperatrues, where you have distance x and distance y on different axis and in the cells you have the temperature values. Except maybe you also have height on the z axis and it represents temperature in volumes of the atmosphere, so now you have a cube. And maybe you also have an other axis which represents time, so now you have a hypercube showing temperatures in volumes of the atmosphere over time (don't try and visualise this). etc

Then there are loads of ways of extracting subsets of the information. You might only want to know temperature at 8km up, or maybe just how it changed over time at a specific point in time.

distortion park
Apr 25, 2011


you could also have another dimension for measures, eg pressure or humidity (and then all your previous values turn out to just have been in the "temperature" slice). obviously you can quickly end up with a lot of data as the total size of your table is the product of the lengths of the different dimensions

e: to be clear you will probably only have one dimension defining all the different measures

distortion park fucked around with this message at 11:51 on May 22, 2016

distortion park
Apr 25, 2011


I had to try and make a user interface to help non technical management and children make charts from any slice of an olap cube and failed miserably. we just hard code them now as no one else in the org understands it

qhat
Jul 6, 2015


MALE SHOEGAZE posted:

why is that? just wondering because we have a thing in maria db and it sucks rear end and we want to go to post gres but we wont be able to get `platinum support` from our massive ops team so we're sorta stuck with mysql unless we want to support the db ourselves (we do not)

i was going to try to make the db situation better with sprocs but if that's not an option ugh

easily the most glaring and severe problem with mysql replication is it's all done in a single thread on the replicas. basically when changes happen on the master they get written to what's called a binary log, and when a slave connects to the master two threads are set up, one that streams the log constantly from the master and the other which executes replication events contained within the log. the thread which executes the replication events from the log is what i mean by single threaded being a limitation. i suppose it's not a huge deal if your use case is fairly small with a low amount of writes and mostly reads, but if you have fucktons of writes coming in then it's a real possibility that your replication will not be able to keep up. ratio doesn't even really matter with large enough DBs, eventually writes are going to scale up. we used to use mysql at our company back in the earlier days and the way we got around it was building an application layer toolkit which literally replicates write queries out to two identical db hosts lol. yeah it's ugly, especially when one of the boxes goes out of service for whatever reason, but still more scalable than dogshit mysql replication. now we use SQL server mostly though and it's great.

also wrt stored procedures, when most people think of server side stored procedures, they think about the performance benefits of having the query plans generated and cached server-side waiting and ready for any lucky soul who wants to call it. no not with mysql though, query plans are generated every single time for each new connection, so it's kind of pointless for application that have a high volume of re-connections (like basically any web app that doesn't cache connection objects).

refleks
Nov 21, 2006



pointsofdata posted:

it's like map of temperatrues, where you have distance x and distance y on different axis and in the cells you have the temperature values. Except maybe you also have height on the z axis and it represents temperature in volumes of the atmosphere, so now you have a cube. And maybe you also have an other axis which represents time, so now you have a hypercube showing temperatures in volumes of the atmosphere over time (don't try and visualise this). etc

Then there are loads of ways of extracting subsets of the information. You might only want to know temperature at 8km up, or maybe just how it changed over time at a specific point in time.

that sounds like a hosed thought from someone who looked at pivot tables in Excel and thought: "This, but only more insane"

Deep Dish Fuckfest
Sep 6, 2006

Advanced
Computer Touching


Toilet Rascal
yeah someone should pass a law that forces the universe to make all values depend on at most two variables. would solve this whole thing right quick

the talent deficit
Dec 20, 2003

self-deprecation is a very british trait, and problems can arise when the british attempt to do so with a foreign culture





refleks posted:

can someone explain to me what the gently caress an olap cube is. please keep in mind im a non-technical woman or childe that doesnt touch computers for a living.

most sql dbs are oltp (on line transaction processing) which means they are optimized for inserting, updating and retrieving rows. you can do joins to do complex queries across tables but they are never as fast as just retrieving rows via indexes already defined

olap (on line analytical processing) dbs are optimized instead for those complex queries where you want to see all users that clicked on a certain link, all links on your page they clicked, the times they clicked them and you want it all grouped by the referrer and maybe by the region of the planet they were located in or something. have fun making that query fast with a normalized schema

in practice this means refactoring your db to use star schemas and fact tables, using a column store like vertica or redshift or building an olap cube

an olap cube is a database that precomputes denormalized rows containing all attributes you might want to query on. so instead of multiple joins and self joins you just do a boring query on a single table with a bunch of indexable WHERE clauses

the talent deficit fucked around with this message at 17:45 on May 22, 2016

distortion park
Apr 25, 2011


refleks posted:

that sounds like a hosed thought from someone who looked at pivot tables in Excel and thought: "This, but only more insane"

more or less, yeah. it's a very useful way of looking at some datasets though though!

Moo Cowabunga
Jun 15, 2009

[Office Worker.




BooLoo posted:

it's still very boring though.

agreed

Moo Cowabunga
Jun 15, 2009

[Office Worker.




I know how to HTML is that like sql?

qhat
Jul 6, 2015


Displeased Moo Cow posted:

I know how to HTML is that like sql?

how about i inner join your face with a table

Moo Cowabunga
Jun 15, 2009

[Office Worker.




wat

Moo Cowabunga
Jun 15, 2009

[Office Worker.




I will fight you

Moo Cowabunga
Jun 15, 2009

[Office Worker.




your dreams and pride will be crushed

Moo Cowabunga
Jun 15, 2009

[Office Worker.




you will be no match for my number 8 wire and a fence post

spankmeister
Jun 15, 2008






7 pages and nobody posted http://howfuckedismydatabase.com ?

spankmeister
Jun 15, 2008






also if you absolutely need to do an mysql then percona is a better choice than oracle mysql and now mariadb has caught up as well with 5.6


But just use postgres instead

Moo Cowabunga
Jun 15, 2009

[Office Worker.




OK

cowboy beepboop
Feb 24, 2001

i had some troubles with a percona cluster today, because selinux was enabled. the official advice from percona was "just disable selinux lol". php level garbage.

Captain Foo
May 11, 2004

we vibin'
we slidin'
we breathin'
we dyin'

pointsofdata posted:

it's like map of temperatrues, where you have distance x and distance y on different axis and in the cells you have the temperature values. Except maybe you also have height on the z axis and it represents temperature in volumes of the atmosphere, so now you have a cube. And maybe you also have an other axis which represents time, so now you have a hypercube showing temperatures in volumes of the atmosphere over time (don't try and visualise this). etc

Then there are loads of ways of extracting subsets of the information. You might only want to know temperature at 8km up, or maybe just how it changed over time at a specific point in time.

this sounds insane but also cool

qhat
Jul 6, 2015



lol i read that article about the nuclear material and saw "SQL Server 6.5" and thought god dang that's old as gently caress, but then i realised the article was written 14 years ago

qhat
Jul 6, 2015


a guy at our company recently was trying to connect to SQL Server 2014 servers via multisubnet failover + alwayson using some ancient driver for SQL Server 6.0 but for some reason kept insulting people who recommended he not use a driver from the loving mid-nineties. maybe he's a poster here

refleks
Nov 21, 2006



the talent deficit posted:

most sql dbs are oltp (on line transaction processing) which means they are optimized for inserting, updating and retrieving rows. you can do joins to do complex queries across tables but they are never as fast as just retrieving rows via indexes already defined

olap (on line analytical processing) dbs are optimized instead for those complex queries where you want to see all users that clicked on a certain link, all links on your page they clicked, the times they clicked them and you want it all grouped by the referrer and maybe by the region of the planet they were located in or something. have fun making that query fast with a normalized schema

in practice this means refactoring your db to use star schemas and fact tables, using a column store like vertica or redshift or building an olap cube

an olap cube is a database that precomputes denormalized rows containing all attributes you might want to query on. so instead of multiple joins and self joins you just do a boring query on a single table with a bunch of indexable WHERE clauses

But cant the same things be achieved with views in SQL, and GROUP CLAUSES, or are those still inefficient?

foxy boxing babe
Jan 17, 2010



Lysidas
Jul 26, 2002

John Diefenbaker is a madman who thinks he's John Diefenbaker.
Pillbug

lol, i remember this

↳ Oracle

quote:

That depends.

How much money do you have?
↳ Less than a million

quote:

It might work!

Warning: oci_connect(): ORA-$$$$: Insert coin to continue

Asymmetric POSTer
Aug 17, 2005

my stepdads beer posted:

i had some troubles with a percona cluster today, because selinux was enabled. the official advice from percona was "just disable selinux lol". php level garbage.

lol

Valeyard
Mar 30, 2012


Grimey Drawer

St Evan Echoes posted:

its actually real good aside from a few weird design decisions like no boolean/bit datatype

unsurprisingly, Django does not really work well with Oracle

qhat
Jul 6, 2015


The oracle one is vgood

the talent deficit
Dec 20, 2003

self-deprecation is a very british trait, and problems can arise when the british attempt to do so with a foreign culture





refleks posted:

But cant the same things be achieved with views in SQL, and GROUP CLAUSES, or are those still inefficient?

you can make all the same queries, they're just gonna be slow if you don't do the transformation to a cube or star schema

Captain Foo
May 11, 2004

we vibin'
we slidin'
we breathin'
we dyin'

the talent deficit posted:

you can make all the same queries, they're just gonna be slow if you don't do the transformation to a cube or star schema

Just lmao if u don't set up a token ring schema

spankmeister
Jun 15, 2008






Yo g wash pass that tuple

Breakfast All Day
Oct 21, 2004

gem from my annual survey of the festering shitheap of graph dbs and graph db creators and users

http://orientdbleaks.blogspot.com/

qhat
Jul 6, 2015


is there a good olap database that's open source?

Adbot
ADBOT LOVES YOU

the talent deficit
Dec 20, 2003

self-deprecation is a very british trait, and problems can arise when the british attempt to do so with a foreign culture





qhat posted:

is there a good olap database that's open source?

apache kylin, sort of

  • Locked thread