by Peter Zaitsev | Sep 12, 2006 | Insight for Developers
This is a pretty simple approach I often use called to optimize web application performance if problem happens with few pages. If we have “everything is slow” problem looking at slow query logs may be better start. So what could you do ? Look at the...
by Peter Zaitsev | Sep 8, 2006 | Insight for DBAs
Have you ever seen index which refused to be used even if there is every reason for it to work (from the glance view): mysql> explain select * from article where article_id=10;...
by Peter Zaitsev | Sep 6, 2006 | Insight for Developers
This is probably well known issue for everyone having some MySQL experience or experience with any other SQL database. Still I see this problem in many production applications so it is worth to mention it, especially as it is connected to MySQL Performance. No it...
by Vadim Tkachenko | Sep 4, 2006 | Insight for Developers
(There is an updated version of this post here) MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. Where it can be useful?...
by Peter Zaitsev | Aug 21, 2006 | Insight for DBAs
If someone asks me about MySQL Backup advice my first question would be if they have LVM installed or have some systems with similar features set for other operation systems. Veritas File System can do it for Solaris. Most SAN systems would work as well. What is...
by Peter Zaitsev | Aug 17, 2006 | Insight for DBAs
About every second application I look at has some tables which have redundant or duplicate indexes so its the time to speak about these a bit. So what is duplicate index ? This is when table has multiple indexes defined on the same columns. Sometimes it is indexes...
by Peter Zaitsev | Aug 14, 2006 | Insight for Developers
Loose Index Scan with Double IN Few days ago I wrote an article about using UNION to implement loose index scan. First I should mention double IN also works same way so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM...
by Peter Zaitsev | Aug 11, 2006 | Insight for Developers
Article about database design problems is being discussed by Kristian. Both article itself and responce cause mixed feellings so I decided it is worth commenting: 1. Using mysql_* functions directly This is probably bad but I do not like solutions proposed by original...
by Peter Zaitsev | Aug 10, 2006 | Insight for Developers
One little known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and how it is going to do them. So if you have table people with KEY(age,zip) and you will run...
by Peter Zaitsev | Jul 30, 2006 | Insight for DBAs
MySQL is known for its stability but as any other application it has bugs so it may crash sometime. Also operation system may be flawed, hardware has problems or simply power can go down which all mean similar things – MySQL Shutdown is unexpected and there...
by Peter Zaitsev | Jul 24, 2006 | Insight for Developers
One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation...
by Peter Zaitsev | Jul 24, 2006 | Insight for Developers
Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of things you may wish to watch out. EXPLAIN...
by Peter Zaitsev | Jul 7, 2006 | Insight for DBAs
Brian Aker recently published good write up about using MySQL replication. The piece I find missing however is good description of warning about limits of this approach as well as things you need to watch out for. You can call me person with negative mind but I tend...
by Peter Zaitsev | Jun 29, 2006 | Insight for DBAs
“What cache hit rate is good for optimal MySQL Performance” is typical question I’m asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does not really matter. In both cases trying to come up with constant “good” hit rate...
by Vadim Tkachenko | Jun 26, 2006 | Insight for Developers
by Peter Zaitsev | Jun 9, 2006 | Insight for DBAs, MySQL
If you’ve been reading enough database-related forums, mailing lists, or blogs you have probably heard complains about MySQL being unable to handle more than 1,000,000 (or select any other number) rows by some of the users. On the other hand, it is well known...
by Peter Zaitsev | Jun 8, 2006 | Insight for DBAs
MySQL Server has tons of variables which may be adjusted to change behavior or for performance purposes. They are documented in the manual as well as on new page Jay has created. Still I see constant confusion out where which of variables apply to storage engines only...
by Peter Zaitsev | Jun 6, 2006 | Uncategorized
Sometimes I see people thinking about buffers as “larger is always better” so if “large” MySQL sample configuration is designed for 2GB and they happen to have 16, they would simply multiply all/most values by 10 and hope it will work well....
by Vadim Tkachenko | Jun 4, 2006 | Insight for DBAs
As maybe you know InnoDB uses hard page size 16Kb for datafiles and for buffer pool. However this size can be changed if you need it for your workload. go to file innobase/include/univ.i, lines: /* The universal page size of the database */ #define UNIV_PAGE_SIZE (2 *...
by Peter Zaitsev | May 29, 2006 | Insight for DBAs
MySQL INSERT ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was introduced in MySQL 4.1 but I still constantly see people unaware of it. Myself I like this feature big deal because it is designed in truly MySQL style – very...
