Posts Tagged “slow”

15 07 2010

mySQL >= 5.0.37 community profiling SQL queries.

Posted by Buzz in mySQL

Whilst there indeed seems to be a veritable plethora of SQL profiling / benchmarking tools, most of them with insane commercial license fees (>= $400 per annum on most)

I have found it intriguing that as of mySQL community edition >= 5.0.37 mySQL offers an inbuilt method for profiling SQL queries, as can be see here the downside is that this is session based, meaning it can only provide profiling information for the current connection, almost useless for trying to profile a running web app (that is without code modification to set profiling and harvest the data).

However it can be useful if you have a known slow query.

So lets work on the basis that we have a known slow SQL query we’d like profiling information for,

check to see if profiling is enabled:

1
SELECT  @@profiling;

The returned value is generally 0 so lets enable it.

1
2
SET profiling_history_size=100;
SET profiling=1;

This tells mySQL to retain the profile of 100 queries in memory, and to enable profiling.

Now at this point this can also be used to diagnose slow loading datases, simply

1
2
3
USE <dbname>;
SHOW profiles;
SHOW profile FOR 1;

Upon running the above you will now be using your database and will see an output similar to

1
2
3
4
5
6
7
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.00011400 | SELECT DATABASE() |
|        2 | 0.00048900 | show databases    |
|        3 | 0.00026600 | show tables       |
+----------+------------+-------------------+

Followed by

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000009 |
| init                 | 0.000011 |
| optimizing           | 0.000004 |
| executing            | 0.000017 |
| end                  | 0.000003 |
| end                  | 0.000002 |
| query end            | 0.000002 |
| freeing items        | 0.000005 |
| logging slow query   | 0.000002 |
| cleaning up          | 0.000002 |
+----------------------+----------+

In my case here nothing really eventful, lets assume for the moment you are using a wordpress database, and you have numerous posts

1
2
SELECT COUNT(*) FROM wp_posts WHERE ID > 100
SELECT COUNT(ID) FROM wp_posts WHERE ID > 100

in my case I got the following results:

1
2
0.00072600 | select count(*) from wp_posts where ID &gt; 100
0.00069900 | select count(ID) from wp_posts where ID &gt; 100

a simple demonstration showing the difference between a count() on an indexed field vs *, in this case the saving is ~4%.

1
2
SHOW profiles;
SHOW profile FOR query <n>;

Will give you an output similar to:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000079 |
| Opening tables     | 0.000014 |
| System lock        | 0.000005 |
| Table lock         | 0.000008 |
| init               | 0.000025 |
| optimizing         | 0.000012 |
| statistics         | 0.000049 |
| preparing          | 0.000012 |
| executing          | 0.000006 |
| Sending data       | 0.000461 |
| end                | 0.000004 |
| end                | 0.000003 |
| query end          | 0.000003 |
| freeing items      | 0.000007 |
| closing tables     | 0.000005 |
| logging slow query | 0.000003 |
| cleaning up        | 0.000003 |
+--------------------+----------+
17 rows in set (0.00 sec)

this is very similar to a stack trace you may run on a problematic script, or xdebug + webgrind, and will gain futher insight into your SQL should EXPLAIN no give you enough of an insight.

I’ll post more information on this as I get time to work with it more, this is still knew to me, and aside from knowing how to use it I know relatively little about this profiling functionality, please feel free to post references / examples in the comments.

Cheers

Buzz, , , , , , ,

Comments No Comments »

10 03 2008

Microsoft Office (Word, Excel, Outlook) Slow When Connected To Network, But Fast When Not?

Posted by Buzz in Microsoft Office 2003, Windows

Yes the title seems a little weird, but it’s a little problem that has been the bane of my and a fellow techs existence for a couple months now.

An international office on permanent VPN connection to where I work would experience extremely slow load times of office documents even if they were local to the machine, strangely this only occurred when connected to the network (and hence to the domain).

What was eventually found by the afor mentioned fellow tech was that every time an office application would load (i.e. open a new document) it was “polling” the entire domain for listed printers and folders, not a problem in the local office with Gigabit connectivity, but a major problem for an international office with several employees over a low bandwidth line …

So how to fix this?!?

 (Assuming windows XP & Classic Menu)

START > CONTROL PANEL > Folder Options > View

 Uncheck “Automatically search for network printers and folders” > OK

 Now make sure you exit all office applications (better yet just reboot)

If this does not solve the issue, or only provides a slight gain it’s time to remove the “recent files” list, from within excel

TOOLS > OPTIONS > General

 Uncheck “Recently use file list” … Exit all office apps (or reboot).

And try again, this _appears_ to solve the slow down.

Any problems drop me a comment.

Tags: , , , , , ,

Comments 22 Comments »