You searched for active users mysql - Page 2 of 5

Checking for a live database connection considered harmful

May 5, 2010 By Baron Schwartz 38 Comments

It is very common for me to look at a customer’s database and notice a lot of overhead from checking whether a database connection is active before sending a query to it. This comes from the following design pattern, written in pseudo-code:

function query_database(connection, sql)
   if !connection.is_alive() and !connection.reconnect() then
      throw exception
   end
   return connection.execute(sql)
end

function query_database(connection, sql)

if !connection.is_alive() and !connection.reconnect() then

throw exception

end

return connection.execute(sql)

end

Many of the popular development platforms do something similar to this. Two [...]

Filed Under: Insight for Developers, MySQL

Active Cache for MySQL

January 10, 2010 By Peter Zaitsev 11 Comments

One of the problems I have with Memcache is this cache is passive, this means it only stores cached data. This means application using Memcache has to has to special logic to handle misses from the cache, being careful updating the cache – you may have multiple data modifications happening at the same time. Finally [...]

Filed Under: Insight for Developers, MySQL Tagged With: Ideas, Memcached

What do we optimize with mk-query-digest ?

December 15, 2009 By Peter Zaitsev 2 Comments

When we’re looking at mk-query-digest report we typically look at the Queries causing the most impact (sum of the query execution times) as well as queries having some longest samples. Why are we looking at these ? Queries with highest Impact are important because looking at these queries and optimizing them typically helps to improve [...]

Filed Under: Insight for Developers, MySQL

How (not) to find unused indexes

October 16, 2009 By Morgan Tocker 2 Comments

I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.Â This method is flawed – here’s the first reason why:

CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`status` enum('archived','active') DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;
mysql> SELECT count(*), status FROM sales GROUP by status;
+----------+---------+
| count(*) | statusÂ  |
+----------+---------+
|Â Â Â  65536 | archived |
|Â Â Â Â Â  154 | activeÂ  |
+----------+---------+
2 rows in set (0.17 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | keyÂ Â Â  | key_len | refÂ Â  | rows | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | refÂ  | statusÂ Â Â Â Â Â Â  | status | 2Â Â Â Â Â Â  | const |Â  196 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | keyÂ  | key_len | refÂ  | rowsÂ  | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | ALLÂ  | statusÂ Â Â Â Â Â Â  | NULL | NULLÂ Â Â  | NULL | 65690 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

CREATE TABLE `sales` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`customer_id` int(11) DEFAULT NULL,

`status` enum('archived','active') DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `status` (`status`)

) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;

mysql> SELECT count(*), status FROM sales GROUP by status;

+----------+---------+

| count(*) | statusÂ |

+----------+---------+

|Â Â Â 65536 | archived |

|Â Â Â Â Â 154 | activeÂ |

+----------+---------+

2 rows in set (0.17 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)

The cardinality of status index is woeful, but provided that the application [...]

Filed Under: Insight for DBAs, MySQL Tagged With: explain, indexing, MySQL, Tips

Crashes while using MyISAM with multiple key caches

July 18, 2009 By Peter Zaitsev Leave a Comment

Over last couple of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent or critical enough issue so I could spend time creating repeatable test case and search of the bug in the MySQL database did not find anything. Recently we had this [...]

Filed Under: Insight for DBAs Tagged With: Bugs, MyISAM Key Cache

When would you use SAN with MySQL ?

March 9, 2009 By Peter Zaitsev 18 Comments

One question which comes up very often is when one should use SAN with MySQL, which is especially popular among people got used to Oracle or other Enterprise database systems which are quite commonly deployed on SAN. My question in such case is always what exactly are you trying to get by using SAN ?

Filed Under: Hardware and Storage Tagged With: Production

XtraDB storage engine release 1.0.2-3 (Spring edition) codename Sapporo

March 2, 2009 By Evgeniy Stepchenko 10 Comments

Today we announce release 1.0.2-3 of our XtraDB storage engine. Here is a list of enhancements: Move to MySQL 5.1.31 Scalability fix â€” ability to use several rollback segments Increasing the number of rseg may be helpful for CPU scale of write-intentional workloads. See benchmark results. Scalability fix â€” replaced page_hash mutex to page_hash read-write [...]

Filed Under: Percona Software Tagged With: Percona, Release, XtraDB, XtraDB InnoDB scalability

7 Reasons why MySQL Quality will never be the same

December 10, 2008 By Peter Zaitsev 15 Comments

I had a call with Monty the other day and I told him why I think MySQL Server Quality will never be the same again. I’ve been thinking a bit more about it and here is the extended list. In particular I think MySQL Server will never be able to reach its original quality guidelines [...]

Filed Under: Events and Announcements Tagged With: Community

How Percona does a MySQL Performance Audit

November 24, 2008 By Baron Schwartz 17 Comments

Our customers or prospective customers often ask us how we do a performance audit (it’s our most popular service). I thought I should write a blog post that will both answer their question, so I can just reply “read all about it at this URL” and share our methodology with readers a little bit. This [...]

Filed Under: Insight for Developers Tagged With: Tuning

MySQL End Of Life (EOL) Policy

August 22, 2008 By Peter Zaitsev 6 Comments

We’ve discussed today how we should implement MySQL Version advisory in mk-audit tool. One obvious questions was to look at the end of life – it is often bad idea to run MySQL versions past end of life as even security bugs may not be fixed in these (though do not get paranoid, if you’re [...]

Filed Under: Events and Announcements Tagged With: Community, Production

Checking for a live database connection considered harmful

Active Cache for MySQL

What do we optimize with mk-query-digest ?

How (not) to find unused indexes

Crashes while using MyISAM with multiple key caches

When would you use SAN with MySQL ?

XtraDB storage engine release 1.0.2-3 (Spring edition) codename Sapporo

7 Reasons why MySQL Quality will never be the same

How Percona does a MySQL Performance Audit

MySQL End Of Life (EOL) Policy

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA