Posts Tagged “sql”

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

Tags: , , , , , , ,

Comments No Comments »

20 03 2008

PHP Security considerations, a quick reference for the newbies.

Posted by: Buzz in mySQL, php, security

To often I get passed code to review that quite frankly is so full of holes it wouldn’t make an adequate sieve…

So in this quick blog I outline a few simple and easily implemented steps to ensure as you start out in the world of PHP, your first site isn’t hacked within 5 minutes, leaving you a whimpering wrek …

PHP DON’T EXAMPLE 1:

Passing RAW globals to mysql!

i.e.

1
2
$sql = "SELECT * FROM users WHERE email='.$_GET['email']."' and password='".$_GET['password']"';";
$result = mysql_query($sql);

So what is wrong with the above? SQL INJECTION welcome to a world where people want to break your website, simply because they can …

I am not going to add more description, just click through to the wiki pedia entry linked above …

To avoid this PHP comes with two functions mysql_escape_string() and mysql_real_escape_string()

An example taken from the mysql_real_escape_string() page:

Example#2 An example SQL Injection Attack

1
2
3
4
<span style="color: #000000;"><span style="color: #0000bb;">&lt;?php
</span><span style="color: #ff8000;">// Query database to check if there are any matching users
</span><span style="color: #0000bb;">$query </span><span style="color: #007700;">= </span><span style="color: #dd0000;">"SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'"</span><span style="color: #007700;">;
</span><span style="color: #0000bb;">mysql_query</span><span style="color: #007700;">(</span><span style="color: #0000bb;">$query</span><span style="color: #007700;">);</span></span>
1
2
3
4
5
<span style="color: #000000;"><span style="color: #007700;"> </span><span style="color: #ff8000;">// We didn't check $_POST['password'], it could be anything the user wanted! For example:
</span><span style="color: #0000bb;">$_POST</span><span style="color: #007700;">[</span><span style="color: #dd0000;">'username'</span><span style="color: #007700;">] = </span><span style="color: #dd0000;">'aidan'</span><span style="color: #007700;">;
</span><span style="color: #0000bb;">$_POST</span><span style="color: #007700;">[</span><span style="color: #dd0000;">'password'</span><span style="color: #007700;">] = </span><span style="color: #dd0000;">"' OR ''='"</span><span style="color: #007700;">;</span></span><span style="color: #007700;"><span style="color: #ff8000;">// This means the query sent to MySQL would be:
</span><span style="color: #007700;">echo </span><span style="color: #0000bb;">$query</span><span style="color: #007700;">;
</span><span style="color: #0000bb;">?&gt;</span></span>

The query sent to MySQL:

1
<code>SELECT * FROM users WHERE user='aidan' AND password='' OR ''=''

This would allow anyone to log in without a valid password.

So in summary READ the mysql_real_escape_string() page, and even if you don't implement the "best practice" example on that page PLEASE make sure you at least escape $_SESSION $_GET $_POST inputs with a mysql escape function!

Tags: , , ,

Comments 3 Comments »

Creative Commons License