Re: Doubts tuning MySQL Percona Server 5.5

[Johan De Meersman]

- Original Message -

> From: "Antonio Fernández Pérez" 
> Subject: Re: Doubts tuning MySQL Percona Server 5.5

> ​I was checking MySQL performance ... Sometimes my database could be working
> slow. I have some queries that spend 9-10 seconds updating some columns by
> primary key. I'm not sure if is a data base problem ...

If the same query is sometimes OK and sometimes not, that's usually a 
consequence of varying load, or possibly rushes on various resources. Those are 
typically things that are, honestly, rather hard to figure out over email. The 
Percona boys have some rather good blog posts and tutorials that may be of 
interest. 

Do an explain of the naughty queries, if the explain comes up good, there's 
going to be an underlying cause. 

> Moreover, I have checked tuning scripts and appear these variables.

Tuning scripts are a good first look, but they're just stupid little things, 
they have no idea about the baseline performance for your environment. They 
also mostly don't look at performance over time, they just see an average from 
start of server until now - which is obviously mostly useless if you have 
several months of uptime. 

> InnoDB log waits is 103; innodb_log_buffer_size is 8M --> Maybe the next 
> best value could be 16M? 

8M is not a bad value, but you'd have to have a look at how much logging you 
push to disk, and how fast that goes. Incidentally, as this is a theme in your 
questions, you don't always have to double the values :-) 

> Table cache hit rate is 12%; Open_tables is 627; table_open_cache is 1024 

Well, the table cache isn't full, so I suspect not. Keep an eye on 
Opened_tables - that tells you how many tables have been opened since service 
start, so it shouldn't increase dramatically once it's up to speed. 

> Key buffer hit rate is 93.7%; I have some queries that not using indexes .. 

I keep coming back to the same question: why do you think that's a bad number? 
:-) 

Have a look at wether you can add indexes or otherwise optimise those queries, 
but it's perfectly possible and acceptable if that's not possible - maybe it's 
possible to offload the "hard" queries to a separate slave? Optimise in 
function of your environment. Only your can define what constitutes acceptable 
performance in your environment. 

> join_buffer_size is 4M --> Next best value? Maybe 8M and then check it again? 

I strongly recommend not touching those at all - oftentimes, those kind of 
variables either don't do quite what you think at first glance, or are part of 
a more complex system. 

Specifically for the join_buffer_size, note that: 

* it is the MINIMUM that gets allocated, wether or not it's needed 
* it is not allocated per-session, but PER-JOIN, so a single complex query 
may allocate several. 

So, that means that even the smallest query that needs a join buffer will 
allocate 4M, and while it depends on your environment, it's very probably that 
you have more small queries than large ones :-) 

Keep it small by default, and if you know a query is going to need big buffers, 
you can still set it larger as a session variable when you need it. 

The best advice I can give you is to set up Munin, Cacti or another tool to 
monitor server status and performance over time; that way you will get a 
baseline for what's "normal"; see any behaviour that deviates from the 
baseline, *and* can meaningfully see the impact over time of any changes you 
make. 

/johan 

-- 
Unhappiness is discouraged and will be corrected with kitten pictures. 

Re: Doubts tuning MySQL Percona Server 5.5

[Antonio Fernández Pérez]

​Hi,

I was checking MySQL performance ... Sometimes my database could be working
slow. I have some queries that spend 9-10 seconds updating some columns by
primary key. I'm not sure if is a data base problem ...

Moreover, I have checked tuning scripts and appear these variables.

Good sentence!

Thanks in advance.

Regards,

Antonio.​

Re: Doubts tuning MySQL Percona Server 5.5

[Johan De Meersman]

I'm missing something rather essential in your mail... are you actually 
experiencing performance problems, or are you just looking at variables and 
randomly deciding you don't like their value? 

Always remember the golden rule: if it ain't broken, don't fix it. 

On July 4, 2014 8:00:31 PM CEST, "Antonio Fernández Pérez" 
 wrote:
>Hi list,
>
>I have some doubts adjusting some MySQL variables.
>​ I have checked MySQL status and maybe I should to increase some
>variables
>...
>
>For example:
>InnoDB log waits is 103; innodb_log_buffer_size is 8M --> Maybe the
>next
>best value could be 16M?
>Table cache hit rate is 12%; Open_tables is 627; table_open_cache is
>1024
>--> Maybe the next best value could be 2048?
>Key buffer hit rate is 93.7%; I have some queries that not using
>indexes ..
>join_buffer_size is 4M --> Next best value? Maybe 8M and then check it
>again?
>
>That's all.
>
>I hope your advices.
>
>Regards,
>
>Antonio.

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Doubts tuning MySQL Percona Server 5.5

[Antonio Fernández Pérez]

Hi list,

I have some doubts adjusting some MySQL variables.
​ I have checked MySQL status and maybe I should to increase some variables
...

For example:
InnoDB log waits is 103; innodb_log_buffer_size is 8M --> Maybe the next
best value could be 16M?
Table cache hit rate is 12%; Open_tables is 627; table_open_cache is 1024
--> Maybe the next best value could be 2048?
Key buffer hit rate is 93.7%; I have some queries that not using indexes ..
join_buffer_size is 4M --> Next best value? Maybe 8M and then check it
again?

That's all.

I hope your advices.

Regards,

Antonio.

Re: Tuning mysql

[Michael Dykman]

Good advice, all of it.  What hasn't been said and should be noted: in
most cases, the bottleneck is the queries themselves.  You will
generally get a lot more boost from tuning those than from any
configuration tweaking (excepting the pathological cases).

 - michael dykman


On Thu, Feb 9, 2012 at 10:52 AM, Giovanni Bechis  wrote:
> Grant  wrote:
>> I'm running mysql on Gentoo with 4GB RAM and I'm wondering if I should
>> change any settings.  I'm using mysql with a website on the same
>> server so I have skip-networking, and I increased key_buffer and
>> innodb_buffer_pool_size from 16M to 256M.  Everything else is default.
>> Should I consider changing these or any other settings?
>>
> pt-variable-advisor from percona-toolkit 
> (http://www.percona.com/downloads/percona-toolkit/2.0.3/)
>  Giovanni
> --
> /*
>  * SnB - Hosting and software solutions
>  * http://www.snb.it
>  */
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Tuning mysql

[Giovanni Bechis]

Grant  wrote:
> I'm running mysql on Gentoo with 4GB RAM and I'm wondering if I should
> change any settings.  I'm using mysql with a website on the same
> server so I have skip-networking, and I increased key_buffer and
> innodb_buffer_pool_size from 16M to 256M.  Everything else is default.
> Should I consider changing these or any other settings?
> 
pt-variable-advisor from percona-toolkit 
(http://www.percona.com/downloads/percona-toolkit/2.0.3/)
 Giovanni
-- 
/*
 * SnB - Hosting and software solutions
 * http://www.snb.it
 */


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Tuning mysql

[Luis Motta Campos]

On 7 Feb 2012, at 00:55, Grant wrote:

> I'm running mysql on Gentoo with 4GB RAM and I'm wondering if I should
> change any settings.  I'm using mysql with a website on the same
> server so I have skip-networking, and I increased key_buffer and
> innodb_buffer_pool_size from 16M to 256M.  Everything else is default.
> Should I consider changing these or any other settings?

Grant, 

What I usually recommend about database tuning:

1. Visibility.
   There's no way you can see if your changes are good or bad for performance 
if you don't have a waterline and visibility. I recommend you to pick your 
favourite performance graphing tool and ensure we have good graphs available 
for all the most important key performance indicators that you can think of. 
The more graphs, the easier it gets to spot bottlenecks and understand 
root-causes of performance issues.

2. Monogamy
   Tuning MySQL databases that mix MyISAM and InnoDB storage engines is hard 
and tricky. If you have the chance, I strongly recommend you choosing one 
storage engine and sticking to it above everything else. This might sound like 
a hard choice to make in the beginning but it will pay you back with good 
dividends later on, both in improved performance and lower resource consumption 
than the equivalent databases with hybrid storage engine options.

3. Control
   Before you start making changes to your configuration, I strongly recommend 
you to version-control it. I used to use and recommend RCS for this, but Git is 
a more modern, yet flexible and powerful version control to which I've feel in 
love. Version controlling your changes gives you control over your 
configuration and helps determining what changes happened in which order and 
when. This helps you pin-pointing results of your changes on your graphs and 
correlating what you do with how the server reacts. 

4. Logs
   Make sure you enable all the information you can about your slow-query log, 
this is an essential part of detecting query and schema related bottlenecks. 
Here at the office we use Percona Patched MySQL databases, and I usually enable 
all the Percona-provided extensions to the slow query log.

5. Work Holistically
   Database Tuning isn't an isolated process. You should start from a "best 
guess" configuration and work in cycles towards a configuration that satisfies 
your needs without compromising more resources than you have available. 
Remember, there's no point in adjusting a couple of database variables without 
understanding what it means for the application and for the queries / schema. 
Make sure you include your developers (if you're not one of them) and take into 
consideration all the information you have at hand.

I hope this helps with getting started. 

Good luck, and kind regards.
--
Luis Motta Campos
is a DBA, Foodie, and Photographer


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Tuning mysql

[Reindl Harald]

Am 07.02.2012 00:55, schrieb Grant:
> I'm running mysql on Gentoo with 4GB RAM and I'm wondering if I should
> change any settings.  I'm using mysql with a website on the same
> server so I have skip-networking, and I increased key_buffer and
> innodb_buffer_pool_size from 16M to 256M.  Everything else is default.
>  Should I consider changing these or any other settings?

https://github.com/rackerhacker/MySQLTuner-perl




signature.asc
Description: OpenPGP digital signature

RE: Tuning mysql concurrent running threads

[nabila.salmi]

Hello Mike,

Thank you very much for your answer. 
MySQl is on Ubuntu server and i tried thread_concurrency=x, it doesn't work.


Do you mean, i should configure MySQL with INNODB engine ? How to do that ?
Thanks again !

Nabila

-Message d'origine-
De : mos [mailto:mo...@fastmail.fm] 
Envoyé : mardi 8 septembre 2009 17:21
À : mysql@lists.mysql.com
Objet : Re: Tuning mysql concurrent running threads

If you are talking about MyISAM tables, in my.cnf you can set the

thread_concurrency=x

where x=2*# of CPU's

but this unfortunately only works on Solaris and not on Linux. It may work on 
Windows, I'm not sure.
InnoDb has their own innodb_thread_concurrency  variable that you can see the 
value of using SHOW INNODB STATUS.

If you want true multi-threading then you may have to use MySQL Clusters. 
See http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-programs-ndbmtd.html

Mike

At 02:39 AM 9/8/2009, nabila.sa...@orange-ftgroup.com wrote:


>  Hello,
>
>I have a mysql server where thousands of users connect. I configured 
>max connections but i didn't found how to configure concurrent raunning 
>threads. When i do show status, always RUNNING THREADS=1, even several 
>threads are pending.
>
>How to change this please ?
>
>Anyone can help me please ?
>
>Thank you very much !
>
>Nabila


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=nabila.sa...@orange-ftgroup.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: Tuning mysql concurrent running threads

[mos]

If you are talking about MyISAM tables, in my.cnf you can set the

thread_concurrency=x

where x=2*# of CPU's

but this unfortunately only works on Solaris and not on Linux. It may work 
on Windows, I'm not sure.
InnoDb has their own innodb_thread_concurrency  variable that you can see 
the value of using SHOW INNODB STATUS.


If you want true multi-threading then you may have to use MySQL Clusters. 
See http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-programs-ndbmtd.html


Mike

At 02:39 AM 9/8/2009, nabila.sa...@orange-ftgroup.com wrote:



 Hello,

I have a mysql server where thousands of users connect. I configured max 
connections but i didn't found how to configure concurrent raunning 
threads. When i do show status, always RUNNING THREADS=1, even several 
threads are pending.


How to change this please ?

Anyone can help me please ?

Thank you very much !

Nabila



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Tuning mysql concurrent running threads

[nabila.salmi]

 Hello, 

I have a mysql server where thousands of users connect. I configured max 
connections but i didn't found how to configure concurrent raunning threads. 
When i do show status, always RUNNING THREADS=1, even several threads are 
pending. 

How to change this please ? 

Anyone can help me please ? 

Thank you very much ! 

Nabila 

Re: Tuning MySQL

[Chris]

Jürgen Ladstätter wrote:

hi all,

 


i developed a programm which needs my external mysql database server. now i
have to transfer a lot of data and i have todo a lot of select queues. how
can i tune that, so that it would be faster? in my monitoring system i saw,
that CPU load is 0, the only thing thats working when my program collects
data is the PROC


"Tune" which bit - the transfer or the select queries?


The transfer can be sped up (if it's across a network) by compressing 
the data before sending it.


Use the extended insert syntax (which will speed up the import and 
reduce the size of the import at the same time).

http://dev.mysql.com/doc/refman/5.1/en/insert.html

or use load data infile:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Disable indexes before importing and re-enable them after it has 
finished loading:

ALTER TABLE tbl_name DISABLE KEYS;
..load data here..
ALTER TABLE tbl_name ENABLE KEYS;


The select queries? Turn on mysql logging and look at the queries you 
are running. Make sure they are indexed properly.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Tuning MySQL

[Jürgen Ladstätter]

hi all,

 

i developed a programm which needs my external mysql database server. now i
have to transfer a lot of data and i have todo a lot of select queues. how
can i tune that, so that it would be faster? in my monitoring system i saw,
that CPU load is 0, the only thing thats working when my program collects
data is the PROC

 

Mit freundlichen Grüßen

Jürgen Ladstätter ( [EMAIL PROTECTED] )

___

P.I.C.S. EDV GmbH.
A-5020 Salzburg, Landsturmstraße 18
Tel.: +43 (662) 455467
Fax.: +43 (662) 455468

Web:   http://www.pics.co.at
E-Mail: [EMAIL PROTECTED]
Ihr Partner für Internet, Computer und Software
___

 

Re: Total newb at performance tuning mysql

[Bruce Dembecki]

One of our engineers first installed MySQL on one of our Sun boxes  
which was doing nothing more than MySQL... It seems we also put it on  
the server and turned it on... it behaved very badly. Essentially  
when we started to investigate MySQL and find out if we could use it  
we discovered that our Sun box with four processors and 4Gbytes of  
ram was running MySQL in 64M of memory... it's an easy mistake to  
make, and the lesson here is that out of the box (as it were) MySQL  
settings are a little on the low side for performance... but work  
well for a shared environment where you may have web server, mail  
server and more all running on the same box. If you want MySQL to  
sing... you are going to have to do a lot of tuning.


On the table_cache issue... We have about 40 tables per database, and  
some of our servers have 30 databases. Our servers have as many as  
500 connections... one server at random which has an uptime of 60  
days shows:


mysql1 (none): show status like 'open%_tables';
+---+---+
| Variable_name | Value |
+---+---+
| Open_tables   | 2748  |
| Opened_tables | 3288  |
+---+---+
2 rows in set (0.01 sec)

mysql1 (none): show variables like 'table_cache';
+---+---+
| Variable_name | Value |
+---+---+
| table_cache   | 4096  |
+---+---+
1 row in set (0.01 sec)

So we have a table cache value, but it's clearly on the high side and  
could be lower. Yours at 64 is on the low side.


While I won't ignore table cache as being important, there are many  
many performance tuning things that need to get done to have an  
impact on the server.


Firstly (knowing the type of things you are doing) InnoDB will likely  
be a far better choice for most of your tables than the default  
database engine, myisam. You need to tune the machine to run InnoDB,  
and then convert your data to InnoDB. If you are using MySQL 4.1  
(which I can't remember) I would advise using innodb_file_per_table.  
If you are looking at upgrading to 4.1 I'd do that first before  
switching to innodb_file_per_table... it's a little hard to claim  
back the shared table space after the fact. If not 4.1 then go with  
InnoDB and build a big enough shared table space file set to hold all  
your data with room to spare. We typically build it with 20 2Gbyte  
files... for 40Gbytes of InnoDB table space.


Decide how much memory you have to run MySQL... i the server does  
only MySQL, this is easy... if it's also a web server running Apache  
and so on, then you have to decide the mix. Assuming MySQL only give  
InnoDB 80% of the server's total memory, up to certain limits with 32  
bit operating systems and the like... For OS X we found these are  
pretty much the magic numbers for max values if you have more than  
2Gbytes of ram but can't handle 64 bit:


 innodb_buffer_pool_size=1850M
 innodb_additional_mem_pool_size=256M
innodb_log_files_in_group=2
innodb_log_file_size=250M
innodb_log_buffer_size=20M
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=30

Once you convert everything to InnoDB the regular MySQL buffers have  
less importance, but should still have some values.


InnoDB or not the query_cache is a good thing, but don't set it too  
high... We are at 128Mbytes and that's a little higher than we  
need... it appears we could live in under 64Mbytes, and our query  
cache handles about 25% of our queries... properly set it's a good  
thing.


So... most likely switching to InnoDB will improve performance...  
Managing all your memory settings and caches so that the ones that  
matter have values that will help will make a great deal of  
difference... of course to do this you'll need to make a my.cnf file  
and install it where MySQL will look for it.


Also important for tuning is watching the slow queries, finding out  
if there are moe things you can do with indexes, or if there are  
other ways to optimize the queries. Turn on the slow query log...  
leave it set to the default 10 seconds... find out what queries are  
running longer than 10 seconds and figure out how to optimize them...  
changing indexes, changing the query etc... Once you have worked that  
out and your slow query log gets few hits, reduce it to 5 seconds and  
work through those queries... again reduce it further as you work out  
the performance issues and you'll find that you are streaming along.


There's a lot more that can be done with specific memory settings and  
so on... but I think I've given you a handful of things to get  
started on, and you can come back for more when you have made some  
headway on this part.


Best Regards, Bruce

On Sep 7, 2005, at 4:28 PM, Scott Haneda wrote:


Unless I am totally off base here, the mysql docs tell very little in
regards to how to performance tune mysql.

So far I have found this article to help:

Re: Total newb at performance tuning mysql

[Terence]

Scott Haneda wrote:

Unless I am totally off base here, the mysql docs tell very little in
regards to how to performance tune mysql.

So far I have found this article to help:

But it still leaves me with a ton of questions.

For starters, and don't laugh, I just installed mysql and let it run,
started adding sites to it and such.  The machine has 1 gig of ram in in,
but at times, I could ask it were a little snappier.  It is a 1.2Ghz
machine.

First thing I did last night was to try to get a hnalde on this.  From what
I can tell, I have no my.cnf file in place, so there must be some default
settings that are compiled in.  I set up a cron job to run the following:

show variables like 'table_cache'
show status like 'open%_tables'

I have been running this once a minute for a day now, how long should I run
it to get an idea of what I need to change to better suit my servers load
usage?

First entry after a mysql restart:
Wed Sep 7 03:18:00 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 1050
---

Last entry as of now:
---
Wed Sep 7 16:27:01 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 4407

So it looks like Opened_tables is going to increase forever at the rate I
have it, which I am guessing is not a good thing, but not sure what I need
to do to fix this.

I am really looking for some pretty detailed docs on exactly what I can do
in my.cnf to make this work out better.

Thanks again, and if there are any questions that would help me get a better
answer, please let me know.



Try to find a book called High Performance MySQL from Derek J. Balling & 
Jeremy Zawodny (the guy from Yahoo). It's an interesting and useful book 
which skips all the basic stuff and gets you into performance tuning.

ISBN : 0-596-00306-4

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Total newb at performance tuning mysql

[Daniel]

Instead of your cron job, I suggest http://codenode.com/mysqlreport
The mysqlreport documentation (http://codenode.com/mysqlreportdoc)
is a pretty baseline introduction to comprehending server performance
via a mysqlreport report, and a little bit about what you can do for 
certain issues.


-Daniel

Scott Haneda wrote:


Unless I am totally off base here, the mysql docs tell very little in
regards to how to performance tune mysql.

So far I have found this article to help:

But it still leaves me with a ton of questions.

For starters, and don't laugh, I just installed mysql and let it run,
started adding sites to it and such.  The machine has 1 gig of ram in in,
but at times, I could ask it were a little snappier.  It is a 1.2Ghz
machine.

First thing I did last night was to try to get a hnalde on this.  From what
I can tell, I have no my.cnf file in place, so there must be some default
settings that are compiled in.  I set up a cron job to run the following:

show variables like 'table_cache'
show status like 'open%_tables'

I have been running this once a minute for a day now, how long should I run
it to get an idea of what I need to change to better suit my servers load
usage?

First entry after a mysql restart:
Wed Sep 7 03:18:00 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 1050
---

Last entry as of now:
---
Wed Sep 7 16:27:01 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 4407

So it looks like Opened_tables is going to increase forever at the rate I
have it, which I am guessing is not a good thing, but not sure what I need
to do to fix this.

I am really looking for some pretty detailed docs on exactly what I can do
in my.cnf to make this work out better.

Thanks again, and if there are any questions that would help me get a better
answer, please let me know.
 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Total newb at performance tuning mysql

[Gleb Paharenko]

Hello.





> I can tell, I have no my.cnf file in place, so there must be some default



Create your own and the appropriate value for the table_cache. See:

http://dev.mysql.com/doc/mysql/en/option-files.html

http://dev.mysql.com/doc/mysql/en/program-variables.html







Scott Haneda <[EMAIL PROTECTED]> wrote:

> Unless I am totally off base here, the mysql docs tell very little in

> regards to how to performance tune mysql.

> 

> So far I have found this article to help:

> 

> But it still leaves me with a ton of questions.

> 

> For starters, and don't laugh, I just installed mysql and let it run,

> started adding sites to it and such.  The machine has 1 gig of ram in in,

> but at times, I could ask it were a little snappier.  It is a 1.2Ghz

> machine.

> 

> First thing I did last night was to try to get a hnalde on this.  From what

> I can tell, I have no my.cnf file in place, so there must be some default

> settings that are compiled in.  I set up a cron job to run the following:

> 

> show variables like 'table_cache'

> show status like 'open%_tables'

> 

> I have been running this once a minute for a day now, how long should I run

> it to get an idea of what I need to change to better suit my servers load

> usage?

> 

> First entry after a mysql restart:

> Wed Sep 7 03:18:00 PDT 2005

> table_cache 64

> Open_tables 64 Opened_tables 1050

> ---

> 

> Last entry as of now:

> ---

> Wed Sep 7 16:27:01 PDT 2005

> table_cache 64

> Open_tables 64 Opened_tables 4407

> 

> So it looks like Opened_tables is going to increase forever at the rate I

> have it, which I am guessing is not a good thing, but not sure what I need

> to do to fix this.

> 

> I am really looking for some pretty detailed docs on exactly what I can do

> in my.cnf to make this work out better.

> 

> Thanks again, and if there are any questions that would help me get a better

> answer, please let me know.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Total newb at performance tuning mysql

[Scott Haneda]

Unless I am totally off base here, the mysql docs tell very little in
regards to how to performance tune mysql.

So far I have found this article to help:

But it still leaves me with a ton of questions.

For starters, and don't laugh, I just installed mysql and let it run,
started adding sites to it and such.  The machine has 1 gig of ram in in,
but at times, I could ask it were a little snappier.  It is a 1.2Ghz
machine.

First thing I did last night was to try to get a hnalde on this.  From what
I can tell, I have no my.cnf file in place, so there must be some default
settings that are compiled in.  I set up a cron job to run the following:

show variables like 'table_cache'
show status like 'open%_tables'

I have been running this once a minute for a day now, how long should I run
it to get an idea of what I need to change to better suit my servers load
usage?

First entry after a mysql restart:
Wed Sep 7 03:18:00 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 1050
---

Last entry as of now:
---
Wed Sep 7 16:27:01 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 4407

So it looks like Opened_tables is going to increase forever at the rate I
have it, which I am guessing is not a good thing, but not sure what I need
to do to fix this.

I am really looking for some pretty detailed docs on exactly what I can do
in my.cnf to make this work out better.

Thanks again, and if there are any questions that would help me get a better
answer, please let me know.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL

[Devananda]

If you can, I recommend installing "mytop" 
(http://jeremy.zawodny.com/mysql/mytop/) - it has helped me immensely to 
identify which particular queries are putting the heaviest load on the 
server.


>>I have a lot of two column tables consisting
>>of integer primary key and varchar in the second column.
>>I repeatedly search the second column

depending on how you search the second column, different types of 
indexes will help you a LOT. searching on a column with a query like 
"WHERE colname = 'value'", when there is no index on that column, always 
results in a table scan, which is just what you don't want. However, if 
your search is "WHERE colname LIKE '%some_string%'", then an ordinary 
index won't help either - you will need a FULLTEXT index, and you will 
also need to change the query to "WHERE MATCH colname AGAINST 
'%some_string%'".


Good luck!
~Deva



Atle Veka wrote:

Here are two ways to find the queries:

1) 'SHOW FULL PROCESSLIST\G' in the mysql client and taking note of what
queries seem to be taking the most time

2) enable update logging and slow query logging

When you have gathered a list of queries that you want to look into
optimizing, run [in the client]: 'EXPLAIN ' . That will give you
an idea of where index(es) would benefit. Check the manual for EXPLAIN to
decipher the output.


Good luck!

Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Fri, 1 Jul 2005, Siegfried Heintze wrote:



Are there any tools for finding hot spots in one's database? My screen
scraper is maxing out my CPU. I'm thinking I might need some secondary
indexes in some of my tables. I have a lot of two column tables consisting
of integer primary key and varchar in the second column. I repeatedly search
the second column and, if there is no match, return mysql_insertid.


Are there any tools to help me tell which SQL statements are gobbling up my
CPU and disk? I suppose I could blindly put secondary indexes everywhere.

Siegfried








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL

[Atle Veka]

Here are two ways to find the queries:

1) 'SHOW FULL PROCESSLIST\G' in the mysql client and taking note of what
queries seem to be taking the most time

2) enable update logging and slow query logging

When you have gathered a list of queries that you want to look into
optimizing, run [in the client]: 'EXPLAIN ' . That will give you
an idea of where index(es) would benefit. Check the manual for EXPLAIN to
decipher the output.


Good luck!

Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Fri, 1 Jul 2005, Siegfried Heintze wrote:

> Are there any tools for finding hot spots in one's database? My screen
> scraper is maxing out my CPU. I'm thinking I might need some secondary
> indexes in some of my tables. I have a lot of two column tables consisting
> of integer primary key and varchar in the second column. I repeatedly search
> the second column and, if there is no match, return mysql_insertid.
>
>
> Are there any tools to help me tell which SQL statements are gobbling up my
> CPU and disk? I suppose I could blindly put secondary indexes everywhere.
>
> Siegfried
>
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Tuning MySQL

[Siegfried Heintze]

Are there any tools for finding hot spots in one's database? My screen
scraper is maxing out my CPU. I'm thinking I might need some secondary
indexes in some of my tables. I have a lot of two column tables consisting
of integer primary key and varchar in the second column. I repeatedly search
the second column and, if there is no match, return mysql_insertid.


Are there any tools to help me tell which SQL statements are gobbling up my
CPU and disk? I suppose I could blindly put secondary indexes everywhere.

Siegfried


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Tuning MySQL

[Dathan Pattishall]

 

> -Original Message-
> From: Eric Gunnett [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, January 11, 2005 9:36 AM
> To: mysql@lists.mysql.com
> Subject: Tuning MySQL
> 
>   I have a quad processor server, with 4 gigs of memory. 
> It is only running MySQL right now and seems really slow. Can 
> someone give me a few suggestions on optimizing My.cnf file 
> for this system.  We are running 
> mysql-standard-4.0.23-pc-linux-i68, on it. Here is the my.cnf file

1st how many disks are in the server. What is your key cache hit
percentage? This can be figured out by 

Issue SHOW STATUS

Take 

Key_reads/Key_read_requests * 100 give the %


Next look at handler_read_rnd_next

If this number is high then your doing table scans so optimizing your
keys would be better.

Additional comments below.


> 
> # The MySQL server
> [mysqld]
> port= 3306
> socket  = /tmp/mysql.sock
> skip-locking
> key_buffer = 384M

You will prob have to raise the key buffer

> max_allowed_packet = 2M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> myisam_sort_buffer_size = 64M

Add tmp_table_size=16M

> thread_cache = 8

Look at your connection rate if it's high raise the above

> query_cache_size = 32M
> # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8

Thread_concurrency is only a valid option for solaris

> 
> user=mysql
> basedir=/usr/local/mysql
> datadir=/usr2/mysql/data
> max_connections=400
> max_connect_errors=300
> interactive_timeout=2400
> wait_timeout=60
> back_log=100
> #skip-networking
> server-id   = 2
> 
> [isamchk]
> key_buffer = 256M
> sort_buffer_size = 256M
> read_buffer = 2M
> write_buffer = 2M
> 
> [myisamchk]
> key_buffer = 256M
> sort_buffer_size = 256M
> read_buffer = 2M
> write_buffer = 2M
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Tuning MySQL

[Eric Gunnett]

I have a quad processor server, with 4 gigs of memory. It is only 
running MySQL right now and seems really slow. Can someone give me a few 
suggestions on optimizing My.cnf file for this system.  We are running 
mysql-standard-4.0.23-pc-linux-i68, on it. Here is the my.cnf file

# The MySQL server
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

user=mysql
basedir=/usr/local/mysql
datadir=/usr2/mysql/data
max_connections=400
max_connect_errors=300
interactive_timeout=2400
wait_timeout=60
back_log=100
#skip-networking
server-id   = 2

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Tuning MySQL for Large Database

[Jon Drukman]

matt ryan wrote:
 >There is a perl script that comes with MySQL called mysqldumpslow.  
You can just run it on your slow log and it will output summary 
statistics about the slow log.

I saw that in the docs, but I definitly dont want to install perl on a 
production server, I never looked to see if I could do it offline, I 
only have ms boxes, no linux here, none of our techs know linux well 
enough to move to it.

Matt
copy the slow log from the windows box to a linux box and run the script 
there.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL for Large Database

[Egor Egorov]

"Michael Sleman" <[EMAIL PROTECTED]> wrote:


> key_buffer = 384M

Try to enlarge this up to, say, 1G and check it out how that helps. 

> sort_buffer_size = 2M

You may want to enlarge this as well. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Tuning MySQL for Large Database

[matt ryan]

>There is a perl script that comes with MySQL called mysqldumpslow.  
You can just run it on your slow log and it will output summary 
statistics about the slow log.

I saw that in the docs, but I definitly dont want to install perl on a 
production server, I never looked to see if I could do it offline, I 
only have ms boxes, no linux here, none of our techs know linux well 
enough to move to it.

Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Tuning MySQL for Large Database

[Harrison]

Hi,
On Thursday, July 22, 2004, at 01:42  PM, matt ryan wrote:
I've found the slow query log is useless to me, it's 50 meg right now.
Is there a tool that will identify common querys?   I could probably 
come up with some sql's if I load it into a table, but it would take 
quite a while to sort out.
There is a perl script that comes with MySQL called mysqldumpslow.  You 
can just run it on your slow log and it will output summary statistics 
about the slow log.

Another possible solution, is to delete the old one and just use it for 
a few hours to see what is going on right now.

Regards,
Harrison
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Tuning MySQL for Large Database

[matt ryan]

I've found the slow query log is useless to me, it's 50 meg right now.
Is there a tool that will identify common querys?   I could probably 
come up with some sql's if I load it into a table, but it would take 
quite a while to sort out.

I posted a request on the mysql bugtraq to move it to a table instead of 
that raw file, but they closed the request, guess they didnt like that idea

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Tuning MySQL for Large Database

[SGreen]

From: http://dev.mysql.com/doc/mysql/en/Server_status_variables.html

Key_reads 
The number of physical reads of a key block from disk. If Key_reads is 
big, then your key_buffer_size value is probably too small. The cache miss 
rate can be calculated as Key_reads/Key_read_requests. 

Select_full_join 
The number of joins that do not use indexes. If this value is not 0, you 
should carefully check the indexes of your tables. This variable was added 
in MySQL 3.23.25. 

Select_full_range_join 
The number of joins that used a range search on a reference table. This 
variable was added in MySQL 3.23.25. 

Select_range 
The number of joins that used ranges on the first table. (It's normally 
not critical even if this is big.) This variable was added in MySQL 
3.23.25. 

Select_range_check 
The number of joins without keys that check for key usage after each row. 
(If this is not 0, you should carefully check the indexes of your tables.) 
This variable was added in MySQL 3.23.25. 

Select_scan 
The number of joins that did a full scan of the first table. This variable 
was added in MySQL 3.23.25. 

Sort_scan 
The number of sorts that were done by scanning the table. This variable 
was added in MySQL 3.23.25.

So... it's not as bad as it could be ;-) 

I would still check the slow query log to see if there are any common 
queries that could use an index or two. I would also look at changing some 
simple indexes into compound indexes (indices?) as another means of 
speeding things up.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


matt ryan <[EMAIL PROTECTED]> wrote on 07/22/2004 01:17:12 PM:

> Hmm
> 
> I'm guessing my stats arent too good, lots of full table scans, but this 

> is to be expected, my users can query any table by any column, and I 
> cant index all column combinations
> 
> Variable_name 
> Value  
> 
> Select_full_join 
> 0  
> 
> Select_full_range_join 
> 24  
> 
> Select_range 
> 145321  
> 
> Select_range_check 
> 0  
> 
> Select_scan29402 
> Sort_Scan   15360
> Key_reads 37811885
> 
> 
> and on the other big db..
> 
> Variable_name 
> Value  
> 
> Select_full_join 
> 535  
> 
> Select_full_range_join 
> 0  
> 
> Select_range 
> 1098  
> 
> Select_range_check 
> 0  
> 
> Select_scan10443 
> Sort_Scan2464
> Key_reads 20282002
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Fw: Tuning MySQL for Large Database

[matt ryan]

Resend, firefox did not send the way it looked when I typed it!
I'm guessing my stats arent too good, lots of full table scans, but this
is to be expected, my users can query any table by any column, and I
cant index all column combinations
Variable_name   Value
Select_full_join 0
Select_full_range_join 24
Select_range   145321
Select_range_check 0
Select_scan29402
Sort_Scan   15360
Key_reads 37811885
and on the other big db..
Variable_name   Value
Select_full_join   535
Select_full_range_join 0
Select_range   1098
Select_range_check 0
Select_scan10443
Sort_Scan 2464
Key_reads 20282002

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Tuning MySQL for Large Database

[matt ryan]

Hmm
I'm guessing my stats arent too good, lots of full table scans, but this 
is to be expected, my users can query any table by any column, and I 
cant index all column combinations

Variable_name  
Value  

Select_full_join   
0  

Select_full_range_join 
24 

Select_range   
145321 

Select_range_check 
0  

Select_scan29402   
Sort_Scan   15360
Key_reads 37811885

and on the other big db..
Variable_name  
Value  

Select_full_join   
535

Select_full_range_join 
0  

Select_range   
1098   

Select_range_check 
0  

Select_scan10443 
Sort_Scan2464
Key_reads 20282002

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fw: Tuning MySQL for Large Database

[SGreen]

This is part of the results of a SHOW STATUS command. See one of the 
earlier posts for the full list of his settings.

Read These For More:
http://dev.mysql.com/doc/mysql/en/SHOW.html
http://dev.mysql.com/doc/mysql/en/SHOW_STATUS.html
http://dev.mysql.com/doc/mysql/en/Server_status_variables.html

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


matt ryan <[EMAIL PROTECTED]> wrote on 07/22/2004 12:43:55 PM:

> 
> >I went over your data. This is what I noticed first:
> >| Select_full_join | 0|
> >| Select_full_range_join   | 0|
> >| Select_range | 1|
> >| Select_range_check   | 0|
> >| Select_scan  | 301  |
> > 
> >
> 
> What command will provide this data?
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Fw: Tuning MySQL for Large Database

[matt ryan]

I went over your data. This is what I noticed first:
| Select_full_join | 0|
| Select_full_range_join   | 0|
| Select_range | 1|
| Select_range_check   | 0|
| Select_scan  | 301  |
 

What command will provide this data?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fw: Tuning MySQL for Large Database

[SGreen]

I agree with Matthew. A database is a lot like a car. You can have a 
well-built, high-powered vehicle that could do 200mph while still carrying 
15 people (good hardware + good tuning) but if you drive it off-road, 
alone, and in second gear (bad table structure, poor index coverage, poor 
SQL statement optimization, and poor application concurrency) all of that 
capacity and performance do you no good.

I went over your data. This is what I noticed first:
| Select_full_join | 0|
| Select_full_range_join   | 0|
| Select_range | 1|
| Select_range_check   | 0|
| Select_scan  | 301  |

Which implies me that when you needed to combine data between two tables 
(JOINs), you could not use an index 301 of 302 times. I might be reading 
it wrong so I hope the list will correct me if I am mistaken.. 

I also that you are at the point you need to read your slow query log and 
do a lot of EXPLAIN SELECTs to improve your index coverage.

long_query_time = 5
| Slow_queries | 626  |
| Com_select   | 23934|

I read this as: 626 or 23934 queries took more than 5 seconds to complete. 
 At the bare minimum those queries consumed 626x5=3130 query-seconds ( or 
just over 52 query-minutes) to execute. That may be a big part of your 
bottleneck. 

Also, one more thing to worry about in a high-traffic situation. 
Connection bloat:

| Max_used_connections | 78   |

Make sure your code opens its connections AS LATE AS POSSIBLE and closes 
them AS EARLY AS POSSIBLE. Sometimes connections can be pooled, it depends 
on your library and/or the available developer's skills. Many designers 
when building a web-based application (PERL, PHP, ASP, etc) will forget to 
close the connection before the end of the script *on each page*.  Sure, 
the database library may eventually close it for you (as your variables go 
out of scope and get garbage collected) but it is MUCH more efficient to 
ALWAYS close it yourself (or manually return it to the pool). 

In some scripting languages, the garbage collection utilities will go on 
"indefinite hold" during high-traffic conditions. This is generally not 
good for database performance as it means you will have orphaned objects 
(possibly still open database connections and result datasets) hanging 
around on the heap waiting to be recovered.  Implementations vary but you 
can usually help your garbage collectors by manually de-referencing all 
object variables before you exit the script.

(I know you have probably already read this already but...) Here is the 
page in the docs describing all of the system variables:
http://dev.mysql.com/doc/mysql/en/Server_system_variables.html

You may want to re-examine all of your "buffer" settings to make sure you 
are getting the most out of your available memory.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Matthew McNicol <[EMAIL PROTECTED]> wrote on 07/22/2004 03:25:29 
AM:

> 
> My 5 cents... tuning the MySQL database config file is half a solution. 
> In my experience optimizing the application code and table/index 
> structure delivers performance results.
> 
> 
> 
> Michael Sleman wrote:
> 
> > Hello,
> > 
> > We're running 1 web server (apache 2 & php) / 1 dedicated DB server
> > (MySQL 4.0.20-standard) and are experiencing serious performance 
issues
> > on the DB during some load testing.
> > 
> > Hardware on both
> > Dual Xeon 2.8GHz, 2GB RAM
> > 
> > The database size is a little under 1 GB.
> > 
> > Naturally, we started taking a look at MySQL config and have gotten 
some
> > significant speed improvements but the app is really slow and queries
> > take 10secs on average to execute. From my past experiences with 
MySQL,
> > I still think the DB can do much better. 
> > 
> > Any recommendations for additional configuration changes?
> > 
> > The stats during a 15 minute load test was up the roof at:
> > Load Average: 12 avg, 26 peak, 
> > MySQL processes: 20 avg, 80 peak 
> > Memory usage was 280MB used, 1.4GB cached, 122MB buffers, 216MB FREE.
> > 
> > I'm attaching my.cnf, show status, show variables from a 15 minute 
load
> > test.
> > 
> > Thanks,
> > -Michael
> > 
--8<--- clipped for space 8<---

Re: Tuning MySQL for Large Database

[Matthew McNicol]

My 5 cents... tuning the MySQL database config file is half a solution. 
In my experience optimizing the application code and table/index 
structure delivers performance results.


Michael Sleman wrote:
Hello,
We're running 1 web server (apache 2 & php) / 1 dedicated DB server
(MySQL 4.0.20-standard) and are experiencing serious performance issues
on the DB during some load testing.
Hardware on both
Dual Xeon 2.8GHz, 2GB RAM
The database size is a little under 1 GB.
Naturally, we started taking a look at MySQL config and have gotten some
significant speed improvements but the app is really slow and queries
take 10secs on average to execute. From my past experiences with MySQL,
I still think the DB can do much better. 

Any recommendations for additional configuration changes?
The stats during a 15 minute load test was up the roof at:
Load Average: 12 avg, 26 peak, 
MySQL processes: 20 avg, 80 peak 
Memory usage was 280MB used, 1.4GB cached, 122MB buffers, 216MB FREE.

I'm attaching my.cnf, show status, show variables from a 15 minute load
test.
Thanks,
-Michael

===
my.cnf
===
[mysqld]
log_slow_queries = /var/log/slow-queries.log
long_query_time = 5
log-long-format
skip-locking
key_buffer = 384M
max_allowed_packet = 16M
max_connections = 400
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
thread_concurrency = 8
query_cache_size = 32M
log-bin
max_binlog_size = 100M
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 384M
sort_buffer = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 384M
sort_buffer = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout



SHOW STATUS:

+--+--+
| Variable_name| Value|
+--+--+
| Aborted_clients  | 0|
| Aborted_connects | 9|
| Bytes_received   | 18494586 |
| Bytes_sent   | 50916200 |
| Com_admin_commands   | 0|
| Com_alter_table  | 0|
| Com_analyze  | 0|
| Com_backup_table | 0|
| Com_begin| 0|
| Com_change_db| 201360   |
| Com_change_master| 0|
| Com_check| 0|
| Com_commit   | 0|
| Com_create_db| 0|
| Com_create_function  | 0|
| Com_create_index | 0|
| Com_create_table | 0|
| Com_delete   | 0|
| Com_delete_multi | 0|
| Com_drop_db  | 0|
| Com_drop_function| 0|
| Com_drop_index   | 0|
| Com_drop_table   | 0|
| Com_flush| 0|
| Com_grant| 0|
| Com_ha_close | 0|
| Com_ha_open  | 0|
| Com_ha_read  | 0|
| Com_insert   | 1724 |
| Com_insert_select| 128  |
| Com_kill | 0|
| Com_load | 0|
| Com_load_master_data | 0|
| Com_load_master_table| 0|
| Com_lock_tables  | 0|
| Com_optimize | 0|
| Com_purge| 0|
| Com_rename_table | 0|
| Com_repair   | 0|
| Com_replace  | 0|
| Com_replace_select   | 0|
| Com_reset| 0|
| Com_restore_table| 0|
| Com_revoke   | 0|
| Com_rollback | 0|
| Com_savepoint| 0|
| Com_select   | 23934|
| Com_set_option   | 0|
| Com_show_binlog_events   | 0|
| Com_show_binlogs | 0|
| Com_show_create  | 0|
| Com_show_databases   | 0|
| Com_show_fields  | 0|
| Com_show_grants  | 0|
| Com_show_keys| 0|
| Com_show_logs| 0|
| Com_show_master_status   | 0|
| Com_show_new_master  | 0|
| Com_show_open_tables | 0|
| Com_show_processlist | 0|
| Com_show_slave_hosts | 0|
| Com_show_slave_status| 0|
| Com_show_status  | 1|
| Com_show_innodb_status   | 0|
| Com_show_tables  | 0|
| Com_show_variables   | 0|
| Com_slave_start  | 0|
| Com_slave_stop   | 0|
| Com_truncate | 0|
| Com_unlock_tables| 0|
| Com_update   | 1921 |
| Connections  | 1579 |
| Created_tmp_disk_tables  | 0|
| Created_tmp_tables   | 505  |
| Created_tmp_files| 0|
| Delayed_insert_threads   | 0|
| Delayed_writes   | 0|
| Delayed_errors   | 0 

Tuning MySQL for Large Database

[Michael Sleman]

Hello,

We're running 1 web server (apache 2 & php) / 1 dedicated DB server
(MySQL 4.0.20-standard) and are experiencing serious performance issues
on the DB during some load testing.

Hardware on both
Dual Xeon 2.8GHz, 2GB RAM

The database size is a little under 1 GB.

Naturally, we started taking a look at MySQL config and have gotten some
significant speed improvements but the app is really slow and queries
take 10secs on average to execute. From my past experiences with MySQL,
I still think the DB can do much better. 

Any recommendations for additional configuration changes?

The stats during a 15 minute load test was up the roof at:
Load Average: 12 avg, 26 peak, 
MySQL processes: 20 avg, 80 peak 
Memory usage was 280MB used, 1.4GB cached, 122MB buffers, 216MB FREE.

I'm attaching my.cnf, show status, show variables from a 15 minute load
test.

Thanks,
-Michael



===
my.cnf
===
[mysqld]
log_slow_queries = /var/log/slow-queries.log
long_query_time = 5
log-long-format

skip-locking
key_buffer = 384M
max_allowed_packet = 16M
max_connections = 400
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
thread_concurrency = 8
query_cache_size = 32M

log-bin
max_binlog_size = 100M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 384M
sort_buffer = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 384M
sort_buffer = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout






SHOW STATUS:


+--+--+
| Variable_name| Value|
+--+--+
| Aborted_clients  | 0|
| Aborted_connects | 9|
| Bytes_received   | 18494586 |
| Bytes_sent   | 50916200 |
| Com_admin_commands   | 0|
| Com_alter_table  | 0|
| Com_analyze  | 0|
| Com_backup_table | 0|
| Com_begin| 0|
| Com_change_db| 201360   |
| Com_change_master| 0|
| Com_check| 0|
| Com_commit   | 0|
| Com_create_db| 0|
| Com_create_function  | 0|
| Com_create_index | 0|
| Com_create_table | 0|
| Com_delete   | 0|
| Com_delete_multi | 0|
| Com_drop_db  | 0|
| Com_drop_function| 0|
| Com_drop_index   | 0|
| Com_drop_table   | 0|
| Com_flush| 0|
| Com_grant| 0|
| Com_ha_close | 0|
| Com_ha_open  | 0|
| Com_ha_read  | 0|
| Com_insert   | 1724 |
| Com_insert_select| 128  |
| Com_kill | 0|
| Com_load | 0|
| Com_load_master_data | 0|
| Com_load_master_table| 0|
| Com_lock_tables  | 0|
| Com_optimize | 0|
| Com_purge| 0|
| Com_rename_table | 0|
| Com_repair   | 0|
| Com_replace  | 0|
| Com_replace_select   | 0|
| Com_reset| 0|
| Com_restore_table| 0|
| Com_revoke   | 0|
| Com_rollback | 0|
| Com_savepoint| 0|
| Com_select   | 23934|
| Com_set_option   | 0|
| Com_show_binlog_events   | 0|
| Com_show_binlogs | 0|
| Com_show_create  | 0|
| Com_show_databases   | 0|
| Com_show_fields  | 0|
| Com_show_grants  | 0|
| Com_show_keys| 0|
| Com_show_logs| 0|
| Com_show_master_status   | 0|
| Com_show_new_master  | 0|
| Com_show_open_tables | 0|
| Com_show_processlist | 0|
| Com_show_slave_hosts | 0|
| Com_show_slave_status| 0|
| Com_show_status  | 1|
| Com_show_innodb_status   | 0|
| Com_show_tables  | 0|
| Com_show_variables   | 0|
| Com_slave_start  | 0|
| Com_slave_stop   | 0|
| Com_truncate | 0|
| Com_unlock_tables| 0|
| Com_update   | 1921 |
| Connections  | 1579 |
| Created_tmp_disk_tables  | 0|
| Created_tmp_tables   | 505  |
| Created_tmp_files| 0|
| Delayed_insert_threads   | 0|
| Delayed_writes   | 0|
| Delayed_errors   | 0|
| Flush_commands   | 1|
| Handler_commit   | 0|
| Handler_delete   | 0|
| Handler_read_first   | 2|
| Handler_read_key 

Tuning mysql performance

[Paolo Audiberti]

Hello,

I need some help tuning mysql. I'm running 3.23.58-Max-log on a Red Hat
Linux Enterprise server with 1 gig of memory and 4 cpus. The database is
used by a web application which runs on a separate machine. The performance
is not so good. Can anybody tell me if my configurations are incorrect, and
what I may need to change? My /etc/my.cnf file is as follows:

user= mysql
port= 3306
skip-locking

set-variable= key_buffer=384M
set-variable= max_allowed_packet=1M
set-variable= table_cache=512
set-variable= sort_buffer=2M
set-variable= record_buffer=2M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=8
set-variable= myisam_sort_buffer_size=64M
##set-variable  = net_buffer_length=8K
set-variable = max_connections = 200
log-bin
server-id   = 1


# Uncomment the following if you are using Innobase tables
##innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_data_file_path = ibdata1:800M
innodb_data_home_dir = /var/mysql/data/
innodb_log_group_home_dir = /var/log/mysql/
innodb_log_arch_dir = /var/log/mysql/
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
##set-variable = innodb_log_file_size=100M
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=384M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_file_io_threads=8
set-variable = innodb_lock_wait_timeout=50


[mysqldump]
quick
set-variable= max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[myisamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[mysqlhotcopy]
interactive-timeout

Running SHOW STATUS displays the following:

| Connections  | 723666 |
| Created_tmp_disk_tables  | 6230   |
| Created_tmp_tables   | 10179  |
| Created_tmp_files| 0  |
| Delayed_insert_threads   | 0  |
| Delayed_writes   | 0  |
| Delayed_errors   | 0  |
| Flush_commands   | 1  |
| Handler_delete   | 138|
| Handler_read_first   | 197703 |
| Handler_read_key | 203042308  |
| Handler_read_next| 377071037  |
| Handler_read_prev| 0  |
| Handler_read_rnd | 1377204|
| Handler_read_rnd_next| 913180300  |
| Handler_update   | 193164 |
| Handler_write| 14314266   |
| Key_blocks_used  | 193053 |
| Key_read_requests| 26041126   |
| Key_reads| 178671 |
| Key_write_requests   | 1463800|
| Key_writes   | 1432420|
| Max_used_connections | 15
| Not_flushed_key_blocks   | 0  |
| Not_flushed_delayed_rows | 0  |
| Open_tables  | 174|
| Open_files   | 255|
| Open_streams | 0  |
| Table_locks_immediate| 2137916|
| Table_locks_waited   | 437|
| Threads_cached   | 7  |
| Threads_created  | 32 |
| Threads_connected| 1  |
| Threads_running  | 1  |
| Uptime   | 759513 |
+--++

thanks,
Paolo


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL 4.0.20 for large full table scans

[Matt W]

Hi Dan,

- Original Message -
From: "Dan Nelson"
Sent: Thursday, June 03, 2004 12:34 PM
Subject: Re: Tuning MySQL 4.0.20 for large full table scans

[snip]

> > Not sure what can be done about making it not go straight to tmpdir
> > with a BLOB column in the SELECT clause, though.  Probably nothing,
> > in 4.0.
>
> Do you actually see a temp file being created?  With tmp_table_size set
> to 2gb, it shouldn't have to go to disk.  Some more interesting data
> would be the State column from "show processlist" during the query,
> every 10 seconds or so.

Currently, temporary tables with TEXT/BLOB columns are always created on
disk because HEAP tables don't support variable-length rows.  I think this
limitation will be lifted in 5.0 or 5.1.

For the original poster, maybe this is one of the times that a RAM disk
could be useful for MySQL's tmpdir.


Matt


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL 4.0.20 for large full table scans

[Dan Nelson]

In the last episode (Jun 03), Dossy said:
> On 2004.06.03, Dan Nelson <[EMAIL PROTECTED]> wrote:
> > Consider multicolumn indexes; if you always "SELECT field1 from
> > table where field2=blah", creating an index on (field2,field1) will
> > let mysql bypass table lookups completely.
> 
> Our typical queries look something like:
> 
>   SELECT level1, level2, level3, SUM(count1), SUM(count2)
>   FROM table
>   WHERE level1 = 'value1'
>   AND level2 = 'value2'
>   AND level3 IS NULL
>   GROUP BY level1, level2, level3
> 
> In the real data, we have many more than 3 levels.  Perhaps I should
> have been including the count columns in the indexes as well -- hmm.

Yes, that would help.  Make sure your WHERE clause fields come first in
the index.  Assuming you have an index on (lavel1,level2,level3) and
the number of matching rows is relatively low compared to the total
record volume, though, there's no reason mysql should be doing full
table scans here.
 
> > > Also, joins on this table are miserable since the BLOB columns
> > > make MySQL use tmp disk for sorting instead of keeping everything
> > > in memory.
> > 
> > Unless you're selecting those blob fields, I don't think mysql will
> > keep them during the join operation.  I could be wrong though.
> 
> The problem is that our longest running queries are this shape:
> 
> INSERT INTO destination_table SELECT
> level1, level2, ... leveN, SUM(count1), SUM(count2) ...
> FROM source_merge_table
> -- no WHERE clause
> GROUP BY level1 ... etc
> 
> source_merge_table is a MERGE table type that can union 12 or more
> tables.  (Once we go to 4.1, we can eliminate the MERGE tables and
> use a derived table in the FROM clause with UNION ALL, but for now
> in 4.0, we have to use MERGE tables.)
> 
> Yes, the BLOB columns are included in the SELECT clause.  This forces
> the query to go straight to tmpfile on disk.

Since you're GROUPing, how to you pull the blob field?  MIN, MAX, and
GROUP_CONCAT are the only aggregate functions I can think of that even
make sense on BLOB columns.

> Well, what seems to happen is the data gets read from the MyISAM
> table, gets written back out to disk in the tmpdir, then when it's
> all done, gets actually placed in the .MYD file -- it's the
> back-and-forthing to disk that I presume is eating a lot of the time.
> 
> Not sure what can be done about making it not go straight to tmpdir
> with a BLOB column in the SELECT clause, though.  Probably nothing,
> in 4.0.

Do you actually see a temp file being created?  With tmp_table_size set
to 2gb, it shouldn't have to go to disk.  Some more interesting data
would be the State column from "show processlist" during the query,
every 10 seconds or so.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL 4.0.20 for large full table scans

[Dossy]

On 2004.06.03, Dan Nelson <[EMAIL PROTECTED]> wrote:
> 
> Consider multicolumn indexes; if you always "SELECT field1 from table
> where field2=blah", creating an index on (field2,field1) will let mysql
> bypass table lookups completely.

Our typical queries look something like:

  SELECT level1, level2, level3, SUM(count1), SUM(count2)
  FROM table
  WHERE level1 = 'value1'
  AND level2 = 'value2'
  AND level3 IS NULL
  GROUP BY level1, level2, level3

In the real data, we have many more than 3 levels.  Perhaps I should
have been including the count columns in the indexes as well -- hmm.

> > Also, joins on this table are miserable since the BLOB columns make
> > MySQL use tmp disk for sorting instead of keeping everything in
> > memory.
> 
> Unless you're selecting those blob fields, I don't think mysql will
> keep them during the join operation.  I could be wrong though.

The problem is that our longest running queries are this shape:

INSERT INTO destination_table SELECT
level1, level2, ... leveN, SUM(count1), SUM(count2) ...
FROM source_merge_table
-- no WHERE clause
GROUP BY level1 ... etc

source_merge_table is a MERGE table type that can union 12 or more
tables.  (Once we go to 4.1, we can eliminate the MERGE tables and
use a derived table in the FROM clause with UNION ALL, but for now
in 4.0, we have to use MERGE tables.)

Yes, the BLOB columns are included in the SELECT clause.  This forces
the query to go straight to tmpfile on disk.

> > I've got read_buffer_size and read_rnd_buffer_size cranked up to 64M,
> > which I'm hoping will make these full table scans do better -- I
> > could be wrong, though.  Storage is not local disk but on
> > HBA-attached SAN.
> 
> What's your I/O throughput (run "iostat -xcnz 2") during one of these
> full table scans?  If you can get 100MB/sec, a scan of a 365MB file
> should take under 5 seconds.

I'll have to look at iostat next time the query runs.  Thanks for the
reminder to look at iostat, duh.

> > As I said, obvious steps to take are to rework the schema and
> > introduce composite/conjoint tables where possible and to move those
> > BLOB columns out of the main fact table ... but even then, there
> > should be "optimal" settings for a DB that generally does full table
> > scans on 2M rows ... a lot of the data can be kept in that 10 GB of
> > memory, if I could only force MySQL to use it: those BLOB columns are
> > probably killing me.
> 
> With 10GB of RAM, Solaris should be caching your entire table in
> memory.  You will still have the overhead of mysql reading the data
> from the OS cache but you should still get good performance.  Make
> soure you're not mounting your filesystem with the forcedirectio
> option, which will disable the OS cache.

Well, what seems to happen is the data gets read from the MyISAM table,
gets written back out to disk in the tmpdir, then when it's all done,
gets actually placed in the .MYD file -- it's the back-and-forthing to
disk that I presume is eating a lot of the time.

Not sure what can be done about making it not go straight to tmpdir with
a BLOB column in the SELECT clause, though.  Probably nothing, in 4.0.

-- Dossy

-- 
Dossy Shiobara   mail: [EMAIL PROTECTED] 
Panoptic Computer Network web: http://www.panoptic.com/ 
  "He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL 4.0.20 for large full table scans

[Dan Nelson]

In the last episode (Jun 03), Dossy said:
> The schema in question needs a redesign (dynamic row format, contains
> two BLOB columns) but I was wondering if anyone has written up some
> guidelines for general data warehouse configuration of MySQL 4.0 --
> Google has not turned up anything useful.
> 
> An example table has 2.1M rows and is 365MB in size.  Queries against
> the table are generally full table scans as efforts to index the
> table yield little gain (the indexes don't seem to be selective
> enough).

Consider multicolumn indexes; if you always "SELECT field1 from table
where field2=blah", creating an index on (field2,field1) will let mysql
bypass table lookups completely.

> Also, joins on this table are miserable since the BLOB columns make
> MySQL use tmp disk for sorting instead of keeping everything in
> memory.

Unless you're selecting those blob fields, I don't think mysql will
keep them during the join operation.  I could be wrong though.
 
> I've got read_buffer_size and read_rnd_buffer_size cranked up to 64M,
> which I'm hoping will make these full table scans do better -- I
> could be wrong, though.  Storage is not local disk but on
> HBA-attached SAN.

What's your I/O throughput (run "iostat -xcnz 2") during one of these
full table scans?  If you can get 100MB/sec, a scan of a 365MB file
should take under 5 seconds.
 
> As I said, obvious steps to take are to rework the schema and
> introduce composite/conjoint tables where possible and to move those
> BLOB columns out of the main fact table ... but even then, there
> should be "optimal" settings for a DB that generally does full table
> scans on 2M rows ... a lot of the data can be kept in that 10 GB of
> memory, if I could only force MySQL to use it: those BLOB columns are
> probably killing me.

With 10GB of RAM, Solaris should be caching your entire table in
memory.  You will still have the overhead of mysql reading the data
from the OS cache but you should still get good performance.  Make
soure you're not mounting your filesystem with the forcedirectio
option, which will disable the OS cache.
 
-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Tuning MySQL 4.0.20 for large full table scans

[Dossy]

The schema in question needs a redesign (dynamic row format, contains
two BLOB columns) but I was wondering if anyone has written up some
guidelines for general data warehouse configuration of MySQL 4.0 --
Google has not turned up anything useful.

An example table has 2.1M rows and is 365MB in size.  Queries against
the table are generally full table scans as efforts to index the table
yield little gain (the indexes don't seem to be selective enough).

Also, joins on this table are miserable since the BLOB columns make
MySQL use tmp disk for sorting instead of keeping everything in memory.
There's 10 GB RAM on the box, we're using 64-bit build of MySQL on
Solaris 8, and tmp_table_size = 2G, sort_buffer_size = 2G,
max_heap_table_size = 2G.

I've got read_buffer_size and read_rnd_buffer_size cranked up to 64M,
which I'm hoping will make these full table scans do better -- I could
be wrong, though.  Storage is not local disk but on HBA-attached SAN.

As I said, obvious steps to take are to rework the schema and introduce
composite/conjoint tables where possible and to move those BLOB columns
out of the main fact table ... but even then, there should be "optimal"
settings for a DB that generally does full table scans on 2M rows ... a
lot of the data can be kept in that 10 GB of memory, if I could only
force MySQL to use it: those BLOB columns are probably killing me.

Any tuning advice would be much appreciated.  Thanks.

-- Dossy

-- 
Dossy Shiobara   mail: [EMAIL PROTECTED] 
Panoptic Computer Network web: http://www.panoptic.com/ 
  "He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tuning MySQL Server Parameter

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Qunfeng Dong wrote:

Thanks! I copied
/usr/share/doc/mysql-server-3.23.49/my-huge.cnf into
/etc/my.cnf and restarted mysqld from
/etc/rc.d/init.d/mysqld

but it's not improving anything. 
my join query is very simple


select count(B.columnb) from B left join A on


B.columnb = A.columna. 

What does 'EXPLAIN select count(B.columnb) from B left join A on 
B.columnb = A.columna' say?

Maybe your query is not using indexes for some reason?!

	-Mark
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE98UKCtvXNTca6JD8RAsvXAKCDkJnkhTcDg7r6pLHRZ5nNEKN81ACgqr+Z
S62fWVXyFkzZpJyleVUpd/8=
=6O0j
-END PGP SIGNATURE-


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Tuning MySQL Server Parameter

[Qunfeng Dong]

Thanks! I copied
/usr/share/doc/mysql-server-3.23.49/my-huge.cnf into
/etc/my.cnf and restarted mysqld from
/etc/rc.d/init.d/mysqld

but it's not improving anything. 
my join query is very simple

>select count(B.columnb) from B left join A on
B.columnb = A.columna. 

Both columna and columnb are varchar(11) and indexed. 
Table B has about 34,000 records and Table A has about
2,500,000 records. The above query took about 3 hours
to finish. Something is just not right. 

Qunfeng Dong

--- David Bordas <[EMAIL PROTECTED]> wrote:
> > I wish to tune our MySQL Server Parameter to
> increase
> > the speed of Join. I was trying to do a simple
> join
> > with two tables. One is big (~2,500,000 records);
> the
> > other one is small. The current join seems to take
> > forever to finish even on the indexed attribute.
> >
> > I am trying to learn from
> > http://www.mysql.com/doc/en/Server_parameters.html
> but
> > not confident enough to play with our server yet.
> Any
> > advice will be much appreciated. I am running
> > mysql3.23.49 on linux7.3 with 4 GB memory. So I
> want
> > to try the following from that doc:
> >
> > shell> safe_mysqld -O key_buffer=64M -O
> > table_cache=256 -O sort_buffer=4M -O
> > read_buffer_size=1M &
> >
> > My questions: if I run the above command (as
> root),
> > should I run it every time when the server starts?
> If
> > so, how can I set the above option automatically
> when
> > server starts. Thanks!
> 
> Modify you my.cnf to add or change this parameter
> and mysql will "normally"
> read this cnf file each time you launch it via
> mysql.server script ...
> 
> David
> 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Tuning MySQL Server Parameter

[Georg Richter]

On Friday 06 December 2002 01:11, Qunfeng Dong wrote:
> Hi,
>
> I wish to tune our MySQL Server Parameter to increase
> the speed of Join. I was trying to do a simple join
> with two tables. One is big (~2,500,000 records); the
> other one is small. The current join seems to take
> forever to finish even on the indexed attribute.

Before you change some server parameters, you should analyze your join query 
with EXPLAIN SELECT yourquery.

Regards

Georg

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Tuning MySQL Server Parameter

[David Bordas]

> I wish to tune our MySQL Server Parameter to increase
> the speed of Join. I was trying to do a simple join
> with two tables. One is big (~2,500,000 records); the
> other one is small. The current join seems to take
> forever to finish even on the indexed attribute.
>
> I am trying to learn from
> http://www.mysql.com/doc/en/Server_parameters.html but
> not confident enough to play with our server yet. Any
> advice will be much appreciated. I am running
> mysql3.23.49 on linux7.3 with 4 GB memory. So I want
> to try the following from that doc:
>
> shell> safe_mysqld -O key_buffer=64M -O
> table_cache=256 -O sort_buffer=4M -O
> read_buffer_size=1M &
>
> My questions: if I run the above command (as root),
> should I run it every time when the server starts? If
> so, how can I set the above option automatically when
> server starts. Thanks!

Modify you my.cnf to add or change this parameter and mysql will "normally"
read this cnf file each time you launch it via
mysql.server script ...

David


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Tuning MySQL Server Parameter

[Qunfeng Dong]

Hi, 

I wish to tune our MySQL Server Parameter to increase
the speed of Join. I was trying to do a simple join
with two tables. One is big (~2,500,000 records); the
other one is small. The current join seems to take
forever to finish even on the indexed attribute.

I am trying to learn from 
http://www.mysql.com/doc/en/Server_parameters.html but
not confident enough to play with our server yet. Any
advice will be much appreciated. I am running
mysql3.23.49 on linux7.3 with 4 GB memory. So I want
to try the following from that doc:

shell> safe_mysqld -O key_buffer=64M -O
table_cache=256 -O sort_buffer=4M -O
read_buffer_size=1M &

My questions: if I run the above command (as root),
should I run it every time when the server starts? If
so, how can I set the above option automatically when
server starts. Thanks!

Qunfeng Dong



__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Tuning MySQL Full-text Search

[Sinisa Milivojevic]

On Wed, 27 Nov 2002 11:53:25 +0200
"Nick Kostirya" <[EMAIL PROTECTED]> wrote:

> Hi, All.
> 
>  How can I add additional characters (Ukrainian alphabet) to the source
>  code
> for parse?
> 
>  Thanks,  Nick.
> 
> database,sql,query,table,handler,compile,ChangeSet
> 
> 
> 

Just add a charset.

Take a look at sql/share/charsets/

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, FullTime Developer
/_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
   <___/   www.mysql.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Tuning MySQL Full-text Search

[Nick Kostirya]

Hi, All.

 How can I add additional characters (Ukrainian alphabet) to the source code
for parse?

 Thanks,  Nick.

database,sql,query,table,handler,compile,ChangeSet




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

tuning mysql

[Sönke Ruempler]

hi,

do you have hints/documents for me were to find information about mysql
server tuning (not table/index optimizing, but memory-tuning etc).

thx!


regards
Sönke Ruempler

top concepts Internetmarketing GmbH
--
http://www.topconcepts.com  Tel. +49 4141 9912 30
mail: [EMAIL PROTECTED]  Fax. +49 4141 9912 33
--
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,CNS24)
--


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

tuning mysql

[Scott Helms]

I have a fairly decent server who is handling lots of connections, but very
simple SQL and I am looking for tuning advice.  I have a pretty high number
of simultaneous connections and the box is showing a few signs of stress,
that I want to address before they become a problem.  Any help will be
greatly appreciated.

Scott



Here are some box stats:

The disk is 10k UltraSCSI.

model:UltraAX-i2
Sun development name: Flapjack2
Solaris 8 10/00 s28s_u2wos_11b SPARC, 64-bit kernel, SunOS 5.8
1 UltraSPARC-IIe cpu, cpu freq: 500MHz, system freq: 100MHz
CPU Units:
= CPUs =
Run   Ecache   CPUCPU
Brd  CPU   Module   MHz MBImpl.   Mask
---  ---  ---  -  --  --  
 0 0 0  500 0.2   13   1.4
Memory Units:
socket DIMM0 has a 256MB DIMM (address 0x-0x0fff)
socket DIMM1 has a 256MB DIMM (address 0x2000-0x2fff)
socket DIMM2 has a 512MB DIMM (address 0x4000-0x5fff)
empty sockets: DIMM3
total memory = 1024MB (1GB)

This is what I have in the mysqld section of the my.cnf:
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
skip-name-resolve
set-variable= key_buffer=512M
set-variable= max_allowed_packet=1M
set-variable= table_cache=768
set-variable= sort_buffer=2M
set-variable= record_buffer=2M
set-variable= myisam_sort_buffer_size=64M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=8
set-variable= max_connections=1000
log-bin




Below is the output of a show status:
mysql> show status
-> ;
+--+---+
| Variable_name| Value |
+--+---+
| Aborted_clients  | 97|
| Aborted_connects | 818   |
| Bytes_received   | 62528355  |
| Bytes_sent   | 103284617 |
| Connections  | 180746|
| Created_tmp_disk_tables  | 0 |
| Created_tmp_tables   | 14|
| Created_tmp_files| 0 |
| Delayed_insert_threads   | 0 |
| Delayed_writes   | 0 |
| Delayed_errors   | 0 |
| Flush_commands   | 1 |
| Handler_delete   | 25|
| Handler_read_first   | 0 |
| Handler_read_key | 290436|
| Handler_read_next| 605   |
| Handler_read_prev| 0 |
| Handler_read_rnd | 115416|
| Handler_read_rnd_next| 449963586 |
| Handler_update   | 115780|
| Handler_write| 115929|
| Key_blocks_used  | 4950  |
| Key_read_requests| 1743430   |
| Key_reads| 3902  |
| Key_write_requests   | 464   |
| Key_writes   | 533   |
| Max_used_connections | 207   |
| Not_flushed_key_blocks   | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables  | 198   |
| Open_files   | 259   |
| Open_streams | 0 |
| Opened_tables| 185   |
| Questions| 647665|
| Select_full_join | 0 |
| Select_full_range_join   | 0 |
| Select_range | 122   |
| Select_range_check   | 0 |
| Select_scan  | 59926 |
| Slave_running| OFF   |
| Slave_open_temp_tables   | 0 |
| Slow_launch_threads  | 0 |
| Slow_queries | 0 |
| Sort_merge_passes| 0 |
| Sort_range   | 0 |
| Sort_rows| 0 |
| Sort_scan| 0 |
| Table_locks_immediate| 465361|
| Table_locks_waited   | 763   |
| Threads_cached   | 4 |
| Threads_created  | 6167  |
| Threads_connected| 47|
| Threads_running  | 4 |
| Uptime   | 9010  |
+--+---+





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Performance tuning Mysql

[Jani Tolonen]

Vien Huynhle writes:
 > I am getting this error in my log files from mysql
 >  
 > (Got timeout reading communication packets)
 >  
 > I'm starting safemysql with the variables
 >  
 > -O key_buffer=16M -O sort_buffer=1M -O back_log=200 &


Hi Vien,

Please increase the 'net_write_timeout' and 'net_read_timeout'
-variables for mysqld. Make sure that your wait_timeout is big enough
for your clients. If they've been idle for longer than that, mysqld
will close the connection. Please make sure also that the (TCP/IP ?) 
connection between the server and the clients is OK, you may have an
ethernet problem.

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Jani Tolonen <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
   <___/   www.mysql.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Performance tuning Mysql

[Vien Huynhle]

I am getting this error in my log files from mysql
 
(Got timeout reading communication packets)
 
I'm starting safemysql with the variables
 
-O key_buffer=16M -O sort_buffer=1M -O back_log=200 &
 
mysql> show status
-> ;
+--+--+
| Variable_name| Value|
+--+--+
| Aborted_clients  | 0|
| Aborted_connects | 1|
| Bytes_received   | 9204605  |
| Bytes_sent   | 13211959 |
| Connections  | 23   |
| Created_tmp_disk_tables  | 0|
| Created_tmp_tables   | 0|
| Created_tmp_files| 0|
| Delayed_insert_threads   | 0|
| Delayed_writes   | 0|
| Delayed_errors   | 0|
| Flush_commands   | 1|
| Handler_delete   | 0|
| Handler_read_first   | 39275|
| Handler_read_key | 97534|
| Handler_read_next| 79518|
| Handler_read_prev| 0|
| Handler_read_rnd | 0|
| Handler_read_rnd_next| 39287|
| Handler_update   | 0|
| Handler_write| 0|
| Key_blocks_used  | 485  |
| Key_read_requests| 1575217  |
| Key_reads| 19971|
| Key_write_requests   | 0|
| Key_writes   | 0|
| Max_used_connections | 20   |
| Not_flushed_key_blocks   | 0|
| Not_flushed_delayed_rows | 0|
| Open_tables  | 30   |
| Open_files   | 37   |
| Open_streams | 0|
| Opened_tables| 36   |
| Questions| 136803   |
| Select_full_join | 0|
| Select_full_range_join   | 0|
| Select_range | 0|
| Select_range_check   | 0|
| Select_scan  | 0|
| Slave_running| OFF  |
| Slave_open_temp_tables   | 0|
| Slow_launch_threads  | 0|
| Slow_queries | 0|
| Sort_merge_passes| 0|
| Sort_range   | 0|
| Sort_rows| 0|
| Sort_scan| 0|
| Table_locks_immediate| 136813   |
| Table_locks_waited   | 0|
| Threads_cached   | 0|
| Threads_created  | 22   |
| Threads_connected| 21   |
| Threads_running  | 1|
| Uptime   | 1084 |
+--+--+
 
There is a large amount selects made to the database.  
 
I have tried to tune it but no luck.


Vien Huynhle
Clearsail Communications



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Anyone got suggestions for tuning MYSQL (my.cnf suggestion request)

[Apolinaras Apollo Sinkevicius - Carmel Music & Entertainment]

Well, finally I have launched full blown MySQL database. Speed is amazing (in 
comparison with our previous monogenous M$Access database).
I am now tuning MySQL (3.23.30 on RH7.0/ PIII300 Dell server with 128RAM) server via 
my.cnf
So far here is what I got:
1. ANSI mode tables
2. logs are off
3. no slave

I need ideas!?!? I looked at the manual (and Paul DUBois book), but I really need more 
insight from people who have mission critical productions 
systems running.
Database is not too large. 12 tables with about 1 rows each and an average of 10 
columns each. Lots of reads, sorts and joins. Inserts, adds 
and deletes comprise about 5 percent of all activities, and a staff of very impatient 
non tech savyy associates (hey they'd rather play Win98 
tetris).

Any ideas would be appretiated. I really would like to set this server and forget 
about it (just like after my switch from NT to RH6 and RH7.0 later). 
I can't remember the last time I had to reboot the server.

Thanks  

-- 
-
Please check "http://www.mysql.com/documentation/manual.php" before
posting. To request this thread, e-mail [EMAIL PROTECTED]

To unsubscribe, send a message to:
<[EMAIL PROTECTED]>

If you have a broken mail client that cannot send a message to
the above address (Microsoft Outlook), you can use:
http://lists.mysql.com/php/unsubscribe.php