Sun and Oracle today announced a definitive agreement for Oracle to acquire Sun for $9.50 per share in cash. The Sun Board of Directors has unanimously approved the transaction. It is anticipated to close this summer.
What will happened in MySQL, JAVA.
I think its enough.
http://www.sun.com/aboutsun/pr/2009-04/sunflash.20090420.1.xml
Database Administrator specialized MySQL. Topics: mysql tutorial, database design, mysql data types, mysql commands, mysql dump, database development, mysql training, mysql scalability, mysql sharding, mysql performance tuning, mysql tools.
Monday, April 20, 2009
Wednesday, April 15, 2009
Warning Aborted connection, log_warnings and wait_timeout
In my server error log i see
090415 10:55:57 [Warning] Aborted connection 481 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 10:56:16 [Warning] Aborted connection 582 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 11:05:13 [Warning] Aborted connection 2693 to db: 'db' user: 'root' host: 'localhost' (Got timeout reading communication packets)
every thread connected to mysql and sleep more than wait_timeout mysql will close it.
the previous warning depend on log_warnings level, by default log_warnings = 1, if its =2 all the closed connection will written in mysql error log.
I think we don't need this warning.;)
090415 10:55:57 [Warning] Aborted connection 481 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 10:56:16 [Warning] Aborted connection 582 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 11:05:13 [Warning] Aborted connection 2693 to db: 'db' user: 'root' host: 'localhost' (Got timeout reading communication packets)
every thread connected to mysql and sleep more than wait_timeout mysql will close it.
the previous warning depend on log_warnings level, by default log_warnings = 1, if its =2 all the closed connection will written in mysql error log.
I think we don't need this warning.;)
--log-warnings[=level], -W [level]
Option Sets Variable Yes, log-warnings
Variable Name log_warnings
Variable Scope Both
Dynamic Variable Yes
Disabled by skip-log-warnings
Value Set Type numeric
Default 1
Print out warnings such as Aborted connection... to the error log. Enabling this option is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). This option is enabled (1) by default, and the default level value if omitted is 1. To disable this option, use --log-warnings=0. If the value is greater than 1, aborted connections are written to the error log. See Section B.1.2.11, “Communication Errors and Aborted Connections”.
If a slave server was started with --log-warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.
wait_timeout
The number of seconds the server waits for activity on a non-interactive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made via named pipes, or shared memory.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.
Sunday, April 12, 2009
mysqlsla amazing tool
mysqlsla is interesting tool to analyze slow log query, aggregate same query in one and generate unique sql statement withCount, (max, min, avg) execute time, lock time, Rows sent, Rows examined for each unique one.
you can use it to review indexes and drop unused index, and create another.
Report for slow logs: slowquery1day.txt
791 queries total, 85 unique
Sorted by 't_sum'
Grand Totals: Time 23.05k s, Lock 2.18k s, Rows sent 24.17M, Rows Examined 120.61M
____________________________________________________________ 001 ___
Count : 355 (44.88%)
Time : 7588 s total, 21.374648 s avg, 11 s to 203 s max (32.92%)
95% of Time : 6352 s total, 18.848665 s avg, 11 s to 44 s max
Lock Time (s) : 675 s total, 1.901408 s avg, 0 to 191 s max (30.95%)
95% of Lock : 57 s total, 169.139 ms avg, 0 to 6 s max
Rows sent : 141 avg, 1 to 150 max (0.21%)
Rows examined : 37.17k avg, 24 to 310.07k max (10.94%)
Database : db_test
Users :
FrashaSlvReplic@ 70.84.164.205 : 92.11% (327) of query, 87.74% (694) of all users
Frashat_test@ 70.84.164.205 : 7.89% (28) of query, 5.44% (43) of all users
Query abstract:
SELECT post.postid, post.pagetext, ifnull( user.username , post.username ) AS username, dateline FROM noway_post AS post LEFT JOIN noway_user AS user ON (user.userid = post.userid) WHERE threadid = N AND visible = N AND post.userid NOT IN (N1) ORDER BY dateline ASC LIMIT N,N;
Query sample:
SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
FROM noway_post AS post
LEFT JOIN noway_user AS user ON (user.userid = post.userid)
WHERE threadid = 314734
AND visible = 1
AND post.userid NOT IN (28816)
ORDER BY dateline ASC
LIMIT 300,150;
_____________________________________________________________ 002 ___
Count : 90 (11.38%)
Time : 1840 s total, 20.444444 s avg, 11 s to 128 s max (7.98%)
95% of Time : 1445 s total, 17 s avg, 11 s to 35 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 25 avg, 0 to 25 max (0.01%)
Rows examined : 13.89k avg, 146 to 26.54k max (1.04%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (90) of query, 87.74% (694) of all users
Query abstract:
SELECT COUNT(*) AS COUNT, threadid, MAX(dateline) AS lastpost FROM noway_post AS post WHERE post.userid = N AND post.visible = N AND post.threadid IN (N25) GROUP BY threadid;
Query sample:
SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM noway_post AS post
WHERE post.userid = 268569 AND
post.visible = 1 AND
post.threadid IN (0820005, 636923, 803089, 735659, 735645, 808314, 765143, 688062, 738500, 787405, 633043, 454061, 703866, 707968, 676619, 647881, 645328, 451514, 674289, 650001, 654712, 562233, 645180, 638518, 619261)
GROUP BY threadid;
_____________________________________________________________ 003 ___
Count : 15 (1.90%)
Time : 1574 s total, 104.933333 s avg, 21 s to 282 s max (6.83%)
95% of Time : 1292 s total, 92.285714 s avg, 21 s to 261 s max
Lock Time (s) : 47 s total, 3.133333 s avg, 0 to 42 s max (2.15%)
95% of Lock : 5 s total, 357.143 ms avg, 0 to 5 s max
Rows sent : 84 avg, 10 to 100 max (0.01%)
Rows examined : 69.30k avg, 1.03k to 219.67k max (0.86%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (15) of query, 87.74% (694) of all users
Query abstract:
SELECT DISTINCT thread.threadid, thread.forumid, post.userid FROM noway_thread AS thread INNER JOIN noway_post AS post ON(thread.threadid = post.threadid ) WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N20) AND thread.forumid IN(N1) AND post.visible = N LIMIT N;
Query sample:
SELECT
DISTINCT thread.threadid, thread.forumid, post.userid
FROM noway_thread AS thread
INNER JOIN noway_post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('هذه الدنيا عجايب') AND thread.forumid NOT IN (0,38,41,117,55,94,99,100,72,39,42,43,57,44,37,26,40,27,34,53) AND thread.forumid IN(86) AND post.visible = 1
LIMIT 100;
http://hackmysql.com/mysqlsla
you can use it to review indexes and drop unused index, and create another.
Report for slow logs: slowquery1day.txt
791 queries total, 85 unique
Sorted by 't_sum'
Grand Totals: Time 23.05k s, Lock 2.18k s, Rows sent 24.17M, Rows Examined 120.61M
____________________________________________________________ 001 ___
Count : 355 (44.88%)
Time : 7588 s total, 21.374648 s avg, 11 s to 203 s max (32.92%)
95% of Time : 6352 s total, 18.848665 s avg, 11 s to 44 s max
Lock Time (s) : 675 s total, 1.901408 s avg, 0 to 191 s max (30.95%)
95% of Lock : 57 s total, 169.139 ms avg, 0 to 6 s max
Rows sent : 141 avg, 1 to 150 max (0.21%)
Rows examined : 37.17k avg, 24 to 310.07k max (10.94%)
Database : db_test
Users :
FrashaSlvReplic@ 70.84.164.205 : 92.11% (327) of query, 87.74% (694) of all users
Frashat_test@ 70.84.164.205 : 7.89% (28) of query, 5.44% (43) of all users
Query abstract:
SELECT post.postid, post.pagetext, ifnull( user.username , post.username ) AS username, dateline FROM noway_post AS post LEFT JOIN noway_user AS user ON (user.userid = post.userid) WHERE threadid = N AND visible = N AND post.userid NOT IN (N1) ORDER BY dateline ASC LIMIT N,N;
Query sample:
SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
FROM noway_post AS post
LEFT JOIN noway_user AS user ON (user.userid = post.userid)
WHERE threadid = 314734
AND visible = 1
AND post.userid NOT IN (28816)
ORDER BY dateline ASC
LIMIT 300,150;
_____________________________________________________________ 002 ___
Count : 90 (11.38%)
Time : 1840 s total, 20.444444 s avg, 11 s to 128 s max (7.98%)
95% of Time : 1445 s total, 17 s avg, 11 s to 35 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 25 avg, 0 to 25 max (0.01%)
Rows examined : 13.89k avg, 146 to 26.54k max (1.04%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (90) of query, 87.74% (694) of all users
Query abstract:
SELECT COUNT(*) AS COUNT, threadid, MAX(dateline) AS lastpost FROM noway_post AS post WHERE post.userid = N AND post.visible = N AND post.threadid IN (N25) GROUP BY threadid;
Query sample:
SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM noway_post AS post
WHERE post.userid = 268569 AND
post.visible = 1 AND
post.threadid IN (0820005, 636923, 803089, 735659, 735645, 808314, 765143, 688062, 738500, 787405, 633043, 454061, 703866, 707968, 676619, 647881, 645328, 451514, 674289, 650001, 654712, 562233, 645180, 638518, 619261)
GROUP BY threadid;
_____________________________________________________________ 003 ___
Count : 15 (1.90%)
Time : 1574 s total, 104.933333 s avg, 21 s to 282 s max (6.83%)
95% of Time : 1292 s total, 92.285714 s avg, 21 s to 261 s max
Lock Time (s) : 47 s total, 3.133333 s avg, 0 to 42 s max (2.15%)
95% of Lock : 5 s total, 357.143 ms avg, 0 to 5 s max
Rows sent : 84 avg, 10 to 100 max (0.01%)
Rows examined : 69.30k avg, 1.03k to 219.67k max (0.86%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (15) of query, 87.74% (694) of all users
Query abstract:
SELECT DISTINCT thread.threadid, thread.forumid, post.userid FROM noway_thread AS thread INNER JOIN noway_post AS post ON(thread.threadid = post.threadid ) WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N20) AND thread.forumid IN(N1) AND post.visible = N LIMIT N;
Query sample:
SELECT
DISTINCT thread.threadid, thread.forumid, post.userid
FROM noway_thread AS thread
INNER JOIN noway_post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('هذه الدنيا عجايب') AND thread.forumid NOT IN (0,38,41,117,55,94,99,100,72,39,42,43,57,44,37,26,40,27,34,53) AND thread.forumid IN(86) AND post.visible = 1
LIMIT 100;
http://hackmysql.com/mysqlsla
Wednesday, April 08, 2009
A Brief Introduction to MySQL Performance Tuning
Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.
Server Variables
For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.
For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.
Other variables that are worth investigating for performance tuning are:
query_cache_size - This can be very useful if you have a small number of read queries that are repeated frequently, with no write queries in between. There have been problems with too large a query cache locking up the server, so you will need to experiment to find a value that’s right for you.
innodb_log_file_size - Don’t fall into the trap of setting this to be too large. A large InnoDB log file group is necessary if you have lots of large, concurrent transactions, but comes at the expense of slowing down InnoDB recover, in event of a crash.
sort_buffer_size - Another one that shouldn’t be set too large. Peter Zaitsev did some testing a while back showing that increasing sort_buffer_size can in fact reduce the speed of the query.
Server Hardware
There are a few solid recommendations for improving the performance of MySQL by upgrading your hardware:
- Use a 64-bit processor, operating system and MySQL binary. This will allow you to address lots of RAM. At this point in time, InnoDB does have issues scaling past 8 cores, so you don’t need to go out of your way to have lots of processors.
- Speaking of RAM, buy lots of it. Enough to fit all of your data and indexes, if you can.
- If you can’t fit all of your data into RAM, you’ll need fast disks, RAID if you can. Have multiple disks, so you can seperate your data files, OS files and log files onto different physical disks.
Query Tuning
Finally, though probably the most important, we look at tuning queries. In particular, we make sure that they’re using indexes, and they’re running quickly. To do so, turn on the Slow Query Log for a day, with log_queries_not_using_indexes enabled as well. Run the resulting log through mysqldumpslow, which will produce a summary of the log. This will help you prioritize which queries to tackle first. Then, you can use EXPLAIN to find out what they’re doing, and adjust your indexes accordingly.
Upgrading MySQL with minimal downtime through Replication
Problem
With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:
- Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
- mysql_upgrade: A much faster method, but it can still be slow for very large data sets.
I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.
Preparation
First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:
- If you haven’t already, enable Binary Logging on S1. We will need it to act as a replication Master.
- Add an extra bit of functionality to your backup procedure. You will need to store the Binary Log position from when the backup was taken.
- If you’re using mysqldump, simply add the –master-data option to your mysqldump call.
- If you’re using InnoDB Hot Backup, there’s no need to make a change. The Binary Log position is shown when you restore the backup.
- For other backup methods, you will probably need to get the Binary Log position manually:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
(Perform backup now...)
mysql> UNLOCK TABLES;
Once you have a backup with the corresponding Binary Log position, you can setup S2:
- Install MySQL 5.1 on S2.
- Restore the backup from S1 to S2.
- Create the Slave user on S1.
- Enter the Slave settings on S2. You should familiarise yourself with the Replication documentation.
- Enable Binary Logging on S2. We’ll need this during the upgrade process.
- Setup S2 as a Slave of S1:
- If you used mysqldump for the backup, you will need to run the following query:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password';
- For any other method, you’ll need to specify the Binary Log position as well:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.nnnnnnn', MASTER_LOG_POS=mmmmmmmm;
- If you used mysqldump for the backup, you will need to run the following query:
- Start the Slave on S2:
mysql> START SLAVE;
The major pre-upgrade work is now complete.
Upgrade
Just before beginning the upgrade, take a backup of S2. For speed, I’d recommend running the following queries, then shutting down the MySQL server and copying the data files for the backup.
mysql> STOP SLAVE;
mysql> SHOW MASTER STATUS;
Once the backup is complete, restart S2 and let it catch up with S1 again.
When you’re ready to begin the upgrade, you will need a minor outage. Stop your application, and let S2 catch up with S1. Once it has caught up, they will have identical data. So, switch your application to using S2 instead of S1. Your application can continue running unaffected while you upgrade S1 server.
- Stop the Slave process on S2:
mysql> STOP SLAVE; - Stop S1.
- Upgrade S1 to MySQL 5.1.
- Move the S1 data files to a backup location.
- Move the backup from S2 into S1’s data directory.
- Start S2.
- Setup S2 as a Slave to S1, same as when we made S1 a Slave of S2.
- Let S2 catch up with S1. When it has caught up, stop your application, and make sure S2 is still caught up with S1.
- Switch your application back to using S1.
Complete! Hooray! You just need to run a couple of queries on S1 to clean up the Slave settings:
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='';
Conclusion
You can keep the outage to only a few minutes while performing this upgrade, removing the need for potentially expensive downtime. If you need the downtime to be zero, you probably want to be looking at a Circular Replication system, though that’s getting a little outside of this blog post.
Subscribe to:
Posts (Atom)