Tag Archives: MySQL

Memcached & MySQL

memcached (pronunciation: mem-cash-dee.) is a general-purpose distributed memory caching system that was originally developed by Danga Interactive for LiveJournal, but is now used by many other sites. It is often used to speed up dynamic database-driven websites by caching data and objects in memory to reduce the number of times an external data source (such as a database or API) must be read. Memcached is distributed under a permissive free software license. Memcached lacks authentication and security features, meaning it should only be used on servers with a firewall set up appropriately. By default, memcached uses the port 11211. Among other technologies, it uses libevent. Memcached’s APIs provides a giant hash table distributed across multiple machines. When the table is full, subsequent inserts cause older data to be purged in least recently used (LRU) order. Applications using memcached typically layer memcached requests and additions into core before falling back on a slower backing store, such as a database. You can download memcached API from http://www.danga.com/memcached/

 

Advertisements

How to recover root password in MySQL?

  1. As Linux system root user stop the database process: /etc/init.d/mysql stop
    (or: service mysql stop)
  2. 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 &
  3. Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword
  4. Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`
  5. Start MySQL: /etc/init.d/mysql start
  6. 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.

  1. Hardware (RAM, DISK, CPU etc)
  2. Operating System (i.e. Linux OS will give the more performance compare to Windows OS )
  3. Application
  4. 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 3rd 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

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.

MySQL Question and Answers

  1. How do you start and stop MySQL on Windows? – net start MySQL, net stop MySQL
  2. How do you start MySQL on Linux? – /etc/init.d/mysql start
  3. Explain the difference between mysql and mysqli interfaces in PHP? – mysqli is the object-oriented version of mysql library functions.
  4. What’s the default port for MySQL Server? – 3306
  5. What does tee command do in MySQL? – tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
  6. Can you save your connection settings to a conf file? – Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
  7. How do you change a password for an existing user via mysqladmin? – mysqladmin -u root -p password “newpassword”
  8. Use mysqldump to create a copy of the database? – mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
  9. Have you ever used MySQL Administrator and MySQL Query Browser? Describe the tasks you accomplished with these tools.
  10. What are some good ideas regarding user security in MySQL? – There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
  11. Explain the difference between MyISAM Static and MyISAM Dynamic. – In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
  12. What does myisamchk do? – It compressed the MyISAM tables, which reduces their disk usage.
  13. Explain advantages of InnoDB over MyISAM? – Row-level locking, transactions, foreign key constraints and crash recovery.
  14. Explain advantages of MyISAM over InnoDB? – Much more conservative approach to disk space management – each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
  15. What are HEAP tables in MySQL? – HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
  16. How do you control the max size of a HEAP table? – MySQL config variable max_heap_table_size.
  17. What are CSV tables? – Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
  18. Explain federated tables. – Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
  19. What is SERIAL data type in MySQL? – BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
  20. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table? – It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
  21. Explain the difference between BOOL, TINYINT and BIT. – Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
  22. Explain the difference between FLOAT, DOUBLE and REAL. – FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
  23. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table? – 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
  24. What happens if a table has one column defined as TIMESTAMP? – That field gets the current timestamp whenever the row gets altered.
  25. But what if you really want to store the timestamp data, such as the publication date of the article? – Create two columns of type TIMESTAMP and use the second one for your real data.
  26. Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP – The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
  27. What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do? – On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
  28. Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP. – A default value is used on initialization, a current timestamp is inserted on update of the row.
  29. If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table? – CHAR(3), since MySQL automatically adjusted the data type.
  30. Explain MySQL architecture. – The front layer takes care of network connections and security authentications, the middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL (MyISAM) or a commercial one supplied by a third-party vendor (ScaleDB, InnoDB, etc.)
  31. Explain MySQL locks. – Table-level locks allow the user to lock the entire table, page-level locks allow locking of certain portions of the tables (those portions are referred to as tables), row-level locks are the most granular and allow locking of specific rows.
  32. Explain multi-version concurrency control in MySQL. – Each row has two additional columns associated with it – creation time and deletion time, but instead of storing timestamps, MySQL stores version numbers.
  33. What are MySQL transactions? – A set of instructions/queries that should be executed or rolled back as a single atomic unit.
  34. What’s ACID? – Automicity – transactions are atomic and should be treated as one in case of rollback. Consistency – the database should be in consistent state between multiple states in transaction. Isolation – no other queries can access the data modified by a running transaction. Durability – system crashes should not lose the data.
  35. Which storage engines support transactions in MySQL? – Berkeley DB and InnoDB.
  36. How do you convert to a different table type? – ALTER TABLE customers TYPE = InnoDB
  37. How do you index just the first four bytes of the column? – ALTER TABLE customers ADD INDEX (business_name(4))
  38. What’s the difference between PRIMARY KEY and UNIQUE in MyISAM? – PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.
  39. How do you prevent MySQL from caching a query? – SELECT SQL_NO_CACHE …
  40. What’s the difference between query_cache_type 1 and 2? – The second one is on-demand and can be retrieved via SELECT SQL_CACHE … If you’re worried about the SQL portability to other servers, you can use SELECT /* SQL_CACHE */ id FROM … – MySQL will interpret the code inside comments, while other servers will ignore it.

How do I reclaim Space from the INNODB (Ibdata) file?

This is the major issue with the Innodb engine, the bulk delete/truncate operation will deleted the data but it is not reclaiming the space from the Innodb file. There are some techniques to reclaim the space but please make sure you must have the extra space on your servers.

1. Alter Database Set Engine = Innodb

2. Instead of truncate use the DROP statement.

3. Migrate your MySQL databases to another server MySQL server using MySQL dump utility and remove the ibdata file. Further migrate the backup database to orginal server which will makes the ibdata1 file fully de-fragmented.