mysql rdbms is not a real rdbms system but it has got a lot of similarities like any of them.
mysql has a configuration file called my.cnf (usually placed under the /etc folder of the linux box).
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 which toke almost 90% of the cpu performance for more than 15mn…no ram was improperly used and no buffers was altered during these 15mn.
i succeeded in reducing the query time to 1 ~ 3mn …

this query touchs many tables in the db, in a repetitive select mode.
it uses too a big number of joined fields/columns, which takes also a huge amount of the cpu time.
this means, as a good tuner, that you must know what is the behavior of your application with the db, what are the used tables, their weights (number of records) and the complexity of the queries.
this post will not show you how to optimize already written querries, but it’s better to rewrite them if necessary.
it’s better too to use innodb engine than the myisam one. innodb offers a more powerful engin with more flexibility and relability.
it’s imperative also to know what is the OS and hardware hardiness, threading ability, cpu’s number, disk’s rpm, ram usage and availibility, shared buffers…
for this case, my idea was to let mysql uses the ram and buffers and let the cpu breaths without acceding all the time to the hard disk.
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 between two standard cofiguration 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 more tuned, after some observations, some parameters can be re-edited for better use of machine’s ressources.