MySQL Consulting – Being on your own
About half a year have passed since me and Vadim have left MySQL to do MySQL Consulting on our own. Bunch of people have been wondering about our experiences so I thought it would be worth to share it here.
Peter ZaitsevPeter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.
MySQL Error control changes
In MySQL 5.0 mainly error control was improved, such as strict mode was added to change famous MySQL behavior of cutting too large strings, too big numbers and allowing you to use dates such as February 31st. In one case however reverse change was done – in regards to storage engine initialization. Previously if you […]
Using index for ORDER BY vs restricting number of rows.
One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and there […]
Beware: key_buffer_size larger than 4G does not work
I was working with customer today which has MySQL on a system with some 64GB or RAM running MyISAM, so they set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size over 4GB in size is not really supported (checked with latest and greatest MySQL Enterprise 5.0.34). It works just fine […]
Getting use of Slave in MySQL Replication
MySQL Replication is asynchronous which causes problems if you would like to use MySQL Slave as it can contain stale data. It is true delay is often insignificant but in times of heavy load or in case you was running some heavy queries on the master which not take time to replicate to the slave […]
Content delivery system design mistakes
This week I helped dealing with performance problems (part MySQL related and part related to LAMP in general) of system which does quite a bit of content delivery, serving file downloads and images – something a lot of web sites need to do these days. There were quite a bit of mistakes in design for […]
Debugging Sleeping Connections with MySQL
Have you ever seen a connection in the SHOW PROCESSLIST output which is in “Sleep” state for a long time and you have no idea why this would happen? MySQL Sleep I see if frequently with web applications and it is often indication of trouble. Not only it means you may run out of MySQL […]
Why do you need many apache children ?
I already wrote kind of about same topic a while ago and now interesting real life case makes me to write again 🙂 Most Web applications we’re working with have single tier web architecture, meaning there is just single set of apache servers server requests and nothing else – no dedicated server for static content, […]
Getting MySQL to use full key length
There is one bug, or “missing feature” in MySQL Optimizer which may give you hard time causing performance problems which may be hard to track down, it is using only part of the index when full index can be used or using shorter index while there is longer index available. The last item is yet […]
Linux IO Schedulers and MySQL
Found a great article about Linux IO Schedulers today which is quite interesting. It goes in details about schedulers and explains in which of workloads which of schedulers is best. The interesting thing this article points out is – there are multiple versions of each of the schedulers, while name remains the same. This means […]
Making MySQL Replication Parallel
Kevin Burton writes about making MySQL Replication Parallel. Many of us have been beaten by the fact MySQL Replication is single threaded so in reality it is only able to use only single CPU and single disk effectively which is getting worse and worse as computers are getting “wider” these days with multi-core CPUs. Kevin […]
TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to the fact that you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them. […]
MySQL Binaries availability
It looks like it looks like there are going a lot of discussions about future of MySQL Community Binaries, see for example this post and Kaj’s clarifications. Obviously now it grew to a lot of speculations and many comments are far from real story. It is also really interesting to watch MySQL try to balance […]
INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
Jonathan Haddad writes about REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both are non ANSI SQL extensions ? The story here seems to be the following – REPLACE INTO existed forever, at least since MySQL 3.22 and was a way to do replace faster and what […]
Performance impact of complex queries
What is often underestimated is impact of MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries which were taking milliseconds to stall for few seconds, especially in certain OS configurations, and on low profile servers (ie having only one disk drive) […]
Speaking at MySQL Users Conference 2007
Just got email from Jay today about two of my talks being accepted to MySQL Users Conference 2007 “MySQL Server Settings Tuning” and “MySQL Performance Cookbook”. This is great as MySQL Conferences are always fun and great way to get to know people and learn things about MySQL and connected areas. This will be my […]
Some of our articles are translated to Russian
A friend pointed out to me number of our our articles were translated and included in PHPInside.RU – electronic magazine about PHP and surrounding technologies which of course include MySQL. You can download PDF for free right here. We love our articles being translated and or republished, as long as they are available for free […]
Falcon Storage Engine Design Review
Now as new MySQL Storage engine – Falcon is public I can write down my thought about its design, which I previously should have kept private as I partially got them while working for MySQL. These thought base on my understanding, reading docs, speaking to Jim, Monty, Arjen and other people so I might miss […]
Looking for someone with Chinese knowledge
We’re looking to implement CJK Support in Open Source Full Text search engine Sphinx . Initially we’re thinking to base search ob bi-gram indexing to keep it simple, especially as according to research papers it offers decent quality for most cases. This is not that complex to implement however there is no way we can […]
Getting real life query speeds with MySQL
To check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query and see if there is any significant improvement. Leaving aside question of checking single query alone might not be the best way to see real improvement for your application, the problem they usually run […]