by Peter Zaitsev | Apr 2, 2008 | Insight for Developers
Today a customer asked me to help them to convert their sequence generation process to the stored procedure and even though I have already seen it somewhere I did not find it with two minutes of googling so I wrote a simple one myself and posting it here for public...
by Aurimas Mikalauskas | Mar 27, 2008 | Insight for DBAs
Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt – adding/dropping columns or...
by Peter Zaitsev | Mar 20, 2008 | Insight for DBAs
Commenting on my previous post on MySQL Query Cache Gerry pokes me as I’m all wrong and both comments and whitespace are fixed in MySQL 5.0. This was not what I remember seeing in production so I decided to do some tests on the matter: I did the test two ways....
by Peter Zaitsev | Mar 17, 2008 | Insight for DBAs
I posted a simple INFORMATION_SCHEMA query to find largest MySQL tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes: Find total number of tables, rows, total data...
by Peter Zaitsev | Mar 14, 2008 | Insight for DBAs
For large number of online applications once you implemented proper sharding you can consider your scaling problems solved – by getting more and more hardware you can grow. As I recently wrote it however does not mean it is the most optimal way by itself to do...
by Vadim Tkachenko | Oct 12, 2007 | Uncategorized
We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that’s why we did not escalate MyISAM scalability question. This time working on...
by Peter Zaitsev | Sep 28, 2007 | Insight for Developers
Many Open Source software solutions use database per user (or set of tables per user) which starts to cause problems if it is used on massive scale (blog hosting, forum hosting etc), resulting of hundreds of thousands if not millions of tables per server which can...
by Vadim Tkachenko | Sep 26, 2007 | Insight for DBAs
There was long played scalability issue with InnoDB auto-increment field. For details check Bug 16979. In short words the problem is in case of insert into table with auto-increment column the special AUTO_INC table level lock is obtained, instead of usual row-level...
by Peter Zaitsev | Sep 17, 2007 | Benchmarks, Insight for Developers
Quite typical query for reporting applications is to find top X values. If you analyze Web Site logs you would look at most popular web pages or search engine keywords which bring you most of the traffic. If you’re looking at ecommerce reporting you may be...
by Peter Zaitsev | Sep 12, 2007 | Insight for Developers
Quite frequently I run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I run it, why is that ? The answer to this question is simple – because SQL_NO_CACHE only bypasses query cache but it has no change...
by Vadim Tkachenko | Sep 7, 2007 | Percona Events
Returning to my post about memory consumption with Stored Function at this moment I want to annul that post. Trying to repeat described behavoir I cannot repeat that again and probably there was something wrong with the whole box, not MySQL. I apologize if it was...
by Vadim Tkachenko | Aug 28, 2007 | Benchmarks, Insight for DBAs
I believe we wrote about this before, but this topic popups again and again. Today I’ve read opinion that if we have clause WHERE has_something=1 we should have index on column has_something (the column has two values 0 and 1). In reality the right answer is not...
by Vadim Tkachenko | Aug 28, 2007 | Benchmarks, Insight for DBAs
About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I’m speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as...
by Vadim Tkachenko | Aug 26, 2007 | Insight for DBAs
UPDATE : Post is not actual anymore Not so long time ago I had task to update string column in table with 10mil+ rows, and, as the manipulation was non-trivial, I decided this task is good to try Stored Function. Function written – go ahead. Since 5 min I got...
by Peter Zaitsev | Aug 12, 2007 | Insight for DBAs
I start to see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the queries – to keep queries simple without really thinking how it affects server performance. Even worse...
by Peter Zaitsev | Aug 12, 2007 | Percona Events
Yesterday I ran into the article which sheds some light on FaceBook search implementation. As we’re recently a lot into search having implemented a bunch of search projects ourselves and helped number a of customers with their full text search needs I decided to...
by Vadim Tkachenko | Aug 9, 2007 | Percona Events
I’ve just read post http://www.planetmysql.org/kaj/?p=123 about MySQL plans of including community contributions into releases. I understand MySQL’s interest to make releases stable, and includes contributions only into development tree, but this is not...
by Peter Zaitsev | Jul 27, 2007 | Insight for DBAs
Working on large upgrade of MySQL 4.1 running Innodb to MySQL 5.0 and doing oprofile analyzes we found very interesting issue of buf_get_latched_pages_number being responsible for most CPU usage. It did not look right. The close look revealed this is the function...
by Peter Zaitsev | Jul 27, 2007 | Insight for DBAs
It is true you might be better of being unaware about problems, because in this case you might not run into them 🙂 Just couple of days ago Kaj Arno told me there are issues reported with SHOW PROFILE patch available in latest MySQL Community Edition and I’ve...
by Peter Zaitsev | Jul 23, 2007 | Insight for Developers
I’ve already wrote a few times about various projects using Sphinx with MySQL for scalable Full Text Search applications. For example on BoardReader we’re using this combination to build search against over 1 billion of forum posts totaling over 1.5TB of...
