optimization

I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
running the latest mysql-server from debian lenny (5.0.1). I have databases
for drupal, moodle, spamassassin, horde3, and a small database for
departmental stuff.

The problem is that inserts/updates are sometimes very slow, on the order of
a minute. I am hoping somebody can sspot something wrong in my config.
Here's the optimization settings section (for your convenience). The whole
my.cnf is reproduced below that:

key_buffer = 256M
max_allowed_packet = 16M
max_connections=2000
myisam_sort_buffer_size = 64M
open_files_limit = 8192
query_cache_min_res_unit= 2K
query_cache_size= 36M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
sort_buffer_size = 1M
table_cache = 512
thread_cache = 32
thread_cache_size = 8
thread_concurrency = 8

---
# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes

port = 3306
socket = /var/run/mysqld/mysqld.sock
skip-locking

#wait_timeout=14400
key_buffer = 256M
max_allowed_packet = 16M
max_connections=2000
myisam_sort_buffer_size = 64M
open_files_limit = 8192
query_cache_min_res_unit= 2K
query_cache_size= 36M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
sort_buffer_size = 1M
table_cache = 512
thread_cache = 32
thread_cache_size = 8
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
expire_logs_days = 10
max_binlog_size = 100M


# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host =
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user =
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port =
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 64M
#bdb_max_lock = 100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M
#comments = true
#compact = true

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: optimization

2010/1/25 John G. Heim :
> I have a server with 16Gb of RAM and a dual-core  2Ghz processor. =
 It is
> running the latest mysql-server from debian lenny (5.0.1). I have databas=
es
> for drupal, moodle, spamassassin, horde3, and a small database for
> departmental stuff.
>
> The problem is that inserts/updates are sometimes very slow, on the order=
of
> a minute. I am hoping somebody can sspot something wrong in my config.
> Here's the optimization settings section (for your convenience). The whol=
e
> my.cnf is reproduced below that:

Are your databases using MyISAM or InnoDB?

* If MyISAM, you could be suffering contention problems on writes
because of full table locks. No easy solution but engine change or
database sharding. Also key_buffer, (and the other buffers) coud be
too small for 16GB of RAM. Are you really using more thant 10% of it?
You could also disable other engines if unused.
* If InnoDB, you have not set innodb_buffer_pool_size nor log size.
You could increase the pool to >50% of ram available.

Those are very general suggestions. It depends a lot on your hardware
(slow storage?), other apps installed on the same machine or the load
of the server, among others.

There also exists a tool to get introduced into MySQL server variables
tuning: Tuning primer - https://launchpad.net/mysql-tuning-primer It
is also a very general tool, but it could be helpful for a starting
point.

Regards,
--=20
Jaime Crespo
MySQL & Java Instructor
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: optimization

From: "Jaime Crespo Rincón"
Sent: Monday, January 25, 2010 5:30 PM


2010/1/25 John G. Heim :
> I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
> running the latest mysql-server from debian lenny (5.0.1). I have
> databases
> for drupal, moodle, spamassassin, horde3, and a small database for
> departmental stuff.
>
> The problem is that inserts/updates are sometimes very slow, on the order
> of
> a minute. I am hoping somebody can sspot something wrong in my config.
> Here's the optimization settings section (for your convenience). The whole
> my.cnf is reproduced below that:

Are your databases using MyISAM or InnoDB?

Both. Maybe that's the problem? I started creating database tables for my
own web apps with the default mysql configuration. I believe the default
database engine is MyISAM. But then I wanted to use foreign keys and I saw
that it required me to use InnoDB. So I converted some tables to InnoDB but
not all. Maybe it was a mistake not to convert all of them.

After that, I installed drupal, moodle, and mediawiki. I haven't looked at
what kind of tables those packages create. They may not specify it and the
default is still whatever it is when you install mysql, MyISAM I think.

>* If MyISAM, you could be suffering contention problems on writes
because of full table locks. No easy solution but engine change or
database sharding. Also key_buffer, (and the other buffers) coud be
too small for 16GB of RAM. Are you really using more thant 10% of it?
You could also disable other engines if unused.
* If InnoDB, you have not set innodb_buffer_pool_size nor log size.
You could increase the pool to >50% of ram available.

Those are very general suggestions. It depends a lot on your hardware
(slow storage?), other apps installed on the same machine or the load
of the server, among others.


Well, it could be that the disks aren't real fast. The server is also
running a print server (lprng). I don't think that's very CPU intensive but
it might be slowing down writes.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: optimization

Get yourself a copy of the book High Performance MySQL 2nd Edition
Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English=20
ISBN-10: 0596101716 ISBN-13: 978-0596101718

Here is a brief preview of the first edition:=20
http://books.google.ca/books?id=3DiaCCQ13_zMIC&printsec=3Dfr ontcover&dq=3Dhi=
gh+performance+mysql&cd=3D1#v=3Donepage&q=3D&f=3Dfalse

Mike

At 10:19 AM 1/26/2010, John G. Heim wrote:
>From: "Jaime Crespo Rincón"
>Sent: Monday, January 25, 2010 5:30 PM
>
>
>2010/1/25 John G. Heim :
>>I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
>>running the latest mysql-server from debian lenny (5.0.1). I have=
databases
>>for drupal, moodle, spamassassin, horde3, and a small database for
>>departmental stuff.
>>
>>The problem is that inserts/updates are sometimes very slow, on the order=
of
>>a minute. I am hoping somebody can sspot something wrong in my config.
>>Here's the optimization settings section (for your convenience). The whole
>>my.cnf is reproduced below that:
>
>Are your databases using MyISAM or InnoDB?
>
>Both. Maybe that's the problem? I started creating database tables for my=
=20
>own web apps with the default mysql configuration. I believe the default=20
>database engine is MyISAM. But then I wanted to use foreign keys and I saw=
=20
>that it required me to use InnoDB. So I converted some tables to InnoDB=20
>but not all. Maybe it was a mistake not to convert all of them.
>
>After that, I installed drupal, moodle, and mediawiki. I haven't looked at=
=20
>what kind of tables those packages create. They may not specify it and the=
=20
>default is still whatever it is when you install mysql, MyISAM I think.
>
>>* If MyISAM, you could be suffering contention problems on writes
>because of full table locks. No easy solution but engine change or
>database sharding. Also key_buffer, (and the other buffers) coud be
>too small for 16GB of RAM. Are you really using more thant 10% of it?
>You could also disable other engines if unused.
>* If InnoDB, you have not set innodb_buffer_pool_size nor log size.
>You could increase the pool to >50% of ram available.
>
>Those are very general suggestions. It depends a lot on your hardware
>(slow storage?), other apps installed on the same machine or the load
>of the server, among others.
>
>
>Well, it could be that the disks aren't real fast. The server is also=20
>running a print server (lprng). I don't think that's very CPU intensive=20
>but it might be slowing down writes.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: optimization

--000e0cd7063423f2db047e144e81
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

â™=AB
I would recommend the same to you about reading High Perf. MySQL as Baron,
et al wrote a great book about performance on MySQL. That being said, it ha=
s
been my experience that in 99% of client cases they don't really need to ru=
n
two different types of tables. If I were you, I would use InnoDB exclusivel=
y
unless there is legitimate reason to do otherwise. In an environment that i=
s
running 25% writes and a decent query rate you are bound to have contention
issues with MyISAM. While there are always going to be edge cases for
MyISAM, your default should be innodb and your config should reflect this.

Changing your tables to InnoDB is a simple ALTER TABLE which you can script
if there are a number of tables to convert. Allocate as much of your
available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of
total RAM) and I bet you would see a dramatic difference. That is
simplifying things somewhat, but should give an idea.


keith


On Tue, Jan 26, 2010 at 11:53 AM, mos wrote:

> Get yourself a copy of the book High Performance MySQL 2nd Edition
> Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English
> ISBN-10: 0596101716 ISBN-13: 978-0596101718
>
> Here is a brief preview of the first edition:
> http://books.google.ca/books?id=3DiaCCQ13_zMIC&printsec=3Dfr ontcover&dq=
=3Dhigh+performance+mysql&cd=3D1#v=3Donepage&q=3D&f=3Dfalse
>
> Mike
>
> At 10:19 AM 1/26/2010, John G. Heim wrote:
>
>> From: "Jaime Crespo Rincón"
>>
>> Sent: Monday, January 25, 2010 5:30 PM
>>
>>
>> 2010/1/25 John G. Heim :
>>
>>> I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
>>> running the latest mysql-server from debian lenny (5.0.1). I have
>>> databases
>>> for drupal, moodle, spamassassin, horde3, and a small database for
>>> departmental stuff.
>>>
>>> The problem is that inserts/updates are sometimes very slow, on the ord=
er
>>> of
>>> a minute. I am hoping somebody can sspot something wrong in my config.
>>> Here's the optimization settings section (for your convenience). The
>>> whole
>>> my.cnf is reproduced below that:
>>>
>>
>> Are your databases using MyISAM or InnoDB?
>>
>> Both. Maybe that's the problem? I started creating database tables for m=
y
>> own web apps with the default mysql configuration. I believe the default
>> database engine is MyISAM. But then I wanted to use foreign keys and I s=
aw
>> that it required me to use InnoDB. So I converted some tables to InnoDB=
but
>> not all. Maybe it was a mistake not to convert all of them.
>>
>> After that, I installed drupal, moodle, and mediawiki. I haven't looked =
at
>> what kind of tables those packages create. They may not specify it and t=
he
>> default is still whatever it is when you install mysql, MyISAM I think.
>>
>> * If MyISAM, you could be suffering contention problems on writes
>>>
>> because of full table locks. No easy solution but engine change or
>> database sharding. Also key_buffer, (and the other buffers) coud be
>> too small for 16GB of RAM. Are you really using more thant 10% of it?
>> You could also disable other engines if unused.
>> * If InnoDB, you have not set innodb_buffer_pool_size nor log size.
>> You could increase the pool to >50% of ram available.
>>
>> Those are very general suggestions. It depends a lot on your hardware
>> (slow storage?), other apps installed on the same machine or the load
>> of the server, among others.
>>
>>
>> Well, it could be that the disks aren't real fast. The server is also
>> running a print server (lprng). I don't think that's very CPU intensive =
but
>> it might be slowing down writes.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmos99@fastmail.f=
m
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dbmurphy@paragon-cs.com
>
>


--=20
Chief Training Officer
Paragon Consulting Services
850-637-3877

--000e0cd7063423f2db047e144e81--

Re: optimization

Just to be clear, you're suggesting I convert all of the spamassassin,
drupal, and mediawiki tables to innodb too? Or just my own database? What
about the mysql database itself? I wouldn't convert those tables, would I?

----- Original Message -----
From: "Keith Murphy"
To:
Sent: Tuesday, January 26, 2010 11:06 AM
Subject: Re: optimization


♫
I would recommend the same to you about reading High Perf. MySQL as Baron,
et al wrote a great book about performance on MySQL. That being said, it has
been my experience that in 99% of client cases they don't really need to run
two different types of tables. If I were you, I would use InnoDB exclusively
unless there is legitimate reason to do otherwise. In an environment that is
running 25% writes and a decent query rate you are bound to have contention
issues with MyISAM. While there are always going to be edge cases for
MyISAM, your default should be innodb and your config should reflect this.

Changing your tables to InnoDB is a simple ALTER TABLE which you can script
if there are a number of tables to convert. Allocate as much of your
available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of
total RAM) and I bet you would see a dramatic difference. That is
simplifying things somewhat, but should give an idea.


keith


On Tue, Jan 26, 2010 at 11:53 AM, mos wrote:

> Get yourself a copy of the book High Performance MySQL 2nd Edition
> Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English
> ISBN-10: 0596101716 ISBN-13: 978-0596101718
>
> Here is a brief preview of the first edition:
> http://books.google.ca/books?id=iaCCQ13_zMIC&printsec=frontc over&dq=high+performance+mysql&cd=1#v=onepage&q=&f=false
>
> Mike
>
> At 10:19 AM 1/26/2010, John G. Heim wrote:
>
>> From: "Jaime Crespo Rincón"
>>
>> Sent: Monday, January 25, 2010 5:30 PM
>>
>>
>> 2010/1/25 John G. Heim :
>>
>>> I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
>>> running the latest mysql-server from debian lenny (5.0.1). I have
>>> databases
>>> for drupal, moodle, spamassassin, horde3, and a small database for
>>> departmental stuff.
>>>
>>> The problem is that inserts/updates are sometimes very slow, on the
>>> order
>>> of
>>> a minute. I am hoping somebody can sspot something wrong in my config.
>>> Here's the optimization settings section (for your convenience). The
>>> whole
>>> my.cnf is reproduced below that:
>>>
>>
>> Are your databases using MyISAM or InnoDB?
>>
>> Both. Maybe that's the problem? I started creating database tables for my
>> own web apps with the default mysql configuration. I believe the default
>> database engine is MyISAM. But then I wanted to use foreign keys and I
>> saw
>> that it required me to use InnoDB. So I converted some tables to InnoDB
>> but
>> not all. Maybe it was a mistake not to convert all of them.
>>
>> After that, I installed drupal, moodle, and mediawiki. I haven't looked
>> at
>> what kind of tables those packages create. They may not specify it and
>> the
>> default is still whatever it is when you install mysql, MyISAM I think.
>>
>> * If MyISAM, you could be suffering contention problems on writes
>>>
>> because of full table locks. No easy solution but engine change or
>> database sharding. Also key_buffer, (and the other buffers) coud be
>> too small for 16GB of RAM. Are you really using more thant 10% of it?
>> You could also disable other engines if unused.
>> * If InnoDB, you have not set innodb_buffer_pool_size nor log size.
>> You could increase the pool to >50% of ram available.
>>
>> Those are very general suggestions. It depends a lot on your hardware
>> (slow storage?), other apps installed on the same machine or the load
>> of the server, among others.
>>
>>
>> Well, it could be that the disks aren't real fast. The server is also
>> running a print server (lprng). I don't think that's very CPU intensive
>> but
>> it might be slowing down writes.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=bmurphy@paragon-cs.com
>
>


--
Chief Training Officer
Paragon Consulting Services
850-637-3877


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: optimization

--000e0cd23e220812ce047e16373c
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

You absolutely *should not* convert the mysql database to InnoDB.

Read the above sentence again :)

All others, unless you had a specific reason not to do so, yes, I would
convert them.

keith

On Tue, Jan 26, 2010 at 2:18 PM, John G. Heim wrote:

> Just to be clear, you're suggesting I convert all of the spamassassin,
> drupal, and mediawiki tables to innodb too? Or just my own database? What
> about the mysql database itself? I wouldn't convert those tables, would I=
?
>
> ----- Original Message ----- From: "Keith Murphy" >
> To:
> Sent: Tuesday, January 26, 2010 11:06 AM
> Subject: Re: optimization
>
>
>
> â™=AB
> I would recommend the same to you about reading High Perf. MySQL as Baron=
,
> et al wrote a great book about performance on MySQL. That being said, it
> has
> been my experience that in 99% of client cases they don't really need to
> run
> two different types of tables. If I were you, I would use InnoDB
> exclusively
> unless there is legitimate reason to do otherwise. In an environment that
> is
> running 25% writes and a decent query rate you are bound to have contenti=
on
> issues with MyISAM. While there are always going to be edge cases for
> MyISAM, your default should be innodb and your config should reflect this=
..
>
> Changing your tables to InnoDB is a simple ALTER TABLE which you can scri=
pt
> if there are a number of tables to convert. Allocate as much of your
> available RAM as possible to the innodb_buffer_pool (typically 50 - 80% o=
f
> total RAM) and I bet you would see a dramatic difference. That is
> simplifying things somewhat, but should give an idea.
>
>
> keith
>
>
> On Tue, Jan 26, 2010 at 11:53 AM, mos wrote:
>
> Get yourself a copy of the book High Performance MySQL 2nd Edition
>> Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English
>> ISBN-10: 0596101716 ISBN-13: 978-0596101718
>>
>> Here is a brief preview of the first edition:
>>
>> http://books.google.ca/books?id=3DiaCCQ13_zMIC&printsec=3Dfr ontcover&dq=
=3Dhigh+performance+mysql&cd=3D1#v=3Donepage&q=3D&f=3Dfalse
>>
>> Mike
>>
>> At 10:19 AM 1/26/2010, John G. Heim wrote:
>>
>> From: "Jaime Crespo Rincón"
>>>
>>> Sent: Monday, January 25, 2010 5:30 PM
>>>
>>>
>>> 2010/1/25 John G. Heim :
>>>
>>> I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
>>>> running the latest mysql-server from debian lenny (5.0.1). I have
>>>> databases
>>>> for drupal, moodle, spamassassin, horde3, and a small database for
>>>> departmental stuff.
>>>>
>>>> The problem is that inserts/updates are sometimes very slow, on the
>>>> order
>>>> of
>>>> a minute. I am hoping somebody can sspot something wrong in my config.
>>>> Here's the optimization settings section (for your convenience). The
>>>> whole
>>>> my.cnf is reproduced below that:
>>>>
>>>>
>>> Are your databases using MyISAM or InnoDB?
>>>
>>> Both. Maybe that's the problem? I started creating database tables for =
my
>>> own web apps with the default mysql configuration. I believe the defaul=
t
>>> database engine is MyISAM. But then I wanted to use foreign keys and I
>>> saw
>>> that it required me to use InnoDB. So I converted some tables to InnoD=
B
>>> but
>>> not all. Maybe it was a mistake not to convert all of them.
>>>
>>> After that, I installed drupal, moodle, and mediawiki. I haven't looked
>>> at
>>> what kind of tables those packages create. They may not specify it and
>>> the
>>> default is still whatever it is when you install mysql, MyISAM I think.
>>>
>>> * If MyISAM, you could be suffering contention problems on writes
>>>
>>>>
>>>> because of full table locks. No easy solution but engine change or
>>> database sharding. Also key_buffer, (and the other buffers) coud be
>>> too small for 16GB of RAM. Are you really using more thant 10% of it?
>>> You could also disable other engines if unused.
>>> * If InnoDB, you have not set innodb_buffer_pool_size nor log size.
>>> You could increase the pool to >50% of ram available.
>>>
>>> Those are very general suggestions. It depends a lot on your hardware
>>> (slow storage?), other apps installed on the same machine or the load
>>> of the server, among others.
>>>
>>>
>>> Well, it could be that the disks aren't real fast. The server is also
>>> running a print server (lprng). I don't think that's very CPU intensive
>>> but
>>> it might be slowing down writes.
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmos99@fastmail.=
fm
>>>
>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Dbmurphy@paragon-cs.com
>>
>>
>>
>
> --
> Chief Training Officer
> Paragon Consulting Services
> 850-637-3877
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dbmurphy@paragon-cs.com
>
>


--=20
Chief Training Officer
Paragon Consulting Services
850-637-3877

--000e0cd23e220812ce047e16373c--

Re: optimization

On Tue, Jan 26, 2010 at 11:23 AM, Keith Murphy wro=
te:
> You absolutely *should not* convert the mysql database to InnoDB.
>
> Read the above sentence again :)
>
> All others, unless you had a specific reason not to do so, yes, I would
> convert them.
>
> keith
>
> On Tue, Jan 26, 2010 at 2:18 PM, John G. Heim wrote=
:
>
>> Just to be clear, you're suggesting I convert all of the spamassassin,
>> drupal, and mediawiki tables to innodb too? Or just my own database? Wha=
t
>> about the mysql database itself? I wouldn't convert those tables, would =
I?
>>
>> ----- Original Message ----- From: "Keith Murphy" m>
>> To:
>> Sent: Tuesday, January 26, 2010 11:06 AM
>> Subject: Re: optimization
>>
>>
>>
>> â™=AB
>> I would recommend the same to you about reading High Perf. MySQL as Baro=
n,
>> et al wrote a great book about performance on MySQL. That being said, it
>> has
>> been my experience that in 99% of client cases they don't really need to
>> run
>> two different types of tables. If I were you, I would use InnoDB
>> exclusively
>> unless there is legitimate reason to do otherwise. In an environment tha=
t
>> is
>> running 25% writes and a decent query rate you are bound to have content=
ion
>> issues with MyISAM. While there are always going to be edge cases for
>> MyISAM, your default should be innodb and your config should reflect thi=
s.
>>
>> Changing your tables to InnoDB is a simple ALTER TABLE which you can scr=
ipt
>> if there are a number of tables to convert.  Allocate as much of yo=
ur
>> available RAM as possible to the innodb_buffer_pool (typically 50 - 80% =
of
>> total RAM) and I bet you would see a dramatic difference. That is
>> simplifying things somewhat, but should give an idea.
>>
>>
>> keith
>>
>>
>> On Tue, Jan 26, 2010 at 11:53 AM, mos wrote:
>>
>>  Get yourself a copy of the book High Performance MySQL 2nd Edition
>>> Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English
>>> ISBN-10: 0596101716 ISBN-13: 978-0596101718
>>>
>>> Here is a brief preview of the first edition:
>>>
>>> http://books.google.ca/books?id=3DiaCCQ13_zMIC&printsec=3Dfr ontcover&dq=
=3Dhigh+performance+mysql&cd=3D1#v=3Donepage&q=3D&f=3Dfalse
>>>
>>> Mike
>>>
>>> At 10:19 AM 1/26/2010, John G. Heim wrote:
>>>
>>>  From: "Jaime Crespo Rincón"
>>>>
>>>> Sent: Monday, January 25, 2010 5:30 PM
>>>>
>>>>
>>>> 2010/1/25 John G. Heim :
>>>>
>>>>  I have a server with 16Gb of RAM and a dual-core 2Ghz processor.=
It is
>>>>> running the latest mysql-server from debian lenny (5.0.1). I have
>>>>> databases
>>>>> for drupal, moodle, spamassassin, horde3, and a small database for
>>>>> departmental stuff.
>>>>>
>>>>> The problem is that inserts/updates are sometimes very slow, on the
>>>>> order
>>>>> of
>>>>> a minute. I am hoping somebody can sspot something wrong in my config=
..
>>>>> Here's the optimization settings section (for your convenience). The
>>>>> whole
>>>>> my.cnf is reproduced below that:
>>>>>
>>>>>
>>>> Are your databases using MyISAM or InnoDB?
>>>>
>>>> Both. Maybe that's the problem? I started creating database tables for=
my
>>>> own web apps with the default mysql configuration. I believe the defau=
lt
>>>> database engine is MyISAM. But then I wanted to use foreign keys and I
>>>> saw
>>>> that it required me to use  InnoDB. So I converted some tables to=
InnoDB
>>>> but
>>>> not all. Maybe it was a mistake not to convert all of them.
>>>>
>>>> After that, I installed drupal, moodle, and mediawiki. I haven't looke=
d
>>>> at
>>>> what kind of tables those packages create. They may not specify it and
>>>> the
>>>> default is still whatever it is when you install mysql, MyISAM I think=
..
>>>>
>>>>  * If MyISAM, you could be suffering contention problems on write=
s
>>>>
>>>>>
>>>>>  because of full table locks. No easy solution but engine change=
or
>>>> database sharding. Also key_buffer, (and the other buffers) coud be
>>>> too small for 16GB of RAM. Are you really using more thant 10% of it?
>>>> You could also disable other engines if unused.
>>>> * If InnoDB, you have not set innodb_buffer_pool_size nor log size.
>>>> You could increase the pool to >50% of ram available.
>>>>
>>>> Those are very general suggestions. It depends a lot on your hardware
>>>> (slow storage?), other apps installed on the same machine or the load
>>>> of the server, among others.
>>>>
>>>>
>>>> Well, it could be that the disks aren't real fast. The server is also
>>>> running a print server (lprng). I don't think that's very CPU intensiv=
e
>>>> but
>>>> it might be slowing down writes.
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dmos9=
9@fastmail.fm
>>>>
>>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=3Dbmurphy@paragon-cs.com
>>>
>>>
>>>
>>
>> --
>> Chief Training Officer
>> Paragon Consulting Services
>> 850-637-3877
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Dbmurphy@paragon-cs.com
>>
>>
>
>
> --
> Chief Training Officer
> Paragon Consulting Services
> 850-637-3877
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg