Which tables must be InnoDB?

Make all tables InnoDB

But InnoDB is slower. I don't wanna hurt the performance.

There are cases where InnoDB is slower than MyISAM (ie: doing a count(*) without a where clause). Other examples are alter tables, especially on large datasets. While this isn't an everyday operation, it can cause a noticeable performance degradation on some module updates.

Having said that, doing a mixture of tables really is fine. Tables that see a lot of writes should become InnoDB to stop the locks. I wouldn't base Watchdog off of that. If Watchdog is causing a problem then I would look at changing storage on that to some other mechanism (log files, couchdb, etc.). On one site I manage I just hijacked the dblog module and had it not write certain things, like new comments being posted.

One site I run does a mixture of the two storage engines. We once saw over 65,000 page views, 13,000 authenticated users and over 700 comments posted in 1 hour. That was all on a single quad-core server (Xeon 2.5g) with 4gb ram. During that time the server load peaked at 1.65, so things ran great. The only other extra we had was the static content coming off a CDN.

One thing about InnoDB - you need to stay up on it. It does require attention and tuning, much more so than MyISAM.

Thanks!

dbtuner:

My config is wrong? Did I forgot something? Any advise? (quad core + 8gb ram):

Thanks!

It is true?

If you are using Nginx Proxy to Apache

the HTTP 504 TIMEOUT is likely coming from Nginx acting a front-end Proxy to Apache.
In this case, the 504 is caused by a proxy timeout, or Nginx having to wait too long for the expected Apache PHP response.

** Basically this an issue with whatever serves your PHP (Nginx or Apache).

In this Nginx proxy to Apache, the Apache server is waiting for MySQL process to finish fetching the data, but it’s not finished. So Nginx waits for Apache, and Apache is 'hung' waiting for MySQL. This process will keep loading while a new connection arrives, in the end it is time deadlock = 504 !

If mysql is the problem, you can check it with this command (Linux):

watch "mysqladmin -u username -ppassword processlist"

Note that username should be your primary MySQL user [root], and password should be that user's
password. There is no space between the -p in the command and the password.

If you're using fastcgi:

Proxy timeouts are well, for proxies, not for FastCGI...

For FastCGI there's fastcgi_read_timeout which is affecting the fastcgi process response timeout.

eg:

Comment: You have something terribly wrong if you need to raise this limit.

Try this: Assuming your using PHP-FPM:

Setup The php-fpm Slow Log

Once your timeout settings are in place the problem is effectively isolated to some runaway code that only stops when it hits one of the timeouts.
The next step is identifying where the runaway code is. That’s were the php-fpm slow log may help trace the problem.

By configuring the request_slowlog_timeout and setting up a slowlog file, php-fpm will dump (write) a php backtrace into to the slow log file whenever a script hits the normal timeout value.

set the request_slowlog_timeout to 25 seconds in your Apache / Nginx .conf (which ever serves the PHP)

Now when you restart everything and hit the page causing the 504 error you’ll get a helpful backtrace in your slow log file. The backtrace will show you the specific line of PHP code that was being executed when it hit the typical 25 second limit.

Check if your php is 'sigfaulting'

If you are using php 5.3, increase the backlog.

If you are using php 5.2, backport the patch to increase the backlog size from 128.

Also, use a unix socket rather than a TCP socket. unix:/tmp/php5-cgi.sock (or the relevant path)

CPU loading raise to 65%

This is likely related to PHP server problems [errors]; and to me, it seems very related to the Proxy 504 timeout.
At this point I think PHP is hitting a 'brick wall' = 'Deadlock'

Is OK for 8gb server?
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 2048M

1) On a dedicated MySQL Server innodb_buffer_pool_size can be as high as 90% of Server memory.
On a Server shared with PHP, Nginx, Apache, etc; 30% - 50% of server memory is better.
So in your case 4Gb / 8Gb for innodb_buffer_pool_size is good.
2) The innodb_log_file_size for InnoDB is probably 'a bit large'; mine is 384Mb (of 8Gb Server Total)

Another important InnoDB 'my.cnf' setting is:
innodb_buffer_pool_instances = 8
Where '8' represents the number of CPU cores x 2

After that: innodb_buffer_pool_instances = 8 mysql failed to running.

superfedya Your current settings look good!

Four more 'edge-cutting' InnoDB my.cnf settings you might want
to try: (assuming a recent version of MySQL 5.5+)

1) The 1st item; 'innodb_flush_log_at_trx_commit = 2'
In this case, the MySQL log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.
Notes: The flushing on the log file takes place once per second(varies with process scheduling needs).
There is a slight risk (cost); you can lose at most 'one second worth of transactions' in a crash.

2) READ-COMMITTED: With the READ-COMMITTED isolation level, the phenomenon of dirty read is avoided, because any uncommitted changes is not visible to any other transaction, until the change is committed. Within this isolation level each SELECT uses its own snapshot of the committed data that was committed before the execution of the SELECT. This has improved with MySQL 5.5.

3) Mmap is a new MyISAM feature available with MySQL 5.1+.
Typically improves MyISAM write/read performance ~6%.
myisam_use_mmap = 1

4) There is a new MySQL5.1+ option 'innodb_autoinc_lock_mode' = "Less InnoDB Locking (compared to previous MySQL 5.0)"
Just to summarize, we can 'almost' remove auto_increment locking in InnoDB.
We need to set both innodb_autoinc_lock_mode=2 and binlog_format=row.

New Summary: