Percona 5.6

[MattW]

Just seen that Percona 5.6 is now on their stable branch, and available via their yum repo.

So, just spent the last 20 minutes upgrading.

Had a bit of a scare when it wouldn't start back up again, but per their instruction, you need to start it with skip-grant-tables to run the initial mysql_upgrade. This didn't stop when I killed it and tried to restart it properly, and it couldn't get a lock on the ibdate1 file

2013-10-12 05:34:09 10933 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2013-10-12 05:34:09 10933 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2013-10-12 05:34:10 10933 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2013-10-12 05:34:10 10933 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2013-10-12 05:34:10 10933 [Note] InnoDB: Unable to open the first data file

kill -9 on the previous process stopped it, and I eventually got it started again.

I had to remove some settings from my.cnf to get it back up again:

2013-10-12 05:25:20 8279 [ERROR] /usr/sbin/mysqld: unknown variable 'innodb_flush_neighbor_pages=0'
2013-10-12 05:25:56 8331 [ERROR] /usr/sbin/mysqld: unknown variable 'innodb_ibuf_max_size=4M'
2013-10-12 05:26:47 8418 [ERROR] /usr/sbin/mysqld: unknown variable 'innodb_adaptive_flushing_method=keep_average'

Other than that, it's back up and running on 5.6

Initial impressions are that it's definitely quicker.

For example, posting a thread in my announcement section where a lot of people are watching with notifications used to take ~4 seconds to post the thread. It's now doing it < 1 second!

 

[Moshe1010]

From many benchmarks that I've seen, 5.5 was much faster than 5.6
Have you tried to run benchmarks? I know they don't tell a lot and it really depends what system we are running, but if you say there are noticeable differences in xenForo, then I guess this what matters the most.

 

[MattW]

I've not ran any benchmarks as people are just starting to log into the forum as it's a Saturday.

It's obviously not been up long enough to really tell, but I can only go on what I've initially noticed, which was the posting on the highly "watched" forum (1066 users)

 

[Moshe1010]

did you disable any default values in 5.6? there are some that can affect performance (diabling Performance Schema and/or sql cache for example).

 

[MattW]

did you disable any default values in 5.6? there are some that can affect performance (diabling Performance Schema and/or sql cache for example).

I had the query cache disabled on 5.5

query_cache_type=0
query_cache_size=0

Performance Schema is currently enabled (didn't you anything about disabling that)

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+--------+
| Variable_name                                          | Value  |
+--------------------------------------------------------+--------+
| performance_schema                                    | ON    |
| performance_schema_accounts_size                      | 100    |
| performance_schema_digests_size                        | 10000  |
| performance_schema_events_stages_history_long_size    | 10000  |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 10000  |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000  |
| performance_schema_events_waits_history_size          | 10    |
| performance_schema_hosts_size                          | 100    |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 8612  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768  |
| performance_schema_max_file_instances                  | 100824 |
| performance_schema_max_mutex_classes                  | 200    |
| performance_schema_max_mutex_instances                | 42440  |
| performance_schema_max_rwlock_classes                  | 30    |
| performance_schema_max_rwlock_instances                | 25136  |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | 180    |
| performance_schema_max_stage_classes                  | 150    |
| performance_schema_max_statement_classes              | 176    |
| performance_schema_max_table_handles                  | 8192  |
| performance_schema_max_table_instances                | 12500  |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 260    |
| performance_schema_session_connect_attrs_size          | 512    |
| performance_schema_setup_actors_size                  | 100    |
| performance_schema_setup_objects_size                  | 100    |
| performance_schema_users_size                          | 100    |
+--------------------------------------------------------+--------+
31 rows in set (0.00 sec)

 

[MattW]

Just disabled performance_schema

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                    | OFF  |
| performance_schema_accounts_size                      | -1    |
| performance_schema_digests_size                        | -1    |
| performance_schema_events_stages_history_long_size    | -1    |
| performance_schema_events_stages_history_size          | -1    |
| performance_schema_events_statements_history_long_size | -1    |
| performance_schema_events_statements_history_size      | -1    |
| performance_schema_events_waits_history_long_size      | -1    |
| performance_schema_events_waits_history_size          | -1    |
| performance_schema_hosts_size                          | -1    |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | -1    |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | -1    |
| performance_schema_max_mutex_classes                  | 200  |
| performance_schema_max_mutex_instances                | -1    |
| performance_schema_max_rwlock_classes                  | 30    |
| performance_schema_max_rwlock_instances                | -1    |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | -1    |
| performance_schema_max_stage_classes                  | 150  |
| performance_schema_max_statement_classes              | 176  |
| performance_schema_max_table_handles                  | -1    |
| performance_schema_max_table_instances                | -1    |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | -1    |
| performance_schema_session_connect_attrs_size          | -1    |
| performance_schema_setup_actors_size                  | 100  |
| performance_schema_setup_objects_size                  | 100  |
| performance_schema_users_size                          | -1    |
+--------------------------------------------------------+-------+
31 rows in set (0.00 sec)

 

[Moshe1010]

Any difference in speed?
Without it should be better

 

[MattW]

Any difference in speed?
Without it should be better

Just updating some addons, so will see how quickly they rebuild

 

[p4guru]

FYI for MySQL 5.6, Percona 5.6 and MariaDB 10, disabling performance schema will be faster

 

[Moshe1010]

which was the posting on the highly "watched" forum (1066 users)

How do you know how many people are following a specific forum?

 

[MattW]

You can query the DB and count how many user_id's are watching a specific forum_id

 

[p4guru]

You can query the DB and count how many user_id's are watching a specific forum_id

yup

something like

MariaDB [dbname]> select count(user_id) from session_activity where params='node_id=2';

+----------------+
| count(user_id) |
+----------------+
|              1 |
+----------------+

 

[MattW]

yup

something like

MariaDB [dbname]> select count(user_id) from session_activity where params='node_id=2';

+----------------+
| count(user_id) |
+----------------+
|              1 |
+----------------+

More specifically (in my case, node ID 64):

SELECT COUNT( user_id ) AS TOTAL
FROM  `xf_forum_watch`
WHERE node_id =64

 

[Moshe1010]

Yeah, I was thinking I missed that in ACP or something like that

 

[Adam Howard]

Percona is wonderful thing, but I too sometimes notice issues when doing a major upgrade (by major, I don't mean small point releases).

For me I just uninstalled and purged old Percona 5.5 and did a fresh install. No problems and was quick to do so.

(Of course you should backup any configuration files before doing that)

 

[Slavik]

5.6 only contains bugfixes and some new configuration options IIRC, there was nothing specific to extra performance included.

 

[MattW]

I've had to roll back to Percona 5.5 this evening.

Tried to restart apache, and it wouldn't restart.

httpd: Syntax error on line 40 of /usr/local/apache/conf/httpd.conf: Syntax error on line 9 of /usr/local/apache/conf/php.conf: Cannot load /usr/local/apache/modules/libphp5.so into server: /usr/local/apache/modules/libphp5.so: symbol client_errors, version libmysqlclient_18 not defined in file libmysqlclient.so.18 with link time reference

MySQL 5.6 ISN'T compatible with Cpanel yet!!!!

 

[Slavik]

I've had to roll back to Percona 5.5 this evening.

Tried to restart apache, and it wouldn't restart.

httpd: Syntax error on line 40 of /usr/local/apache/conf/httpd.conf: Syntax error on line 9 of /usr/local/apache/conf/php.conf: Cannot load /usr/local/apache/modules/libphp5.so into server: /usr/local/apache/modules/libphp5.so: symbol client_errors, version libmysqlclient_18 not defined in file libmysqlclient.so.18 with link time reference

MySQL 5.6 ISN'T compatible with Cpanel yet!!!!

Did you update the shared client files, you may have accidentally excluded it?

 

[MattW]

Did you update the shared client files, you may have accidentally excluded it?

Yeah, this is what I had installed:

Percona-Server-shared-56-5.6.13-rel61.0.461.rhel6.x86_64
Percona-Server-server-56-5.6.13-rel61.0.461.rhel6.x86_64
Percona-Server-test-56-5.6.13-rel61.0.461.rhel6.x86_64
Percona-Server-client-56-5.6.13-rel61.0.461.rhel6.x86_64
Percona-Server-devel-56-5.6.13-rel61.0.461.rhel6.x86_64

Luckily there isn't much to change rolling it back, and I had my existing my.cnf backed up, so only took 5 minutes to revert.

This is the error EasyApache throws when building PHP

ext/mysqli/.libs/mysqli.o: In function `php_local_infile_error':
/home/cpeasyapache/src/php-5.4.20/ext/mysqli/mysqli.c:1493: undefined reference to `client_errors'
ext/mysqli/.libs/mysqli.o: In function `php_local_infile_read':
/home/cpeasyapache/src/php-5.4.20/ext/mysqli/mysqli.c:1417: undefined reference to `client_errors'
ext/mysqli/.libs/mysqli.o: In function `php_local_infile_init':
/home/cpeasyapache/src/php-5.4.20/ext/mysqli/mysqli.c:1370: undefined reference to `client_errors'

 

[Slavik]

Hm, I don't see why it would break things though, will have a play tomorow if I remember.