The best MySQL configuration for my server I'm little newbie in configuring mysql and I need some advices.

Few days ago I made little home server on Ubuntu Server 12.04 with samba, mysql 5.5, apache 2.2, PHP 5.4.6 etc.

I'm using it to sharing files per samba and hosting my local projects in PHP/MySQL - I'm programmer, and I'm often testing new solutions etc

It is working good, but INSERTS on InnoDB tables are slow as hell.

I think it is bad config in my.cnf, becouse a few months earlier I used the same computer on Windows WAMP and queries were faster...

Some information:


CPU: Intel(R) Pentium(R) 4 CPU 3.00GHz
RAM: 1GB
HDD: Maxtor 6L200M0 (189GiB (203GB))


Actual my.cnf:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer      = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size        = 32M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=10M
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=80M
expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M
You may find this surprising but MySQL 5.5 can be configured to access multiple cores for InnoDB. The features are not on by default. In fact, there are occasions when older versions of MySQL run faster "out-of-the-box" than MySQL 5.5.

I have written posts about this before:


Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Jun 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5


You must set a few options to wake up InnoDB as to its multiple core capabilities

[mysqld]
innodb_io_capacity=2000
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_log_buffer_size=32M
innodb_thread_concurrency=0


Please see my other posts on these settings


May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?


BTW your log buffer is a little too big. I lowered it to 32M because while a bigger log buffer saves on Disk I/O, it makes for longer COMMITs.

I also noted that you have all the InnoDB inside ibdata1 because innodb_file_per_table is disabled. Try the above setting anyway. If you want to move data and indexes out of ibdata1 and into their own tablespace files, please see my past posts on cleaning up InnoDB for data and index storage outside of ibdata1:


May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?


This will control excessive growth of ibdata1 and allow you to compress data and indexes.