My.cnf improvment

Stephan_Craft · 04-04-2009, 03:56 AM

what kind of 'skips' I can use to make mysql work better?

right now i have this:

PHP Code:



skip-locking 
skip-innodb 
skip-external-locking 
skip-name-resolve

tronayne · 04-05-2009, 08:31 AM

Well, that kind of depends -- which of the configuration files did you start with (they are my-huge.cnf, my-large.cnf, my-medium.cnf or my-small.cnf)? If you've got 1G - 2G of memory, my-huge.cnf may be the best choice to copy to my.cnf. The "huge" configuration provides larger buffer and cache sizes which significantly improves performance over the others.

On my machines, I have only skip-locking and skip-federated active and the performance is more than satisfactory (I run multiple data bases that are anywhere from 3G to 20G in size). This is a single 3G processor, 2G of RAM. I have /var/lib/mysql in a separate 64G partition. You may want to comment out skip-networking so MySQL doesn't listen on a TCP/IP port, but you need to think that one through -- the blurb is

Code:

# 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

You may need to do this in any event if you want to use ODBC.

Hope this helps some.

mosesdel · 04-07-2009, 10:28 PM

Hello all
I will ask about the type of configuration for mysql server on ubuntu.

I have a server with 8 GB memory and used for public access and the average hit 1,000 pages per day.

If I look at some mysql configuration such as:

-rw-r - r - 1 root root 4929 2009-04-07 19:09 my-huge.cnf
-rw-r - r - 1 root root 20927 2009-04-07 19:09 my-innodb-heavy-4G.cnf
-rw-r - r - 1 root root 4905 2009-04-07 19:09 my-large.cnf
-rw-r - r - 1 root root 2113 2009-04-07 19:09 my-medium.cnf.gz
-rw-r - r - 1 root root 2482 2009-04-07 19:09 my-small.cnf
-rw-r - r - 1 root root 723 2009-04-07 19:09 ndb_mgmd.cnf

If I want to use my-huge.cnf, is it enough to copy /etc/mysql/ my.cnf?

If I see in my configurasi-huge.cnf, innodb line to comment on whether I need to menuncomment that?

Thanks

Stephan_Craft · 04-08-2009, 02:57 AM

Quote:

Originally Posted by tronayne

Well, that kind of depends -- which of the configuration files did you start with (they are my-huge.cnf, my-large.cnf, my-medium.cnf or my-small.cnf)? If you've got 1G - 2G of memory, my-huge.cnf may be the best choice to copy to my.cnf. The "huge" configuration provides larger buffer and cache sizes which significantly improves performance over the others.

On my machines, I have only skip-locking and skip-federated active and the performance is more than satisfactory (I run multiple data bases that are anywhere from 3G to 20G in size). This is a single 3G processor, 2G of RAM. I have /var/lib/mysql in a separate 64G partition. You may want to comment out skip-networking so MySQL doesn't listen on a TCP/IP port, but you need to think that one through -- the blurb is

Code:

# 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

You may need to do this in any event if you want to use ODBC.

Hope this helps some.

Thanks I'll remember that.

how this option skip-federated effects on mysql?

tronayne · 04-08-2009, 08:57 AM

Quote:

how this option skip-federated effects on mysql?

A quick look at http://dev.mysql.com/doc/refman/5.0/...ge-engine.html indicates

Quote:

The FEDERATED storage engine is available beginning with MySQL 5.0.3. It is a storage engine that accesses data in tables of remote databases rather than in local tables.

You'd use it if you have a server farm with data bases spread across multiple servers; if not, don't.