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

Percona 5.6

Discussion in 'Server Configuration and Hosting' started by MattW, Oct 12, 2013.

  1. MattW

    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!
     
    Adam Howard, Moshe1010 and Bob like this.
  2. Moshe1010

    Moshe1010 Well-Known Member

    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.
     
  3. MattW

    MattW Well-Known Member

    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)
     
  4. Moshe1010

    Moshe1010 Well-Known Member

    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 likes this.
  5. MattW

    MattW Well-Known Member

    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)
     
  6. MattW

    MattW Well-Known Member

    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)
     
  7. Moshe1010

    Moshe1010 Well-Known Member

    Any difference in speed?
    Without it should be better
     
  8. MattW

    MattW Well-Known Member

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

    p4guru Well-Known Member

    FYI for MySQL 5.6, Percona 5.6 and MariaDB 10, disabling performance schema will be faster ;)
     
    MattW likes this.
  10. Moshe1010

    Moshe1010 Well-Known Member

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

    MattW Well-Known Member

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

    p4guru Well-Known Member

    yup

    something like

    Code:
    MariaDB [dbname]> select count(user_id) from session_activity where params='node_id=2';
    
    +----------------+
    | count(user_id) |
    +----------------+
    |              1 |
    +----------------+
     
  13. MattW

    MattW Well-Known Member

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

    Code:
    SELECT COUNT( user_id ) AS TOTAL
    FROM  `xf_forum_watch`
    WHERE node_id =64
     
  14. Moshe1010

    Moshe1010 Well-Known Member

    Yeah, I was thinking I missed that in ACP or something like that
     
  15. Adam Howard

    Adam Howard Well-Known Member

    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)
     
  16. Slavik

    Slavik XenForo Moderator Staff Member

    5.6 only contains bugfixes and some new configuration options IIRC, there was nothing specific to extra performance included.
     
    Dinh Thanh likes this.
  17. MattW

    MattW Well-Known Member

    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!!!!
     
  18. Slavik

    Slavik XenForo Moderator Staff Member

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

    MattW Well-Known Member

    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'
     
  20. Slavik

    Slavik XenForo Moderator Staff Member

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

Share This Page