Percona server, „less-known“ MySQL drop-in replacement for enterprises stands for high quality software. Percona is offering a lot and I really do recommend to test it!
Environment
Manjaro Openbox 0.8.8, Virtualbox 4.3.6
[j@jman ~]$ uname -a
Linux jman 3.10.27-1-MANJARO #1 SMP Sat Jan 18 09:42:13 UTC 2014 x86_64 GNU/Linux
Percona, let´s go!
Arch Linux is using MariaDB from March (2013). Percona is still rather rare in repositories. Let´s see if Manjaro is following market trends + offers free will 🙂
[j@jman ~]$ sudo pacman -S mysql
[sudo] password for j:
:: There are 2 providers available for mysql:
:: Repository extra
1) mariadb
:: Repository community
2) percona-server
Enter a number (default=1):
That´s pitty! No sweat, tears, blood? Just number 2 and here we go? 🙁
Enter a number (default=1): 2
resolving dependencies…
looking for inter-conflicts…
Packages (4): libaio-0.3.109-7 libmariadbclient-5.5.34-3 mariadb-clients-5.5.34-3 percona-server-5.6.15_rel63.0-1
Total Download Size: 22.92 MiB
Total Installed Size: 171.96 MiB
:: Proceed with installation? [Y/n]
:: Retrieving packages …
libaio-0.3.109-7-x86_64 4.3 KiB 0.00B/s 00:00 [##############################################] 100%
libmariadbclient-5.5.34-3-x86_64 6.6 MiB 659K/s 00:10 [##############################################] 100%
mariadb-clients-5.5.34-3-x86_64 888.7 KiB 718K/s 00:01 [##############################################] 100%
percona-server-5.6.15_rel63.0-1-x86_64 15.4 MiB 746K/s 00:21 [##############################################] 100%
(4/4) checking keys in keyring [##############################################] 100%
(4/4) checking package integrity [##############################################] 100%
(4/4) loading package files [##############################################] 100%
(4/4) checking for file conflicts [##############################################] 100%
(4/4) checking available disk space [##############################################] 100%
(1/4) installing libaio [##############################################] 100%
(2/4) installing libmariadbclient [##############################################] 100%
(3/4) installing mariadb-clients [##############################################] 100%
(4/4) installing percona-server [##############################################] 100%
Installing MySQL system tables…2014-01-27 12:12:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2014-01-27 12:12:40 1067 [Note] InnoDB: The InnoDB memory heap is disabled
2014-01-27 12:12:40 1067 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-01-27 12:12:40 1067 [Note] InnoDB: Compressed tables use zlib 1.2.8
2014-01-27 12:12:40 1067 [Note] InnoDB: Using Linux native AIO
2014-01-27 12:12:40 1067 [Note] InnoDB: Not using CPU crc32 instructions
2014-01-27 12:12:40 1067 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-01-27 12:12:40 1067 [Note] InnoDB: Completed initialization of buffer pool
2014-01-27 12:12:40 1067 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2014-01-27 12:12:40 1067 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2014-01-27 12:12:40 1067 [Note] InnoDB: Database physically writes the file full: wait…
2014-01-27 12:12:40 1067 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-01-27 12:12:41 1067 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-01-27 12:12:43 1067 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-01-27 12:12:43 1067 [Warning] InnoDB: New log files created, LSN=45781
2014-01-27 12:12:43 1067 [Note] InnoDB: Doublewrite buffer not found: creating new
2014-01-27 12:12:44 1067 [Note] InnoDB: Doublewrite buffer created
2014-01-27 12:12:44 1067 [Note] InnoDB: 128 rollback segment(s) are active.
2014-01-27 12:12:44 1067 [Warning] InnoDB: Creating foreign key constraint system tables.
2014-01-27 12:12:44 1067 [Note] InnoDB: Foreign key constraint system tables created
2014-01-27 12:12:44 1067 [Note] InnoDB: Creating tablespace and datafile system tables.
2014-01-27 12:12:44 1067 [Note] InnoDB: Tablespace and datafile system tables created.
2014-01-27 12:12:44 1067 [Note] InnoDB: Waiting for purge to start
2014-01-27 12:12:44 1067 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.15-63.0 started; log sequence number 0
2014-01-27 12:12:44 1067 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2014-01-27 12:12:44 1067 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2014-01-27 12:12:57 1067 [Note] Binlog end
2014-01-27 12:12:57 1067 [Note] InnoDB: FTS optimize thread exiting.
2014-01-27 12:12:57 1067 [Note] InnoDB: Starting shutdown…
2014-01-27 12:12:59 1067 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
Filling help tables…2014-01-27 12:12:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2014-01-27 12:12:59 1114 [Note] InnoDB: The InnoDB memory heap is disabled
2014-01-27 12:12:59 1114 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-01-27 12:12:59 1114 [Note] InnoDB: Compressed tables use zlib 1.2.8
2014-01-27 12:12:59 1114 [Note] InnoDB: Using Linux native AIO
2014-01-27 12:12:59 1114 [Note] InnoDB: Not using CPU crc32 instructions
2014-01-27 12:12:59 1114 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-01-27 12:12:59 1114 [Note] InnoDB: Completed initialization of buffer pool
2014-01-27 12:12:59 1114 [Note] InnoDB: Highest supported file format is Barracuda.
2014-01-27 12:12:59 1114 [Note] InnoDB: 128 rollback segment(s) are active.
2014-01-27 12:12:59 1114 [Note] InnoDB: Waiting for purge to start
2014-01-27 12:13:00 1114 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.15-63.0 started; log sequence number 1625977
2014-01-27 12:13:00 1114 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2014-01-27 12:13:00 1114 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2014-01-27 12:13:00 1114 [Note] Binlog end
2014-01-27 12:13:00 1114 [Note] InnoDB: FTS optimize thread exiting.
2014-01-27 12:13:00 1114 [Note] InnoDB: Starting shutdown…
2014-01-27 12:13:02 1114 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‚new-password‘
/usr/bin/mysqladmin -u root -h jman password ‚new-password‘
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Default config file /etc/mysql/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
–defaults-file argument to mysqld_safe when starting the server
Optional dependencies for percona-server
perl-dbi
perl-dbd-mysql
Inputing root password:
/usr/bin/mysqladmin -u root password 'lalala'
Starting mysql console under root (Percona root) user:
[j@jman ~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.15 Source distribution
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type ‚help;‘ or ‚\h‘ for help. Type ‚\c‘ to clear the current input statement.
MySQL [(none)]>
MySQL [mysql]> show variables like ‚%version%‘;
+————————-+———————+
| Variable_name | Value |
+————————-+———————+
| innodb_version | 5.6.15-63.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.15 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+————————-+———————+
7 rows in set (0.00 sec)
Small indication that we can expect some differencies can be found in version_comment. MySQL have something like „MySQL Community Edition - Standard (GPL)„, „MySQL Enterprise Server (GPL)„, MariaDB fork: „mariadb.org binary distribution„.
A lot of tuning features (not visible for BFU from first sight) are obvious after listening innodb parameters, their usage is precisely described on Percona web page (%Improvements items):
MySQL [(none)]> show variables like ‚%innodb%‘;
+——————————————-+————————+
| Variable_name | Value |
+——————————————-+————————+
| ignore_builtin_innodb | OFF |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_partitions | 1 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_corrupt_table_action | assert |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_empty_free_list_algorithm | backoff |
| innodb_fake_changes | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbors | 1 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponential_backoff |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_kill_idle_transaction | 0 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locking_fake_changes | ON |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_lru_scan_depth | 1024 |
| innodb_max_bitmap_file_size | 104857600 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 431 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sched_priority_cleaner | 19 |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_track_changed_pages | OFF |
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_atomic_writes | OFF |
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.15-63.0 |
| innodb_write_io_threads | 4 |
+——————————————-+————————+
140 rows in set (0.00 sec)
Within querying storage engines there´s also visible Percona comment inside InnoDB line:
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Percona (similar to TokuDB) can offer great performance jump. The only issue for mysql-BFU is that changes are invisible =causing bad evaluation of this product. Main target remains between top companies, relying on MySQL/MariaDB, looking for *something* more faster, stable, scalable with huge tuning potential. I´m dreaming about IT directors with these visions = wishing good luck to Percona! 🙂
-a-