http://blog.gmane.org/gmane.comp.db.mysql.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.mysql.general/110062 <pre>I got a solution maybe step 1: mysql&gt; explain select * from users; +----+-------------+-------+------+---------------+------+---------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+----------+-------+ | 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 32883093 | | +----+-------------+-------+------+---------------+------+---------+------+----------+-------+ 1 row in set (0.00 sec) so you get the "rows" field Step2: select * from users, limit $r,1 What do you think? Is the only way i found what delays seconds not minuts. USERS is a 19GB Table for me. LD 2011/10/30 Jan Steinman &lt;Jan&lt; at &gt;bytesmiths.com&gt;: </pre> Luis Daniel Lucio Quiroz 2012-05-24T06:59:43 http://permalink.gmane.org/gmane.comp.db.mysql.general/110061 <pre> On second thought, an index on thold_enabled won't mean much I think, so either leave it off or create an index on data_id plus thold_enabled. Someone more knowledgeable may correct me. Alex </pre> Alex Schaft 2012-05-24T05:55:49 http://permalink.gmane.org/gmane.comp.db.mysql.general/110060 <pre>You are selecting a record based on the value of data_id and thold_enabled, but don't have an index on either? Add an index for both. If data_id is unique, then you would only need an index on that. Alex </pre> Alex Schaft 2012-05-24T05:37:11 http://permalink.gmane.org/gmane.comp.db.mysql.general/110059 <pre>Rick Thank you for the reply. The page is really cool. Its very simple and easy to understand. | thold_data | CREATE TABLE `thold_data` ( `id` int(11) NOT NULL auto_increment, `rra_id` int(11) NOT NULL default '0', `data_id` int(11) NOT NULL default '0', `thold_hi` varchar(100) default NULL, `thold_low` varchar(100) default NULL, `thold_fail_trigger` int(10) unsigned default NULL, `thold_fail_count` int(11) NOT NULL default '0', `thold_alert` int(1) NOT NULL default '0', `thold_enabled` enum('on','off') NOT NULL default 'on', `bl_enabled` enum('on','off') NOT NULL default 'off', `bl_ref_time` int(50) unsigned default NULL, `bl_ref_time_range` int(10) unsigned default NULL, `bl_pct_down` int(10) unsigned default NULL, `bl_pct_up` int(10) unsigned default NULL, `bl_fail_trigger` int(10) unsigned default NULL, `bl_fail_count` int(11) unsigned default NULL, `bl_alert` int(2) NOT NULL default '0', `lastread` varchar(100) default NULL, `oldvalue` varchar(100) NOT NULL default '', `repeat_alert` int(10) unsigned default NULL, `notify_default` enum('on','off') default NULL, `notify_extra` varchar(255) default NULL, `host_id` int(10) default NULL, `syslog_priority` int(2) default '3', `cdef` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `rra_id` (`rra_id`) ) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 | You must be mentioning about the "show table status" mysql&gt; show table status where name = "thold_data"; +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+--------------------- +---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+--------------------- +---------------------+-------------------+----------+----------------+---------+ | thold_data | MyISAM | 10 | Dynamic | 6161 | 90 | 555128 | 281474976710655 | 140288 | 0 | 70258 | 2012-05-24 10:41:47 | 2012-05-24 10:47:19 | 2012-05-24 10:41:47 | latin1_swedish_ci | NULL | | | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+--------------------- +---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) I have seen the following select query in the slow query log. I also saw update queries as well. mysql&gt; explain select * from thold_data where thold_enabled='on' AND data_id = 91633; +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | thold_data | ALL | NULL | NULL | NULL | NULL | 6161 | Using where | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.06 sec) If cache size tuning is not an option , do you think that following action would be an choice to faten the queries little bit more? 1. depriving the database and setup as an another process. (multiple mysql processes) 2. Move the MYD, MYI, frm to ram disk (/dev/shm) Thanks, Yu Rick James さんは書きました: </pre> Yu Watanabe 2012-05-24T02:10:07 http://permalink.gmane.org/gmane.comp.db.mysql.general/110058 <pre>100% CPU --&gt; A slow query. Tuning will not help. Period. 1. There are only a few things worth tuning -- see http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried) 2. Instead INDEXes and schema design must be studied. Please provide: SHOW CREATE TABLE SHOW TABLE SIZE EXPLAIN SELECT ... </pre> Rick James 2012-05-23T16:28:45 http://permalink.gmane.org/gmane.comp.db.mysql.general/110057 <pre> I've been playing with it a bit. Their "virtual columns" enhancement is pretty cool -- something I miss from my FileMaker days. ---------------- Economics is extremely useful as a form of employment for economists. -- John Kenneth Galbraith :::: Jan Steinman, EcoReality Co-op :::: </pre> Jan Steinman 2012-05-23T13:42:49 http://permalink.gmane.org/gmane.comp.db.mysql.general/110056 <pre>Hi, How much ever tuning you do at my.cnf will not help much, if you do not tune your sql's. Your first priority should be tune sql's, which will give you good performance even with decent memory allocations and other settings regards anandkl On Wed, May 23, 2012 at 3:45 PM, Andrew Moore &lt;eroomydna&lt; at &gt;gmail.com&gt; wrote: </pre> Ananda Kumar 2012-05-23T10:35:30 http://permalink.gmane.org/gmane.comp.db.mysql.general/110055 <pre>Yu, The upgrade to 5.5 that Jonny advises should NOT your first action. If MySQL is mis-configured on 5.0 it will likely be misconfigured on 5.1 and 5.5. Test your application thoroughly on the new version before heeding that advice. Read the change logs and known bugs. Running the upgrade might seem painless but if you have some legacy feature in place then things will not work how you may expect them to. Review your needs and see if a switch to innodb storage engine will give you any performance gain. The locking differences alone might make this worthwhile. TEST it. You did not state your data and index size. You will benefit from having enough RAM so that your 'working' data set fits to memory. This isn't possible/practical for large data but if you have a 5G dataset and 8G available memory you might not need to rush out and spend money. If you're heavily using MyISAM, review and tune the MyISAM related buffers. If you are working mostly with InnoDB tune those variables. Measure, change measure again. It might be an iterative process but you will learn lots along the way. Good luck. Andy On Wed, May 23, 2012 at 5:44 AM, Tsubasa Tanaka &lt;yoku0825&lt; at &gt;gmail.com&gt; wrote: </pre> Andrew Moore 2012-05-23T10:15:05 http://permalink.gmane.org/gmane.comp.db.mysql.general/110053 <pre>Hello, Yu-san, (へろへろな英語で申し訳ないです) Can I think that you already tweaked Index on the tables? if you yet,please create apt indexes. MyISAM caches only Index without data. i take way for decreasing disk seek, 1) create more indexes on the tables,if the tables doesn't update quite often. including data into index forcibly. this makes slow for insert and update,and this is dirty idea,i think. (よくSELECTされるカラムをINDEXに含めてしまいます。 ただし、SELECT * FROMで呼ばれることが多い場合には使えない上に かなり美しくない策です。。) 2) tune filesystem and disk drive parameter for datadir. MyISAM table's data caches only in the filesystem cache. But i regret that i don't have knowledge around filesystem. あまり力になれなくて申し訳ないです。 regards, ts. tanaka// 2012/5/23 Yu Watanabe &lt;yu.watanabe&lt; at &gt;jp.fujitsu.com&gt;: </pre> Tsubasa Tanaka 2012-05-23T04:44:04 http://permalink.gmane.org/gmane.comp.db.mysql.general/110052 <pre>Hello Tsubasa. Thank you for the reply. (返信ありがとうございます。) Our high loaded DB are both INNODB and MyISAM. Espicially , on MyISAM. I will consider the tuning of innodb_buffer_pool_size as well. Do you know the tips for how to tune the disk access for MyISAM? Thanks, Yu Tsubasa Tanaka さんは書きました: </pre> Yu Watanabe 2012-05-23T03:57:05 http://permalink.gmane.org/gmane.comp.db.mysql.general/110051 <pre>I don't see any attachments. First, I would upgrade to 5.5 as 5.0 is very old. The upgrade process is painless. Second, make sure your Innodb buffer pool is allocating as much ram as possible. I'd even go as far as adding another 8gb of ram to the server. The buffer pool setting is going to give you the best performance increase. Also, what kind of hard disks do you have the data files on? Raid? No raid? Sent from my iPad On May 22, 2012, at 9:08 PM, Yu Watanabe &lt;yu.watanabe&lt; at &gt;jp.fujitsu.com&gt; wrote: </pre> Johnny Withers 2012-05-23T03:18:06 http://permalink.gmane.org/gmane.comp.db.mysql.general/110050 <pre>Hello, I seem your mysqld doesn't use enough memory. if your mysqld uses InnoDB oftenly, edit innodb_buffer_pool_size in you my.cnf. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size It is solution for only sql's large result,i think. if you doesn't recognize that problem causes large result, you should approach other way,too. regards, ts. tanaka// 2012/5/23 Yu Watanabe &lt;yu.watanabe&lt; at &gt;jp.fujitsu.com&gt;: </pre> Tsubasa Tanaka 2012-05-23T02:35:59 http://permalink.gmane.org/gmane.comp.db.mysql.general/110049 <pre>Also following is the free command result. total used free shared buffers cached Mem: 8162380 7843676 318704 0 95632 5970892 -/+ buffers/cache: 1777152 6385228 Swap: 8032492 23560 8008932 Thanks, Yu Yu Watanabe さんは書きました: </pre> Yu Watanabe 2012-05-23T02:09:30 http://permalink.gmane.org/gmane.comp.db.mysql.general/110048 <pre>Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU : Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read "Hiperformance Mysql vol.2" , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe </pre> Yu Watanabe 2012-05-23T02:07:08 http://permalink.gmane.org/gmane.comp.db.mysql.general/110047 <pre>To shrink ibdata1: 1. Dump everything 2. Stop mysql 3. change to innodb_file_per_table = 1 4. change allocation in my.cnf (my.ini) to something smaller, say 50M,AUTOEXTEND 5. remove (rm / delete) ibdata1 6. restart mysql 7. reload data Step 3: innodb_file_per_table gives you more fine-grained control -- you can rebuild (ALTER TABLE) individual tables, thereby giving back unused space to the OS. Step 4: ibdata1 is still needed, but will probably not grow nearly as much once you have file_per_table. </pre> Rick James 2012-05-22T16:28:22 http://permalink.gmane.org/gmane.comp.db.mysql.general/110046 <pre>Okay, my mistake. I should write precisely when communicating with precise people. :-) What I meant was, dumping and importing is the "common knowledge" way of "virtually" shrinking innodb files. So, now that I've conceded the meta-argument, what do you think of the linked procedure for reducing innodb files? On 22 May 12, at 06:40, Claudio Nanni wrote: ---------------- No man is so foolish but he may sometimes give another good counsel, and no man so wise that he may not easily err if he takes no other counsel than his own. He that is taught only by himself has a fool for a master. -- Ben Johnson :::: Jan Steinman, EcoReality Co-op :::: </pre> Jan Steinman 2012-05-22T16:23:38 http://permalink.gmane.org/gmane.comp.db.mysql.general/110045 <pre>Despite the conventional wisdom, converting to innodb_file_per_table will not necessarily help you. It depends on your situation. If most of your growth is in a single table, you will only have transferred the problem from the ibdata1 file to a new file. The ibdata1 file may also continue to grow, since innodb uses it for several kinds of temporary storage such as the insert buffer and the undo logs (AKA "rollback segment"). Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139   Fax: 515.281.6137 Email:  Kay.Rozeboom&lt; at &gt;iowa.gov </pre> Rozeboom, Kay [DAS] 2012-05-22T16:21:34 http://permalink.gmane.org/gmane.comp.db.mysql.general/110044 <pre>Jan, that's not common wisdom, Innodb datafiles ***never*** shrink, that in the blog from 22th of May is a workaround, one of the many. If you ask my my favourite is to use a stand by instance and work on that. Claudio 2012/5/22 Jan Steinman &lt;Jan&lt; at &gt;bytesmiths.com&gt; </pre> Claudio Nanni 2012-05-22T13:40:53 http://permalink.gmane.org/gmane.comp.db.mysql.general/110043 <pre>----- Original Message ----- In brief: convert all your tables to myisam, delete ibdatafile during a restart, convert tables back to compressed innodb. My first thought is mentioned, but "will be adressed in next post": you lose your relations. I gotta run now, but I wonder what other incompatibilities between the engines one might run into. For simple databases this would probably work, though. </pre> Johan De Meersman 2012-05-22T13:30:03 http://permalink.gmane.org/gmane.comp.db.mysql.general/110042 <pre> That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... "Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump." http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required ---------------- Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid :::: Jan Steinman, EcoReality Co-op :::: </pre> Jan Steinman 2012-05-22T12:27:49 http://permalink.gmane.org/gmane.comp.db.mysql.general/110041 <pre>or it could be that your buffer size is too small, as mysql is spending lot of CPU time for compress and uncompressing On Tue, May 22, 2012 at 5:45 PM, Ananda Kumar &lt;anandkl&lt; at &gt;gmail.com&gt; wrote: </pre> Ananda Kumar 2012-05-22T12:16:49 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.mysql.general