ANN: MySQL Performance Monitoring
Greetings MySQL users -- If MySQL monitoring is something you are doing with one specific tool -- or not at all -- then you might find this blog post on MySQL Performance Monitoring in SPM worth a read: http://wp.me/pwdA7-Xo We frequently hear from organizations across industries who tackle performance monitoring with a mish-mash of different monitoring and alerting tools cobbled together in an uneasy coexistence that is often far from seamless. Think Ganglia+Nagios. A single tool like SPM takes all that hassle away and makes it easy and comprehensive in one step. Here's more info on what SPM monitors: http://sematext.com/spm/index.html Happy monitoring! Mick Emmett Sematext Group, Inc. *http://sematext.com/ http://sematext.com/*
Re: MySQL tweaking.
On Tue, 21 Dec 2004 15:16:02 -0700 Sasha Pachev [EMAIL PROTECTED] wrote: The hardware you have is just fine. But start thinking about a clustered solution. As the traffic increases, you eventually reach a point where no hardware is good enough on one box. Hi, Thankyou for your reply. I have the system load down a fair bit.Was something silly on my behalf. I had mysql logging all queries to disk. serious i/o loads. I have since stopped this and now system is seeing 100qps and only 1% aborted attempts. ( was 20qps and 20% Aborted queries. ) -- Regards Mick Pollard (lunix) BOFH Excuse of the day: Temporary Syntax Expiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL tweaking.
Hi list. I have a server that is having a little MySQL load issue. I have done many hours of reading and can't quite get this worked out. I am asking for your help please. The server is to be a dedicated MySQL server only. The following is the current setup but currently is doing some apache aswell.( around 1gb total data out per day ) I would like the advice on setting this up purely for a dedicated MySQL server. There is a mix of both MyISAM and INNODB stuff. I am told by the programmers that they use the MyISAM stuff cause of the indexing power.:S Dont need transactions. My questions are: 1. Is this hardware going to be enough ? 2. What sort of gain is to be had if I linux software raid-1 the 2 x sata 150 drives ?? 3. If this is sufficient hardware then what can I do to improve my.cnf and the overall server performance. The current live status of the server can be viewed at http://sql.kdwshosting.com/sql-stat.php DETAILS == The server is a : P4 3.0gb HT, 2gb dual channel corsair ram, sata150 80gb drives x 2. ( not raid ) OS: Slackware 10.0 with 2.6.9smp and most server progs compiled from source with: -- ARCH=i686 MYIN=lun SLKCFLAGS=-march=pentium4 -O3 -pipe -fomit-frame-pointer ## CFLAGS we get these from the above settings CFLAGS=$SLKCFLAGS CHOST=i686-pc-linux-gnu #CXX=gcc ## CXXFLAGS same as the CFLAGS CXXFLAGS=$SLKCFLAGS --- ---/etc/my.cnf-- # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs # mainly MySQL. # The following options will be passed to all MySQL clients [client] port= 3306 socket = /var/run/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] skip-locking max_connections = 500 key_buffer = 512M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M table_cache = 128 thread_cache_size = 128 wait_timeout = 14400 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 thread_concurrency = 4 #log = /var/log/mysql/mysql.log - /usr/bin/top output is : --- top - 22:23:46 up 18 days, 7:30, 3 users, load average: 0.00, 0.00, 0.00 Tasks: 221 total, 1 running, 220 sleeping, 0 stopped, 0 zombie Cpu0 : 0.3% us, 0.3% sy, 0.0% ni, 99.3% id, 0.0% wa, 0.0% hi, 0.0% si Cpu1 : 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 2075736k total, 2028972k used,46764k free,69268k buffers Swap: 2008116k total,0k used, 2008116k free, 1533628k cached - Regards Mick Pollard ( lunix ) BOFH Excuse of the day: Nonfatal Hardware Timeout Warning -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import Query From Access?
Hi I am pretty new to mysql. I am porting an access driven website to mysql. All has gone pretty much to plan bar one final page. This page is basically a listing of duplicate users based on the email field. In the access db I had a predefined query called emailduplicate The site asp page called a recordset from this query. For starters, can I create a similar stored query with mysql? If so how do I do it? I use mysql front but can see no way to create a stored query similar to access. What I require at the end is a web page listing member records where the emaila ddress occurs more than once. Also all other data associated with each record as the additional data is very likely to be different even if emails are the same. Should it help the original access query stored as email duplicate was as per below. SELECT Members.Email, Members.ID, Members.Worldpayid, Members.yourname, Members.Password, Members.Email2, Members.fullmember, Members.DateFullmemberpaid, Members.unsubcribed, Members.dateunsubscribed, Members.Paidbycheck, Members.dtmMembershipExpires FROM Members WHERE (((Members.Email) In (SELECT [Email] FROM [Members] As Tmp GROUP BY [Email] HAVING Count(*)1 ))) ORDER BY Members.Email; The asp page used a recorset as follows. Recordset1.Source = SELECT * FROM emailduplicate ORDER BY Email, fullmember Any help or advice on how best to achieve the final end result would be appreciated. best wishes Michael
Re: group by help?
You're right, it wasn't the answer I wanted to hear ;-) But it has a ring of truth to it. Thanks for all your help Mick Paul DuBois wrote: At 13:34 +0100 4/19/02, Mick Watson wrote: Thank you :-) I do realise the SQL is not being used as it should be, but as it is not my database and nor is it my SQL, there's not much I can do to really change either of these I merely have to work with a database that has been produced by a third party group, and I must say at this point that I have nothing but praise for the ensembl group for the difficult job that they have done very well (see http://www.ensembl.org) So, I realise this is not the problem of mySQL, but what I want to try and find out is really the internal workings of mySQL when it performs this operation so that I can understand why my copy of ensembl, which is produced from direct dumps of the main ensembl, behaves differently to the main ensembl. I guess you won't want to hear this, but that is completely the wrong approach. You *might* be able to achieve the same query output on both machines by dumping the database on one machine, then dropping and reloading the tables on both machines using the same copy of the dump. Then presumably both tables will have both records loaded in the same order, and the query *might* produce the same result on both machines. - ...until the table gets modified - ...until MySQL is upgraded on one machine and not the other, and something happens in the query optimizer that changes the query output - ...etc. The group you're working with *must* change the queries to be correct. Otherwise, you're entirely at the mercy of any number of external factors that can change the output and over which you have no control. I guess from what you are saying, that the data is stored randomly, that there is very little I can do to actually make my database behave the same as the main database? Could it be affected in any way by operating system and/or file system? Superficially the data is organised in exactly the same way in both databases, but I have no doubt that things like memory locations are completely different, but possibly if I could understand what the variables are that affect this behaviour I could minimise the inconsistancy...? Thanks for your time Mick Richard Emery wrote: mysql is acting correctly. GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your SELECT statement makes not such request. You have simply requested the value of a specific field. Data are stored in mysql databases randomly. Therefore, when you request a field's data, you are getting whatever is first in the list of records matching your WHERE clause. Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your SQL is in error, not mysql. - 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: GUI for mySQL
I have to concur, MS Access with ODBC linked tables is not a good solution when those tables contain large amounts of data. I had queries run for hours and then come up with a simple ODBC Failed message at the end of it. Maybe Access has improved though. Tod Harter wrote: Watch out with using the Jet database engine this way. MS Access tries to keep linked external table views in sync, which means that with large tables and several PCs running Access linked to MySQL over ODBC driver it will bring your network to its knees. The Jet engine will constantly be scanning tables on the database server, generating GIGANTIC amounts of traffic. I've seen 100mb ethernet completely swamped by just 3 or 4 clients. There are probably work-arounds for that, but you should definitely test first, MS Access is a very badly behaved piece of KAKA. On Monday 22 April 2002 07:29, Hathaway, Scott L wrote: I would not look at front ends to mySQL. Instead, I would let them keep using access to do this and link them to the mySQL database or get an SQL query tool that allows for graphical queries. Scott -Original Message- From: Pierre du Parte [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 20, 2002 3:06 AM Cc: MySQL Subject: GUI for mySQL I am gradually winning the war in promoting opensource to a number of my key customers. Notably a couple have made the move to OpenOffice.org, albeit on Windows. I am currently re-writing a key application to use mySQl (previously Access), so in all I'm pleased. However, the head bean counter in one office tackled me on this - In Access I can quickly create queries and export the results in all manner of ways. What do I do if we let you talk us into mySql? Where indeed? Are there any nice, user friendly GUI available for mySQL ad-hoc query and report design? Thanks - 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 - 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
group by help?
Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical order, random choice (though mySQL is always consistent in which it chooses) ... or is there some other way it will make the decision? Furthermore, and more confusingly, mySQL chooses differently if I parameterise the SQL. For example: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId and gene_id = 24173 - group by gene_id - order by gene_id; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql select * from test; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | +-++--+ 1 row in set (0.00 sec) compare this to: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId - group by gene_id - order by gene_id; Query OK, 11674 rows affected (6.84 sec) Records: 11674 Duplicates: 0 Warnings: 0 mysql select * from test where gene_id = 24173; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q96GS5 | SPTREMBL | +-++--+ 1 row in set (0.01 sec) So here we see that mySQL has chosen differently simply because of the presence or absence of the gene_id = 24173 in the create table command I want to try and figure out why mySQL is making these rather inconsistant decisions and see if it's possible to remove this feature Thanks for your time, if there is another mailing list which is more appropriate, please tell me! Thanks Mick - 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: group by help?
Thank you :-) I do realise the SQL is not being used as it should be, but as it is not my database and nor is it my SQL, there's not much I can do to really change either of these I merely have to work with a database that has been produced by a third party group, and I must say at this point that I have nothing but praise for the ensembl group for the difficult job that they have done very well (see http://www.ensembl.org) So, I realise this is not the problem of mySQL, but what I want to try and find out is really the internal workings of mySQL when it performs this operation so that I can understand why my copy of ensembl, which is produced from direct dumps of the main ensembl, behaves differently to the main ensembl. I guess from what you are saying, that the data is stored randomly, that there is very little I can do to actually make my database behave the same as the main database? Could it be affected in any way by operating system and/or file system? Superficially the data is organised in exactly the same way in both databases, but I have no doubt that things like memory locations are completely different, but possibly if I could understand what the variables are that affect this behaviour I could minimise the inconsistancy...? Thanks for your time Mick Richard Emery wrote: mysql is acting correctly. GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your SELECT statement makes not such request. You have simply requested the value of a specific field. Data are stored in mysql databases randomly. Therefore, when you request a field's data, you are getting whatever is first in the list of records matching your WHERE clause. Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your SQL is in error, not mysql. hope this helps... - Original Message - From: Mick Watson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 6:01 AM Subject: group by help? Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical order, random choice (though mySQL is always consistent in which it chooses) ... or is there some other way it will make the decision? Furthermore, and more confusingly, mySQL chooses differently if I parameterise the SQL. For example: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId and gene_id = 24173 - group by gene_id - order by gene_id; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql select * from test; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | +-++--+ 1 row in set (0.00 sec) compare this to: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID
Re: group by help?
It's ok, I do actually fully understand relational database theory and what the group by statement is used for, but like I say, I personally have no control over the database or the SQL :-) I was hoping to get hold of someone who maybe knows the source code quite well already and could help me out before I take the drastic step of trawling through the source code myself. It does strike me that, on the rare occasions when a truly arbitrary decision must be made, when there is a choice but it really doesn't matter which is chosen, there is no method in relational databases to make this choice in a consistent manner. A tough task probably, and obviously group by is not the perfect answer, but you can see why the developer made that choice as it does make mySQL make an arbitrary decision... just not a consistent one :-) Thanks Mick Richard Emery wrote: As a relational database, it doe not matter the order in which data are stored. Rather, what matters are the methods by which data are extracted via the SELECT statement. In order to derive benefits of GROUP BY, you must use it with SELECTs that use GROUP BY functionality, such as SUM(), COUNT(). If you need to know the internal machinations of mysql, I can only suggest that you study the source code. - Original Message - From: Mick Watson [EMAIL PROTECTED] To: Richard Emery [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 7:34 AM Subject: Re: group by help? Thank you :-) I do realise the SQL is not being used as it should be, but as it is not my database and nor is it my SQL, there's not much I can do to really change either of these I merely have to work with a database that has been produced by a third party group, and I must say at this point that I have nothing but praise for the ensembl group for the difficult job that they have done very well (see http://www.ensembl.org) So, I realise this is not the problem of mySQL, but what I want to try and find out is really the internal workings of mySQL when it performs this operation so that I can understand why my copy of ensembl, which is produced from direct dumps of the main ensembl, behaves differently to the main ensembl. I guess from what you are saying, that the data is stored randomly, that there is very little I can do to actually make my database behave the same as the main database? Could it be affected in any way by operating system and/or file system? Superficially the data is organised in exactly the same way in both databases, but I have no doubt that things like memory locations are completely different, but possibly if I could understand what the variables are that affect this behaviour I could minimise the inconsistancy...? Thanks for your time Mick Richard Emery wrote: mysql is acting correctly. GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your SELECT statement makes not such request. You have simply requested the value of a specific field. Data are stored in mysql databases randomly. Therefore, when you request a field's data, you are getting whatever is first in the list of records matching your WHERE clause. Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your SQL is in error, not mysql. hope this helps... - Original Message - From: Mick Watson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 6:01 AM Subject: group by help? Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical
MySql fatal error in mysql-opt.exe
Hi, I'm trying to run MySQL ver 3.23.49 on Win98 with Apache, PHP Perl. Apache reports that PHP Perl are running OK. When I try to start MySQL, or let it start on boot, or try to use the WinMySqladmin program, I get a Windows error from MYSQLD-OPT.exe, with the usual Win98 close and details and the console screen for that program reads: c:\serve\MySql\bin\mysql-opt.exe: Fatal error: Can't find messagefile 'C: erve\MySql\share\english\errmsg.sys' It seems the program is looking in C:/ erve when in fact the relevant errmsg.sys file is in the C:/serve folder into which everything was installed, along with the complete MySQL installation. There is no C:/ erve folder. I can't get MySql to run. I assumed a data error, or perhaps a wrong line in a config file somewhere, but I uninstalled and reinstalled MySql twice (including installing to the default folder), no change,. and I can't find any reference to this non-existent folder anywhere in a config file, so I can't correct it. Please help me, it's taken me (admittedly not a networking guru) 3 days to get Apache running happily with Perl and PHP, and this is going to tip me over the edge...! Thanks in advance, Mick. - 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
