• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Percona 5.6

MattW

Well-known member
#1
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!
 

Moshe1010

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

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

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

Well-known member
#5
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)
 

MattW

Well-known member
#6
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)
 

p4guru

Well-known member
#12
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 |
+----------------+
 

MattW

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

Adam Howard

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

XenForo moderator
Staff member
#16
5.6 only contains bugfixes and some new configuration options IIRC, there was nothing specific to extra performance included.
 

MattW

Well-known member
#17
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!!!!
 

Slavik

XenForo moderator
Staff member
#18
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?
 

MattW

Well-known member
#19
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'