MySQL Optimization

[admin]

Server (PC):
- 64 bit CPUs will give much better performance;
- the optimization order is normally RAM, Fast disks, CPU power;
- more RAM can speed up key updates by keeping most of the used key pages in RAM;
- a UPS is good idea to be able to take the system down nicely in case of a power failure;
- split databases and tables over different disks. In MySQL you can use symbolic links for this;
- striping disks (RAID 0) will increase both read and write throughput;
- striping with mirroring (RAID 0+1) will give you safety and increase the read speed. Write speed will be slightly lower;
- on Linux use hdparm -m16 -d1 on the disks on boot to enable reading/writing of multiple sectors at a time, and DMA. This may increase the response time by 5-50 %;
- on Linux, mount the disks with async (default) and noatime;
- increase number of open files for system and for the SQL server (add ulimit -n # in the safe_mysqld script);
- increase the number of processes and threads for the system.

Maintenance:
- if possible, run OPTIMIZE table once in a while. This is especially important on variable size rows that are updated a lot;
- update the key distribution statistics in your tables once in a while with myisamchk -a; Remember to take down MySQL before doing this!
- if you have problems, check your tables with myisamchk or CHECK table;
- monitor MySQL status with: mysqladmin -i10 processlist extended-status;
- use mysqladmin debug to get information about locks and performance.

Speed SQL (MyISAM Engine):
- keys are good for searches, but bad for inserts / updates of key columns;
- 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);
- 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))) ;
- MySQL will create the table in fixed size table format if all columns are of fixed length format (no VARCHAR, BLOB or TEXT columns). If not, the table is created in dynamic-size format;
- fixed-size format is much faster and more secure than the dynamic format;
- the dynamic-size row format normally takes up less space but may be fragmented over time if the table is updated a lot;
- in some cases it's worth it to move all VARCHAR, BLOB and TEXT columns to another table just to get more speed on the main table;
- use fixed row size if possible;
- use the different lock types (SELECT HIGH_PRIORITY, INSERT LOW_PRIORITY);
- Use: Delayed_keys # INSERT DELAYED # LOAD DATA INFILE / LOAD_FILE() # SELECT INTO OUTFILE # LEFT JOIN, STRAIGHT JOIN # GET_LOCK()/RELEASE_LOCK() # LOCK TABLES
- use multi-row INSERT to insert many rows at a time;
- INSERT and SELECT can run concurrently.

Source: http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html

Benchmarks:
- MySQL 5.0.51a-24+lenny1 – Debian stable version is the worst performer (also the MySQL version is a little bit old).
- MySQL 5.1.30/InnoDB 1.0.3 with Google SMP patch – Compiled by SUN and InnoDB compiled by ORACLE outperform PostgreSQL in some cases.
- PostgreSQL 8.3.7-0lenny1 – Debian Stable version – It’s a top performer from the Debian standard distro and is out performed only by the latest InnoDB 1.0.3.
- Differences between PostgreSQL and MySQL 5.1.30/InnoDB 1.0.3 are very small
- PostgreSQL is outperforming any MySQL version on Creating, Loading, Created Indexes operations (this can be very usefull on a DB recovery).

Source: http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html

Next step, MySQL 5.x: http://dev.mysql.com/doc/refman/5.0/en/query-speed.html
_________________

[nospam]

What the?

XPertMailer goes MySQL?

[admin]

This post about MySQL is like a reminder for me, i want to see from every place i'm
_________________