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
Subscribe to:
Posts (Atom)