|Showing entries 1 to 10|
1. Tune those queries
By far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn't necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn't receive[Read more...]
Hopefully you are caught up on the recommended reading from the last post as today we will look at creating a simple benchmark using a common tool to determine which option file to use. The MySQL server will use default values if it can not find an option file. Under the /usr/local/mysql/support-files for MySQL 5.6 are eight1
sample configuration files that can be renamed and dropped in place as a staring point. See the Using Option File page for details.
But these files have not kept pace with hardware. For instance, a small system is listed as being under 64m of memory while huge is 1-2G. More modern settings will be covered in a later post but we can use these files in a simple test on a fairly simple test machine2.
In my previous post, I’ve showed how to use Dtrace to find queries creating disk temporary tables (only available for OS with dtrace: solaris, freebsd, etc).
In MySQL 5.6 (which is not released yet, use “labs” version for now) we can use new performance_schema table events_statements_history or events_statements_history_long to find all performance metrics for all queries including created disk/memory tables, use of index, etc. WOW! This is what I have been waiting for a long time!
To illustrate, I have grabbed mysql-5.6.3-labs-performance-schema-linux2.6-x86_64.tar.gz from labs.mysql.com (this feature is only in labs version) and run sysbench readonly test (you need[Read more...]
For all those in New York this is an upcoming MySQL presentation held in conjunction with our colleagues at General Assembly on March 22nd 2011.
This presentation “How Better Indexes Save You Money” will be discussing how one simple technique can result in huge MySQL performance improvements and with zero code changes necessary. Many people think they know indexes, however MySQL and MySQL Storage Engines have some specifics that differ from more traditional RDBMS products. Learn some of the key analysis and verification techniques and be able to see immediate potential results in performance.
You can find more details at Meetup.com EffectiveMySQL. This new group is all about highly technical MySQL related content “no fluff, just stuff”.
Loose index scan in MySQL can really help optimizing “group by” queries in some cases (for example, if you have only min() and/or max() as your aggregate functions). For example, if you have this query (to find maximum delay for all US flights with departure on Sundays in 2010):
select max(DepDelayMinutes), carrier, dayofweek from ontime_2010 where dayofweek = 7 group by Carrier, dayofweek
the usual case will be adding a covered index on (dayofweek, Carrier, DepDelayMinutes). And MySQL will use this index fine (using index mean it will use the covered index):
[Read more...]mysql> explain select max(DepDelayMinutes), Carrier, dayofweek from ontime_2010 where dayofweek =7 group by Carrier, dayofweek\G *************************** 1. row *************************** id: 1 select_type:
Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).
(hr:min:sec)Details (Conf. site link)
Sometimes we have a lots of small and rather fast queries which use group by/order by, thus creating temporary tables. Some of those queries are retrieving text fields and mysql have to use disk (myisam) temporary tables. Those queries usually run for less than 1-2 seconds, so they did not get into slow query log, however, they sometimes add serious load on the system.
Here is the stat example:
bash-3.00$ /usr/local/mysql/bin/mysqladmin -uroot -p -i 2 -r extended-status|grep tmp_disk ... | Created_tmp_disk_tables | 109 | | Created_tmp_disk_tables | 101 | | Created_tmp_disk_tables | 122 | ...
40-50 tmp_disk_tables created per second
So, how can we grab those queries? Usually we have to temporary enable general log, filter out queries with “group[Read more...]
Reporting queries (I will use this term here) are the queries which summaries and groups data over the certain period of time. For example, in Social Network site we want to know how many messages have been sent for the given period of time, group by region and status (sent, received, etc), order by number of messages sent.
As an example I will take a table which is used to send SMS (text messages).
SQL: select concat('+', substring(region_code,1 ,2), 'xxx') as reg, status, count(*) as cnt
where submition_date between '2009-01-01' and '2009-04-01' group by reg, status
having cnt>100 order by cnt desc, status limit 100;
This query will do a range scan over the submition_date and perform a filesort. There are common well known approaches which can be used to optimize table[Read more...]
Revision: 8 - Last Update: September 03 2008
This is the first part of a series of short articles with a how-to approach about MySQL® Performance Monitoring, Tuning & Auditing. We will see the question from a GUI prospective. In particular we will describe which monitoring-oriented features HoneyMonitor, a GUI for MySQL® currently in alpha development, implements.
I will explain how HoneyMonitor let you
We will use only the 5.1.x series of the Server as we use some Scheduled Events and the[Read more...]
|Showing entries 1 to 10|