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:

port        = 3306
socket      = /var/run/mysqld/mysqld.sock

socket      = /var/run/mysqld/mysqld.sock
nice        = 0

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
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
expire_logs_days    = 10
max_binlog_size         = 100M

max_allowed_packet  = 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:

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


Please see my other posts on these settings

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:

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

| improve this answer | |
  • do you think it's good to activate multi-core capabilities with a Pentium4 machine? (yes, i know there were a few dual-core P4s, but this doesn't seem like one) – Javier Oct 23 '12 at 18:54
  • One thing is for sure: InnoDB will never perform any better without the changes. You will have to experiment with the values I gave you for innodb_read_io_threads, innodb_write_io_threads, innodb_log_buffer_size. innodb_thread_concurrency must be zero at all times (it should be the default for MySQL 5.5) – RolandoMySQLDBA Oct 23 '12 at 19:05

quick drive-by suggestion...

innodb_flush_method=O_DIRECT should only be used for storage devices that have their own write cache (battery-backed RAID card, or a SAN, for example). On a normal hard drive like it appears you have, let the OS do the buffering.

You may also want to verify you haven't filled your memory and started swapping. If so you'll probably want to lower your innodb_buffer_pool_size. I would aim for having at least 25% free memory for the OS to use for caching.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy