# MySQL port settings for the best performance



## kenorb (Nov 10, 2010)

I really need the fastest solution, which following settings are the best?

```
You may use the following build options:
	WITH_CHARSET=charset	Define the primary built-in charset (latin1).
	WITH_XCHARSET=list	Define other built-in charsets (may be 'all').
	WITH_COLLATION=collate	Define default collation (latin1_swedish_ci).
	WITH_OPENSSL=yes	Enable secure connections
				(define WITHOUT_YASSL for backward compatibility).
	WITH_LINUXTHREADS=yes	Use the linuxthreads pthread library.
	WITH_PROC_SCOPE_PTH=yes	Use process scope threads
				(try it if you use libpthread).
	WITH_FAST_MUTEXES=yes	Replace mutexes with spinlocks.
	BUILD_OPTIMIZED=yes	Enable compiler optimizations
				(use it if you need speed).
	BUILD_STATIC=yes	Build a static version of mysqld.
				(use it if you need even more speed).
	WITH_NDB=yes		Enable support for NDB Cluster.
```
BUILD_OPTIMIZED=yes for sure.

My make.conf:

```
# specific options/etc for ports
.if ${.CURDIR:M*/databases/mysql*-server}
BUILD_OPTIMIZED=yes
.endif
```

Do WITH_LINUXTHREADS/WITH_PROC_SCOPE_PTH or BUILD_STATIC will boost the performance on Intel Xeon Core2?


----------



## SirDice (Nov 10, 2010)

Your database organization and choice of database engine will have a much higher impact with regards to it's performance. 

Database performance is mainly dictated by I/O (how fast can you read/write data) not CPU.


----------



## kenorb (Nov 10, 2010)

SirDice said:
			
		

> Your database organization and choice of database engine will have a much higher impact with regards to it's performance.
> 
> Database performance is mainly dictated by I/O (how fast can you read/write data) not CPU.



You saying that BUILD_OPTIMIZED=yes doesn't change anything with apache+php+mysql performance and page loading, even on complex queries on 0,5G-1,5G databases?
I've got around 500 mysql queries per one page load.
It's not always I/O, databases should be cached in memory, then it's matter of CPU.
Why doesn't say then: "Enable compiler optimizations (use it if you need speed)." ?


----------



## SirDice (Nov 10, 2010)

kenorb said:
			
		

> You saying that BUILD_OPTIMIZED=yes doesn't change anything with apache+php+mysql performance and page loading, even on complex queries on 0,5G-1,5G databases?


Yes. We have a lot more databases exceeding that. I think the biggest we have is around 7GB.



> I've got around 500 mysql queries per one page load.


Then there's something seriously wrong with that page or your queries.



> It's not always I/O, databases should be cached in memory, then it's matter of CPU.


This is done with DMA, which doesn't use the CPU.

The only time our CPUs are hit (hard) is when the database backup runs. But that CPU hit is mostly on gzip. Another reason the CPU would get hit is when you haven't optimized mysql's configuration and it's using more memory then the machine has. Correctly tuning mysql and the queries is the key to getting the best performance.


----------



## kenorb (Nov 10, 2010)

SirDice said:
			
		

> Then there's something seriously wrong with that page or your queries.



It's the way how the Drupal works Can't do anything with that.


----------



## SirDice (Nov 10, 2010)

I don't use Drupal but all I can suggest is to have a seriously long and hard look at how the pages are build up. Perhaps some tables benefit from InnoDB instead of MyISAM. Perhaps tables need extra indexes. Perhaps you can benefit from a bigger query cache, key buffers, thread cache, join buffers, tmp table sizes, etc.

Tuning databases is almost an art form in and of itself.


----------



## kenorb (Nov 10, 2010)

Even on JOIN, DISTINCT, etc?

```
# Time: 101104 17:28:58
# User@Host: root[root] @ localhost []
# Query_time: 2.233344  Lock_time: 0.000178 Rows_sent: 11  Rows_examined: 440396
SET timestamp=1288891738;
SELECT DISTINCT(n.nid) FROM node n LEFT JOIN node_revisions ns ON (n.nid = ns.nid) INNER JOIN viddies nx ON (n.vid = nx.vid) WHERE (ns.nid LIKE '%46537%');

# Time: 101029 17:17:06
# User@Host: root[root] @ localhost []
# Query_time: 2.746130  Lock_time: 0.000000 Rows_sent: 1027877  Rows_examined: 1027877
SET timestamp=1288369026;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_index`;
```
For sure if you are sorting 1m rows which are already loaded and joining them, it uses some CPU.


----------



## SirDice (Nov 10, 2010)

Oh.. Have a look at the tuning-primer.sh script on http://www.day32.com/MySQL/.

That should at least give you some figures to play with.


----------



## SirDice (Nov 10, 2010)

kenorb said:
			
		

> Even on JOIN, DISTINCT, etc?
> 
> {..snip..}
> 
> For sure if you are sorting 1m rows which are already loaded and joining them, it uses some CPU.



Use EXPLAIN and see if it properly uses indexes. If it has to resort to file sorts it's going to be a lot slower.

When I first started where I work now our MySQL database performed like crap. Just by tuning it's parameters and optimizing a lot of queries we managed to increase the overall performance 2-3 times. And that's running on the same MySQL, same box, just some tuning.

Edit: Another thing you should seriously consider is putting MySQL on it's own box. Separate Apache/PHP from it. Both have different tuning requirements and both benefit from different things. It'll be hard trying to find a common improvement and it'll be a lot easier to tune when separated.


----------



## kenorb (Nov 10, 2010)

I already using some of the huge config found in /usr/local/share/mysql

```
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
```
 on host with 8GB memory.
But not much difference.


----------



## kenorb (Nov 10, 2010)

So BUILD_OPTIMIZED=yes should be enough.
Thanks for your help and for the links.


----------



## SirDice (Nov 10, 2010)

kenorb said:
			
		

> I already using some of the huge config found in /usr/local/share/mysql
> 
> ```
> /usr/local/share/mysql/my-innodb-heavy-4G.cnf
> ...



Use that tuning script I posted above. Every database use is different and those configs are just examples.


----------



## chrcol (Nov 12, 2010)

I am not sure why it is called BUILD_OPTIMIZED because when I use it mysql uses more cpu and the number of slow queries sky rockets.


----------

