Percona 5.6

MattW

Well-known member
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

Code:
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:

Code:
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!
 
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.
 
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)
 
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).
 
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

Code:
query_cache_type=0
query_cache_size=0

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

Code:
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)
 
Just disabled performance_schema

Code:
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)
 
You can query the DB and count how many user_id's are watching a specific forum_id

yup

something like

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

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

something like

Code:
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):

Code:
SELECT COUNT( user_id ) AS TOTAL
FROM  `xf_forum_watch`
WHERE node_id =64
 
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)
 
5.6 only contains bugfixes and some new configuration options IIRC, there was nothing specific to extra performance included.
 
I've had to roll back to Percona 5.5 this evening.

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

Code:
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!!!!
 
I've had to roll back to Percona 5.5 this evening.

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

Code:
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?
 
Did you update the shared client files, you may have accidentally excluded it?
Yeah, this is what I had installed:

Code:
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
Code:
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'
 
Hm, I don't see why it would break things though, will have a play tomorow if I remember.
 
Back
Top Bottom