2

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
Improve this question
1

2 Answers 2

Reset to default
1

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

[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

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
2
  • 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, 2012 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, 2012 at 19:05
0

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 and acknowledge that you have read and understand our privacy policy and code of conduct.