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