rant

[abecx]

I've been doing SQL administration for a long time. I increase performance through query optimization, hardware, configuration settings, data aggregation, storage types, and many other methods that i have learned through practical implementation and reading more books about performance optimization than women I've slept with.

I run into people often that believe InnoDB is faster ( its not ), that MyISAM is obsolete ( its not ), that their queries are fine ( they arent ), or that its the hardwares problem ( its not ).

I hear ridiculous things like "We need to use redis!".

Redis is a non relational data store that is only 'faster' because it stores everything in ram. What many developers ( and sysAdmins for that matter ) do not understand is that MySQL does the same thing via the operating systems disk cache or through MySQL layer buffer settings with InnoDB or key buffers with MyISAM. MariaDB has Cassandra for a key/value store if you really need relationless data store, but having it stored in memory is 50 layers of ridiculous unless you're ok with volatile data.

"Why arent we using hadoop!"

Hadoop is not a relational database, its also far slower than MySQL as a reporting layer. They are NOT competing platforms. Hadoop is used for long term original format ( or dim/fact ) storage of your data. You typically use Hadoop to aggregate your click streams into into a reporting layer or directly into your Analytics platform for unmolested searching. Hadoop is not a performance solution, it is a storage and aggregate solution.

"Why arent we using a netezza!"

Netezza is a relational database that uses FPGA's programmed by your sql queries. It sieves your data in its entirety through its SPU's for every query. Netezza is blazing fast for analytics, especially if you are used for your queries taking hours to run, they will finish in minutes on Netzza. It is however slow for reporting because it does not use indexes. You should never implement it for user/customer facing data, only for business analytics.

Dont even get me started on docStore databases.

Now for my rant, it pisses me off the I have to have this conversation with every single group of developers that I meet. They have absolutely no idea how their queries impact performance. I can not fix every query en mass because I have to reverse engineer their logic to figure out what they are trying to truly ascertain.

Typically the best result is through aggregating their data before hand, aka putting the data in tables as they would via their group by queries with the joined data already there.

for instance select blah from ( select blah from fagits join on shit.loot = fagits.blah group by idiots, noobs, dorks ) a join on a gooks.id = a.blah order by noty

I would fix this query by analyzing what it does. It requires two subqueries to run before it can finally order the data by noty. I would take the two subqueries and create a new table with that aggregate data, and during our ETL process aggregate it to the new table to maintain consistancy. Now when they run the query, they can simply do select blah from newTable order by noty, with an index on that table, the response would be instant, versus minutes before.

It blows my mind that people think the solution is more hardware, more cpu, more ram, faster disks without understanding that they are using MySQL to aggregate volumes of data for reporting which is like using a vacuum cleaner to mow the yard.

Anyway, I am off to buy 8 SSD's and to overclock a server because you can only scale a CPU horizontally so much and its been requested that I just do it.

[Mach1]

hell yeah brother!

[mstng86]

So, you most definitely read at least 1 book.

[Craizie]

I like turtles.

[Baron Von Crowder]

[Mach1]

your gook join is racist

[ceyko]

Don't educate too much, helps keep business like the one I work for going.

Also, don't worry if it's not the HW being blamed it's the network, firewall....something else. Can't show them logs of a 1Gbps link being .13 utilized and they'll demand a 10Gbps link...or 40 or 80.

Of course that is a good idea, and lets implement end to end QoS...etc..etc.

[abecx]

That was the first problem, too much IO over the 1GB link. I fixed that by adding more memory so the files would be stored and manipulated in ram and only flush updates would go over NFS, network went from 100% to 1% utilization, then the problem was the amount of data they were sorting. There is nothing I can quickly do to fix that besides creating indexes, but since they are creating temporary tables with their group by and join statements, there is nothing for me to index since those tables dont exist until the queries are run. Since MySQL is single threaded per query, the only way to make filesort faster is to increase CPU frequency.

I was asked to max out the machine, so I am going to max out the sata 3 bus followed by maxing out the CPU.

I dont mind educating, I hate working on the same problems over and over again. As well, if our developers can work smarter it saves us money because we need less. But most importantly I am trying to educate since now we are at the pinnacle of what we can throw at this hardware wise without restructuring our data.

That is my second quarter 2016 task, redo our entire data warehouse. Lucky me!

[FunFordCobra]

I skipped a sentence or two but I did not recognize any of that. I wish I did and almost feel like looking it up but it will blow my high. I wana get into IT but it looks like I'm too far off.

[abecx]

I tried to be as basic as I could, I do not want to come off as pretentious or better because I'm not better than anyone else can be at this stuff.

I have 15 years of experience that I consolidated into what I said above, this field needs better people in it that are willing to skip the bullshit, and to actually provide a real answer and not just hearsay. The vast majority of my time is spent separating myth from fact and finding the root cause so things work.

If you like investigating, I would do it. I am not sure what my field is, probably devOps mostly, but everything above was DBA related.

[Silverback]

My entire IT career, including working for Microsoft troubleshooting performance issues, has taught me that if someone can write bad code/queries then they will.

[Torinoman]

Now I know how you afford that "can install and tune"

[FunFordCobra]

Hey man if u think I think that your better then me because of some fancy words, you'd probably be right.
Train me in the ways obi wan.

[Unicorn Jeff]

Our sweepstakes systems at my work mainly run off MySQL. Been wanting to learn more about MySQL, and how it works. Seems like you really now your shit. You looking for work or anything? We have a side development firm working on some other stuff that might be looking for someone.

[abecx]

Yeah, I do side work but I charge $100 an hour. You get what you pay for with me, I dont bullshit.