Reddit mentions of High Performance MySQL: Optimization, Backups, and Replication

The book all MySQL DBAs should read: http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287

As DBAs tend to do a lot of scripting/coding, its possible your friend has a favorite text editor/ide they use, you may want to get them a license for that. A video game (if they like them, which they likely do). Something that will keep the away from a computer.

Funny teeshirts: http://www.zazzle.com/mysql+tshirts (not saying these are all funny, but some may be)

For the lazy: https://www.amazon.com/_/dp/1449314287

The book is good but now a bit outdated.

There's always new technology to learn, especially if you're pursuing the "full stack engineer" title. Do you know all the details of browser performance? Familiar with http? TCP? TLS? Deep understanding of MySQL?

But really, technology receives too much attention, especially from junior developers. Most of the attributes of senior engineers are people-oriented, either in how you interact with others or the attitude you yourself take when approaching situations. Actually, I'll just stop here; go read that last link and listen to someone who tells it better than I can.

I don't recommend either of those books, try these instead:

High Performance MySQL: Optimization, Backups, and Replication by Baron Schwartz, http://amzn.com/1449314287

A Practical Guide to Linux Commands, Editors, and Shell Programming (3rd Edition) by Mark G. Sobell, http://amzn.com/013308504X

http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287

Probabil vine din state, nu-i genul care sa fie in stoc mare.

I buy books because they go into a lot more details, or often are written really well, and easy to follow. Also, it's really nice to read paper. Often I keep books in the office as it's a quick and reliable way to research a topic in-depth without scrolling through hundreds of shit articles on a particular (and even controversial subject).

I really recommend these:

High Performance MySQL: Optimization, Backups, and Replication - I've read snippets, but it's recommended a lot and very good for more advanced readers.

SQL Antipatterns: Avoiding the Pitfalls of Database Programming - VERY beginner friendly, easy to read, follow, provides real and common scenarios and explains the anti-pattern, it's problems, the reasons to sometime excuse their use, and solutions. I love this book.

The Go Programming Language - Very good read, not TOO technical jargon, very nice to read, explains in depth and in an understandable way.

I've had plenty more over the years, but these are my current I have at home. Still more on order. :)

The other posters have some great suggestions. Ditch the Unique index on ID but keep the Primary index, ditch the "searchloc" and "searchchunk" indexes in their current form, I don't think they will be working the way you think they will be.

Have you tried benchmarking the types of queries you want to use? Try filling the table with a few million random records and then run the types of queries you'll be using in your application. Then experiment with different indexes and see what works and what doesn't.

If you could give us a few examples of the queries you'll be using then it would be easier to suggest appropriate indexes.

Another thing you might want to consider is making the ID column an unsigned integer. I'm guessing you'll start the numbers in that column at zero and go up from there. You're wasting lots of space by allowing for negative numbers in that column and not using them.

The others are suggesting using InnoDB. Personally I think MyISAM is still useful in some applications, but whatever table type you choose, decide based on your application requirements, storage engine strengths and results of your benchmarking if appropriate. Don't just pick one and hope for the best.

One type of index you may want to consider would be a spatial index. You'd need to combing the x and y columns into the spatial column. Again, this suggestion depends entirely on the types of queries you plan to make. I believe InnoDB has just introduced spatial columns in a recent MySQL release, the MyISAM storage engine has that capability for some time.

If you only plan to query by 16x16 blocks then make a separate map of all the 16x16 chunks and give each of them a unique ID. Then add an extra column to your heatmap table called "chunkID" or whatever and index that. You may see much better performance if your index covers only one column. You can create a spatial index of the centroids and extent of all the 16x16 chunks if you need greater versatility or need to select groups of these chunks at a time.

Do you really need to index the "z" column at all? will you ever be querying things like "select foo from heatmap where z>150;"? If your where clauses won't include "z" then exclude it from your indexes. Again, it's hard to make suggestions like this without knowing what type of queries you plan to run.

One book that I found very useful for this sort of thing is this -> http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287 I got a lot out of it and saw huge performance increases after implementing some of the suggestions in that book.

The reason I'm saying all this is that I was recently in a situation where I had to manage similar data to what you describe, but my datasets had several hundred billion rows. My first pass was like your suggestion above, but it was useless above about 10 million rows and ground things to a halt. I realised the the indexes really did nothing when I did some benchmarking to find out why the queries were running slowly. My second pass used spatial indexes on the x,y column but the size of the index became way too big when the database grew to hundreds of millions of rows and I had to abandon that approach too. Eventually I partitioned the points into tiles, and made one table for each tile of points, then stored the extent of the tiles in a separate table and indexed the tile locations and extents using spatial indexes. I actually ended up with no indexes at all on the points tables at all (this was the surprising result of a lot of benchmarking). I found that MySQL was loading and unloading indexes in and out of memory so often that it was affecting disk performance. My database had hundreds of thousands of tables, but that didn't seem to affect performance, in fact it seemed to be optimal for our application. Your mileage may vary of course.

I learned a lot about it from reading this guy:

http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287/ref=sr_1_1?ie=UTF8&qid=1407623587&sr=8-1&keywords=high+performance+mysql

But things like normalization and denormalization are foundational to building the table schemas right from the get-go. I don't remember if the book covers it, and I don't have it on hand to check. You can google search that stuff easily, though.

When you get to a project larger than a small business or personal website it's less about the language and more about imposing a structure that makes sense backed by a data model that's thought out and performant.

I recommend these books:

http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287/ref=sr_1_1?ie=UTF8&qid=1425831227&sr=8-1&keywords=high+performance+mysql
http://www.amazon.com/gp/product/0321127420/ref=oh_aui_detailpage_o05_s00?ie=UTF8&psc=1

Lots of other people have commented on specific MyISAM or InnoDB tuning options, but I haven't seen anyone post resources other than MySQL Manual or mysqlperformanceblog.com. I strongly encourage you to get the book High Performance MySQL (it is in 3rd edition). It is packed with real world examples and in-depth discussion of specific tuning models and various ways of re-architecting your database infrastructure.

Regarding your stall diagnosis, I recommend you look at the pt-stalk tool from Percona Toolkit -- among the 30 or so data points it collects, one is the output of SHOW ENGINE INNODB STATUS which will help you understand if there are any queries waiting on locks, or mutexes held, etc. Using pt-sift against the the stalk collection also provides a very simple overview of that collection period.

If you have a hard time understanding the output from SHOW ENGINE INNODB STATUS, or want a second opinion, contacting a company such as Percona is a viable option -- you could buy as little as 1hr of a Consultant's time to review your pt-stalk collection and talk about options for improving performance.

In general though: if you don't know what a variable does, don't change it. Defaults are normally sane. Perhaps start with a configuration generated from Percona Online Tools and then adjust variables as needed, specific to your workload and hardware.

Disclaimer: I work for Percona.

One of the best/quickest/cheapest resources is http://use-the-index-luke.com/

>SQL indexing is the most effective SQL tuning method and requires the same care as schema design. Yet it is neglected during development. Use The Index, Luke explains SQL indexing from the source code perspective—covering ORM tools like Hibernate or Doctrine.

SQL indexing and it's fundamentals allow for queries to be fast even with millions/billions of records. If the queries at your work are large they likely take advantage of foreign keys and subselects to which the fundamentals of SQL indexes will be of great assistance.

Additionally, if you don't already have it, but it -- High Performance MySQL I've read this book numerous times and keep it and the 2nd edition by my desk for reference.

Certs are there to show that you have a good grasp of certain concepts, not as learning tools.

Something like O'Reilly's High-Performance MySQL book would be a great way to get the knowledge part down, but nothing beats hands-on experience.

I was kind of thrown into MySQL for trial by fire. The company that I worked at as an Operations Engineer needed someone to focus on the databases. I volunteered and, through learning as I went, I picked up a lot.

I recommend a few things. First of all, pick up High Performance MySQL. It is a great read and will give you some very useful insights as to how MySQL operates internally. I think I have 4 or 5 copies sitting around the house.

Next, download the Sakila database. Poke around in there and see what you can find. Google "Sakila Database Exercises" and I'm sure you will find a lot of exercises to do on that dataset.

Personally, I learn best two ways. First, by trial and error. I have to get my hands in a system and try things to see what works, how it works and so on. Next, I learn from other people. Don't be afraid to google and ask about things. There are TONS of resources out there to help people learn mysql.