InnoDB Buffer Pool Status
Hi, I got this result on InnoDB Buffer Pool Status: Free pages 1 Dirty pages 2,040 Pages containing data 31,359 Pages to be flushed 457,083,205 Busy pages 1,408 Read requests 31,348,288,497 Write requests 7,913,407,934 Read misses 39,736,110 Write waits 0 Read misses in %0.13 % Write waits in %0.00 % I see there are millions of Read misses. What's that mean? And how to tuning up my server to get faster, stable, and reliable? Many thanks for any response. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB Buffer Pool Status
Willy Mularto wrote: Hi, I got this result on InnoDB Buffer Pool Status: Free pages1 Dirty pages 2,040 Pages containing data 31,359 Pages to be flushed 457,083,205 Busy pages1,408 Read requests 31,348,288,497 Write requests7,913,407,934 Read misses 39,736,110 Write waits 0 Read misses in % 0.13 % Write waits in % 0.00 % I see there are millions of Read misses. What's that mean? And how to tuning up my server to get faster, stable, and reliable? Many thanks for any response. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ I'm a novice myself, so I can't offer much in the way of wise advice. I can, however, point you to a neat script that might give you some useful pointers. [ https://launchpad.net/mysql-tuning-primer ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB Buffer Pool Status
On Tue, Sep 21, 2010 at 12:49 PM, Willy Mularto sangpr...@gmail.com wrote: Hi, I got this result on InnoDB Buffer Pool Status: Free pages 1 Dirty pages 2,040 Pages containing data 31,359 Pages to be flushed 457,083,205 Busy pages 1,408 Read requests 31,348,288,497 Write requests 7,913,407,934 Read misses 39,736,110 Write waits 0 Read misses in %0.13 % Write waits in %0.00 % I see there are millions of Read misses. What's that mean? Nothing much, in and of itself. Divide by your uptime, and you'll get a vaguely meaningful number. Keep track of the counter over time, and you may see something actually useful. And how to tuning up my server to get faster, stable, and reliable? Many thanks for any response. By understanding how it works, what it does, and what the different counters and variables mean. There's no magic trick for it - start by investing time in reading the documentation; play with test systems; and if you've got the dough, get mysql-sanctioned training. Nothing in life is free. If it doesn't cost money, maybe you have to spend time. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
document for mysql performance improvement
Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Yes we have changed some arguments in my.cnf like key_buffer_size, sort_buffer_size etc. 2010/9/21 Machiel Richards machiel.richa...@gmail.com: Good day There is quite a lot of documentation available for MySQL performance management. However, a quick question on this matter. Have you changed any of the default buffer and cache sizes as yet? Regards Machiel -Original Message- From: vokern vok...@gmail.com To: mysql@lists.mysql.com Subject: document for mysql performance improvement Date: Tue, 21 Sep 2010 20:37:49 +0800 Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Yep. There's rather extensive documentation on http://www.mysql.com. You'll need to read it and compare to the metrics you're taking off your own server, draw conclusions and apply them to your setup. You *are* pulling metrics, aren't you, and not hoping for some magic wand to make it all happen ? On Tue, Sep 21, 2010 at 2:37 PM, vokern vok...@gmail.com wrote: Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- 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: document for mysql performance improvement
If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. What is your current disk configuration? Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
2010/9/21 a.sm...@ukgrid.net: If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. What is your current disk configuration? The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: document for mysql performance improvement
I find this quite good http://www.mysqlperformanceblog.com/ Send your my.cnf and maybe we could look at it and pick anything that would help. Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: vokern [mailto:vok...@gmail.com] Sent: 21 September 2010 2:38 PM To: mysql@lists.mysql.com Subject: document for mysql performance improvement Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jang...@jangita.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: document for mysql performance improvement
Quoting vokern vok...@gmail.com: The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. And how many disks do you have, and what type (SATA/SAS/FC etc) what RPM? To improve IO you can add more disks, or upgrade to faster disks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Thank you all for the kind helps. I will check them and if still have problems I will come back. 2010/9/21 Machiel Richards machiel.richa...@gmail.com: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ http://www.mysql.com/why-mysql/performance/ http://www.debianhelp.co.uk/mysqlperformance.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
2010/9/21 a.sm...@ukgrid.net: Quoting vokern vok...@gmail.com: The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. And how many disks do you have, and what type (SATA/SAS/FC etc) what RPM? To improve IO you can add more disks, or upgrade to faster disks. Two disks with SAS driver, 15K rpm. BTW, we are running a TTServer before mysql for caching the query, is this better for performance? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting vokern vok...@gmail.com: Two disks with SAS driver, 15K rpm. Ok so you have fast disks, but with only 2 disks it is normal you will be quite restricted by DISK IO. By adding more in multiples of 2 and stripping across all you achieve RAID1+0 and higher max IO... Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. Two disks with SAS driver, 15K rpm. RAID-10 with 2 disks? or do you mean RAID0 or RAID1??? Can't see how you would get RAID10, minimum of 6 disks for that, no? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Mysql tuner is a very useful tool to pull metrics http://blog.mysqltuner.com/ On 09/21/2010 05:48 AM, Jangita wrote: I find this quite good http://www.mysqlperformanceblog.com/ Send your my.cnf and maybe we could look at it and pick anything that would help. Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: vokern [mailto:vok...@gmail.com] Sent: 21 September 2010 2:38 PM To: mysql@lists.mysql.com Subject: document for mysql performance improvement Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
On Tue, Sep 21, 2010 at 4:03 PM, a.sm...@ukgrid.net wrote: Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- 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: document for mysql performance improvement
Also, mailing list doesn't want to distribute attachments :-) Here's a link to the metrics view I was on about earlier: http://www.tuxera.be/mysqlstats.zip On Tue, Sep 21, 2010 at 4:08 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Tue, Sep 21, 2010 at 4:03 PM, a.sm...@ukgrid.net wrote: Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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: document for mysql performance improvement
Hi, Check your slow queries first. Large full scans can cause unwanted disk io. Do you use MyISAM or InnoDB? From your status, you seem to have intensive MyISAM locking. Peter On 09/21/2010 04:10 PM, Johan De Meersman wrote: Also, mailing list doesn't want to distribute attachments :-) Here's a link to the metrics view I was on about earlier: http://www.tuxera.be/mysqlstats.zip On Tue, Sep 21, 2010 at 4:08 PM, Johan De Meersmanvegiv...@tuxera.bewrote: On Tue, Sep 21, 2010 at 4:03 PM,a.sm...@ukgrid.net wrote: Quoting Johan De Meersmanvegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SELECT WHERE IN help
Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil
Re: SELECT WHERE IN help
If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- 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: SELECT WHERE IN help
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- 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: SELECT WHERE IN help
I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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: SELECT WHERE IN help
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a table. I think in this case I need to change my design . On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote: I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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: SELECT WHERE IN help
On 21/09/2010 16:44, Tompkins Neil wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? No, that isn't possible. Why do you want a duplicate record to be retrieved? There may be a better way of doing it. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT WHERE IN help
Hi Neil, all! Tompkins Neil wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? This is a case where you may safely use natural language and logic. The command is SELECT all fields FROM the records in mytable FOR WHICH THE FOLLOWING CONDITION IS TRUE: the field record_id has a value which is IN the list 3, 4, 5, 6, 7, 3 The condition can only evaluate to true or false (ignoring NULL values and the unknown truth value for now), and for that evaluation it does not matter whether a matching value appears in your list only once or repeatedly. To achieve your desired effect, you might use a generator to create a UNION statement. Roughly, the approach would be (+= means appending to a string): value = first value of the list; statement = SELECT * FROM my_table WHERE record_id = $value; while (there are more values in the list) do value = next value of the list; statement += UNION SELECT * FROM my_table WHERE record_id = $value; done; statement += ;; execute statement; Obviously, this will create a huge statement if the value list is long, and it doesn't seem to be efficient, so I don't recommend this technique in general. Before going that route, you should question your assumptions: Why is it necessary to return the same record twice? Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SELECT WHERE IN help
-Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, September 21, 2010 11:48 AM To: Johan De Meersman Cc: [MySQL] Subject: Re: SELECT WHERE IN help Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a table. I think in this case I need to change my design . [JS] You can accomplish your goal by using a sub-select to create a table that has 3 in it twice, and then JOIN it to the original table. As for why you would want to do this, that's another story. It sounds like you went down the wrong road. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote: I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: prime number table
Thank you so much Chris. But is this mean that myisam table stores integer very differntly and inefficiently than InnoDB table? I see that oid can be ommitted. But in the case that I like to query on ordinal number of the prime. oid can make things faster, am i right? seperate the storage for descript is a great idea. thanks again! - Original Message - From: Chris W 4rfv...@cox.net To: Elim PDT e...@pdtnetworks.net Cc: mysql@lists.mysql.com Sent: Saturday, September 18, 2010 6:57 AM Subject: Re: prime number table Keep in mind that to store a number as a 32 bit unsigned integer takes 4 bytes of data no matter how small or large that number is as long as it is less than 4,294,267,296. If you store numbers as a string like it is in your file it takes 8 bits per digit so for 19,999,999 it would take 8 bytes or 64 bits. I assume the file has a line feed between each number, that is another 8 bits and may also have a carriage return adding another 8 bits. You could probably make your table even smaller if you were to move the descript filed to a new table. I highly recommend this option based on your statement that most of them are empty. A table with only integer values will have a fixed row size and probably be a little faster to access. If most of the rows will have descript text them leave it in this table, other wise it would be more efficient to keep in a different table. Also I'm not sure you really need an independent field for a primary key since the prime numbers could be used as the primary key. That would make your table even smaller. I'm not sure why you would what a table of prime numbers unless you had an index on that field and making it the primary key does that for you. Chris W On 9/17/2010 1:28 PM, Elim PDT wrote: I got a file of the list of the 1st 1270607 prime numbers (the 1270607th prime is 1999, beat the $227 book at http://www.amazon.com/prime-numbers-Carnegie-institution-Washington/dp/B0006AH1S8). the file is an output of a python script. the file size is about 12Mb. Then I created a simeple mysql table prime as mysql desc prime; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prime; --+ | Table | Create Table --+ | prime | CREATE TABLE `prime` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k; 24,817KB; 12,754KB) Then I do mysql create table prm select * from prime order by prime.oid; mysql alter table prm modify oid int unsigned primary key auto_increment; mysql desc prm; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prm; +---+-- | Table | Create Table +---+-- | prm | CREATE TABLE `prm` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 | +---+-- The table file prm.frm is only 9KB My question is that how come it's SO SMALL? (currently the colum description in both tables prime and prm are empty except one identical row, with very short string value. Is is recommend to index the other twoo columns? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=e...@pdtnetworks.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3143 - Release Date: 09/18/10 00:34:00 -- MySQL General
Re: document for mysql performance improvement
This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack= 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections= 800 query_cache_limit = 1M query_cache_size= 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.293.59 0.97 7.93 dm-0 0.00 0.000.560.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
And this is the innodb file size, does this matter for performance? $ du -h ibdata* 11G ibdata1 11G ibdata2 11G ibdata3 59G ibdata4 2010/9/22 vokern vok...@gmail.com: This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack = 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections = 800 query_cache_limit = 1M query_cache_size = 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.29 3.59 0.97 7.93 dm-0 0.00 0.00 0.56 0.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
