difference btw Analyze and Optimize table..
hi all what is the difference between OPTIMIZE TABLE tablename; and ANALYZE TABLE tablename; thank you Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
Re: difference btw Analyze and Optimize table..
OPTIMIZE TABLE tablename; analyze table ,stores the key distribution for a table ,reclaim the unused space and to defragment the data file. ANALYZE TABLE tablename; This action only analyze table and stores the key distribution for a table. 2010-04-13 River wubx Gtalk: wubin...@gmail.com http://www.mysqlsupport.cn China 发件人： F.A.I.Z.A.L 发送时间： 2010-04-13 16:52:28 收件人： mysql; mysql-help 抄送： 主题： difference btw Analyze and Optimize table.. hi all what is the difference between OPTIMIZE TABLE tablename; and ANALYZE TABLE tablename; thank you Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
Re: reshaping challenge
Are you quite sure there can never be more than 16 orders per ID ? The form your table is in now is actually the preferred form. It's called an associative table, and logic suggests that it sits between a table that holds more info on the IDs, and a table that holds more info on the orders. On Mon, Apr 12, 2010 at 5:11 PM, Mitchell Maltenfort mmal...@gmail.comwrote: I have a table with two columns, ID and order. Each ID can be repeated up to 16 timers. I need to reshape it so that I have one row per ID, and columns order1, order 2,...order 16, and one number that lists how many orders there actually were. No this is not a homework assignment. I'm trying to make sense out of a legacy project. Can someone help me out? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: difference btw Analyze and Optimize table..
hi River thanks for your mail. could you please tel me the correct syntax for optimize table command. i plan to put this in cronjob and execute every 8 hours in a day. optimize table tab1; is that correct or anything need to add with his statement. my environment is solaria Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Tue, Apr 13, 2010 at 2:37 PM, River wubx cnw...@gmail.com wrote: OPTIMIZE TABLE tablename; analyze table ,stores the key distribution for a table ,reclaim the unused space and to defragment the data file. ANALYZE TABLE tablename; This action only analyze table and stores the key distribution for a table. 2010-04-13 -- River wubx Gtalk: wubin...@gmail.com http://www.mysqlsupport.cn China -- *发件人：* F.A.I.Z.A.L *发送时间：* 2010-04-13 16:52:28 *收件人：* mysql; mysql-help *抄送：* *主题：* difference btw Analyze and Optimize table.. hi all what is the difference between OPTIMIZE TABLE tablename; and ANALYZE TABLE tablename; thank you Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
RE: difference btw Analyze and Optimize table..
http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html The entire page is highly recommended due to the number of helpful information. Regards, m -Original Message- From: F.A.I.Z.A.L [mailto:sac.fai...@gmail.com] Sent: Tuesday, April 13, 2010 10:56 AM To: River wubx Cc: mysql; mysql-help Subject: Re: difference btw Analyze and Optimize table.. hi River thanks for your mail. could you please tel me the correct syntax for optimize table command. i plan to put this in cronjob and execute every 8 hours in a day. optimize table tab1; is that correct or anything need to add with his statement. my environment is solaria Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Tue, Apr 13, 2010 at 2:37 PM, River wubx cnw...@gmail.com wrote: OPTIMIZE TABLE tablename; analyze table ,stores the key distribution for a table ,reclaim the unused space and to defragment the data file. ANALYZE TABLE tablename; This action only analyze table and stores the key distribution for a table. 2010-04-13 -- River wubx Gtalk: wubin...@gmail.com http://www.mysqlsupport.cn China -- *发件人：* F.A.I.Z.A.L *发送时间：* 2010-04-13 16:52:28 *收件人：* mysql; mysql-help *抄送：* *主题：* difference btw Analyze and Optimize table.. hi all what is the difference between OPTIMIZE TABLE tablename; and ANALYZE TABLE tablename; thank you Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT INTO multiple tables
I have no idea how you got here but there is no reason to do it that
way. This will work just fine and I do it every day in php.
However I don't use mysqli I still use ...
mysql_connect
mysql_select_db
mysql_real_escape_string
mysql_query
Don't forget to use the mysql_real_escape_string function to be sure sql
injection can't happen.
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error
connecting to
MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
Gary wrote:
Michael
Thank you for your response. It gave me the idea how to solve this, and it
seemed to have worked!
For those following hoping to see a solution, what I did was open the
connection, insert into one table, closed the connection, closed the php
script, and the data was inserted into 2 of the tables... The code looks
like this:
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
It seems a bit redundant for php, but it seems to work.
If by the way anyone sees a problem with this solution, I would love to read
it.
Again, thank you for your response.
Gary
Michael Dykman mdyk...@gmail.com wrote in message
news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fd...@mail.gmail.com...
It is not a question of multiple tables, it is a question of multiple
statements. Most PHP configurations prohibit the application of more
than one statement per call to execute. This is generally thought to
be a security issue as the vast majority of simple PHP-based SQL
injection attacks only work on servers that allow multiple statements.
I haven't been deep in PHP land for a little while, but I think you
will find the default driver/config is expressly preventing you from
doing this.
- michael dykman
On Mon, Apr 12, 2010 at 9:44 AM, Gary gwp...@ptd.net wrote:
Seriously
You should read your answers before you post, the SA link did not provide
the answer. Had you read the page you sent, you would notice it does not
apply to mulitple tables...
Gary
Colin Streicher co...@obviouslymalicious.com wrote in message
news:201004112310.16594.co...@obviouslymalicious.com...
Seriously...
I found the answer in the first result.
http://lmgtfy.com/?q=mysqli+multiple+insert+statements
Assuming mysqli, if you are using a different driver, then google that
Colin
On April 11, 2010 10:36:41 pm viraj wrote:
is it mysqli query or 'multi_query'?
http://php.net/manual/en/mysqli.multi-query.php
~viraj
On Sun, Apr 11, 2010 at 10:27 PM, Gary gwp...@ptd.net wrote:
I am experimenting with multiple tables, it is only a test that is my
local machine only. This is the current code, which does not work , I
have tried to concatonate the insert statements. I have tried multiple
$query variables, but it is just overwriting itself (only the last one
gets inserted). I also tried writing the $query as an array, which got
me
an error message (saying it was expecting a string and I offered an
array).
Someone point me in the right direction?
Gary
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleUntitled Document/title
/head
body
form action=?php echo $_SERVER['PHP_SELF'];? method=post
labelFirst Name /label input name=fname type=text /br /br
/
labelLast Name /labelinput name=lname type=text /br /br
/
labelStreet Address /labelinput name=street type=text /br
/br /
labelTown /labelinput name=town type=text /br /br /
labelState /labelinput name=state type=text /br /br /
labelZip Code/labelinput name=zip type=text /br /br /
labelTelephone/labelinput name=phone type=text /br /br /
labelFax/labelinput name=fax type=text /br /br /
labelE-Mail/labelinput name=email type=text /br /br /
labelComments/labelbr /textarea name=comments cols=100
rows=15/textareabr /br /
input name=submit type=submit value=submit /
/form
?php
$fname=($_POST['fname']);
$lname=($_POST['lname']);
$street=($_POST['street']);
$town=($_POST['town']);
$state=($_POST['state']);
$zip=($_POST['zip']);
$phone=($_POST['phone']);
$fax=($_POST['fax']);
$email=($_POST['email']);
Re: INSERT INTO multiple tables
haven't been deep in PHP land for a little while, but I think you
will find the default driver/config is expressly preventing you from
doing this.
- michael dykman
On Mon, Apr 12, 2010 at 9:44 AM, Gary gwp...@ptd.net wrote:
Seriously
You should read your answers before you post, the SA link did not
provide
the answer. Had you read the page you sent, you would notice it does
not
apply to mulitple tables...
Gary
Colin Streicher co...@obviouslymalicious.com wrote in message
news:201004112310.16594.co...@obviouslymalicious.com...
Seriously...
I found the answer in the first result.
http://lmgtfy.com/?q=mysqli+multiple+insert+statements
Assuming mysqli, if you are using a different driver, then google that
Colin
On April 11, 2010 10:36:41 pm viraj wrote:
is it mysqli query or 'multi_query'?
http://php.net/manual/en/mysqli.multi-query.php
~viraj
On Sun, Apr 11, 2010 at 10:27 PM, Gary gwp...@ptd.net wrote:
I am experimenting with multiple tables, it is only a test that is
my
local machine only. This is the current code, which does not work ,
I
have tried to concatonate the insert statements. I have tried
multiple $query variables, but it is just overwriting itself (only
the last one gets inserted). I also tried writing the $query as an
array, which got me
an error message (saying it was expecting a string and I offered an
array).
Someone point me in the right direction?
Gary
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
meta http-equiv=Content-Type content=text/html; charset=utf-8
/
titleUntitled Document/title
/head
body
form action=?php echo $_SERVER['PHP_SELF'];? method=post
labelFirst Name /label input name=fname type=text /br
/br /
labelLast Name /labelinput name=lname type=text /br
/br
/
labelStreet Address /labelinput name=street type=text
/br
/br /
labelTown /labelinput name=town type=text /br /br /
labelState /labelinput name=state type=text /br /br /
labelZip Code/labelinput name=zip type=text /br /br /
labelTelephone/labelinput name=phone type=text /br /br
/ labelFax/labelinput name=fax type=text /br /br /
labelE-Mail/labelinput name=email type=text /br /br /
labelComments/labelbr /textarea name=comments cols=100
rows=15/textareabr /br /
input name=submit type=submit value=submit /
/form
?php
$fname=($_POST['fname']);
$lname=($_POST['lname']);
$street=($_POST['street']);
$town=($_POST['town']);
$state=($_POST['state']);
$zip=($_POST['zip']);
$phone=($_POST['phone']);
$fax=($_POST['fax']);
$email=($_POST['email']);
$comments=($_POST['comments']);
$REMOTE_ADDR=$_SERVER['REMOTE_ADDR'];
$dbc=mysqli_connect('localhost','root','','test');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip').
INSERT INTO comments(comments).VALUES('$comments').
INSERT INTO
contact(phone,fax,email).VALUES('$phone','$fax','$email').
INSERT
INTO name (fname, lname).VALUES('$fname','$lname');
$result = mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
/body
/html
__ Information from ESET Smart Security, version of virus
signature database 5017 (20100411) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=kali...@gmail.com
--
It is easy to find fault, if one has that disposition. There was once
a
man
who, not being able to find any other fault with his coal, complained
that
there were too many prehistoric toads in it.
-- Mark Twain, Pudd'nhead Wilson's Calendar
__ Information from ESET Smart Security, version of virus
signature database 5021 (20100412) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus
signature database 5021 (20100412) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
--
Q: What's the difference between an Irish wedding and an Irish wake?
A: One less drunk.
__ Information from ESET Smart Security, version of virus
signature database 5023 (20100412) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus signature
database 5025 (20100413
Re: INSERT INTO multiple tables
/labelinput name=phone type=text /br /br
/
labelFax/labelinput name=fax type=text /br /br /
labelE-Mail/labelinput name=email type=text /br /br /
labelComments/labelbr /textarea name=comments cols=100
rows=15/textareabr /br /
input name=submit type=submit value=submit /
/form
?php
$fname=($_POST['fname']);
$lname=($_POST['lname']);
$street=($_POST['street']);
$town=($_POST['town']);
$state=($_POST['state']);
$zip=($_POST['zip']);
$phone=($_POST['phone']);
$fax=($_POST['fax']);
$email=($_POST['email']);
$comments=($_POST['comments']);
$REMOTE_ADDR=$_SERVER['REMOTE_ADDR'];
$dbc=mysqli_connect('localhost','root','','test');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip').
INSERT INTO comments(comments).VALUES('$comments').
INSERT INTO
contact(phone,fax,email).VALUES('$phone','$fax','$email'). INSERT
INTO name (fname, lname).VALUES('$fname','$lname');
$result = mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
/body
/html
__ Information from ESET Smart Security, version of virus
signature database 5017 (20100411) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=kali...@gmail.com
--
It is easy to find fault, if one has that disposition. There was once a
man
who, not being able to find any other fault with his coal, complained
that
there were too many prehistoric toads in it.
-- Mark Twain, Pudd'nhead Wilson's Calendar
__ Information from ESET Smart Security, version of virus
signature database 5021 (20100412) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus
signature database 5021 (20100412) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
__ Information from ESET Smart Security, version of virus
signature database 5025 (20100413) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus signature
database 5025 (20100413) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQLClient 3.x compatible with MySQL 5.x?
Hello, I need to have a MySQL 3.x client to be compatible with a new MySQL 5.x server, is it possible? What do I need to change on my MySQL 5.x server to allow old clients to work? Also, what do I need to change to allow old PHP 4.x code to talk to a MySQL 5.x server? Thanks, POL.
MySQL 5.5.3-m3 and MyQL 5.5.4-m4 have been released
Dear MySQL users, MySQL Server 5.5.3-m3, a new version of the popular Open Source Database Management System, has been released. We've also released MySQL Server 5.5.4-m3 for Linux on x86_64 only at this time; it contains a new version of InnoDB including several performance enhancements in comparison to MySQL Server 5.5.3-m3. The -m3 suffix tells these releases belong to the third milestone according to our milestone release model, also called Celosia. You can read more about the release model and the planned milestones at http://forge.mysql.com/wiki/Development_Cycle The new features in these releases are of beta quality. As with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For a list of known bugs reported against MySQL Server 5.5.3-m3, please see http://bugs.mysql.com/saved/5.5.3-m3-bugs Please note that *downgrading* from these releases to a previous release series, including MySQL Server 5.5 milestone 2 (Betony) releases, is not supported. For production level systems using 5.1, we would like to direct your attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ MySQL 5.5 is based on MySQL 5.4, which won't get any further updates. MySQL 5.5 includes several high-impact changes to address scalability and performance issues in MySQL Server. These changes exploit advances in hardware and CPU design and enable better utilization of existing hardware. For an overview of what's new in MySQL 5.5, please see the section What Is New in MySQL 5.5 below, or view it online at http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html For information on installing MySQL 5.5.3-m3 or MySQL 5.5.4-m3 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html MySQL Server 5.5 is available in source and binary form for a number of platforms from the Development Releases selection of our download pages at http://dev.mysql.com/downloads/mysql/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing The list of all Bugs Fixed may also be viewed online at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html Special note for those of you who build from source: In MySQL 5.5.3-m3, CMake joins GNU autotools as a build framework for all platforms. We've previously already used CMake on Windows. Our existing support for GNU autotools isn't going away just yet, but if you are so inclined, you can now alternatively use CMake as per the instructions on the web page linked below. As always, we value your feedback! http://forge.mysql.com/wiki/CMake Enjoy! Daniel Changes in MySQL 5.5.4: InnoDB Plugin Notes: * This release includes InnoDB Plugin 1.1. Bugs fixed: * The mysqld option to turn on support for large pages was erroneously linked to the internal variable that stores whether large file support is available. Turning large_pages on would show both support for large pages and for large files as available without actually turning on support for large pages. Turning large_pages off would show both as turned off without any actual implication to large file support, which is compiled in at build time. This bug was introduced in MySQL Server 5.5.3-m3. (Bug#52716: http://bugs.mysql.com/bug.php?id=52716) Changes in MySQL 5.5.3: Performance Schema Notes: * MySQL Server now includes Performance Schema, a feature for monitoring server execution at a low level. It is implemented via the PERFORMANCE_SCHEMA storage engine and the performance_schema database. Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata. For more information, see Chapter 20, MySQL Performance Schema. Performance Schema support is included in binary MySQL distributions. It is disabled by default. To enable it, start the server with the --performance_schema option. To create the performance_schema database if you are upgrading from an earlier release, run mysql_upgrade and restart the server. See Section 4.4.7, mysql_upgrade --- Check Tables for MySQL Upgrade. InnoDB Plugin Notes: * This release includes InnoDB Plugin 1.0.6. This version is considered of Release Candidate (RC) quality. Functionality added or changed: * Performance: The performance of internal functions that trim multiple spaces from strings when comparing them has been improved. (Bug#14637:
Re: MySQLClient 3.x compatible with MySQL 5.x?
Yes, If it is a password problem.
do a update with password=old_password('xxpasswdxx');
It will work.
If you are facing any other problems, paste it.
On Tue, Apr 13, 2010 at 10:37 PM, Pecas On Line pol.cota...@gmail.comwrote:
Hello,
I need to have a MySQL 3.x client to be compatible with a new MySQL 5.x
server, is it possible?
What do I need to change on my MySQL 5.x server to allow old clients to
work?
Also, what do I need to change to allow old PHP 4.x code to talk to a MySQL
5.x server?
Thanks,
POL.
--
Thanks
Suresh Kuna
MySQL DBA
Re: difference btw Analyze and Optimize table..
Analyze table : Analyze table analyzes and stores the key distribution for a table. For more details check the below URL http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html Optimize table : OPTIMIZE TABLE is useful when we do more deleted operations on a table with variable columns. It will do the defragmentation of the data file and recliam the space. It sorts the indexes and updates the table statistics if it is not. However, the new inserts will reuse the deleted row space. For more details check the below URL http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html On Tue, Apr 13, 2010 at 2:21 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: hi all what is the difference between OPTIMIZE TABLE tablename; and ANALYZE TABLE tablename; thank you Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Thanks Suresh Kuna MySQL DBA
Recommended swap partition size
Hello all, My organization has a dedicated MySQL server. The system has 32Gb of memory, and is running CentOS 5.3. The default engine will be InnoDB. Does anyone know how much space should be dedicated to swap? Thanks!
Re: InnoDB - 16GB Data
Also, if you have read heavy workload, you might want to try using and tuning your query cache. Start off with something like 32M and incrementally tune it. You can monitor some query cache related server variables. Kyong On Sat, Apr 10, 2010 at 4:28 PM, Rob Wultsch wult...@gmail.com wrote: On Sat, Apr 10, 2010 at 12:10 AM, Junior Ortis jror...@gmail.com wrote: Hi Guys i have a dedicated mysql-server and neeed tips and sugestion to optimize its to a better performance. 1-) Here i have results from mysqltunner MySQLTuner 1.0.1 - Major Hayden ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: toscaoSo Please enter your MySQL administrative password: General Statistics -- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.4.3-beta-community [OK] Operating on 64-bit architecture Storage Engine Statistics --- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 458M (Tables: 349) [--] Data in InnoDB tables: 15G (Tables: 73) [!!] Total fragmented tables: 47 Performance Metrics - [--] Up for: 29d 12h 8m 1s (334M q [131.330 qps], 153K conn, TX: 194B, RX: 77B) [--] Reads / Writes: 31% / 69% [--] Total buffers: 15.5G global + 16.2M per thread (50 max threads) [OK] Maximum possible memory usage: 16.3G (69% of installed RAM) [OK] Slow queries: 0% (386/334M) [OK] Highest usage of available connections: 46% (23/50) [OK] Key buffer size / total MyISAM indexes: 300.0M/87.3M [OK] Key buffer hit rate: 100.0% (78M cached / 22K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4M sorts) [OK] Temporary tables created on disk: 0% (548 on disk / 1M total) [OK] Thread cache hit rate: 99% (23 created / 153K connections) [OK] Table cache hit rate: 44% (467 open / 1K opened) [OK] Open file limit used: 1% (684/65K) [OK] Table locks acquired immediately: 99% (320M immediate / 320M locks) [!!] InnoDB data size / buffer pool: 15.5G/15.0G Recommendations - General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Variables to adjust: query_cache_size (= 8M) innodb_buffer_pool_size (= 15G) 2-) And here is my dedicate server i have (24GB ): 1 [ 0.0%] Tasks: 71 total, 2 running 2 [||| 7.8%] Load average: 0.11 0.18 0.19 3 [| 0.7%] Uptime: 62 days, 19:24:09 4 [| 0.7%] Mem[|16878/24165MB] Swp[| 0/5122MB] 3-) And my.cnf vim .my.cnf [client] #password = [your_password] port = 3306 socket = /tmp/mysql.sock # *** Application-specific options follow here *** # # The MySQL server # [mysqld] #large-pages # generic configuration options port = 3306 socket = /tmp/mysql.sock skip-locking skip-external-locking datadir = /disk3/Datareal/oficial/mysql net_buffer_length = 1024K join_buffer_size = 4M sort_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M table_cache = 1000 max_allowed_packet = 160M max_connections=50 max_user_connections=200 key_buffer = 300M key_buffer_size = 300M #thread_cache = 400 thread_stack = 192K thread_cache_size = 96 thread_concurrency = 8 #thread_stack = 128K default-character-set = utf8 innodb_flush_method=O_DSYNC innodb_buffer_pool_size= 15G innodb_additional_mem_pool_size=128M innodb_log_file_size= 256M innodb_log_buffer_size=72M innodb_flush_log_at_trx_commit=0 innodb_thread_concurrency=8 innodb_file_per_table=1 innodb_log_files_in_group=2 innodb_table_locks=0 innodb_lock_wait_timeout = 50 ~/.my.cnf 72L, 1570C Thanks guys for any tips/suggestion ! First, most performance comes from optimized table structures, index, and queries. Server tuning will not get you you all that much additions performance, if you have a semi sane configuration. What is your current bottleneck or performance problem? Anyways... here are some reactions: innodb_flush_log_at_trx_commit=0 ... THIS MEANS YOU CAN LOSE COMMITTED TRANSACTIONS. Read up on this. innodb_flush_method=O_DSYNC Any particular reason you aren't using O_DIRECT ? Read up on this. Why do you not have skip-name-resolve on? Read up on this. innodb_thread_concurrency... As you are running 5.4 you can probably set this to 0. Assuming you have 4 cores or
Re: Recommended swap partition size
In the last episode (Apr 13), Joe Hammerman said: My organization has a dedicated MySQL server. The system has 32Gb of memory, and is running CentOS 5.3. The default engine will be InnoDB. Does anyone know how much space should be dedicated to swap? I say zero swap, or if for some reason you NEED swap (for crashdumps maybe, but I didn't think Linux supported that), no more than 2GB. With that much RAM, you don't ever want to be in the state where the OS decides to page out 8GB of memory (for example) to swap. We have a few Oracle servers with between 32 and 48 GB of memory and they all live just fine without swap. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Recommended swap partition size
Yeah. One of the telltale signs of something amiss is excessive swap activity. You're not going to be happy with the performance when the swap space is actually in use heavily. Kyong On Tue, Apr 13, 2010 at 8:15 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Apr 13), Joe Hammerman said: My organization has a dedicated MySQL server. The system has 32Gb of memory, and is running CentOS 5.3. The default engine will be InnoDB. Does anyone know how much space should be dedicated to swap? I say zero swap, or if for some reason you NEED swap (for crashdumps maybe, but I didn't think Linux supported that), no more than 2GB. With that much RAM, you don't ever want to be in the state where the OS decides to page out 8GB of memory (for example) to swap. We have a few Oracle servers with between 32 and 48 GB of memory and they all live just fine without swap. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=kykim...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fwd: Mixing Latin and UTF
Hello, I hope that this is the right list. I have a database that has been running for years in latin1 but a software update changed it in to utf8 that would be no big deal if we know it right away so we could change the database. The big problem is that the database has been running for 2 months as utf8 and it is causing problems now. I have like 500mb latin1 and 100mb utf in the database. How can I convert the database to utf8 without breaking it? Best Huib
