It’s been just over three months since the April 2009 MySQL Users Conference and Expo. It took a while for the files to be processed, and then uploaded to www.technocation.org, and then I found out that the online streaming was not working properly. So I started playing with things, re-encoding some videos, updating the software, but to no avail.

Just as I was about to give up I got notification that Technocation, Inc. was accepted into YouTube’s not-for-profit program, which allows movies larger than 10 minutes to be uploaded and viewed advertisement-free.

So then I had to upload the videos to YouTube and add descriptions.

So with no *further* delay, here are all the videos from the 2009 MySQL Conference and 2009 MySQL Camp:

The brief description — just the playlists:
Conference …

Do you need to find qps for peak hours not avg qps through mysql life.

The MySQL 5.1 offers new GLOBAL_STATUS information schema tables. These can be used to report certain performance metrics, such as the number of queries processed per certain seconds, NOT overall avg queries per second, Its good to know how much qps in peak hours.

http://forge.mysql.com/tools/tool.php?id=217

Here I'm following up my previous post Using linguistic indexes for sorting in Oracle. I don't much like the Oracle solution, that requires creating a special index to speed up sorting, but... at the same time its very powerful, allows to index in many languages and no database changes are needed.

In this post I’ll take a look at the two popular open source databases MySQL and PostgreSQL. I'll take a look only at features, that the database has included and that can be used without any special application changes.

PostgreSQL 8.4

Starting from 8.4, collation (sorting) rules can be defined per database and there is no possibility to set it in session level. All sorting and all indexes are ordered according to the database collation locale. In previous versions there was only one …

It is important to know and understand that while indexing columns in MySQL will generally improve performance, using the appropriate type of index can make a greater impact on performance.

There are four general index types to consider when creating an appropriate index to optimize SQL queries.

• Column Index
• Concatenated Index
• Covering Index
• Partial Index

For the purpose of this discussion I am excluding other specialized index types such as fulltext, spatial and hash in memory engine.

Example Table

For the following examples, I will use this test table structure.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  external_id INT UNSIGNED NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
) …

There was small delay in our releases, part of this time we worked on features I mentioned before:
- Moving InnoDB tables between servers
- Improve InnoDB recovery time
and rest time we played with performance trying to align XtraDB performance with MySQL 5.4 ® and also port all performance fixes to 5.0 tree.

So basically we made: new split-buffer-mutex patch, which separate global buffer pool mutex into several small mutexes, and ported some Google IO fixes.
Here are results what we have so far. As usually for benchmarks I used our workhorse Dell PowerEdge R900 with 16 cores and 32GB of RAM and RAID 10 on 8 SAS disks. And again as usually our tpcc-mysql scripts with 100W …

Note This Article was actually written back in May after the UC at the request of Linux Magazine, through a series of events It went unpublished. Between then and now Jeremy ended up doing a great job covering most of the topics, so in the end it was unneeded. Now I had this completed article and thought, what should I do with it? In the I decided to publish them here. Also note I did update a few items.

As more companies move to MySQL and the demands for data increase, we push the bounds of the database further. The challenges large Web properties (who have pioneered many of the large MySQL deployments) faced when they stored 50GB of data and had 5,000 users were nothing like the challenges of storing 500GB of data supporting 100,000 users. Today, as we are seeing more and more 10+TB-sized datasets being …

Video for the presentation at the 2009 MySQL Camp:
Chasing Bottlenecks
by Morgan Tocker

Description:
The best way to performance tune a system is to find out what your bottlenecks are, and attacking those first. In the first part of this session, I'll be looking at some of the issues faced with common database workloads. From there, I'll then be showing how you can get more information out of MySQL and your Operating System to find out about your workload. This session is designed for beginner to intermediate MySQL users.

read more

Summary: An alternate approach, offered in response to our original post, provides excellent improvements for smaller databases, but clustered indexes offer better performance as database size increases.  (This posting is by Dave.)

Jay Pipes suggested an alternate approach to improving MySQL performance of Query 17 on a TPC-H-like database.

1. 
   
2. Add the index (l_partkey, l_quantity) to the lineitem table.
   
3. Re-write the query as:
   

   select 
      sum(li.l_extendedprice) / 7.0 as avg_yearly 
   from lineitem li 
      inner join part p on li.l_partkey = p.p_partkey 
      inner join ( select 
                      l_partkey, 0.2 * avg(l_quantity) as quantity 
                   from lineitem 
                   group by l_partkey 
                 ) as quantities 
         on …

Yves pointed this out to me:

http://www.internetnews.com/software/article.php/3825426/

“Moving beyond just providing Oracle compatibility, EnterpriseDB is adding new scalability features to their database. One of them is a feature called ‘”Infinite Cache,” which is based on the open source memcached application.

Mlodgenski commented that distributed memory caches are common, though management from a system management and a database perspective is often difficult.

“What we did is we took the distributed memory cache that was sitting in front of the database and put it behind the database,” Mlodgenski said. “This allows a simple SQL (define) interface for developers that allows access to the distributed memory cache and now the system management overhead is handed by the database itself.”

Mlodgenski added that Infinite Cache leads to a 10x to 20x performance gain for read application loads. …

Today marks the official launch of Percona.tv. We'll be uploading technical screencasts, conference video, and anything else cool we can think up. If you've got ideas or requests, let us know and we'll do our best to accommodate!

Entry posted by Ryan Lowe | 7 comments

Add to: | | | …