MySQL RDBMS is not a real RDBMS, but it has a lot of similarities with any of them.
MySQL has a configuration file called my.cnf (usually placed in the `/etc folder of the Linux box).
By checking this file, you’ll notice that MySQL’s configuration is set to the classic/standard one dedicated to small machines with no heavy traffic.
The file contains only the basic settings needed by any MySQL server.
In my example, I had a heavy query that took almost 90% of the CPU performance for more than 15 minutes. No RAM was improperly used, and no buffers were altered during these 15 minutes.
I succeeded in reducing the query time to 1 ~ 3 minutes.

This query touches many tables in the database, in a repetitive select mode.
It also uses a large number of joined fields/columns, which takes a huge amount of CPU time.
This means, as a good tuner, that you must know the behavior of your application with the database, which tables are used, their weights (number of records), and the complexity of the queries.
This post will not show you how to optimize already written queries, but it’s better to rewrite them if necessary.
It’s also better to use the InnoDB engine rather than the MyISAM one. InnoDB offers a more powerful engine with more flexibility and reliability.
It’s imperative to know the OS and hardware hardiness, threading ability, number of CPUs, disk’s RPM, RAM usage and availability, shared buffers, etc.
In this case, my idea was to let MySQL use the RAM and buffers and let the CPU breathe without accessing the hard disk all the time.
First of all, I checked all tables’ indexes (using mysql> OPTIMIZE TABLE your_table).
I wrote a new my.cnf file, which is a mixture of two standard configuration files (my-large.cnf & my-huge.cnf, both present under the documentation folder).

# system is : hp-ux 11 box, dual cpu, 512MB
[mysqld]
datadir = /opt/mysql/data
socket = /tmp/mysql.sock
port = 3306
skip-locking
key_buffer_size = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 16M
thread_cache_size = 8M
query_cache_size = 16M
thread_concurrency = 8
join_buffer_size = 8M
memlock
bulk_insert_buffer_size = 64M
[mysql]
no-auto-rehash
[isamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size= 2M
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M
[mysql.server]
basedir = /opt/mysql

as we see, the file got 5 sections

section 1

mysqld : this section is specific to the server itself

  • datadir : directory where are stored the dbs
  • socket : a socket/pipe file to use
  • port : tcp/ip port number
  • skip-locking : do not lock tables
  • key_buffer_size : buffer amount for tables indexes
  • max_allowed_packet : the max size any generated packets/strings
  • table_cache : the number of open tables for all threads
  • sort_buffer_size : buffer’s size to each thread for sort purposes
  • read_buffer_size : buffer’s size allocated to each thread to scan tables
  • myisam_sort_buffer_size : size of buffer for myisam tables
  • thread_cache : buffer’s size to cache already used thread
  • query_cache_size : amount of memory to cache query results
  • thread_concurrency : number of thread to be used simultaneously
  • join_buffer_size : size of buffer used for joins queries
  • memlock : lock the daemon in the memory
  • bulk_insert_buffer_size : buffer’s size to make inserts faster

section 2

mysql : section for the command line

  • no-auto-rehash : no auto scan

section 3,4

{my}isamchk : section for [auto] checking the tables

  • same meaning as mysqld section but only when repairing, checking the tables

section 5

command line to stop/start mysql server

  • basedir : pass to the script the mysql directory

After restarting the server and executing the application, the server is quicker with less execution time. This configuration can be further tuned; after some observations, certain parameters can be adjusted for better use of the machine’s resources.