PHP

I was unable to install today PECL extention PDO_MYSQL via

sudo pecl install PDO_MYSQL


The problem was that after configure, make can't find mysql header files.

...
In file included from /tmp/pear/temp/PDO_MYSQL/pdo_mysql.c:31:
/tmp/pear/temp/PDO_MYSQL/php_pdo_mysql_int.h:25:19: mysql.h: No such file or directory
In file included from /tmp/pear/temp/PDO_MYSQL/pdo_mysql.c:31:
/tmp/pear/temp/PDO_MYSQL/php_pdo_mysql_int.h:36: error: syntax error before "MYSQL"
...


So I learned how to make an extension manually.
I ran locate mysql.h, copyed all mysql headers from there to current folder and now you can make it

phpize --clean
phpize
./configure
make


make install won't worked for me.
pdo_mysql.so placed in modules dir, copy it to extentions dir (php -i | grep extension_dir) and enable in php.ini.

These steps will not add PDO_MYSQL under the PEAR, so you need to maintain it by yourself. But to update list of installed packages, you can do this trick

sudo pecl install -rB pdo_mysql

-r means do not install files, only register the package as installed
-B means don't build C extensions

Some other good to read links
Topic : pdo_mysql.so driver Installation

ideal web environment structure tips

why to compile

i think, you should do this cause this way you can enable only required stuff for all your programs (such apache web server, mysql, php, etc).

for example, i don’t need all default modules in my apache that eat more memory than application can use. same thing — php.

configuration files

vi is good. i even know how to use it. but i prefer to make symbol links to my config files (httpd.conf, my.conf, etc) to some place where it automatically backup’d and i can access via windows editors to change, copy, share them. this files only read by daemons on startup. while windows still default os in many companies…

to be contunied…

mysql performance tips

INSERT DELAYED ...

The DELAYED option is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. It gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread. Also inserts from many clients are bundled together and written in one block.

mysql dba queries

this post is small refcard of useful queries to understand your database and find some problems. list will be updated constantly with new ones.

--- mysql-dba-queries.sql --------------------------------

SHOW DATABASES;

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SELECT VERSION();

/* http://dev.mysql.com/doc/refman/5.0/en/mysqld-server.html */
SHOW VARIABLES;
SHOW VARIABLES LIKE '%innodb%';

/* http://dev.mysql.com/doc/refman/5.0/en/show.html */
SHOW STATUS;
SHOW GLOBAL STATUS;

SHOW GRANTS;
SHOW GRANTS FOR 'viewer';

SHOW ENGINES;
SHOW CHARSET;
SHOW COLLATION;

/* Find total number of tables, rows, total data in index size for given MySQL Instance */

SELECT
 COUNT(*) `tables`
, CONCAT(ROUND(SUM(`table_rows`)/1024, 2), ' K') `rows`
, CONCAT(ROUND(SUM(`data_length`)/1048576, 2), ' M') `data`
, CONCAT(ROUND(SUM(`index_length`)/1048576,2), ' M') `idx`
, CONCAT(ROUND(SUM(`data_length`+`index_length`)/1048576, 2), ' M') `total_size`
, ROUND(SUM(`index_length`)/SUM(`data_length`), 2) `idx_frac`
FROM
 `information_schema`.`TABLES`;

/* Find biggest databases */

SELECT
 `table_schema`
, COUNT(*) `tables`
, CONCAT(ROUND(SUM(`table_rows`)/1024, 2), ' K') `rows`
, CONCAT(ROUND(SUM(`data_length`)/1048576, 2), ' M') `data`
, CONCAT(ROUND(SUM(`index_length`)/1048576, 2), ' M') `idx`
, CONCAT(ROUND(SUM(`data_length`+`index_length`)/1048576, 2), ' M') `total_size`
, ROUND(SUM(`index_length`)/SUM(`data_length`), 2) `idx_frac`
FROM
 `information_schema`.`TABLES`
GROUP BY
 `table_schema`
ORDER BY
 SUM(`data_length`+`index_length`) DESC
LIMIT 10;

/* Data Distribution by Storage Engines */

SELECT
 `engine`
, COUNT(*) `tables`
, CONCAT(ROUND(SUM(`table_rows`)/1024, 2), ' K') `rows`
, CONCAT(ROUND(SUM(`data_length`)/1048576, 2), ' M') `data`
, CONCAT(ROUND(SUM(`index_length`)/1048576, 2), ' M') `idx`
, CONCAT(ROUND(SUM(`data_length`+`index_length`)/1048576, 2), ' M') `total_size`
, ROUND(SUM(`index_length`)/SUM(`data_length`), 2) `idx_frac`
FROM
 `information_schema`.`TABLES`
GROUP BY
 `engine`
ORDER BY
 SUM(`data_length`+`index_length`) DESC
LIMIT 10;

--- mysql-dba-queries.sql ----------------------------EOF-

Thanks to:
— Peter Zaitsev and his MySQL Performance Blog
— MySQL Documentation

cheat sheets

ILoveJackDaniels.com
Reference cards
Prototype 1.5.0 Cheat Sheet
Mootools r.83 Cheat Sheet

mysql profiling

this is my first post about profiling, so we need to understand what is profiling (performance analysis)?

In software engineering, performance analysis (a field of dynamic program analysis) is the investigation of a program's behavior using information gathered as the program runs, as opposed to static code analysis. The usual goal of performance analysis is to determine which parts of a program to optimize for speed or memory usage.

full article you can find at mysql documentation, here i'll just overview it.

profiling gives us option to understand on timeline our queries, to see resource usage for executed statements. SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37 (that's important!).

Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON.

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)

Here is SQL queries that you need to know:

SELECT @@profiling;

SET profiling = 1;
SET profiling = 0;

SHOW PROFILES;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;

/* displays all information */
SHOW PROFILE ALL FOR QUERY 1;
/* displays counts for block input and output operations */
SHOW PROFILE BLOCK IO FOR QUERY 1;
/* displays counts for voluntary and involuntary context switches */
SHOW PROFILE CONTEXT SWITCHES FOR QUERY 1;
/* displays user and system CPU usage times */
SHOW PROFILE CPU FOR QUERY 1;
/* displays counts for messages sent and received */
SHOW PROFILE IPC FOR QUERY 1;
/* displays counts for major and minor page faults */
SHOW PROFILE PAGE FAULTS FOR QUERY 1;
/* displays the names of functions from the source code, together with the name and line number of the file in which the function occurs */
SHOW PROFILE SOURCE FOR QUERY 1;
/* displays swap counts */
SHOW PROFILE SWAPS FOR QUERY 1;

SELECT *

i found this haha img on mysql webinars.


this post not about sql error on img but about post subject.
it's ok when your table is

CREATE TABLE `your_table` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`smthing` tinyint(3) NOT NULL unsigned,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

only two columns and you can use SELECT * and even you can put subj in 10 places, ok? after month, you alter table, add text field. usually, you not remember all places with this code, so in 10 places it overload resource usage with useless traffic between MySQL and application, isn't it? it mean that is always better to do SELECT `id`, `smthing` FROM... - only needed fields.
so, forget to select all!

want to learn mysql?

if you really want to understand how mysql stuff works, start read webinars instructions at MySQL AB On Demand Web Seminars.

step 1 — compiling mysql 5.0.41 on debian linux

mysql help about install from source

wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.41.tar.gz/from/http://mirror.switch.ch/ftp/mirror/mysql/

tar -xvvf mysql-5.0.41.tar.gz

./configure --prefix=/usr/local/mysql --with-collation=latin1_general_ci

packages that may be need: libncurses5-dev

make -j4

make install

useful options:
--localstatedir=/usr/local/mysql/data
--enable-thread-safe-client
--with-low-memory
--prefix=/usr/local/mysql
--with-charset=latin1
--with-collation=latin1_general_ci
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
--with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static
--with-openssl
--with-mysql-user=mysql
--enable-assembler
--with-mysqld-ldflags=-all-static # not for user-defined functions
--with-unix-socket-path=/tmp/mysql.sock
Where to put the unix-domain socket. SOCKET must be
an absolute file name.

after all done, check your /etc/mysql/my.cnf (server specific options) and /etc/my.cnf (global options) for same socket path. i found that in global conf, socket placed in /tmp/mysql.sock, but server specific - /var/run/mysqld/mysqld.sock. that make error when you trying to run mysql tool. if you do not want to change - workaround is to use "-S socket_path" .