Tag Archives: MySQL

Memcached & MySQL

How to recover root password in MySQL?

As Linux system root user stop the database process: /etc/init.d/mysql stop
(or: service mysql stop)
Start MySQL in safe mode and skip the use of the “grant tables”: /usr/bin/mysqld_safe –user=mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid –datadir=/var/lib/mysql –skip-grant-tables –skip-networking &
Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword
Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`
Start MySQL: /etc/init.d/mysql start
The new MySQL root password can now be used: mysql -u root -p
Respond withthe password: newpassword

MySQL Optimization Tips

(for more database related articles)

MYSQL Optimization Tips
The MySQL database server performance depends on the number of factors. The Optimized Query is one of the factors for the MySQL robust performance.

The MySQL performance depends on the below factors.

Hardware (RAM, DISK, CPU etc)
Operating System (i.e. Linux OS will give the more performance compare to Windows OS )
Application
Optimization of MySQL Server & Queries

· Choose compiler and compiler options.

· Find the best MySQL startup options for your system (my.ini/my.cnf).

· Use EXPLAIN SELECT, SHOW VARIABLES, SHOW GLOBAL STATUS, SHOW GLOBAL STATUS and SHOW PROCESSLIST.

· Optimize your table formats.

· Maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE).

· Use MySQL extensions to get things done faster.

· Write a MySQL UDF function if you notice that you would need some function in many places.

· Don’t use GRANT on table level or column level if you don’t really need it

· Use Index columns in joins

· Use better data types for the table design. (i.e. “INT” data type is better than “BIG INT” data type)

· Increase the use of “NOT NULL” at table level, that will save some bits

· Do not use UTF8 where you do not need it. UTF8 has 3 times more space reserved. Also UTF8 comparison and sorting is much more expensive. Only use UTF8 for mixed charset data

· Use staraight_join instead of inner join

· Use joins instead of “IN” or “Sub-Queries”

· Decide the database engine for the table by most effective way. (INNODB, MEMORY, ARCHIVE etc)

· INNODB database engine needs more performance and tuning for the MySQL Server & Query optimization.

· Try to create Unique Index. Avoid duplicate data in the index columns.

· Beware of Large Limit

• LIMIT 1000000, 10 can be slow. Even Google does not let you to page 100000. If large number of groups use

SQL_BIG_RESULT hint. Use FileSort instead of temporary table

· USE Index hints (INDEX/FORCE INDEX/IGNORE INDEX) (i.e SELECT * FROM Country IGNORE INDEX(PRIMARY)). This will give the advice to MySQL for the Index Use.

· Use “UNION ALL” instead of “UNION”

· Do not normalize the schema up to more than 3^rd Level NF

· Avoid the use of cursors in the stored procedure if not required.

· Avoid the use DDL statements in the stored procedure if not required.

· Use SQL for the things it’s good at, and do other things in your application. Use the MySQL server to:

· Find rows based on WHERE clause.

· JOIN tables

· GROUP BY

· ORDER BY

· DISTINCT

Don’t use MySQL server:

· To validate data (like date)

· As a calculator

· Use keys wisely.

· Keys are good for searches, but bad for inserts / updates of key columns.

· Keep by data in the 3rd normal database form, but don’t be afraid of duplicating information or creating summary tables if you need more speed.

· Instead of doing a lot of GROUP BYs on a big table, create summary tables of the big table and query this instead.

· UPDATE table set count=count+1 where key_column=constant is very fast!

· For log tables, it’s probably better to generate summary tables from them once in a while than try to keep the summary tables live.

· Take advantage of default values on INSERT.

· Use Index columns in joins

· Use the explain command
Use multiple-row INSERT statements to store many rows with one SQL statement.

The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.

Example of usage: explain select * from table

Explanation of row output:

o table—The name of the table.

o type—The join type, of which there are several.

o possible_keys—This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.

o key—The key actually used in this query, or NULL if no index was used.

o key_len—The length of the key used, if any.

o ref—Any columns used with the key to retrieve a result.

o rows—The number of rows MySQL must examine to execute the query.

o extra—Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).

· Use less complex permissions

The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.

· Specific MySQL functions can be tested using the built-in “benchmark” command

If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute

· Optimize where clauses

o Remove unnecessary parentheses

o COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.

o If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table

· Run optimize table

This command de-fragments a table after you have deleted/inserted lots of rows into table.

· Avoid variable-length column types when necessary

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.

· Insert delayed

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

· Use statement priorities

o Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.

o Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.

· Use multiple-row inserts

Use multiple-row INSERT statements to store many rows with one SQL statement.

· Synchronize data-types

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

· Optimizing tables

o MySQL has a rich set of different types. You should try to use the most efficient type for each column.

o The ANALYSE procedure can help you find the optimal types for a table: SELECT * FROM table_name PROCEDURE ANALYSE()

o Use NOT NULL for columns which will not store null values. This is particularly important for columns which you index.

o Change your ISAM tables to MyISAM.

o If possible, create your tables with a fixed table format.

o Don’t create indexes you are not going to use.

o Use the fact that MySQL can search on a prefix of an index; If you have and INDEX (a,b), you don’t need an index on (a).

o Instead of creating an index on long CHAR/VARCHAR column, index just a prefix of the column to save space. CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))

o Use the most efficient table type for each table.

o Columns with identical information in different tables should be declared identically and have identical names.

When MySQL uses indexes

o Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.

o SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;

o SELECT * FROM table_name WHERE key_part1 IS NULL;

o When you use a LIKE that doesn’t start with a wildcard.

o SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'

o Retrieving rows from other tables when performing joins.

o SELECT * from t1,t2 where t1.col=t2.key_part

o Find the MAX() or MIN() value for a specific index.

o SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

o ORDER BY or GROUP BY on a prefix of a key.

o SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3

o When all columns used in the query are part of one key.

o SELECT key_part3 FROM table_name WHERE key_part1=1

Pros and Cons of MySQL Table Types

2 Replies

Please visit http://sqldbpool.blogspot.com/

(for more database related articles)

Of all the positive things that MySQL brings to the table, probably the most overlooked is multiple table types. This facet of the application is overlooked as a feature and more importantly is overlooked at design time.

MySQL has six distinct table types.

MyISAM
MERGE
ISAM
HEAP
InnoDB
BDB or BerkeleyDB Tables

Finding a transaction table that’s just my type

Two of these table types currently support transactions. Transactions give the user greater control when working with data. You would use syntax similar to the following for a manual transaction.

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summmary=@A WHERE type=1;

COMMIT;

Of the two commonly used transaction table types, the first is BerkeleyDB transactional tables provided by SleepyCat (www.sleepycat.com). In order to use BDB tables use a binary with BDB support or configure the source with the withberkeleydb option. If you don’t want to use BDB tables, start the MySQL server with the skipbdb option. This will save a lot of memory, since the BDB library won’t be included. However, you won’t be able to use BDB tables. BDB is not used nearly as much as our second alternative which is InnoDB. InnoDB features rowlevel locking, consistent nonlocking read in SELECTs and common tablespace for all tables.

InnoDB Tables are made by Innobase Oy (www.innodb.com), and are distributed under the GPL as well as commercially. If you need commercial support or commercial licenses for your application and cost is a concern, not using InnoDB will save you about 20-50 % for licenses and support contracts. If data integrity is a concern InnoDB provides MySQL with a transactional storage engine and crash recovery capabilities. InnoDB has been designed for maximum performance when processing large data volumes and any other diskbased relational database engine does probably not match CPU efficiency. There are other transactional table types out there (such as Gemini), but they do not seem to be used any more than BDB. So, in a nutshell, most users prefer the speed and features of InnoDB.

A Database is no fun when you are locked out

The default table type for MySQL is MyISAM. It has table level locking, which means during an UPDATE, nobody can access any other record of the same table. BDB uses Page level locking, and during an UPDATE, nobody can access any other record residing in the same database page of that table, until the locking transaction issues a COMMIT.

InnoDB however, uses Row level locking. Row level locking ensures that during an UPDATE, nobody can access that particular row, until the locking transaction issues a COMMIT. Any of the above table types will probably be fine for a web server, but in a LAN application can cause unecessary issues.

Special circumstances call for special tools (or tables)

MERGE tables are a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification. One reasons why you would use MERGE tables is to get more speed. You can split a big read-only table and then put the different table parts on different disks. You could do more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a MERGE table for others. Repairs are more efficient. It’s easier to repair the individual files that are mapped to a MERGE file than trying to repair a really big file. MyISAM and therefore MERGE tables are represented as individual files on the harddrive. You can go around the file-size limit for the operating system.

Some of the disadvantages of using MERGE tables are:

You can only use identical MyISAM tables for a MERGE table.
REPLACE doesn’t work.
Key reads are slower.

Also, you can’t do DROP TABLE, ALTER TABLE, DELETE FROM table_name without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the table that is mapped by a MERGE table that is “open”. If you do this, the MERGE table may still refer to the original table and you will get unexpected results. The easiest way to get around this deficiency is to issue the FLUSH TABLES command, ensuring no MERGE tables remain “open”.

Well, that should make you think twice about using MERGE tables. ISAM tables will disappear in MySQL version 5.0, so it wouldn’t be a good idea to use them. Last but not least is the HEAP table type. HEAP tables use hashed indexes and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. They are very useful for temporary tables. HEAP sounds cool but I don’t think the risk justifies the performance.

The Lowdown on MySQL Table Types

Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine. With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence. Most of my customers want as much speed as they can get, but at the end of the day, good data integrity lets them sleep at night.