by Peter Zaitsev | Dec 29, 2006 | Insight for DBAs
As you might noticed there are no recent MySQL Community versions available for download from MySQL Download Area This applies both to binaries (which is expected with new polices) but also to the source files which were promised to be available. So what is if you...
by Peter Zaitsev | Dec 28, 2006 | Insight for Developers
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example SELECT STRAIGHT_JOIN * FROM A FORCE...
by Peter Zaitsev | Dec 20, 2006 | Insight for Developers
This topic may look boring and obvious but it is extremely important for MySQL Performance Optimization. In fact I probably have to touch it in every second MySQL Consulting work or even more frequently. IO Bound workload is quite different from CPU bound one, which...
by Peter Zaitsev | Dec 12, 2006 | Insight for DBAs
Today I was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little investigation on that matter. Interesting enough it looks like most people do not think about foreign keys overhead in...
by Peter Zaitsev | Dec 4, 2006 | Insight for DBAs
Linux and Unixes have excellent metric of system load called “loadavg”. In fact load average is is 3 numbers which correspond to “load average” calculated for one five and 15 minutes. It is computed as exponential moving average so most recent...
by Peter Zaitsev | Dec 1, 2006 | Insight for Developers
I guess note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often however see omitted is fact it only applies to COUNT(*) queries without WHERE clause. So if you have query like SELECT COUNT(*)...
by Peter Zaitsev | Nov 30, 2006 | Benchmarks
I had found pile of MySQL and PostgreSQL benchmarks on various platforms which I have not seen before. Very interesting reading. It does not share too much information about how MySQL or PostgreSQL was configured or about queries. Furthermore MySQL and PostgreSQL has...
by Peter Zaitsev | Nov 29, 2006 | Percona Events
One may have notice we were not blogging too much recently, this is because we were quite busy, mainly building BoardReader.com – Search Engine which indexes tens of thousands of forums from all over the world. This project was built by us as consulting project...
by Peter Zaitsev | Nov 27, 2006 | Insight for Developers
In the past life was easy in MySQL. Both CHAR and VARCHAR types meant the same, only being difference in the sense of fixed or dynamic row length used. Trailing spaces were removed in both cases. With MySQL 5.0 however things changed so now VARCHAR keeps trailing...
by Peter Zaitsev | Nov 23, 2006 | Insight for DBAs
I already wrote in the blog as well mentioned in presentation there is often a choice you have to make between having prefix index – which can be significantly smaller in size and having index being covering index, which means query can be executed using only...
by Peter Zaitsev | Nov 21, 2006 | Insight for DBAs
I was restarting MySQL on box with 50.000 of Innodb tables and again it took couple of hours to reach decent performance because of “Opening Tables” stage was taking long. Part of the problem is Innodb is updating stats on each table open which is possibly...
by Peter Zaitsev | Nov 17, 2006 | Insight for DBAs
In query examinations it is often interesting which columns query needs to access to provide result set as it gives you ideas if you can use covering indexes to speed things up or even cache some data by denormalizing tables. So far it has to be done manually –...
by Peter Zaitsev | Nov 16, 2006 | Insight for DBAs
Running RPM based or other packaged MySQL Binary you may have a problem if you would like to rebuild binary for some reason – change some build settings, apply some third party patches or simply try latest snapshot (This time I was both applying patches for...
by Vadim Tkachenko | Nov 14, 2006 | Insight for DBAs
I was pretty busy last month with project which will be annonced very soon (I hope), but I can’t miss bug fix of my favorite bug 15815. I wrote about this problem before and also investigated in my presentation. Finally bug fix was pushed into 5.0-bk tree and...
by Peter Zaitsev | Nov 12, 2006 | Benchmarks, Insight for DBAs
Today I noticed one of server used for web request profiling stats logging is taking about 2GB per day for logs, which are written in MyISAM table without indexes. So I thought it is great to try how much archive storage engine could help me in this case. Original...
by Peter Zaitsev | Nov 12, 2006 | Insight for Developers
As you probably know PHP “mysql” extension supported persistent connections but they were disabled in new “mysqli” extension, which is probably one of the reasons some people delay migration to this extension. The reason behind using persistent...
by Peter Zaitsev | Nov 12, 2006 | Insight for Developers
One think I can see with people using EXPLAIN is trusting it too much, ie assuming if number of rows is reported by EXPLAIN is large query must be inefficient. It may not be the case. The question is not only about stats which may be wrong and which is why you may...
by Peter Zaitsev | Nov 9, 2006 | Insight for DBAs
As you can read from my Innodb Architecture and Performance Optimization presentation Innodb automatically manages undo area in system tablespace so you never need to care about it. I present it as positive feature reducing administration effort needed but it also can...
by Peter Zaitsev | Nov 9, 2006 | Percona Events
I’m just back from OpenSource Database Conference and PHP International Conference which took place in Frankfurt. I’ve uploaded slides for two talks I’ve been giving which you might want to check out. In general Database portion of the conference was...
by Peter Zaitsev | Oct 30, 2006 | Insight for Developers
I’ve run into the following thread couple of days ago: Basically someone is using sphinx to perform search simply on attributes (date, group etc) and get sorted result set and claiming it is way faster than getting it with MySQL. Honestly I can well believe it...
