Re: Many Read and Writes...
On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187| This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 31 days, processed 1,373,610,821 queries (496/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. -James On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
On Tue, Sep 02, 2003 at 10:58:03AM -0700, James Kelty wrote: Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. Since you didn't answer the other questions, I'm going to do some guessing here... I'll guess that you have a lot of data and that MySQL hasn't allocated enough memory for its key buffer. That means it hitting the disk more often than it needs to and is slowing things down. I'd suggest setting up a my.cnf file with a larger key buffer to test performance. You might look at mytop, since it'll show your key buffer hit percentage: http://jeremy.zawodny.com/mysql/mytop/ See the sample my.cnf files that come with MySQL. One of them will likely be a good starting point for you. Jeremy On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187| This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,373,779,139 queries (496/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... -James On Tue, 2003-09-02 at 11:06, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:58:03AM -0700, James Kelty wrote: Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. Since you didn't answer the other questions, I'm going to do some guessing here... I'll guess that you have a lot of data and that MySQL hasn't allocated enough memory for its key buffer. That means it hitting the disk more often than it needs to and is slowing things down. I'd suggest setting up a my.cnf file with a larger key buffer to test performance. You might look at mytop, since it'll show your key buffer hit percentage: http://jeremy.zawodny.com/mysql/mytop/ See the sample my.cnf files that come with MySQL. One of them will likely be a good starting point for you. Jeremy On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,373,779,139 queries (496/sec. avg) -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
On Tue, Sep 02, 2003 at 11:23:57AM -0700, James Kelty wrote: Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... Sure. First you need to figure out where MySQL is storing your data files. You can find the value of datadir in the output of SHOW VARIABLES. In that directory, you'll see a sub-directory for each database. And each MyIAM table is composed of three files: table.MYI - indexes table.MYD - data table.frm - table definition Find out how large your various indexs are by doing something like: du -sk *.MYI Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,375,019,175 queries (496/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Ahhh! Ok, yeah the index file was 1.0k and the data file was 8.6M. -James On Tue, 2003-09-02 at 12:17, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 11:23:57AM -0700, James Kelty wrote: Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... Sure. First you need to figure out where MySQL is storing your data files. You can find the value of datadir in the output of SHOW VARIABLES. In that directory, you'll see a sub-directory for each database. And each MyIAM table is composed of three files: table.MYI - indexes table.MYD - data table.frm - table definition Find out how large your various indexs are by doing something like: du -sk *.MYI Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Hi, If the index file is just 1k (the same size as an EMPTY table!), it sounds like you don't have any indexes. The 8.6MB table is probably at least a few thousand rows, right? Well, if all your queries are scanning the whole table, that would cause a few Table_locks_waited! :-) In order to help you add indexes, we will need the output of SHOW CREATE TABLE your_table; for your table(s). Also need examples of queries that you're running. Matt - Original Message - From: James Kelty Sent: Tuesday, September 02, 2003 2:23 PM Subject: Re: Many Read and Writes... Ahhh! Ok, yeah the index file was 1.0k and the data file was 8.6M. -James On Tue, 2003-09-02 at 12:17, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 11:23:57AM -0700, James Kelty wrote: Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... Sure. First you need to figure out where MySQL is storing your data files. You can find the value of datadir in the output of SHOW VARIABLES. In that directory, you'll see a sub-directory for each database. And each MyIAM table is composed of three files: table.MYI - indexes table.MYD - data table.frm - table definition Find out how large your various indexs are by doing something like: du -sk *.MYI Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
