wagnerbianchi.com

MariaDB 10.1, MSR and MTS

março 24th, 2016 Bianchi Posted in MySQL Manutenção, MySQL Replication No Comments »

As a preparation of my presentation together with Max Bubenick at 2016’s Percona Live, happening in Santa Clara, CA, US, I’m running as many tests as I can to check all the maturity of the technology of feature we are about to talking about. This is a common sense that you need to go over the planned to be presented feature in order to address some of the implicit subjects. This way, we stared discussing about a crash on MariaDB 10.1 setup for a Multi-Source Replication Slave, being this slave server a Multi-Threaded Slave as well running with 12 threads dedicated to execute raw updates from the relay log, having at least 3 out of those 12 threads dedicated to each of the exiting domain_id. You can check the numbers of threads dedicated to each domain_id interpreting the contents of mysql.gtid_slave_pos table to keep track of their current position (the global transaction ID of the last transaction applied). Using the table allows the slave to maintain a consistent value for the gtid_slave_pos system variable across server restarts. That is, as a I have setup 3 masters and one multi-source slave, in this scenario I’ve got domains #2, #3, #4, being the multi-source slave the domain #1. That justifies the 12 threads and at least 3 for each domain.

Below, the designed architecture:

Screen Shot 2016-03-24 at 9.23.40 AM

box01 - @@server_id=1, @@gtid_domain_id=1
box02 - @@server_id=2, @@gtid_domain_id=2
box03 - @@server_id=3, @@gtid_domain_id=3
box04 - @@server_id=4, @@gtid_domain_id=4

After configuring the multi-source replication and having configuration files well set, I started some tests.

#: Connection name with box02
MariaDB [(none)]> change master 'box02' to master_host='192.168.0.102', master_user='repl', master_password='Bi@nchI', master_use_gtid=current_pos;
#: Connection name with box03
MariaDB [(none)]> change master 'box03' to master_host='192.168.0.102', master_user='repl', master_password='Bi@nchI', master_use_gtid=current_pos;
#: Connection name with box04
MariaDB [(none)]> change master 'box04' to master_host='192.168.0.104', master_user='repl', master_password='Bi@nchI', master_use_gtid=current_pos;

Just to make sure we’re on the same page, I created on the master’s side individual databases to make the masters to write just to their own database schema to avoid conflicts on writing to the same table (that’s an existing successful case I have to formulate a new blog to tell). So, after that, I used sysbench to prepare the test case, creating 10 tables in each database schema with 10000 rows each table. Finally, I run sysbench with the following structure to execute a simple 60 secs test using OLTP script:

[vagrant@maria0X ~]$ sudo sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=10000 --mysql-db=box0X --oltp-tables-count=10 --mysql-user=root --db-driver=mysql --mysql-table-engine=innodb --max-time=60 --max-requests=0 --report-interval=60 --num-threads=50 --mysql-engine-trx=yes run

I started the above sysbench on all the three masters and then, the multi-source slave has crashed with the below error:

2016-03-23 19:54:57 140604957547264 [ERROR] Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2016-03-23 19:54:57 140604957547264 [Warning] Slave: Running in read-only mode Error_code: 1836
2016-03-23 19:54:57 140604957547264 [Warning] Slave: Table 'sbtest2' is read only Error_code: 1036
2016-03-23 19:54:57 140604957547264 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2016-03-23 19:54:57 140604957547264 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2016-03-23 19:54:57 140604957547264 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
[...snip...]
2016-03-23 19:54:57 140604957244160 [ERROR] Slave (additional info): Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2016-03-23 19:54:57 140604957244160 [Warning] Slave: Running in read-only mode Error_code: 1836
2016-03-23 19:54:57 140604957244160 [Warning] Slave: Table 'sbtest1' is read only Error_code: 1036
2016-03-23 19:54:57 140604957244160 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2016-03-23 19:54:57 140604957244160 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
2016-03-23 19:54:57 140604957244160 [Warning] Slave: Commit failed due to failure of an earlier commit on which this one depends Error_code: 1964
[...snip...]
2016-03-23 19:59:14 140604959972096 [Note] /usr/sbin/mysqld: Normal shutdown

The problem here is clear, “Commit failed due to failure of an earlier commit on which this one depends”.

Furthermore, when I tried to start multi-source slave back, I found the following events added to the error log:

2016-03-23 19:59:17 139987887904800 [Note] /usr/sbin/mysqld (mysqld 10.1.11-MariaDB-log) starting as process 3996 ...
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Memory barrier is not used
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Using Linux native AIO
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Using generic crc32 instructions
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Completed initialization of buffer pool
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: Transaction 46834 was in the XA prepared state.
InnoDB: Transaction 46834 was in the XA prepared state.
InnoDB: Transaction 46835 was in the XA prepared state.
InnoDB: Transaction 46835 was in the XA prepared state.
InnoDB: Transaction 46836 was in the XA prepared state.
InnoDB: Transaction 46836 was in the XA prepared state.
InnoDB: Transaction 46838 was in the XA prepared state.
InnoDB: Transaction 46838 was in the XA prepared state.
InnoDB: Transaction 46839 was in the XA prepared state.
InnoDB: Transaction 46839 was in the XA prepared state.
InnoDB: 6 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 4 row operations to undo
InnoDB: Trx id counter is 47616
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Waiting for purge to start
InnoDB: Starting in background the rollback of uncommitted transactions
2016-03-23 19:59:17 7f51503fe700 InnoDB: Rolling back trx with id 46837, 4 rows to undo
2016-03-23 19:59:17 139987215443712 [Note] InnoDB: Rollback of trx with id 46837 completed
2016-03-23 19:59:17 7f51503fe700 InnoDB: Rollback of non-prepared transactions completed
2016-03-23 19:59:17 139987887904800 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.26-76.0 started; log sequence number 124266988
2016-03-23 19:59:17 139987887904800 [Note] Plugin 'FEEDBACK' is disabled.
2016-03-23 19:59:17 7f517854d820 InnoDB: Starting recovery for XA transactions...
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction 46839 in prepared state after recovery
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction contains changes to 7 rows
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction 46838 in prepared state after recovery
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction contains changes to 5 rows
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction 46836 in prepared state after recovery
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction contains changes to 7 rows
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction 46835 in prepared state after recovery
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction contains changes to 5 rows
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction 46834 in prepared state after recovery
2016-03-23 19:59:17 7f517854d820 InnoDB: Transaction contains changes to 7 rows
2016-03-23 19:59:17 7f517854d820 InnoDB: 5 transactions in prepared state after recovery
2016-03-23 19:59:17 139987887904800 [Note] Found 5 prepared transaction(s) in InnoDB
2016-03-23 19:59:17 139987887904800 [ERROR] Found 5 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery information (last binlog or tc.log file) was manually deleted after a crash. You have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transactions.
2016-03-23 19:59:17 139987887904800 [ERROR] Aborting

So, to get the MSR Slave back:

[vagrant@maria01 ~]$ sudo mysqld --defaults-file=/etc/my.cnf --tc-heuristic-recover=ROLLBACK
2016-03-23 20:18:20 140348206848032 [Note] mysqld (mysqld 10.1.11-MariaDB-log) starting as process 4047 ...
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Memory barrier is not used
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Using Linux native AIO
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Using generic crc32 instructions
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Completed initialization of buffer pool
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: Transaction 46834 was in the XA prepared state.
InnoDB: Transaction 46834 was in the XA prepared state.
InnoDB: Transaction 46835 was in the XA prepared state.
InnoDB: Transaction 46835 was in the XA prepared state.
InnoDB: Transaction 46836 was in the XA prepared state.
InnoDB: Transaction 46836 was in the XA prepared state.
InnoDB: Transaction 46838 was in the XA prepared state.
InnoDB: Transaction 46838 was in the XA prepared state.
InnoDB: Transaction 46839 was in the XA prepared state.
InnoDB: Transaction 46839 was in the XA prepared state.
InnoDB: 5 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 48128
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Waiting for purge to start
InnoDB: Starting in background the rollback of uncommitted transactions
2016-03-23 20:18:21 7fa534bff700 InnoDB: Rollback of non-prepared transactions completed
2016-03-23 20:18:21 140348206848032 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.26-76.0 started; log sequence number 124267433
2016-03-23 20:18:21 140348206848032 [Note] Plugin 'FEEDBACK' is disabled.
2016-03-23 20:18:21 140348206848032 [Note] Heuristic crash recovery mode
2016-03-23 20:18:21 7fa55d039820 InnoDB: Starting recovery for XA transactions...
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction 46839 in prepared state after recovery
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction contains changes to 7 rows
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction 46838 in prepared state after recovery
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction contains changes to 5 rows
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction 46836 in prepared state after recovery
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction contains changes to 7 rows
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction 46835 in prepared state after recovery
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction contains changes to 5 rows
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction 46834 in prepared state after recovery
2016-03-23 20:18:21 7fa55d039820 InnoDB: Transaction contains changes to 7 rows
2016-03-23 20:18:21 7fa55d039820 InnoDB: 5 transactions in prepared state after recovery
2016-03-23 20:18:21 140348206848032 [Note] Found 5 prepared transaction(s) in InnoDB
2016-03-23 20:18:21 140347457898240 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-03-23 20:18:21 140348206848032 [Note] Please restart mysqld without --tc-heuristic-recover
2016-03-23 20:18:21 140348206848032 [ERROR] Can't init tc log
2016-03-23 20:18:21 140348206848032 [ERROR] Aborting

And finally:

[vagrant@maria01 ~]$ sudo service mysql start
Starting MySQL... SUCCESS!

By the way, as per the discussion on twitter, I’m not really sure yet if this is a problem related to the in-order commit when using parallel replication which implies that a transaction commit conflict is happening at that point. Below the configuration file used for the MSR Slave, showing that it’s configured with @@slave_pararllel_mode=optimistic which as per the manual online “tries to apply most transactional DML in parallel, and handles any conflicts with rollback and retry”, more info here.

#: box01 - multi-source slave
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
user=mysql
port=3306
socket=/var/lib/mysql/mysql.sock
basedir=/usr
datadir=/var/lib/mysql
read_only=1
#: repl vars
server_id=1
report_host=box01
report_port=3306
report_user=repl
log_bin=mysql-bin
log_bin_index=mysql.index
log_slave_updates=true
binlog_format=ROW
#: verify checksum on master
master_verify_checksum=1
#: gtid vars
gtid_domain_id=1
gtid_ignore_duplicates=ON
gtid_strict_mode=1
 
#: msr slave parallel mode *
box02.slave_parallel_mode=conservative
box03.slave_parallel_mode=conservative
box04.slave_parallel_mode=conservative
 
slave_parallel_threads=10
slave_domain_parallel_threads=2
slave_parallel_max_queued=512M
slave_net_timeout=15
slave_sql_verify_checksum=1
slave_compressed_protocol=1
#: binary log group commit behavior
#binlog_commit_wait_usec=100000
#binlog_commit_wait_count=20

Maybe a test using @@slave_domain_parallel_threads should be done as the next step, but, if you have any additional thoughts on this, it’s really appreciated.

Continuing with this, I found that Connection Names were not running in optimistic mode (it was conservative, which limits parallelism in an effort to avoid any conflicts) and then after changing that, I did the test again:

#: current values
MariaDB [(none)]> show all slaves status\G
              Connection_name: box02
                Parallel_Mode: conservative
              Connection_name: box03
                Parallel_Mode: conservative
              Connection_name: box04
                Parallel_Mode: conservative
3 rows in set (0.00 sec)
 
#: changing Parallel Mode to Optimistic
MariaDB [(none)]> stop all slaves;
Query OK, 0 rows affected, 3 warnings (0.00 sec)
 
MariaDB [(none)]> set global box02.slave_parallel_mode='optimistic';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global box03.slave_parallel_mode='optimistic';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global box04.slave_parallel_mode='optimistic';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> start all slaves;
Query OK, 0 rows affected, 3 warnings (0.02 sec)
 
MariaDB [(none)]> show all slaves status\G
              Connection_name: box02
                Parallel_Mode: optimistic
              Connection_name: box03
                Parallel_Mode: optimistic
              Connection_name: box04
                Parallel_Mode: optimistic
3 rows in set (0.00 sec)

The parallel threads were like:

MariaDB [(none)]> SELECT ID,TIME,STATE,USER FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='system user';
+----+------+------------------------------------------------------------------+-------------+
| ID | TIME | STATE                                                            | USER        |
+----+------+------------------------------------------------------------------+-------------+
| 46 |   81 | Slave has read all relay log; waiting for the slave I/O thread t | system user |
| 45 |   81 | Waiting for master to send event                                 | system user |
| 44 |   86 | Slave has read all relay log; waiting for the slave I/O thread t | system user |
| 43 |   86 | Waiting for master to send event                                 | system user |
| 42 |  102 | Slave has read all relay log; waiting for the slave I/O thread t | system user |
| 41 |  102 | Waiting for master to send event                                 | system user |
| 35 |    0 | Waiting for prior transaction to commit                          | system user |
| 34 |    0 | Waiting for prior transaction to commit                          | system user |
| 33 |    0 | Waiting for prior transaction to commit                          | system user |
| 32 |  175 | Waiting for work from SQL thread                                 | system user |
| 31 |  175 | Waiting for work from SQL thread                                 | system user |
| 30 |    0 | Unlocking tables                                                 | system user |
| 29 |    0 | Unlocking tables                                                 | system user |
| 28 |    0 | Unlocking tables                                                 | system user |
| 27 |  175 | Waiting for work from SQL thread                                 | system user |
| 26 |  175 | Waiting for work from SQL thread                                 | system user |
+----+------+------------------------------------------------------------------+-------------+
16 rows in set (0.00 sec)

Additionally, I’m curious to check now the Retried_transactions per connection Name variable to check if the retry transactions part of the optimistic parallel replication mode is really working:

MariaDB [(none)]> pager egrep "Connection|Parallel|Gtid_IO|Retried"
PAGER set to 'egrep "Connection|Parallel|Gtid_IO|Retried"'
MariaDB [(none)]> show all slaves status\G
              Connection_name: box02
                  Gtid_IO_Pos: 1-1-68,4-4-87933,3-3-77410,2-2-149378
                Parallel_Mode: optimistic
         Retried_transactions: 12
              Connection_name: box03
                  Gtid_IO_Pos: 1-1-68,4-4-87933,3-3-88622,2-2-131340
                Parallel_Mode: optimistic
         Retried_transactions: 3
              Connection_name: box04
                  Gtid_IO_Pos: 1-1-68,4-4-98365,3-3-77410,2-2-131340
                Parallel_Mode: optimistic
         Retried_transactions: 3
3 rows in set (0.02 sec)

Additionally, we can check that the global status variable Slave_retried_transactions finnally reflects the total value to retried transactions by Connection Names on MSR Slave:

MariaDB [(none)]> show global status like 'Slave_retried%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Slave_retried_transactions | 18    |
+----------------------------+-------+
1 row in set (0.00 sec)

So, it’s solved, slave hasn’t crashed anymore, but the question why did the MSR Slave crashed is not solved yet. But, what was learnt here was that, we can use also minimal besides of conservative for slave_parallel_mode that will play very good in this case as it’s going to only parallelizes the commit steps of transactions, this is the next test I would like to realize as the next step on this ever growing post. I’m going to try another post to check the relation between transaction’s conflicts rate and performance impact over the exiting slave parallel mode.

AddThis Social Bookmark Button

Lock wait timeout exceeded; try restarting transaction

dezembro 26th, 2013 Bianchi Posted in MySQL A&D, MySQL Manutenção No Comments »

It’s very nice when you find some very good and well explained messages in the MySQL error log and the ENGINE INNODB STATUS output. The very good part of the story is to know where you must go to check problems regarding some resources. This is not from today that we’ve seen many messages regarding transaction deadlocks and the connection that is lost in midst of a query execution. This time I used some extra time to execute some tests in order to force MySQL to server me an error explicitly in the mysql client.

As we know, InnoDB is the MySQL’s transactional engine and every transaction has its isolation level well configured by the database administrator or, as happens in the majority of time, the default or standard REPEATABLE READ is used. As the isolation level is beyond this post focus, I’d like to focus the error message around the deadlocks.

Just to put that on the records and give few hints to solve the transaction timeout problem, I played around with the innodb_lock_wait_timeout environment variable which has as a default value 50 seconds; this is the time another transaction will wait to acquire a lock on certain resource,currently locked by another transaction. Imagine a line, if someone is buying a ticket for the show, you must wait that person to finish the buying transaction. But, considering databases, if you’re the second transaction you’ll wait just for innodb_lock_wait_timeout seconds!

Let’s play with that…(I will keep it simple, just to play around…)

mysql> create table test.t1(id int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test.t1 set id=1;
Query OK, 1 row affected (0.01 sec)

On terminal A, I started a transaction that will automatically set autocommit to 0, which needs an explicit commit or rollback. My intention here is to lock a resource that is the table test.t1 previously created.

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test.t1 set id=1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

On terminal B, I firstly configured innodb_lock_wait_timeout with 1 as its value and then…

mysql> set innodb_lock_wait_timeout=1;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> insert into test.t1 set id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This is a typical scenario where deadlocks are happening all the time and it can generate some important performance issues. Before to increase the time innodb_lock_wait_timeout it’s better to check the queries or transactions started by the application so as to fix logic problems. Remember that triggers can be the reason of some problems as this resource will be part of the current transaction as well.

So, just to finish this kidding time, I configured innodb_lock_wait_timeout considering terminal A and B mentioned scenario just to check what ENGINE INNODB STATUS shows up:

------------
TRANSACTIONS
------------
Trx id counter 1826
Purge done for trx's n:o < 1822 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 7, OS thread handle 0x7f50f05dd700, query id 58 localhost root init
show engine innodb status
---TRANSACTION 1825, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 5, OS thread handle 0x7f50f061e700, query id 56 localhost root update
insert into test.t1 set id=3
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 1825 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

A good advice here in case you’re facing issues like this is to develop to your apps connecting to MySQL a structured code using try/catch to raise the exception which is the transaction that died after waiting to acquire a lock in order to re-execute and not lose data at the end. If you’re seeing errors like these around, make sure to address that asap to avoid data inconsistencies on your databases. The same can happen as well on the replication slave side, which can break the replication by almost the same error in case you have different values for innodb_lock_wait_timeout configured for master and slaves.

Have you played around with some InnoDB variable and got some simple scenario?

Cheers!!

AddThis Social Bookmark Button

MySQL 5.5.X – Sort aborted

dezembro 26th, 2013 Bianchi Posted in MySQL A&D, MySQL Manutenção, MySQL Tuning No Comments »

This morning I started investigating a file sort problem that is happening with a report server. Actually, what caught more my attention was what is really behind of the error message that is appearing many time along MySQL report server’s error log. Yes, this particular server is a slave server used just for extract reports on business data and because that, this kind if server generally is prepared to have good response to read queries which use aggregations COUNT(), SUM(), AVG() and consequently group data by some spacial column. BTW, all the data will be more in memory than on disk and all that story.

But, what is behind the message “[Warning] Sort aborted, host:” ? So, researching for the same case on the internet, I found that some of the problems reported by MySQL on log_error is around these possibilites:

Insufficient disk space in tmpdir prevented tmpfile from being created

This one is easier to check, just df -h /tmp will give you the notice about all the avail space you have at this point at the temporary dir. So, a good question here is, what do I research for when get the notice that there is enough space in /tmp dir? This is the time to get the query what is causing the issue and re execute it, monitoring the /tmp dir and checking if it’s getting full.

Somebody ran KILL in the middle of a filesort

At this point, I agree with Suresh Kuna when he said that “as a DBA, we can’t do much with the first point apart from informing customer to check at the application side for connection drop outs”. The query can be stopped by a error reading packages, a transactions timeout or even a replication slave timeout. Many variables get involved when analysing this kind of problem, but, mainly, problems regarding a user that give up the report’s query in the processing midst.

The server was shutdown while some queries were sorting

When the error is reported to the error log, probably you have an opportunity to observe the timestamp associated with that and then, go through the details on MySQL Server shutdown reading along the error log entries.

A transaction got rolled back or aborted due to lock wait timeout or deadlock

At this point we can consider many things but the main ones are the checking of ENGINE INNODB STATUS which will report all the deadlocks in case you’re using InnoDB to handle database tables or even the log_error that will report message errors regrading occurred deadlocks with local server transaction or if the local server act as a slave, the slave, the message can report that the deadlock is happening with a replicated transaction – innodb_lock_wait_timeout and slave_net_timeout can help with this. Another variables that can be used is slave_transaction_retries which reports if a replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because the transaction’s execution time exceeded InnoDB’s innodb_lock_wait_timeout.

Unexpected errors, such as source table or even tmp table was corrupt.

In this case, depending on the size of the involved table (sometimes you won’t be able to know what is the target table just reading the log_error), a simple CHECK TABLE can be effective in get to know if the table has corrupted pages or another errors.

Processing of a subquery failed which was also sorting

This is a classic case in majority of times. The good news is that when a subquery fails to be sorted out it’s a good case to review the value configured to sort_buffer_size. TAKE CARE, do not increase it without checking the reason and the * status variables to study what is the best value to fit the server’s requirements in file sorting process.

Considering what is explained on the online MySQL manual, just increase the sort_buffer_size value when the Sort_merge_passes is greater then zero.

Sheri Cabral wrote about that: http://www.pythian.com/blog/sort_buffer_size-and-knowing-why/

AddThis Social Bookmark Button

Got an error reading communication packets

julho 12th, 2012 Bianchi Posted in MySQL A&D, MySQL Manutenção No Comments »

O nome desse post é exatamente a mensagem de erro que você provavelmente poderá receber ao verificar o estado de saúde do seu servidor de bancos de dados MySQL, nesse caso, um MySQL 5.0. Na semana atual estou trabalhando com um cliente localizado no Brasil que tem cerca de 1502 conexões simultâneas no MySQL, este que é o repositório de informações de um ERP que centraliza as operações da empresa. São várias lojas acessando um mesmo MySQL configurado com um repositório central – obviamente, anteriormente, este cliente passou a operara com servidores em replicação, onde temos um servidor MASTER e outros 7 SLAVEs, cada qual com funções distintas.

Enfim, independentemente da arquitetura do cliente, encontramos um problema logo depois que a mesma começou a rodar. Ao consultar o logo de erro do MySQL, encontramos o seguinte cenário:

root@master1:/var/log# tail -f /var/log/mysql/mysql.err
120712 14:22:55 [Warning] Aborted connection 173570 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
120712 14:23:15 [Warning] Aborted connection 173025 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
120712 14:27:48 [Warning] Aborted connection 169655 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
120712 14:29:00 [Warning] Aborted connection 165547 to db: 'sqldados' user: 'root' host: '' (Got an error reading communication packets)
120712 14:29:23 [Warning] Aborted connection 172752 to db: 'unconnected' user: 'sink02' host: '' (Got an error reading communication packets)
120712 14:30:27 [Warning] Aborted connection 173886 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
120712 14:31:54 [Warning] Aborted connection 174079 to db: 'unconnected' user: 'sink18' host: '' (Got an error reading communication packets)
120712 14:34:16 [Warning] Aborted connection 171530 to db: 'sqldados' user: 'root' host: '' (Got an error reading communication packets)

Inicialmente, pensamos ser um problema de latência de rede onde a conexão para leitura e escrita estavam sen fechadas, mesmo com o status da thread continuando em SLEEP. Sendo assim, ajustamos as variáveis net_% do MySQL. O primeiro passo foi resetar todas elas:

mysql> set net_buffer_length = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'net%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
| net_read_timeout  | 60    |
| net_retry_count   | 10    |
| net_write_timeout | 60    |
+-------------------+-------+
4 rows in set (0.01 sec)

Para testarmos a eliminação do erro, configuramos as variáveis net_read_timeout e net_write_timeout com um valor maior:

mysql> set global net_write_timeout=360;
Query OK, 0 rows affected (0.00 sec)

mysql> set global net_write_timeout=360;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'net%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
| net_read_timeout  | 360   |
| net_retry_count   | 10    |
| net_write_timeout | 360   |
+-------------------+-------+
4 rows in set (0.00 sec)

Mesmo assim, o erro não foi corrigido e acompanhando o log de erro com tail -f, ele voltou a aparecer…a solução foi ajustar o max_allowed_packet para suportar pacotes maiores e então o erro foi corrigido.

mysql> select concat(format(@@max_allowed_packet/1024/1024,2),'MB') "max_allowed_packet";
+--------------------+
| max_allowed_packet |
+--------------------+
| 16.00MB            |
+--------------------+
1 row in set (0.01 sec)

mysql> set max_allowed_packet=128*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> select concat(format(@@max_allowed_packet/1024/1024,2),'MB') "max_allowed_packet";
+--------------------+
| max_allowed_packet |
+--------------------+
| 128.00MB           |
+--------------------+
1 row in set (0.01 sec)

Após isto, observamos o log por mais 2 horas e não houve mais ocorrência do erro “Got an error reading communication packets”. Vale salientar também que este erro pode ser causado quando o aplicativo que se conecta ao MySQL não finaliza uma conexão de maneira apropriada (sem um mysql_close(), por exemplo), incrementando a variável de status Aborted_clients.

mysql> show global status like 'Aborted%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 2866  |
| Aborted_connects | 17    |
+------------------+-------+
2 rows in set (0.00 sec)

AddThis Social Bookmark Button

Estressando o MySQL com o mysqlslap

junho 15th, 2012 Bianchi Posted in MySQL A&D, MySQL Manutenção, MySQL Tuning 2 Comments »

Não é de hoje que é necessário efetuar vários testes antes de colocar um servidor em produção e para isso, as vezes os testes que a turma de desenvolvimento elabora não são os melhores na visão do administrador de bancos de dados. Na verdade, os dois times precisam estar juntos e alinhados para a realização de tal tarefa para que nada escape aos olhos e ao entendimento de ambos os pontos de vista, tanto da aplicação quanto do banco de dados, sendo que, testes de estresse ou ainda, os benchmarks, são um fator determinante para que um produto para ser eleito como solução ou não.

Nessa semana tivemos um interação interessante com um cliente no Brasil que precisou ter certeza de que um servidor de bancos de dados MySQL poderia entrar em produção para atender a uma grande demanda e por isso, nos chamou, para rever toda a configuração, além de corrigir métricas de performance, revisar discos, memória e poder de processamento. É isso, após o trabalho utilizamos o “mysqlslap” que é uma suite de benchmark nativa do MySQL, disponibilizada juntamente com vários programas clientes e não clientes no momento da instalação do servidor de bancos de dados mais popular do mundo. Várias são as opções que podem ser utilizadas com o mysqlslap que ao ser utilizado a primeira vez, cria uma base de dados para administrar os seus próprios metadados.

O que quero mostrar aqui é que, após executar uma auditoria e um bom tuning na instância de MySQL do cliente, que roda em Red Hat 6, rodamos alguns scripts personalizados, criados pela WBConsultinga para otimizar dados em páginas de dados e atualizar estatísticas de objetos, iniciamos os testes com o mysqlslap, primeiro para verificar se teríamos problemas com o número de conexão simultâneas de usuários de 3000 que o cliente requisitou para o sistema.

Executamos então o primeiro teste com 3000 clientes disparando 1000 consultas em conexão simultânea…

[root@mysqlsrv101 ~]# mysqlslap --user=root --password=XXX --auto-generate-sql --concurrency=3000 --number-of-queries=1000
Benchmark
Average number of seconds to run all queries: 33.098 seconds
Minimum number of seconds to run all queries: 33.098 seconds
Maximum number of seconds to run all queries: 33.098 seconds
Number of clients running queries: 3000
Average number of queries per client: 0

O tempo de uma iteração único poderá ser alto se imaginarmos que temos consultas ad hoc. Mas, para isso, o mysqlslap tem uma opção que permite controlar quantas vezes você deseja repetir aquela mesma iteração (-i ou –itereations). Executamos  -i 5 e assim, notamos que os ajustes de caches e buffers estão trabalhando bem…

[root@mysqlsrv101 ~]# mysqlslap --user=root --password=XXX --auto-generate-sql --concurrency=3000 --auto-generate-sql-write-number=100 -i 5
Benchmark
Average number of seconds to run all queries: 19.387 seconds
Minimum number of seconds to run all queries: 17.967 seconds
Maximum number of seconds to run all queries: 22.998 seconds
Number of clients running queries: 3000
Average number of queries per client: 0

Tivemos então os tempos médio (average) mínimo (minimum) mais baixos que executando consultas ad hoc. Consultando as variáveis de status do MySQL, percebemos que muita informação foi agregada às estruturas de memória, tanto para o InnoDB Buffer Pool quanto para o MyISAM Key Buffer.

mysql> show status like 'Innodb_buffer_pool%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_pages_data         | 5638      |
| Innodb_buffer_pool_pages_dirty        | 0         |
| Innodb_buffer_pool_pages_flushed      | 13895     |
| Innodb_buffer_pool_pages_free         | 518648    |
| Innodb_buffer_pool_pages_misc         | 1         |
| Innodb_buffer_pool_pages_total        | 524287    |
| Innodb_buffer_pool_read_ahead_rnd     | 0         |
| Innodb_buffer_pool_read_ahead         | 0         |
| Innodb_buffer_pool_read_ahead_evicted | 0         |
| Innodb_buffer_pool_read_requests      | 764868549 |
| Innodb_buffer_pool_reads              | 1865      |
| Innodb_buffer_pool_wait_free          | 0         |
| Innodb_buffer_pool_write_requests     | 665820    |
+---------------------------------------+-----------+
13 rows in set (0.01 sec)

mysql> show status like 'Key_%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Key_blocks_not_flushed | 1023    |
| Key_blocks_unused      | 17      |
| Key_blocks_used        | 2514736 |
| Key_read_requests      | 0       |
| Key_reads              | 2876589 |
| Key_write_requests     | 4566867 |
| Key_writes             | 4567890 |
+------------------------+---------+
7 rows in set (0.00 sec)

Finalmente, um teste de evolução de conexões simultâneas, inciando em 500, indo a 1000, 1500 e finalmente para 3000:

[root@mysqlsrv101 ~]# mysqlslap --user=root --password=XXX --auto-generate-sql --concurrency=500,1000,1500,3000 --number-of-queries=100
Benchmark
Average number of seconds to run all queries: 3.084 seconds
Minimum number of seconds to run all queries: 3.084 seconds
Maximum number of seconds to run all queries: 3.084 seconds
Number of clients running queries: 500
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 4.054 seconds
Minimum number of seconds to run all queries: 4.054 seconds
Maximum number of seconds to run all queries: 4.054 seconds
Number of clients running queries: 1000
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 6.993 seconds
Minimum number of seconds to run all queries: 6.993 seconds
Maximum number of seconds to run all queries: 6.993 seconds
Number of clients running queries: 1500
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 16.021 seconds
Minimum number of seconds to run all queries: 37.092 seconds
Maximum number of seconds to run all queries: 22.008 seconds
Number of clients running queries: 3000
Average number of queries per client: 0

O resumo da utilização de recursos foi:

Máxima de CPU ao final dos testes: 49%
Máxima de Taxa de IO: 42%
Máxima de utilização de Memória: 70%
Máxima de Swap: 0%

Conseguimos acertar o número de conexões simultâneas que o cliente precisava ajustando as variáveis @@max_connections e @@max_user_connections de acordo com o que é necessário. O mysqlslap nos auxiliou para colocar o MySQL nos limites que o projeto requisitou e comprovar que o servidor de bancos de dados estava pronto para entrar em produção.

AddThis Social Bookmark Button

Verificando o tamanho de índices e dados!

junho 13th, 2012 Bianchi Posted in MySQL Manutenção, MySQL Tuning No Comments »

Muitas são as tarefas diárias (e também noturnas) que um DBA deverá realizar para trazer o seu servidor de bancos de dados em perfeito funcionamento, acessível pelos clientes e aplicações que acessam dados em um rítimo frenético como nos dias de hoje. Um dos pontos fortes que terá sempre grande atenção é quanto à performance de resposta à leituras e escritas que um servidor de bancos de dados poderá ter. O MySQL é um SGBD muito flexível, completamente customizável e tunável, com uma vasta gama de recursos disponíveis para a busca de melhorias no quesito performance.

Quando se trabalha com um banco de dados, cujas suas tabelas são controladas pelo Storage Engine padrão até a versão 5.1, o MyISAM, poderemos facilmente manter os dados de índices em memória por mais tempo possível, ajustando o MySQL para armazenar um quantidade X de dados destes índices em key_buffer, valor de memória atribuído à variável key_buffer_size. Quanto mais os dados estão em memória, menos buscas em disco (disk-seeks), menos overhead, menos processamento.

Para visualizar o tamanho dos índices de uma base de dados, consultamos a tabela TABLES do dicionário de dados, INFORMATION_SCHEMA do MySQL – note que a maioria das tabelas que compõem o dicionário de dados do MySQL é controlada pelo engine MEMORY, com excessão de algumas que são controladas pelo MyISAM. A seguinte consulta trará o tamanho total dos índices, localizados nos arquivos “.MYI” e o tamanho total dos dados, localizados nos arquivos “.MYD“:

Consulta Tamanho de Índices e Dados - utiliznado o INFORMATION_SCHEMA!

Consulta Tamanho de Índices e Dados

Como este é um teste e na minha instância de MySQL eu não tenho nenhum banco de dados que eu possa revelar informações, o tamanho dos índices e o tamanho dos dados estão zerados, mas, quando você rodar esta consulta em sua base de dados de produção, será possível recuperar informações diferentes de zero. A partir disso, teremos então o tamanho necessário de memória para começar a trabalhar as métricas de performance para o MyISAM, baseado no key_buffer_size. Dependendo do tamanho do seu hardware, trabalhar outros pontos do MySQL será mais que necessário para poder dar realmente poder de resposta para o servidor de bancos de dados para que as trocas sejam bem feitas.

Uma dica além das que já foram dadas, mantenha as estatísticas dos seus bancos de dados o mais atualizadas possível com ANALYZE e/ou OPTIMIZE.

Dúvidas? Aguardo seu comentário.

AddThis Social Bookmark Button

Agilizando a carga de dados e restore no MySQL

maio 13th, 2012 Bianchi Posted in MySQL A&D, MySQL Backup, MySQL Manutenção, MySQL Tuning No Comments »

Muitos são os amigos que escrevem perguntando como agilizar a carga de dados ou restore de um backup no MySQL. Realmente, dependendo do tamanho do seu hardware, configuração dos Storage Engines e variáveis per-client e design do seu banco de dados, esse processo poderá levar várias horas caso alguns cuidados não sejam tomados antes do início do processo. Há pouco tempo atrás, trabalhando em uma consultoria aonde o cliente precisava fazer uma carga diária de toda a movimentação nas contas onde todas as informações eram consistidas em arquivos texto, finalizamos a nossa prestação de serviços após termos desenvolvido um aplicativo que, além de fazer a carga dos dados e vários tratamentos em meio aos LOAD DATA INFILE, configuramos vários pontos do MySQL no runtime do aplicativo para que o processo fosse realmente “agilizado”.

São vários os pontos a serem observados:

  • Índices KEY, UNIQUE e FULLTEXT, para tabelas MyISAM;
  • Chaves estrangeiras ou foreign keys, para tabelas InnoDB;
  • o modo AUTOCOMMIT, para tabelas InnoDB.
Para os testes que faremos neste post, utilizaremos uma máquina virtual rodando o CentOS 6.0, com o MySQL 5.6.

[root@master ~]# mysqladmin -u root -p123456 version
mysqladmin Ver 8.42 Distrib 5.6.4-m7, for Linux on i686


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.6.4-m7-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 42 min 17 sec

Para desabilitar Índices KEY e UNIQUE, basta que você crie um select ou mesmo um script para percorrer tabela por tabela do seu modelo físico de bancos de dados para desabilitar os índices de cada uma delas. Gosto de fazer isso via mysql client com a opção -B maiúsculo, que executa a conexão com o mysqld em modo batch. Caso você tenha mais segurança em utilizar os recursos do MySQL em conjunto com o file system, você pode utilizar o SELECT … INTO OUTFILE.

# criamos as tabelas com índices KEY, ou seja, índices que são estruturas utilizadas para melhoria da performance na busca de dados
[root@master ~]# for i in {1..5}; do mysql -u root -p123456 test -e "create table tb$i(id$i int,key(id$i)) engine=myisam;"; done

# exibimos as tabelas criadas
[root@master ~]# mysql -u root -p123456 -e "show tables from test like 'tb%'"
+----------------------+
| Tables_in_test (tb%) |
+----------------------+
| tb1                  |
| tb2                  |
| tb3                  |
| tb4                  |
| tb5                  |
+----------------------+

# exibimos os índices criados nas colunas id das tabelas que acabamos de criar
[root@master ~]# mysql -u root -p123456 -e "select column_name, column_key from information_schema.columns where table_schema='test' and table_name like 'tb%'"
+-------------+------------+
| column_name | column_key |
+-------------+------------+
| id1         | MUL        |
| id2         | MUL        |
| id3         | MUL        |
| id4         | MUL        |
| id5         | MUL        |
+-------------+------------+

Agora que temos índices à desabilitar, podemos rodar um SELECT que nos devolverá os comandos ALTER TABLE necessários para desabilitar os índices das tabelas do banco de dados alvo da carga de dados.

# executando em modo batch
[root@master ~]# mysql -u root -p123456 -B -e "select concat('alter table ',table_name,' disable keys;') from information_schema.tables where table_schema='test'"
concat('alter table ',table_name,' disable_keys;')
alter table t1 disable keys;
alter table t2 disable keys;
alter table t3 disable keys;
alter table tb1 disable keys;
alter table tb2 disable keys;
alter table tb3 disable keys;
alter table tb4 disable keys;
alter table tb5 disable keys;

# executando com SELECT ... INTO OUFILE
[root@master ~]# mysql -u root -p123456 -e "select concat('alter table ',table_name,' disable keys;') into outfile '/tmp/alterDisableKey' from information_schema.tables where table_schema='test'"
[root@master ~]#

Considerando a segunda opção, volte ao mysql e execute o conteúdo do arquivo que foi salvo em /tmp
# executando o arquivo via source

[root@master ~]# mysql -u root -p123456 test -e "source /tmp/alterDisableKey;"
# confirmando que os índices foram desabilitados

mysql> show index from tb1\G
*************************** 1. row ***************************
Table: tb1
Non_unique: 1
Key_name: id1
Seq_in_index: 1
Column_name: id1
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment: disabled # desabilitado!
Index_comment:
1 row in set (0.00 sec)

Após realizar a carga de dados, ALTER TABLE <table_name> ENABLE KEYS!

Para que as foreign keys ou chaves estrangeiras em tabelas InnoDB tenham suas checagens desabilitadas (o processo de checagem de integridade referencial realmente atrasa o restore de dados) é um processo mais tranquilo que o anterior. Basta que você, na sua sessão, reconfigure o valor da variável de ambiente foreign_key_checks, como vemos logo abaixo:

mysql> show variables like 'foreign%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=OFF;
Query OK, 0 rows affected (0.05 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'foreign%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

O ponto final para finalizarmos este post, o AUTOCOMMIT! Primeiro, vamos entender o que esse cara faz e o que ele controla. Como o InnoDB é um Storage Engine transacional, a cada UPDATE, INSERT ou DELETE que é executado, o InnoDB cuida para enviar um COMMIT logo após tais consultas; isso, quando AUTOCOMMIT está configurado como 1 ou ON, que é o valor default. Como queremos fazer várias operações e somente ao final dar um COMMIT explícito, o que é feito pelo mysqldump com um arquivo de backup gerado com a opção -e, precisamos configurar o AUTOCOMMIT com o valor OFF ou 0.

# configurando autocomit no arquivo de configuração do MySQL, salev o mesmo e reinicie o MySQL
[root@master ~]# vim /etc/my.cnf

[mysqld]
autocommit=0

[root@master ~]# service mysql restart
Shutting down MySQL ... [ OK ]
Starting MySQL      ... [ OK ]

Pronto, agora o seu servidor de bancos de dados MySQL já está configurado para passar por processos de restore de forma mais rápida e também ser alvo de cargas de dados pesadas. Um adicional é, procure saber como funciona a variável bulk_insert_buffer_size, ela também ajudará neste quesito.

Até.

 

AddThis Social Bookmark Button

MySQL server has gone away

janeiro 18th, 2012 Bianchi Posted in MySQL Manutenção, MySQL Tuning 1 Comment »

Esta é uma mensagem de erro que acontece em muitos dos servidores de bancos de dados MySQL instalados aí pelo mundo e muitos dos usuários se vêem em uma situação que talvez não tenha solução. Há bastante tempo eu tenho respondido à fóruns de discussão que tratam do tema que é simples de resolver. A minha esperança é que o google indexe logo o título deste post para que tal informação de como se livrar da mensagem MySQL server has gone away em meio à operações de carga de dados, restore de um banco ou mesmo, em meio às interações do aplicação com o servidor MySQL.

Existe uma variável de ambiente no MySQL que controla este comportamento e também, o tamanho máximo dos pacotes que podem trafegar nas threads do MySQL. Você deve saber que cada thread é um conexão e você poderá ter informações sobre elas através do comando SHOW PROCESSLIST. O tamanho inicial é configurado na variável net_buffer_lentgh e o tamanho máximo é configurado em max_allowed_packet – esta variável que poderá ter um valor pequeno para o sistema que já se tornou grande.

Por exemplo, no início deste ano iniciei os trabalhos com um cliente no Brasil e precisamos na quele momento fazer a carga de grande de quantidade de dados em XML, que é um tipo de log que o cliente armazena para devidos fins. Ao iniciar as cargas com os arquivos de mais ou menos 300GB por vez, nos deparamos com o “MySQL server has gone away” ou em Português, “O MySQL foi embora”. Não é para mim uma mensagem e nem um comportamento novo, e foi somente fazer alguns ajustes no my.cnf, mais precisamente, na variável max_allowed_packet e tudo se resolveu:

[root@motoserver189 ~]# mysql -u root -p imoin_package < /files/log1765390.dump
ERROR 2006 (HY000) at line 59: MySQL server has gone away

# Alteramos o valor de max_allowed_packet para comportar pacotes maiores nas threads do MySQL

[mysqld]
max_allowed_packet=1024M

# Reiniciamos o servidor de bancos de dados MySQL para que as alterações passam a valer

[root@motoserver189 ~]# service mysql restart
Starting MySQL....................................... SUCCESS!

# Tentamos novamente e como agora vai dar tudo certo, embrulhamos o comando de restore no nohup que passa a execução do processo para o processo do Linux para se caso nossa conexão com o servidor seja fechada, o processo de restore não sofrerá nenhum impacto.

[root@motoserver189 ~]# nohup mysql -u root -p imoin_package < /files/log1765390.dump &
[1] 26303
[root@bd14 mysql]# nohup: appending output to `nohup.out'

Até…

AddThis Social Bookmark Button

Particionando o InnoDB Buffer Pool

dezembro 21st, 2011 Bianchi Posted in MySQL Manutenção, MySQL Tuning No Comments »

O título deste artigo é bastante sugestivo do ponto de vista de performance em bancos de dados. Geralmente, independente do tipo de particionamento, horizontal ou vertical, ele servirá para eliminar overheads em operações adicionais na escrita e/ou recuperação de dados. Com o InnoDB Buffer Pool, a partir da versão 5.5 não é diferente, pois, poderemos utilizar uma nova variável, aplicada somente ao InnoDB Plugin que nos possibilita dividir o Buffer Pool (área de memória que armazena índices e dados de tabelas InnoDB) em várias instâncias, sendo que cada uma das instâncias deverá ter no mínimo 1GB de espaço. Então, neste cenário, caso tenhamos um innodb_buffer_pool_size igual à 2GB, poderemos ter a variável innodb_buffer_pool_instances=2.

As principais vantagens de ser ter um Buffer Pool particionado é a possibilidade de que cada uma das instâncias poder controlar sua própria lista, que é baseada no algorítimo LRU (Least Recently Used), armazenam bem menos de dados que uma só instância, o que possibilita menos tempo para localizar um determinado dado na memória em meio à menos dados.

Uma boa analogia para a busca do entendimento é, imagine que você deixa o seu carro em um estacionamento de shopping que tem capacidade para 1000 carros. Você pára o seu carro e se você não tiver uma boa noção de espaço, quando voltar para buscá-lo poderá gastar vários minutos para achá-lo. Agora, imagine que este mesmo estacionamento agora conta com setores, algo como A1, A2, B1, B2 e etc. Neste cenário, quando você parar o carro, você saberá em qual setor o seu carro está parado, sendo que em cada setor, a lotação máxima é de somente 50 carros. Você procura seu carro em meio a um número muito menor do que se você tivesse que procurá-lo em meio à todos os carros.

As configurações (exemplo) podem ser como seguem:

[mysqld]
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=8

No exemplo acima, temos 8 instâncias do Buffer Pool, cada uma delas com 2GB de espaço para dados e índices de tabelas InnoDB. Podemos ainda monitorar o que está acontecendo com cada uma das instâncias de InnoDB Buffer Pool através do comando SHOW ENGINE INNODB STATUS, observando a seção “INDIVIDUAL BUFFER POOL INFO”:

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 131071
Free buffers 20999
Database pages 109854
Old database pages 40564
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 106393, created 3461, written 70472
0.00 reads/s, 0.02 creates/s, 0.80 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 109854, unzip_LRU len: 190
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 131071
Free buffers 20192
Database pages 110633
Old database pages 40859
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 107355, created 3278, written 50788
0.00 reads/s, 0.00 creates/s, 0.48 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 110633, unzip_LRU len: 219
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 131071
Free buffers 19981
Database pages 110840
Old database pages 40935
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 107052, created 3788, written 65778
0.00 reads/s, 0.00 creates/s, 0.48 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 110840, unzip_LRU len: 223
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 131071
Free buffers 18616
Database pages 112208
Old database pages 41440
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 17, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108448, created 3760, written 48754
0.00 reads/s, 0.00 creates/s, 0.27 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 112208, unzip_LRU len: 220
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 131071
Free buffers 23980
Database pages 106849
Old database pages 39461
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 103190, created 3659, written 63331
0.00 reads/s, 0.02 creates/s, 0.70 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 106849, unzip_LRU len: 217
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 131071
Free buffers 19814
Database pages 111069
Old database pages 41020
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 106936, created 4133, written 85900
0.00 reads/s, 0.00 creates/s, 0.61 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 111069, unzip_LRU len: 162
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 131071
Free buffers 18889
Database pages 112005
Old database pages 41340
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108175, created 3830, written 83143
0.00 reads/s, 0.00 creates/s, 0.73 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 112005, unzip_LRU len: 149
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 131071
Free buffers 19352
Database pages 111534
Old database pages 41189
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 107999, created 3535, written 57687
0.00 reads/s, 0.00 creates/s, 0.41 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 111534, unzip_LRU len: 158
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Perceba que cada instância tem o seu próprio controle de LRU, páginas jovens e velhas, assim como aquelas que se tornaram jovens por serem mais requisitadas e aquelas que se tornaram velhas por serem pouco requisitadas. Quantidade de páginas e quantidade de buffers livres podem também ser observados. Interessante notar que esta seção somente estará presente na saída do SHOW ENGINE INNODB STATUS caso innodb_buffer_pool_instances for maior que zero.

O mais interessante é, para que o InnoDB Buffer Pool funcione bem, particionado ou não, os dados precisam estar lá e para que você, manualmente efetue um “preload” dos dados no buffer, rode esta consulta e depois rode os comandos que ela gerar: 

SELECT
CONCAT('SELECT ',MIN(c.COLUMN_NAME),' FROM ',c.TABLE_NAME,' WHERE ',MIN(c.COLUMN_NAME),' IS NOT NULL')
FROM
information_schema.COLUMNS AS c
LEFT JOIN (
SELECT DISTINCT
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
FROM
information_schema.KEY_COLUMN_USAGE
) AS k
USING
(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
WHERE
c.TABLE_SCHEMA = 'yourDatabase'
AND k.COLUMN_NAME IS NULL
GROUP BY
c.TABLE_NAME

Enquanto roda as consultas finais para carregar os dados no Buffer Pool, você poderá utilizar uma interface gráfica qualquer para checar a diminuição do espaço configurado para innodb_buffer_pool_size ou mesmo, checar as variáveis de status que o MySQL possui para monitorar o InnoDB:

mysql> show status like 'Innodb_buffer_pool%'\G
*************************** 1. row ***************************
Variable_name: Innodb_buffer_pool_pages_data
Value: 1639
*************************** 2. row ***************************
Variable_name: Innodb_buffer_pool_pages_dirty
Value: 0
*************************** 3. row ***************************
Variable_name: Innodb_buffer_pool_pages_flushed
Value: 2352
*************************** 4. row ***************************
Variable_name: Innodb_buffer_pool_pages_free
Value: 1046928
*************************** 5. row ***************************
Variable_name: Innodb_buffer_pool_pages_misc
Value: 1
*************************** 6. row ***************************
Variable_name: Innodb_buffer_pool_pages_total
Value: 1048568

 Observe o valor de *Innodb_buffer_pool_pages_free* diminuindo. Isso mostrará que o preload dos dados está realmente funcionando.
Até a próxima.

 

AddThis Social Bookmark Button

Problemas de escala de usuários com o MySQL

dezembro 17th, 2011 Bianchi Posted in MySQL Manutenção, MySQL Tuning 2 Comments »

Essa semana tive um problema grave em um cliente que precisava escalar o números de conexões simultâneas no MySQL de forma que estas conexões superassem o número de 2000. Vários problemas foram analisados, desde pontos básicos como configurações do próprio servidor de bancos de dados MySQL como alguns pontos relacionados ao Kernel. Somente para situar o leitor em relação ao que foi analisado, no MySQL, temos duas variáveis muito importantes que determinam a quantidade de usuários que podem se conectar ao servidor de bancos de dados e também o números de tais conexões que podem acontecer em um mesmo momento, ou seja, simultâneas.

  1. max_connections – esse é o parâmetro que controla a quantidade de usuários que podem se conectar ao MySQL;
  2. max_user_connections – esse é o parâmetro utilizado para configurar a quantidade de conexões simultâneas que podem acontecer durante o funcionamento do MySQL – segundo o manual, manter o valor desta variável como zero, é o mesmo que dizer que a coisa é ilimitada;

Interessante sabermos que, além das variáveis de ambiente, que são utilizadas para configurar os vários aspectos do MySQL e é com elas que realizamos o tuning, temos também as variáveis de status que cobrem todo o funcionamento do MySQL e é através delas que consultamos o que está acontecendo com o servidor de bancos de dados. Com as questões relacionadas com usuários não é diferente, veja só:

mysql> show status like '%conn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         |    0  |
| Connections              | 1387  |
| Max_used_connections     |  645  |
| Ssl_client_connects      |    0  |
| Ssl_connect_renegotiates |    0  |
| Ssl_finished_connects    |    0  |
| Threads_connected        |  581  |
+--------------------------+-------+

Sem pensar nas variáveis SSL que foram retornadas na consulta acima, temos três variáveis bastante importantes: Aborted_connects, Connections, Max_used_connections. Cada uma delas tem um significado ligado diretamente às conexões de clientes/usuários com o servidor de bancos de dados.

  1. Aborted_Connects: se o número desta variável de status estiver alto você poderá estar perdendo conexões por quebra das mesmas, sua aplicação não está chamando um método de de “connection_close” antes de fechar a conexão ou mesmo, seu MySQL está evitando consultas;
  2. Connections: é número total de conexões que já acontecerão desde o último restart;
  3. Max_used_connections: é o número de conexões simultâneas que acontecerão desde  o último restart.

Sendo assim, já temos um norte para trabalhar questões de escala de conexões com o MySQL. Já ouvi dizer sobre escalonador de threads e parâmetros de kernel do Linux, mas, a coisa pode ser mais simples que isso. As configirações atuais de um servidor que tenho monitorado são as seguintes:

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 6000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'max_user%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_user_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

Com base nisso, passei a observar que quando as conexões atingiam o número de 1000 acontecendo de forma simultânea, ví que o valor da variável de status Aborted_Connects iniciava a aumentar freneticamente e  quando eu tentava acessar o MySQL via mysql client com qualuqer usuário, o seguinte mensagem de erro era enviada:

Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Ou seja, ou você está rodando o servidor de bancos de dados configurado com um valor de memória além daquele que o servidor tem disponível ou existe um bug no sistema operacional. Por eliminação, o servidor aonde roda este MySQL tem 64GB e está com 16GB sobrando. Então, esse problema tem havir com algo no sistema operacional. Pesquisando no internet, puder ver que outros amigos tiveram um cenário parecido e também criaram um blog sobre o assunto, como fez o amigo Dimitri em http://bit.ly/trVqL4.

Seguindo mais ou menos o que ele relatou nesse seu blog, eu tinha os memos parâmetros de ulimit para o usuário mysql (su – mysql), mas tinha um valor diferente para threads-max, um valor muito inferior ao mostrado por ele no blog, que é 2065067. Então foi assim que procedi:

  1. Configurei a qtd máxima de threads: echo “2065067” > /proc/sys/kernel/threads-max
  2. Configurei o arquivo “limits.conf” para as sessões dos usuários mysql e root:

mysql soft nofile 10240
mysql hard nofile 40960
mysql soft nproc 10240
mysql hard nproc 40960
root soft nofile 10240
root hard nofile 40960
root soft nproc 10240
root hard nproc 40960

A configuração número dois me pareceu muito familiar e foi bem aceita, pois, isso já é realizado quando se instala o Oracle Database. Após feito isso, foram realizados vários um testes de stress com o mysqlslap, biblioteca de benchmark do próprio MySQL e o problema persistia. Vários binários foram testados para verificar questões de escala entre uma versão e outra:

MySQL Oracle 5.5.17

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
Benchmark
Average number of seconds to run all queries: 4.117 seconds
Minimum number of seconds to run all queries: 4.117 seconds
Maximum number of seconds to run all queries: 4.117 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

MySQL Oracle 5.0.92

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
Benchmark
Average number of seconds to run all queries: 3.049 seconds
Minimum number of seconds to run all queries: 3.049 seconds
Maximum number of seconds to run all queries: 3.049 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

Percona Server 5.5.17

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
Benchmark
Average number of seconds to run all queries: 4.137 seconds
Minimum number of seconds to run all queries: 4.137 seconds
Maximum number of seconds to run all queries: 4.137 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

Os testes acima foram realizados em uma mesma máquina com um arquivo de configuração padrão, somente com o valor de max_connections=6000 e max_user_connections=o.

[root@server mysql-coms]# my_print_defaults mysqld
--skip-external-locking
--port=3306
--socket=/var/lib/mysql/mysql.sock
--max_connections=6000
--max_user_connections=0

Uma saída lógica foi checar de mais de perto o erro “Error when connecting to server: 2001 Can’t create UNIX socket (24)” que poderia estar limitando a criação de mais threads, e por consequência mais usuários, no sistema operacional. Foi então que achei o MySQL Dojo aonde já haviam testado esses problemas e se baseavam no ulimit. Resumindo, explorar os valores configurados no ulimit, ou melhor, no arquivo limits.conf e aumentá-los até que os testes fossem satisfatórios. Então foi assim:

[root@server mysql-rpm]# ulimit -a mysql
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 192031
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 90000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 90000
cpu time (seconds, -t) unlimited
max user processes (-u) 90000
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

[root@server mysql-coms]# mysqlslap --user=root --auto-generate-sql --concurrency=1200 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 5.775 seconds
Minimum number of seconds to run all queries: 5.775 seconds
Maximum number of seconds to run all queries: 5.775 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

E para provar que o MySQL está limitado somente pelo hardware ou ainda, neste caso, também pelas configurações do sistema operacional…

[root@server mysql-coms]# mysqlslap --user=root --auto-generate-sql --concurrency=2000 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 18.367 seconds
Minimum number of seconds to run all queries: 18.367 seconds
Maximum number of seconds to run all queries: 18.367 seconds
Number of clients running queries: 2000
Average number of queries per client: 0

[root@server mysql-coms]# mysqlslap --user=root --auto-generate-sql --concurrency=3000 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 41.411 seconds
Minimum number of seconds to run all queries: 41.411 seconds
Maximum number of seconds to run all queries: 41.411 seconds
Number of clients running queries: 3000
Average number of queries per client: 0

E assim, finalizo mais uma aventura com o MySQL e com missão cumprida! Até breve…

 

AddThis Social Bookmark Button

Analisando o InnoDB Buffer Pool

novembro 24th, 2011 Bianchi Posted in MySQL Manutenção No Comments »

A primeira coisa a se fazer ao se trabalhar com o InnoDB é utilizar as variáveis de status para checar se a configuração atual do Buffer Pool, definida em innodb_buffer_pool_size, satisfaz as necessidades dos bancos de dados atualmente armazenados no MySQL. Como já abordei aqui no blog, em outro post, manter os dados (e índices principalmente) em memória é a melhor opção para se obter boa performance de um banco de dados e no caso do MySQL + InnoDB não é diferente…

Selecione as variáveis de status que interessa…

mysql> show status like 'innodb_buffer_pool%';
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_pages_data         | 392124     |
| Innodb_buffer_pool_pages_dirty        | 1          |
| Innodb_buffer_pool_pages_flushed      | 15949040   |
| Innodb_buffer_pool_pages_free         | 0          |
| Innodb_buffer_pool_pages_misc         | 1092       |
| Innodb_buffer_pool_pages_total        | 393215     |
| Innodb_buffer_pool_read_ahead_rnd     | 0          |
| Innodb_buffer_pool_read_ahead         | 8154       |
| Innodb_buffer_pool_read_ahead_evicted | 252        |
| Innodb_buffer_pool_read_requests      | 1444481964 |
| Innodb_buffer_pool_reads              | 7502       |
| Innodb_buffer_pool_wait_free          | 0          |
| Innodb_buffer_pool_write_requests     | 148957406  |
+---------------------------------------+------------+
13 rows in set (0.00 sec)

Como nesta instância não estou utilizando compressão de dados, as páginas de dados do InnoDB continuam com o valor padrão que é 16KB cada. Através da variável de status Innodb_buffer_pool_pages_data temos o número total de páginas atualmente dentro do Buffer Pool. Fazendo uma aritimética simples, Innodb_buffer_pool_pages_data*16KB, temos a quantidade em KB da quantidade de dados que preenche o buffer neste momento.

mysql> select (392124*16) pages;
+---------+
| pages   |
+---------+
| 6273984 |
+---------+
1 row in set (0.02 sec)

Transforme o resultado de bytes em giga:

mysql> select 6273984/1024/1024;
+-------------------+
| 6273984/1024/1024 |
+-------------------+
| 5.98333740        |
+-------------------+
1 row in set (0.00 sec)

E então compare a efetividade entre a quantidade de dados que estão dentro do buffer e o valor configurado para aquela área de memória:

mysql> select format(6273984/1024/1024,2) 'dadosNoBuffer',
-> format(@@innodb_buffer_pool_size/1024/1024/1024,0) 'valorConfigurado';
+---------------+------------------+
| dadosNoBuffer | valorConfigurado |
+---------------+------------------+
| 5.98          | 6                |
+---------------+------------------+
1 row in set (0.00 sec)

Vimos que o Buffer Pool está todo tomado por dados e, caso Innodb_buffer_pool_reads for maior que zero e Innodb_buffer_pool_pages_free for igual a zero, considere aumentar uma pouco o tamanho do Buffer Pool, uma vez que:

mysql> show status like 'innodb_buffer_pool%';
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_pages_data         | 392123     |
| Innodb_buffer_pool_pages_dirty        | 1          |
| Innodb_buffer_pool_pages_flushed      | 15949040   |
| Innodb_buffer_pool_pages_free         | 0          |
| Innodb_buffer_pool_pages_misc         | 1092       |
| Innodb_buffer_pool_pages_total        | 393215     |
| Innodb_buffer_pool_read_ahead_rnd     | 0          |
| Innodb_buffer_pool_read_ahead         | 8154       |
| Innodb_buffer_pool_read_ahead_evicted | 252        |
| Innodb_buffer_pool_read_requests      | 1444481964 |
| Innodb_buffer_pool_reads              | 7502       |
| Innodb_buffer_pool_wait_free          | 0          |
| Innodb_buffer_pool_write_requests     | 148957406  |
+---------------------------------------+------------+
13 rows in set (0.00 sec)

Innodb_buffer_pool_reads -> leitura de dados do disco que não foram satisfeitas ao tentar ler dados do Buffer Pool, ou seja, os dados não estão lá por não haver mais espaço para armazená-los;

Innodb_buffer_pool_pages_free -> quantidade de páginas ainda disponíveis para armazenar dados no Buffer Pool;

Até…

AddThis Social Bookmark Button

InnoDB Buffer Pool

novembro 22nd, 2011 Bianchi Posted in MySQL Manutenção No Comments »

Os exemplos deste artigo contam com uma instalação completamente nova do MySQL, na versão 5.5.18, rodando em CentOS 6.0, conforme exibido abaixo:

[root@mgm01 ~]# rpm -ivh MySQL-server-5.5.18-1.rhel5.i386.rpm
Preparing...             ################################# [100%]
1:MySQL-server           ################################# [100%]
*** PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ***
[root@mgm01 ~]# rpm -ivh MySQL-client-5.5.18-1.rhel5.i386.rpm
Preparing...             ################################# [100%]
   1:MySQL-client        ################################# [100%]
[root@mgm01 ~]# rpm -ivh MySQL-shared-5.5.18-1.rhel5.i386.rpm
Preparing...             ################################# [100%]
   1:MySQL-shared        ################################# [100%]
[root@mgm01 ~]# cp /usr/share/mysql/my-large.cnf /etc/my.cnf
[root@mgm01 ~]# service mysql start
Starting MySQL.....                                      [  OK  ]
 

Muito se tem comentado e discutido sobre a utilização do InnoDB desde que a Oracle colocou no mercado a versão 5.5 do MySQL com o InnoDB Plugin, agora sendo este o Storage Engine padrão do MySQL. Antes disso, talvez fosse mais cômodo somente criar um banco de dados e um bando de tabelas e iniciar os projetos, mas agora, será necessário entender bem como funcionam algumas estruturas do InnoDB, já que as tabelas que você criava antes, agora serão controladas por um motor mais robusto, com integridade referencial, logs para suporte à transação, níveis de isolamento e muitos outros recursos que coloca o MySQL como uma opção robusta para ambientes de missão crítica. Neste post, vou tratar de explicar somente o funcionamento do Buffer Pool, área de memória criada e controlada pelo InnoDB, aonde são armazenados dados e índices de tabelas. Quanto mais dados destes tipos armazenados em memória, mais in-memory será o banco de dados e mais rápido será o trato com informações, seja para recuperação quanto para inserção/atualização de informação.

Em poucas palavras, o InnoDB Buffer Pool é uma estrutura que pode ser configurada através da variável innodb_buffer_pool_size e a quantidade de memória atribuída pode chegar a um número entre 70 e 80% da memória de um host. Na configuração de tal variável de ambiente, um cuidado deverá ser tomado para que esta área não fique grande demais e então seja mal aproveitada pelos dados que podem fragmentar internamente.

Alguns recursos valiosos para evitar tal desproporção ao configurar o InnoDB Buffer Pool são as variáveis de status e também a saída do comando SHOW ENGINE INNODB STATUS. Tanto um quanto o outro poderá orientar o administrador de bancos de dados a ajustar melhor o Buffer Pool. Abaixo, mostro uma parte muito importante da saída do comando SHOW ENGINE INNODB STATUS, que reporta toda a alocação de memória atual pelo InnoDB.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 136806400; in additional pool allocated 0
Dictionary memory allocated 22706
Buffer pool size   8191
Free buffers       7884
Database pages     306
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 306, written 316
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 306, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Percebam que temos um dicionário de dados de 22706 bytes, 306 páginas de dados dentro do buffer que somam o tamanho total de 8191 bytes de dados e índices, não temos páginas modificadas e nem páginas antigas para serem despejadas (processo de “evicted”, veremos mais à frente). Além disso, não existem escritas pendentes, a LRU atual é também 0 e os contadores de read-ahead, o que também veremos à frente, estão zerados. Vou modificar um pouco estes dados, promovendo algum workload no InnoDB para motivar a sua percepção e vou pedir para que você interprete os resultados abaixo:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 136806400; in additional pool allocated 0
Dictionary memory allocated 25579
Buffer pool size   8191
Free buffers       7735
Database pages     455
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 455, written 650
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 455, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Ainda falando a estrutura de armazenamento dos dados e índices de tabelas InnoDB em memória, internamente o Buffer Pool gerencia uma lista baseada no algorítimo LRU ou Least Recently Used (recentemente menos utilizado). Isso faz com os dados mais novos (chamados de “new” ou “young” sublist) sejam colocados na cabeça da lista e os dados mais antigos, e por consequência mais antigos, sejam posicionados na cauda (old sublist) – assim, os dados que não estiverem de acordo com essa lógica serão despejados da memória, cedendo espaço no Buffer Pool para novas entradas.

O registro da quantidade de páginas que foram despejadas sem serem utilizadas estão acessíveis através da variável de status Innodb_buffer_pool_read_ahead_evicted.

[root@mgm01 ~]# mysql -u root -p -e "show status like 'Innodb_buffer_pool_read_ahead%'\G"
Enter password:
*************************** 3. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_evicted
Value: 167

Segundo o manual online, 3/8 do Buffer Pool é destinado aos dados que pertencem à sublista de dados mais antigos, quando um novo dado chega ao buffer pool, el é inserido em um ponto denominado “midpoint” que é localizado na cabeça da sublista da cauda – isso é interessante pois uma operalçao qualquer iniciada pelo usuário poderá ler tal dado de maneira sequencial chamada read-ahead, que automaticamente realizada pelo InnoDB – o read-ahead é um tipo de leitura que poderá ser randômica, caso grande parte dos dados do tabelspace estiverem em memória ou sequencial quando o mecanismo descobre que os dados dentro de um mesmo segmento podem ser lidos todos de uma vez para a memória. Tal recurso de read-ahead poderá ser configurado através da variável global innodb_read_ahead_threshold.As páginas de dados que são modificados em memória são registrados no log buffer pool, que de tempos em tempos realiza um processo denominado “flush” que atualiza os dados do disco com os dados da memória, ou seja, tudo que foi modificado dentro do buffer pool, agora será gravado em disco. Este comportamento é gerenciado pelo InnoDB com base no valor configurado na variável de ambiente innodb_flush_log_at_trx_commit que tem como seus posíveis valores, os seguintes:

  • 0, os logs em memória são escritos em nos arquivos em disco uma vez a cada segundo, mas nada é feito no momento do COMMIT (este que é registrado no transaction log ao final de cada transação realizada com sucesso);
  • 1, os logs em memória são escritos nos arquivos em disco a cada COMMIT;
  • 2, os logs são escritos para os arquivos de log em disco a cada segundo e a cada COMMIT.

Em um ambiente de replicação, recomenda-se que que a variável innodb_flush_log_at_trx_commit seja configurada com o valor 1 e também sync_binlog seja igual a 1. Isso fará com que as alterações estejam armazenadas no log binário o mais breve possível para que esta seja entregue ao servidor SLAVE. Um outro fato que se deve tomar bastante cuidado é que, caso se configure tal variável igual o 0, dados poderão ser perdidos caso o sistema tenha um “crash” antes do próximo “flush”. Problemas poderão ser notados com o SHOW PROCESSLIST em transações que demoram para ser comitadas  dependendo da maneira como seu sistema foi implementado, a configuração desta variável igual a 2 e o MySQL Query Cache ativado – caso tenha um problema similar, além de me deixar saber (@wagnerbianchijr), desative o MySQL Query Cache e reinicie o MySQL. Ative o MySQL Profiling para verificar o que realmente esteja acontecendo:

mysql> SET profiling =1;
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                   |
+----------+------------+-------------------------------------------------------------------------+
|        1 | 0.00096100 | select name,id,competitions from olympic_games where host_city='Sydney' |
|        2 | 0.00029700 | SET GLOBAL query_cache_size=1024*1024*16                                |
|        3 | 0.00837900 | select name,id,competitions from olympic_games where host_city='Sydney' |
|        4 | 0.00009500 | select name,id,competitions from olympic_games where host_city='Sydney' |
+----------+------------+-------------------------------------------------------------------------+

O flush é um processo que conta também com um método, que é controlado pela variável innodb_flush_method, que poderá ser configurada com o valor O_DSYNC ou O_DIRECT, este último que é o mais indicado para ambientes com muita escrita pois evita uma escrita dupla dos dados no cache do InnoDB e no do Sistema Operacional. O_DSYNC é bom para realização de processos de restore, mas o swap poderá aumentar muito utilizando este método. Para medir o aumento do swap, você pode utilizar tanto o primo rico do top, o HTop ou o vmstat.

O Buffer Pool poderá ser configurado com o valor de mais de 4GB no caso de máquinas servidoras que tenham arquitetura 64 bits, sendo assim, uma novidade bem interessante que foi entregue com o MySQL 5.5 foi a possibilidade de particionar o Buffer Pool. A partir daquela versão você poderá criar, por exemplo, a seguinte configuração:

[mysqld]
innodb_buffer_pool_size = 64G # configurado em uma máquina com RAM total = 80GB
innodb_buffer_pool_instances = 10

Sendo assim, uma instância de MySQL com a configuração acima contará com 10 instâncias de Buffer Pool em memória, possibilitando armazenar o mesmo conjunto de dados, mas este conjunto dividido em pequenos subconjuntos que agiliza as operações com dados, sendo que cada uma das instâncias terá um tamanho de 6554MB ou 6.4GB.

Conclusão

Foi um artigo bem rápido, mas, é interessante falarmos sobre o mecanismo de buffer de dados do InnoDB, este que favorece as operações com dados já que mantém tudo ou quase tudo, na maioria dos casos, em memória. Uma vez que os seus dados forem armazenados na memória, a coisa já funcionará melhor.

AddThis Social Bookmark Button

Monitorando discos do servidor

novembro 16th, 2011 Bianchi Posted in MySQL Manutenção 1 Comment »

Pode ser que esse post não tenha haver com o fato de este ser um blog focado em MySQL, mas, pelo contrário, tudo que está em torno de tal software de bancos de dados será abordado aqui com foco na utilidade e melhor funcionamento. Levando em conta que alguns serviços em nuvem ainda oferecem serviços muito aquém daquilo que se espera, precisamos ter pró-atividade suficiente para monitorar hardwarede um servidor e principalmente, monitorar os discos, aonde reside a parte física dos bancos de dados – focando no MySQL – arquivos de dados ou tablespaces, logs de transações, logs binários, relay logs, general logs e logs de erro.

Muito interessante que você DBA entenda que todos estes arquivos são também parte necessária para o seu trabalho diário uma vez que:

  • Arquivo de Dados ou Tablespaces: como o MySQL tem sido mais utilizado ultimamente com o Storage Engine InnoDB, motor de armazenamento que possibilita a utilização de um ou mais arquivos de tablespace compartilhados com prefixo ibdataX (onde X é o número de sequência do nome do tablepace compartilhado) ou ainda com a configuração innodb_file_per_table que criará um tablepsace individual para cada tabela de um banco de dados. Uma falha dos discos poderá corromper os tablepsaces, sejam eles de quaisquer dos tipos citados acima, gerando um erro pareceido com este MySQL Bug # 18410.
  • Logs de Transações: estes arquivos (que por padrão são criados 2 após uma instalação padrão, id_logfile0 e ib_logfile1) são responsáveis por armazenar transações do InnoDB que receberam ou não um COMMIT. Utilizado principalmente para realizar, em conjunto com as áreas internas ao tablespace compartilhado (undo, redo e metadados), o processo de crash-recovery, eliminando do log todas as transações que não contam atualmente com um COMMIT e criando um checkpoint. Outros processo são realizados em conjunto (flush logs, escrita de dados…); tema para outro post.
  • Logs binários: estes arquivos podem ser utilizados como uma fonte muito ágil de extração de backup incremental, já que armazena tudo (STATEMENT ou ROW) que atualiza o estado dos bancos de dados. Além de ser uma ótima fonte para tal estratégia de backup, é obrigatoriamente utilizado para implementação de topologias de replicação entre dois ou mais servidores de bancos de dados MySQL.

Não vou citar a utilidade de todos os arquivos que foram mencionados na introdução deste post para não torná-lo cansativo, pois, o intuito aqui é você ter em mente que, além dos dados, que são a parte mais importante que qualquer outra, você ainda precisa monitorar os seus discos para que não existam surpresas, por exemplo, ao extrair backups com o mysqldump e este backup não contar com todos os dados pois o tablespace de uma tabela específica (geralmente a mais importante de todo o modelo físico) está com uma parte de seus dados corrompida. Isso pode acontecer. Uma outra surpresa é receber uma mensagem de erro de Assertion Thread Failure do inode do InnoDB ao tentar efetuar o CHECKSUM dos dados e não ter conseguido -esse é um dos problemas que uma intermitência nos discos poderá gerar.

98% das falhas atualmente detectadas com tablepace do InnoDB estão relacionadas com hardware mal provisionado, banco de dados mal configurado em relação ao desempenho e a pressão sobre memória secundária, o que implica em muito mais trabalho de disco e CPU e menos da memória (quando deveria ser ao contrário). A utilização intensa de disco e CPU gera overhead e, consequentemente, lentidão.

Mas como fazer para monitorar possíveis problemas com os discos do meu servidor?

Tenho utilizado muito o S.M.A.R.T. com o smartctl e o smartd com que são respectivamente o utilitário e o daemon para verificação contínua da saúde dos discos de um servidor, seja ele crítico ou não. Indenpendente da criticidade, é muito importante que se tenha um pleno monitoramento, pois, o hardware também poderá nos deixar na mão e parar o acesso à informação.

O smartd é o daemon, sendo assim, ele precisa estar rodando para que possamos interagir com o mesmo e gerar os resultados que buscamos ao monitorar os discos de um servidor. O comando cliente é o smartctl que poderá ser utilizado das seguintes formas:

[root@redhat01 ~]# smartctl -i /dev/sda3
smartctl 5.39.1 2010-01-28 r3054 [x86_64-redhat-linux-gnu] (local build)
Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net

=== START OF INFORMATION SECTION ===
Device Model: WDC WD1003FBYX-01Y7B0
Serial Number: WD-WCAW32441497
Firmware Version: 01.01V01
User Capacity: 1,000,204,886,016 bytes
Device is: Not in smartctl database [for details use: -P showall]
ATA Version is: 8
ATA Standard is: Exact ATA specification draft version not indicated
Local Time is: Wed Nov 16 14:40:28 2011 CST
SMART support is: Available - device has SMART capability.
SMART support is: Enabled

O comando mostrado acima nos permite verificar o modelo e o firmware do disco /dev/hda. O SMART mantém um database com vários modelos de disco e possivelmente o seu esteja neste database será reconhecido.

Você pode verificar o conteúdo deste database com o seguinte comando (resultados suprimidos):
[root@redhat01 ~]# smartctl -P showall
...
MODEL REGEXP: QUANTUM FIREBALL EX(3.2|6.4)A
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball EX series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALL ST(3.2|4.3|4300)A
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball ST series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALL SE4.3A
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball SE series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALLP LM(10.2|15|20.[45]|30)
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball Plus LM series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALLP AS(10.2|20.5|30.0|40.0)
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball Plus AS series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALLP KX27.3
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball Plus KX series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

O segundo comando é o mais interessante, pois, possibilita criar um relatório mais completo que lista pontos importantes relacionados com a saúde do disco analisado. É conhecido como “Executive Summary of Disk Health” – o relatório abaixo mostra que o disco foi aprovado e não existem falhas, mas, caso o relatório te mostre o contrário, faça o backup de seus dados imediatamente.

[root@redhat01 ~]# smartctl -Hc /dev/sda3
smartctl 5.39.1 2010-01-28 r3054 [x86_64-redhat-linux-gnu] (local build)
Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net

=== START OF READ SMART DATA SECTION ===
SMART overall-health self-assessment test result: PASSED

General SMART Values:
Offline data collection status: (0x84) Offline data collection activity
was suspended by an interrupting command from host.
Auto Offline Data Collection: Enabled.
Self-test execution status: ( 0) The previous self-test routine completed
without error or no self-test has ever
been run.
Total time to complete Offline
data collection: (16500) seconds.
Offline data collection
capabilities: (0x7b) SMART execute Offline immediate.
Auto Offline data collection on/off support.
Suspend Offline collection upon new
command.
Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities: (0x0003) Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability: (0x01) Error logging supported.
General Purpose Logging supported.
Short self-test routine
recommended polling time: ( 2) minutes.
Extended self-test routine
recommended polling time: ( 170) minutes.
Conveyance self-test routine
recommended polling time: ( 5) minutes.
SCT capabilities: (0x303f) SCT Status supported.
SCT Feature Control supported.
SCT Data Table supported.

Percebam que o primeiro atributo é mesmo aquele que indica que o disco PASSOU no teste do SMART.

Referências:

AddThis Social Bookmark Button

Manutenção do MySQL – mysqlcheck

novembro 13th, 2011 Bianchi Posted in MySQL Manutenção No Comments »

Uma das tarefas mais interessantes e que envolve mais pontos a serem analisados são as rotinas de manutenção dos bancos de dados e com o MySQL não é diferente. Tais pontos vão da análise do melhor momento para se realizar tal manutenção, intervalo de tempo denominado “janela de manutenção” ou do inglês, “maintenance time frame” , o tempo que tal rotina levará para completar o trabalho e quais são os objetivos de tal ação, esta que pode resultar em vários ganhos para o ambiente, seja de performance, seja de backup ou mesmo de prevenção contra desastres.

Comummente, não é muito racional simplesmente colocar um script qualquer no cron do Linux ou agendador de tarefas do MS Windows e deixar este ser executado de acordo com o agendamento. Interessante que você, administrador de bancos de dados, saiba exatamente o que realizar em detrimento de cada objetivo que você tem em relação à manutenção dos seus bancos de dados – é uma ilusão achar que os dados não fragmentarão com o tempo, que dados não serão corrompidos nunca e que, principalmente, você não precisará de um backup, aquele que falhou esta manhã. Muito cuidado com este último tópico e lembre-se, Murphy e sua lei estão sempre aonde existem “pontos simples de falha (SPOF)”.

O MySQL nos dá várias possibilidades de se trabalhar a manutenção de tabelas de qualquer Storage Engine através do *programa-cliente mysqlcheck, que é adicionado ao PATH do sistema operacional após a instalação do MySQL Server. Tanto em sistemas Unix Like quanto em sistemas MS Windows, ao utilizar o terminal ou o prompt de comando, respectivamente, basta teclar as primeiras letras e utilizar TAB para completar o nome do aplicativo, que, logo você perceberá que vários outros aplicativos, além do mysqlcheck, também estão disponíveis. Após ser chamado na linha de comando, como se trata de um programa-cliente, você precisará apontar qual usuário/senha para se conectar ao servidor de bancos de dados MySQL (mysqld), quais bancos de dados e quais as opções, estas que são várias as disponíveis.

Sintaxe de utilização do mysqlcheck:

shell> mysqlcheck [options] db_name [tables]
shell> mysqlcheck [options] --databases DB1 [DB2 DB3...]
shell> mysqlcheck [options] --all-databases

Checagem de Erros – opção “-c”

O mysqlcheck poderá ser utilizado com a opção “-c” para checar os possíveis erros em tabelas de qualquer Storage Engine. Você pode programar algo em shell script para pegar os erros e lhe enviar por e-mail ou acompanhar a execução do programa via linha de comando. Após a checagem, caso algum erro seja reportado, é interessante verificar qual é a tabela e em qual banco de dados está para que as ações de correção possam ser realizadas, o que varia de acordo com o Storage Engine.

Checando erros em todos os bancos de dados:

shell> mysqlcheck [options] --all-databases -c

Atualizando Estatísticas – opção “-a”

Atualizar estatísticas é uma operação essencial em bancos de dados! Normalmente, é interessante que os objetos, índices e linhas de tabelas sejam estatisticamente computadas para que a primeira camada do MySQL, aonde residem os módulos de otimização de consultas (transformação e criação de plano de execução), para que a melhor rota seja traçada com base em tais estatísticas – resumidamente, eu sei quantas linhas, em índices ou não, eu tenho que percorrer de acordo com uma estratégia definida para atender a uma consulta. No caso de consultas que envolvem JOIN, a ordem das tabelas é definida através das estatísticas.

Se eu não sei a quantidade de objetos, índices e linhas que tenho em disco nos arquivos em disco, como farei essa decisão? A atualização das estatísticas garante que haja consistência entre a visão lógica (engine) e física (arquivos em disco) dos bancos de dados.

O mysqlcheck -a ou –analyze é equivalente ou ANALYZE TABLEsão comando equivalentes e ao serem executados, adquirem bloqueio de leitura (bloqueio compartilhado) e funcionando para tabelas MyISAM e InnoDB.

Analisando as tabelas em todos os bancos de dados:

shell> mysqlcheck [options] --all-databases -a

Otimizando tabelas – opção “-o”

A opção “-o” otimiza os dados em disco promovendo melhor alocação dos dados dentro de suas respectivas páginas de dados em disco, assim como promove, consequentemente, a desfragmentção de toda a informação armazenada. Além disso, atualiza as estatísticas de índices fazendo a ordenação dos mesmos (InnoDB Clustered Indexes) e o rearranjo da árvore B-TREE (MyISAM e InnoDB). Durante a operação, um bloqueio exclusivo será adquirido na tabela na qual o processo está rodando (bloqueio exclusivo = WRITE LOCK) e o poder de resposta do servidor de bancos de dados poderá diminuir muito nesse momento. Após o processo, caso seja uma base que tem alterações intensas em seus registros, poderemos perceber diminuição dos espaço consumido em disco pelos bancos de dados.

Otimizando as tabelas em todos os bancos de dados:

shell> mysqlcheck [options] --all-databases -o

A partir da versão 5.1 do MySQL, o InnoDB Plugin passou a não se entender muito bem com o OPTIMIZE TABLE ou a opção “-o” do mysqlcheck. Ao rodar um daqueles comandos contra uma tabela que é controlada pelo Storage Engine InnoDB Plugin, o seguinte erro será enviado ao usuário:

Table does not support optimize, doing recreate + analyze ...

Este tipo de problema poderá ser contornado através do recriação da tabela com o que chamamos de REBUILD, seguido por um ANALYZE, isso para todas as tabelas do banco de dados:

mysql> ALTER TABLE nome_tabela ENGINE=InnoDB;
mysql> ANALYZE TABLE nome_tabela;

Lembro que você poderá montar os comandos para todas as tabelas do banco através de scripts ou mesmo, utilizando SQL em conjunto com a tabela TABLES do dicionário de dados do MySQL, o INFORMATION_SCHEMA.

Tais operações de manutenção podem agora fazer parte do seu plano de otimização para o MySQL e você poderá desenvolver um script para isso utilizando shell ou DOS. Problemas com crash de tabelas e performance poderão ser detectados e resolvidos com o aplicativo mysqlcheck. Basta agora, conceber um plano com padrões para todas as ações que serão realizadas, estudar o padrão de nomenclatura, contar com um profissional especializado para assinar o projeto e colocar em execução. Assim, sua tecnologia tem bem menos chances de ter problemas quando mais se precisar dela.

AddThis Social Bookmark Button