![[info]](http://l-stat.livejournal.com/img/userinfo.gif){kind=small}
Shantanu Oak
Aug. 21st, 2009
05:09 pm - rewriting a sub query to use joins
SELECT u.uid,
u.username,
u.nid,
wd.pay
FROM (users_data AS u
LEFT JOIN winners_data wd
ON u.nid = wd.nid
AND u.uid = wd.uid)
LEFT JOIN dp_node dn
ON u.nid = dn.nid
WHERE u.uid = ".$val."
AND ((dn.TYPE = 'event'
AND (SELECT Count(nid)
FROM tournament_event
WHERE nid = u.nid
AND type_value IN ('A','B','C')) > 0)
OR (dn.TYPE = 'new_event'
AND (SELECT Count(nid)
FROM user_tournament_event
WHERE nid = u.nid
AND type_0_value IN ('Y','X')) > 0))
ORDER BY nid ASC
Apr. 22nd, 2009
02:48 pm - decrease the shutdown time
One way to decrease the shutdown time is to pre-flush the dirty pages, like this:
mysql> set global innodb_max_dirty_pages_pct = 0;
Now run the following command:
$ mysqladmin ext -i10 | grep dirty
| Innodb_buffer_pool_pages_dirty | 1823484 |
| Innodb_buffer_pool_pages_dirty | 1821293 |
| Innodb_buffer_pool_pages_dirty | 1818938 |
And wait until it approaches zero. (If the server is being actively used, it won't get to zero.)
Once it's pretty low, you can perform the shutdown and there'll be a lot less unfinished work to do, so the server should shut down more quickly.
![[mood icon]](http://l-stat.livejournal.com/img/mood/classic/smile.gif){kind=small}
surprisedOct. 12th, 2008
06:01 pm - Analyse mysql config using MySQLTuner
[root@localhost Desktop]# cd /root/
[root@localhost ~]# wget mysqltuner.pl/mysqltuner.pl
--17:58:59-- http://mysqltuner.pl/mysqltuner.pl
Resolving mysqltuner.pl... 209.20.89.226
Connecting to mysqltuner.pl|209.20.89.226|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 38063 (37K) [text/plain]
Saving to: `mysqltuner.pl'
100%[===================================
17:59:01 (36.2 KB/s) - `mysqltuner.pl' saved [38063/38063]
[root@localhost ~]# ./mysqltuner.pl
bash: ./mysqltuner.pl: Permission denied
[root@localhost ~]# chmod +x mysqltuner.pl
[root@localhost ~]# ./mysqltuner.pl
>> MySQLTuner 0.9.9 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
-------- General Statistics ----------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.44sp1-enterprise-gpl-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics ----------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 250M (Tables: 35)
[--] Data in InnoDB tables: 145M (Tables: 29)
[--] Data in ARCHIVE tables: 95M (Tables: 3)
[!!] Total fragmented tables: 2
-------- Performance Metrics ----------------------------------------
[--] Up for: 14h 27m 20s (569 q [0.011 qps], 18 conn, TX: 63K, RX: 9K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 1.0G global + 23.1M per thread (400 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 10.1G (1179% of installed RAM)
[OK] Slow queries: 0% (0/569)
[OK] Highest usage of available connections: 0% (1/400)
[OK] Key buffer size / total MyISAM indexes: 1.0G/130.9M
[!!] Key buffer hit rate: 66.7% (6 cached / 2 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 17% (15 on disk / 88 total)
[OK] Thread cache hit rate: 94% (1 created / 18 connections)
[OK] Table cache hit rate: 92% (86 open / 93 opened)
[OK] Open file limit used: 5% (112/2K)
[OK] Table locks acquired immediately: 100% (25 immediate / 25 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 145.4M/8.0M
-------- Recommendations ----------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage exceeds your installed memory ***
*** Add more RAM before increasing any MySQL buffer variables ***
query_cache_size (>= 8M)
innodb_buffer_pool_size (>= 145M)
Sep. 24th, 2007
11:26 am - Backup from binlog and other tips
We had an unscheduled test of our backups last night. Point-in-time recovery using a mysqldump and binary log files worked fine (thank goodness).
Last night we had some user-generated data corruption on a production server. The database was relatively small (a few Gig), so, after stopping the database and restarting it with ––skip-networking, I imported it from the latest daily mysqldump. Although I didn’t use ––master-data for the mysqldump (we had some locking issues with that in the past) I knew the time that the mysqldump had been started.
I did a little investigation into the binary log files using mysqlbinlog
mysqlbinlog ––start-datetime=”2007-09-17 11:25:00? ––stop-datetime=”2007-09-17 11:30:00? binlog.000003 > my_tempfile
and found out the exact time I wanted to start rolling forward from. (I already knew when to stop rolling forward.) Then I ran it
mysql -p our_database < mysqlbinlog ––start-datetime=”2007-09-17 11:26:00 ––stop-datetime=”2007-09-17 20:45:00?
Checked things out, shut down mysql and restarted it normally, and we were once again off and running.
(Luckily for us, in this case I didn’t need to know an exact or down-to-the-second time to start rolling forward from. Activity was very light around the time of the mysqldump. Also, luckily, we didn’t need a starttime more precise than to-the-second. If we had needed to be more precise, I think I would have needed a mysqldump taken with the ––master-data option.)
http://oracle2mysql.wordpress.com/2
If you’re new to MySQL, you might not know that in the default configuration ‘a’='A’. Ie, string comparisons are by default case-insensitive. If this is a surprise to you, read up on Chapter 9 of the online manual, on character sets and collations.
The default character set is latin1 and the default collation is latin1_swedish_ci (’ci’ stands for ‘case-insensitive). If you don’t want ‘a’ to equal ‘A’, you can change this by setting the variables ‘character-set-server’ and ‘collation-server’ in your config file or your startup options. For example, we want to support unicode, so we use character-set-server=’utf8? in our config file. We also added the following (in the [mysqld] section) so that clients will use the right character set and collation, too: init_connect=’set names utf8; set collation_connection=utf8_bin’.
http://oracle2mysql.wordpress.com/2
MySQL is not so heavily-instrumented as Oracle. (Back to tuning using ratios rather than the wait interface… sigh…) But it does offer (among other tools) the slow query log. Turn it on (put ‘log_slow_queries‘ in your config file), and all queries that take longer than long_query_time seconds (also set in your config file) will be logged to a slow query log file, along with the time they took to execute. Then you can use mysqldumpslow to analyze the output. You can see, for example, which slow queries are taking the most cumulative time on your server, or are being executed most frequently. (The manual doesn’t say much about it, and mysqldumpslow ––help doesn’t give too much help, but I think mysqldumpslow -s t and mysqldumpslow -s c do that.)
If you use InnoDB, though (as we do), be aware that the “lock time” logged in the slow query log only counts time for table-level locks that are taken at the MySQL top level, not InnoDB locks taken at the storage-engine level. (The logging is done at the “top” level.) So the “lock time” in the log is pretty useless for you. (You can use innodb_lock_monitor for that, but that’s another story…)
http://oracle2mysql.wordpress.com/2
Improve Performance of MySQL
If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.
The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.
Add the following line under [mysqld]
max_connections=250
http://kb.mediatemple.net/article.php?i
The following two commands will let you know if query cache is being used or not.
SHOW STATUS LIKE '%qcache%';
SHOW VARIABLES LIKE "query%';
To enable query cache you have to add the following two lines in /etc/my.cnf
query-cache-type = 1
query-cache-size = 20M
http://www.databasejournal.com/feat
Memcached is probably the most popular distributed caching system and it works great. I should write an article comparing performance of various caching systems some time.
http://www.danga.com/memcached/
happyJun. 1st, 2007
04:12 pm - MySQL Backup
If your MySQL server does not need to be available 24x7, a fast and easy offline raw backup method is:
1. Stop the MySQL server:
# /etc/init.d/mysqld stop
2. Copy MySQL's data files and directories. For example, if your MySQL data directory is /var/lib/mysql and you want to save it to /tmp/mysql-backup:
# cp -r /var/lib/mysql /tmp/mysql-backup
Instead of cp, you can use rsync, tar, gzip, or other commands.
3. Start the server again:
# /etc/init.d/mysqld start
http://applications.linux.com/applicati
May. 24th, 2007
08:02 pm - Strict Mode in MySQL
If you try to insert invalid data, MySQL will silently insert the adjusted value. Next time when you try to add invalid data, since the possible value has already been added, it will show duplicate data error and reject to add the second invalid data.
For e.g.
create table mytest (mypk varchar(1) not null primary key, name varchar(100));
insert into mytest values ('m150', 'USA');
insert into mytest values ('m151', 'UK');
#1062 - Duplicate entry 'm' for key 1
The first insert command is actually changed to this by MySQL
insert into mytest values ('m', 'USA');
So, the second set of values ('m', 'UK') is naturally rejected. You can use the strict sql mode (5.0.2 version or above) to avoid this problem.
SET SQL_MODE='STRICT_ALL_TABLES';
drop table mytest;
create table mytest (mypk varchar(1) not null primary key, name varchar(100));
insert into mytest values ('m150', 'USA');
insert into mytest values ('m151', 'UK');
Neither of the records in the above example will be inserted, since they do not satisfy the primary key requirements.
May. 10th, 2007
05:31 pm - INT datatype
If you make the column INT(11) ZEROFILL, on the other hand, 100 is displayed as follows: 00000000100
The 11 says to pad the number out with zeros until 11 characters have been reached, it has nothing to do with the maximum value held by INT.
| Type | Bytes | Minimum Value | Maximum Value |
| (Signed/Unsigned) | (Signed/Unsigned) | ||
TINYINT |
1 | -128 |
127 |
0 |
255 |
||
SMALLINT |
2 | -32768 |
32767 |
0 |
65535 |
||
MEDIUMINT |
3 | -8388608 |
8388607 |
0 |
16777215 |
||
INT |
4 | -2147483648 |
2147483647 |
0 |
4294967295 |
||
BIGINT |
8 | -9223372036854775808 |
9223372036854775807 |
0 |
18446744073709551615 |
Nov. 20th, 2005
12:37 pm - Two auto-TIMESTAMP columns in one table
But if you want to be less strict with your users you can still build something that more closely emulates the built-in behaviour of TIMESTAMP columns:
CREATE TABLE blog_entries (
`published` TIMESTAMP NULL DEFAULT NULL,
`updated` TIMESTAMP NULL DEFAULT NULL,
`title` VARCHAR(128)
);
CREATE TRIGGER blog_entries_publish BEFORE INSERT ON `blog_entries` FOR EACH ROW SET
NEW.published = IFNULL(NEW.published, NOW()),
NEW.updated = IFNULL(NEW.updated, '0000-00-00 00:00:00');
CREATE TRIGGER blog_entries_update BEFORE UPDATE ON `blog_entries` FOR EACH ROW SET
NEW.updated = IF(NEW.updated = OLD.updated OR NEW.updated IS NULL, NOW(), NEW.updated),
NEW.published = IFNULL(NEW.published, OLD.published);
http://www.futhark.ch/mysql/108.htm
12:06 pm - Export / Import MySQL Database
1. Export A MySQL Database
This example shows you how to export a database. It is a good idea to export your data often as a backup.
# mysqldump -u username -p password database_name > FILE.sql
Replace username, password and database_name with your MySQL username, password and database name. File FILE.sql now holds a backup of your database, download it to your computer.
2. Import A MySQL Database
Here, we import a database. Use this to restore data from a backup or to import from another MySQL server. Start by uploading the FILE.sql file to the server where you will be running this command.
# mysql -u username -ppassword database_name < FILE.sql
Replace the parts in red with your own information.
12:00 pm - Formating Output of SQL
But help is at hand, in MySQL you can format the returned results to be returned vertically rather than horizontally. This makes it easy to see data which is relevant to a single row only. To do this you simply append \G to the end of your select statement like so..
mysql> select * from emps where emp_id = 1\G
****************** 1. row *****************
emp_id: 1
emp_name: Ram
dept_id: 1
salary: 100.00
bonus: 100.00
1 row in set (0.00 sec)
10:35 am - Connect Excel to MySQL
he following will connect Excel to a Mysql database, then import a table into Excel.
This was tested on an Excel 2003 machine connecting to MySQL 4.1.14 (this website's database).
After reading numerous tutorials on how to connect Excel to Mysql they were all saying the same thing, and all of them failed (for me). This was because they were never specific on the driver to use. After reading this quick note and modifying some urls to find the download location, I achieved a successful connection, and here's how...
http://www.automateexcel.com/index.p
Oct. 30th, 2005
05:49 pm - Resource id #3
When I echo ed the following statement
$sendto1 = mysql_query("select email from yubmail where mail_alias = '$to'");
I got the message
Resource id #3
I thought it's an array so I tried mysql_fetch_array but did not get the expected results.
I checked using is_array function and found that it's not an arrary.
I tried a lot of other ways to find out what the actual value is.
At last (Should have done it first), I googled
"Resource id" php output
And the Forth result gave me the exact answer to my question.
It's a mysql issue and I have to add mysql_result function
$sendto2 = mysql_result($sendto1, 0);
I don't know how does adding one more layer helps, but it's working now.
I think open source and free software had no meaning if the powerful search engine like google was not there!
Oct. 28th, 2005
09:14 pm - Backing Up All MySQL Databases
To back up all databases on a particular server, use the mysqldump command:
$ mysqldump --all-databases -p | bzip2 -c > databasebackup.sql.bz2
Enter password:
$
This will put all of the databases in a compressed bzip2 file. Transfer the file to your other server and decompress:
$ bzip2 -d databasebackup.sql.bz2
$ ls data*.sql
databasebackup.sql
$ head -n 7 databasebackup.sql
-- MySQL dump 9.11
--
-- Host: localhost Database:
-- ----------------------------------------
-- Server version 4.0.22-standard
--
$
Restore your backup using the mysql command:
$ mysql -p < databasebackup.sql
Enter password:
$
Do be doubly sure that you aren't restoring over something that exists already. This command is ideal for rebuilding a server, but is risky if there are already MySQL databases and tables on the server.
http://www.netadmintools.com/art408.htm
Oct. 24th, 2005
03:43 pm - Programmatically deciding which database to connect in PHP
After writing and debugging the code on my local machine, I would change the values of $user_name, $pwd and $database_name and upload the files to my client’s server. While I was developing the application (it was my first PHP project and I still maintain the program and the database for my client) there was no problem, as I would remember to change the values. But when, after the launch of the website, I routinely started altering the program (according to my clients interminably changing needs) I woud often forget to change the values, and consequently, render the website disfunctional. Then I changed the connect_database() function....
http://www.developertutorials.com/tutor
impressedOct. 11th, 2005
10:28 am - MySQL's Over-looked and Under-worked Slow Query Log
The slow query log is one of the less-used logs, as by default it's not activated, but it's a useful log for identifying queries that are not optimal. Often, in an under-performing system, especially with the default MyISAM tables (that make use of table-level locking, not row-level locking), a single query may be the cause of problems.
To activate the query log, simply place:
log-slow-queries = [slow_query_log_filename]
in your configuration file (my.cnf or my.ini), slow_query_log_filename being the optional filename for your log file. If you don't supply a filename, the default name will be used, which is the name of the host machine, with -slow.log being appended.
The slow query log logs all queries that take longer than long_query_time, which is usually 10 seconds by default (more than long enough for a self-respecting query to complete). You can alter the long_query_time in the configuration file. The following example sets the time to 5 seconds:
set-variable = long_query_time = 5
The slow query log can also optionally log all queries that don't use an index by placing the following in the configuration file:
log-long-format
Let's take a look at a sample slow query log:
/usr/local/mysql/libexec/mysqld, Version: 3.23.54-log, started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 030207 15:03:33
# User@Host: wsuser[wsuser] @ localhost.localdomain [127.0.0.1]
# Query_time: 13 Lock_time: 0 Rows_sent: 117 Rows_examined: 234
use wsdb;
SELECT l FROM un WHERE ip='209.xx.xxx.xx';
The first few lines simply contain version information, but the really useful data begins with the Time. This tells you what time the query completed. The next line contains user data, and the following how long the query took to run, how long it took to secure its locks, how many rows were sent back as a result, and how many rows were examined to determine the result. The final line before the actual query tells you which database was used. A discussion on optimizing queries is beyond the scope of this article, but you should know something about this before you can get the most out of the log. Otherwise, you may be able to identify the un-optimal queries, but not be able to do much with them. You should read the article Optimizing MySQL: Queries and Indexes if you are unclear about the top
http://www.databasejournal.com/feat
Oct. 1st, 2005
05:21 pm - MySQL Bug
Just came across this bug in my code, if you have something like:
DELETE
FROM randomstrings WHERE userid
IN
(
SELECT
userid
FROM users
WHERE username = '$user'
);
And users table does not contain a column named userid, what does MySQL do? Give you an error message? No, it deletes everything from randomstrings, and tells me that the query executed successfully!
http://nerdierthanthou.nfshost.com/2
![[mood icon]](http://l-stat.livejournal.com/img/mood/classic/angry.gif){kind=small}
annoyedSep. 21st, 2005
07:31 am - There can be only –one-database!!
Just a quick tip: let's say you are trying to restore a backup done with mysqldump that contains multiple databases, but you only want to restore a single database.
Start my launching mysql client with –one-database
mysql -u root -p --one-database mydatabase
Then use the SOURCE command:
source ~/multidatabase.sql
The –one-database option forces the mysql client to ignore statements intended for any database other than the one you specified when you launched the client, allowing you to restore your database without affecting any other databases.