I am by far no expert on MySQL. But I learned a lot from the guys from Percona and their MySQL Performance Blog. This post shall be going back to the basics and cover some concepts you should bare in mind, when you install a fresh MySQL server. If you just run a blog or a small forum, you probably do not need to change ANYTHING with your MySQL installation. But if you go for a larger set of data, some tweaking can do wonders in speed and stability.
MySQL 5.x, the older 4.x and basically all ready-for-install versions that come with the big Linux distributions is very stable already. If you have a crashing database on your hands and you do not know why, it is probably one of these 3 reasons:
- Memory issues: The total amount of memory the database can use is more than the RAM you have in your server. In rare cases this issue might kill your database randomly, when driving a server under load. You may want to check your config values with this nice perl script. The script needs some Perl modules that you might NOT have installed. It is worth doing it! Also faulty memory chips can be the reason. There are memtest tools that can help you, but I normally see that as the last resort, if all other potential reasons have been checked for!
- Drive problems: I just experienced a randomly crashing database on my new development server. After some tests I found my new SSD drive was the problem. It was creating false bytes while writing and I had to replace it. This barely happens and check for reason number 1 first.
- Real bug: There are real bugs. In a day-to-day environment of an ecommerce solution – especially with standard solutions out there – you will not bump into those. Real software bugs are for heavy users to discover only, at least in my experience.
MySQL Speed Optimization
A fast shop sells better. A fast website converts better in any form. That is just how it is and every millisecond counts. Some of these basic concepts are just to keep in mind:
- Primary key and Index columns: When you do a select on data – no matter how much data sets you have – it is faster when sorting and filtering by index and primary key columns.
- Numbers beat characters: Filtering data by BOOLEAN values or numbers instead of text is always faster. If you have a lot of color values for your articles or certain states (on stock, eg.) – do not filter by “select * from products where stock=’on stock'” – the “stock column” should be boolean for such cases! Think about your statements in that form!
- In memory beats the drive: You set caching values in your my.cnf to have as much indeces and primary keys in the RAM. Take the time and a calculator to determine, if these caches and indeces (a) CAN be held in RAM and (b) are NOT swapped on the drive. Means: making them too big is as bad as making them too small.
These are some basic principles you can revisit, if you are in doubt or having problems with your database. If you are looking for a speedy text search option, you might want to look at this post, I did yesterday.
More database posts will follow over time, back to the code!