Re: Analysis of a weeks worth of general log
> Minor correction: The post i point to is about the slow log, but I presume > also relevant for the general log. And the good comments I mentioned come in > the followup posting at http://www.bitbybit.dk/carsten/blog/?p=116 > > / Carsten Thanks Carsten, I read the comments and Sheeri mentions mysqlsla which I have already tried. Back to square one. I might look at munin again and see if someone has written a plug-in that graphs query type but that seems too much hassle for my situation. I have the raw data and I want the appropriate tool to analyze it. Part of the reason is that the data is from a MyISAM based web app and I am writing a report recommending it be moved to a transactional storage engine. AIUI a rule of thumb is that if between 15% - 20% of statements are non SELECT/INSERT then one can obtain equal or better performance with something like InnoDB. That being said, the benefits of InnoDB (good recovery features, transactions, advanced indexes, foreign key contraints) make it a good default choice and I will recommend it anyway. Plus we have order processing stuff going on and it seems right to have "atomicity" in that process. It would be a bit better though to confidently state that the query-mix skews it towards InnoDB... if I can only prove it :-) -- GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Analysis of a weeks worth of general log
I have 7 days worth of general log data totalling 4.4GB. I want to analyze this data to get: a) queries per second, minute, hour and day b) a count of the number of selects versus write statements (delete, insert, replace and update) c) a variation of the above with "select, replace, delete and insert" versus "update" How can I do this? I've looked at mysqlsla which is complex, works well but does not quite get what I want. [1] I looked at MyProfi 0.18 which looks like it will get some of the answers but runs out of memory working on the smallest log file (mysql.log) even with memory_limit in php.ini set to 1024MB [2] -rw-r- 1 imran imran 268M 2010-04-19 13:03 mysql.log -rw-r- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1 -rw-r- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2 -rw-r- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3 -rw-r- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4 -rw-r- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5 -rw-r- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6 Any pointers please? If all else fails, I will prolly write a perl script to munge it. [1] http://hackmysql.com/mysqlsla [2] http://myprofi.sourceforge.net -- GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication breakage when Heartbeat Failover occurs
I want to fix a replication issue with a 2-node cluster (one active, one passive) that is using Heartbeat for failover. The nodes are in Master-Master configuration (that is, each is the slave and master of the other). I have several other hosts that are replication slaves from the active node. They connect to MySQL via TCP over an SSH tunnels. When failover occurs, the passive node becomes the active node. However the replication slaves stop replicating. The error from a log on one of the slaves is: Jul 15 07:43:32 mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread: conn ected to master '@127.0.0.1:3307', replication started in log 'mysql-bin.00 0978' at position 23923243 Jul 15 07:43:32 mysqld[1339]: 090715 7:43:32 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( serve r_errno=1236) Jul 15 07:43:32 mysqld[1339]: 090715 7:43:32 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log Jul 15 07:43:32 mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000978', position 23923243 I do not think this is an SSH tunnel issue. I believe this is because of inconsistent binary log file names and positions between the two nodes. Probably because one of the nodes had been in operation a lot longer than the other. At the moment I have to get replication going by dumping the master databases again, re-import to the slave hosts and bootstrap the slaves. What is the best way to make this consistent and ensure that replication continues smoothly after a failover (and failback) event? Thank you, Imran Chaudhry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Interesting SQL Query - Total and Grouped Counts together?
Baron, Thanks very much for that simple but very effective solution. I altered your SQL slightly, the final SQL looks like this: SELECT domain, count(*) AS 'count all', SUM(IF(mime = 'text/html', 1, 0)) AS 'count text', SUM(IF(mime LIKE 'image/%', 1, 0)) AS 'count image' FROM tableA GROUP BY domain ORDER BY domain Thanks again, Imran Chaudhry -- Atom Database A Custom Database Designed for Your Business [EMAIL PROTECTED] http://atomdatabase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interesting SQL Query - Total and Grouped Counts together?
I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of which are defined as: CREATE TABLE tableA ( domain text, mime text ); Where "domain" is a domain, such as: google.com emeraldcity.oohisntitgreen.com teddybears.com An example of rows might be: google.com, text/html google.com, image/gif google.com, image/jpeg google.com, text/html teddybears.com, text/html teddybears.com, image/png google.com, text/html google.com, image/png ... "mime" is defined as having entries such as: text/html image/png image/jpg image/gif application/x-tar What I am after doing with this table is writing an SQL query which produces a count of all domains where the mime is equal to "text/html" and next to that, a total count for that domain where the mime type is "image/*" -- so for example, I might expect the returned resultset to look like: Domain domaincount Mimecountimages - google.com120 12 emeraldcity. 200 40 teddybears.com 11 2 So far, we've considered and tried using a same-table join, various group-by and rollup ideas, but am unable to come up with anything which will produce the above in ONE row for each domain. Any advice and assistance would be great! -- Atom Database A Custom Database Designed for Your Business [EMAIL PROTECTED] http://atomdatabase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error: Duplicate entry 'xxxx-L' for key 6
Key 6 relates to the 6th key, or index, in the CREATE TABLE statement for this table: >> PRIMARY KEY (`prod_id`), >> UNIQUE KEY `prod_no` (`prod_no`), >> KEY `products_index1` (`prod_status`), >> KEY `products_index2` (`prod_start_date`,`prod_end_date`), >> KEY `on_sale` (`on_sale`), >> FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`), >> FULLTEXT KEY `prod_no_2` (`prod_no`) "Key" is another name for "Index", so this is about your FULLTEXT index: FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`) I noticed that the INSERT you listed was for the products table and the CREATE TABLE was for the web_products table. Just to be sure, please can you post the CREATE TABLE statement for the products table? Im -- http://www.atomdatabase.com MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to Relational Model a "Score"
Right guys, I have a score that can be just ONE of several types eg. the score can be a time eg. 01:56.78 (does MYSQL 5.0 now show the fractional part?) or a count eg. 56, 31 etc or a count and a subcategory (just a bit of text) eg. (56, "Variety"), (31, "No Weapon") A score can belong to just one category. A score has one and just one player. What is the best way to model the score? Ideally I want to be able to join score, player and category so I can get a flat list of scores for a player and the category, regardless of how the score is "typed". My bodge solution is to have a score table thus: score: score id category id player id type time char(8) - type of 'time' in 5.0.16 does not show the fractional part :-/ count subcategory Thus for the scores where there is just a time, then type=1 and count and subcategory are NULL. Where the score is a count and subcategory, then type=2 and time is NULL. I want to aim for 3NF and this seems ugly solution, there must be an elegant way to do this? Thanks, Imran Chaudhry -- http://www.atomdatabase.com MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT behavior in Stored Procedure
A while back, someone asked if a stored procedure could take a parameter that can be used within a statement as a value for LIMIT. I have a solution using Prepared Statements and placeholders. Prepared Statements with placeholders are available from 5.0.7. Anyway, here goes using the standard MySQL "world" database as an example: DELIMITER // CREATE PROCEDURE list_cities (IN city_limit INT) BEGIN SET @city_limit = city_limit; PREPARE STMT FROM 'SELECT * FROM City LIMIT ?'; EXECUTE stmt USING @city_limit; END // DELIMITER ; mysql> call list_cities(1); ++---+-+--++ | ID | Name | CountryCode | District | Population | ++---+-+--++ | 1 | Kabul | AFG | Kabol|178 | ++---+-+--++ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call list_cities(5); +++-+---++ | ID | Name | CountryCode | District | Population | +++-+---++ | 1 | Kabul | AFG | Kabol |178 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +++-+---++ 5 rows in set (0.00 sec) Not sure how useful that is. Maybe it is useful if one is frequently querying a certain table when working on the command line? Imran Chaudhry -- http://www.atomdatabase.com MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
Good stuff Peter, can you tell us your solution so folks can benefit from your efforts? (I expect the regex has to be POSIX style such as: mydb./'~(expirations|rtt)'/ right?) Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Issue
On 2/14/06, Ravi Kumar <[EMAIL PROTECTED]> wrote: > Starting mysql with root.I tried withn mysql user account also but still same > error. > thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL PDA Synch
> Thoughts, comments and advice very appreciated Adrian, just in case your question is out of scope for this crowd... MySQL have a number of forums and one is 'Embedded MySQL' http://forums.mysql.com/list.php?58 Hope that helps, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data entry GUI
On 2/1/06, THUFIR HAWAT <[EMAIL PROTECTED]> wrote: > Just as there's MySQL Query Browser for queries and table design, is > there a similar GUI front end for data entry? I'm not building > anything complex, just a simple database to muck with. Is it easier > to do data entry on a spreadsheet, then import the spreadsheet? Seems > a bit silly, but it seems to be either that or I can't recommend a GUI data entry tool but the way I would do it is to use a spreadsheet and save out as Tab-delimited format. Depending on the data I might also rustle up a Perl program to create the data automatically. I would then use LOAD DATA INFILE statement or mysqlimport to get the data into my tables. These methods are extremely quick for inserts and will disable key updating etc. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert performance
> I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin > records. How do I make >my insert run faster.The table has three foreign key > references and the referencing columns are >indexed . Is that impacting the > insert statement performance. Just thought I'd add a little to the good advice already given to you. I find an easy way to derive the extended insert syntax is to perform a mysqldump of a small table. >From 4.1 onwards this wrapped the table dump in the extended insert syntax. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden Mysql Crashes - table damage?
On 1/31/06, Adrian Bruce <[EMAIL PROTECTED]> wrote: > HI > > MySQL 5.0 (windows XP) > > I have a problem where i think some of my tables have somehow been > corrupted, if i try to run a check table command against some tables the > mysql service crashes and i have to restart it. I am not sure how this > damage has occurred but has anyone got any suggestions of what to apart > from dropping the tables? everything i have done so far has caused the > service to crash which makes it pretty hard to find out what is > wrong?!?!?!?! Ade, What ahppens when you perform a mysqldump of the database that contains the corrupts table(s)? Is the process bailing part way through? I've seen people recover from certain corruption issues by dumping the database and recreating from scratch. At any rate, you should take a back-up of either the MyISAM data and .frm files (use BACKUP TABLE) or a mysqldump before starting any repair procedure. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
> Hi; > My query.log is full of 'show innodb status' queries. > How do I get this ascii log file not to log these. OR some help with a > grep script to copy the file without these lines. I noticed the same in the logs of a 4.1 test server. I put it down to MySQL Administrator which was monitoring the server at the time. I believe MYSQL Admin issues these SHOW STATUS commands periodically to refresh it's status info screen. Out of curiosity were/are you using MySQL Administrator or something like a monitoring app with your MySQL servers? Im -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: mysql-plain Digest 24 Jan 2006 10:16:15 -0000 Issue 3629
> My web hosting server runs PHP 4.4 and MySQL 4.1. But
> PHP 4.4 doesn't fully support MySQL 4.1. and i have
> problems restoring a utf8 encoded database dumped from
> a MySQL 4.0 server to the new 4.1 server. Do i have
> any chance to get it working in this setup or should i
> persuade the hosting provider to either upgrade to PHP
> 5 so i can use Connection Character Sets and
> Collations in PHP or downgrade to MySQL 4.0?
Lionel,
Please give us more specific information about the nature of the
problem you are having (eg error messages, codes, log file output).
The password hashing algorithm changed in MySQL 4.1 which is possibly
the cause of your problems with PHP client apps.
Try updating the password for your MySQL database account as follows:
SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Now use these credentials to make a connection at parse some simple
table data. It should work.
Regards,
Im
--
http://www.ImranChaudhry.info
MySQL Database Management & Design Services
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost Connection executing query
The fact that on your Mac installation you re-imported your table data makes me think it's an index efficiency issue. I have seen MySQL just lose the connection if the join becomes massively big. On re-import, the indexes would have been rebuilt and your query would be able to take advantage of the freshest indexes. A bit crude, but on your Production box can may need to do the following on every in your UPDATE: - drop and create every index - perform ANALYZE TABLE (this will help if we ask you to perform EXPLAIN later on) Ideally you have a pre-Production environment with the same database (non-replicated). I would recommend trying the re-index tactic there first if you havne't already. Hope that Helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: logging issue
I'm inclined to think this is a database config issue in MailScanner. Double check the Port setting that MailScanner is using and check that against the port that your MySQL server binds to. It is normally 3306, and they should match. If you don't know how to check, can you send me the output of: netstat -tan | grep 3306 Your log output shows an error code of 110 which means 'Connection timed out'. This is different to 'Connection refused'. Is there a possibility of very high database activity from another source while MailScanner is trying to get at the database? How you tried invoking the MailScanner connection at different times of the day? What interface does MailScanner use to connect? It looks like Perl DBI, but you may be using ODBC? Finally, can you tell me your perl version by: perl -v Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to upgrade
Yes, potentially there are "gotchcas". It all depends on your current MySQL server version and the version you are upgrading to. Assuming you are upgrading 3.23 to 4.0, what I would do is look at the official documentation regarding upgrades from exactly this scenario: http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html I think you'll find the upgrade pretty painless from a data point of view, but you will need to heed the changes to the GRANT tables. Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REVOKE GRANT OPTION - is that possible?
> # mysqlmgr add database www1 > # mysqlmgr add user www1 password xxx > # mysqlmgr allow user www1 database www1 read write define I suppose in some cases this is OK, and I'm sure you will test thoroughly! :-) One thing to bear in mind is that if you define a user and then revoke his privileges, his account details details will still persist in the user Grant table. This may not be a worry to you since he cannot get at any database resource. To remove any trace of him, you must delete him from the user table. (if you're playing with MySQL 5.0 then DROP USER is the ticket). Final thing, as you're a MySQL admin type writing an access control app, you want to know when the priv changes take effect. You will probably find this info useful: http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html Hope that helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REVOKE GRANT OPTION - is that possible?
Yes, and from database and table level too, but you must revoke GRANT OPTION seperately from all other privs like this: REVOKE GRANT OPTION ON .FROM user GRANT and REVOKE are essential to your database security, I would think twice before adding a "layer" on top of them. There is potential for error and you dont want that where security is related. It is probably worth the "pain" of learning the ins and outs of GRANT/REVOKE. Hope that Helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installed modules
Not sure what you mean by 'using MySQL to query your installed modules', sorry. phpinfo() called from a HTML document is one way, but from a command line level, you can do this is you have the php CLI installed: PHP: php -m Perl: If you want to test if a specific module is installed, a way is: perl -M -e '' (where module name is perlish name like DBI or XML::Simple) It should return silently without errors if it is installed. Hope that helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install trouble, perl DBI
Lewis, As you already have perl installed, an easier way of getting modules is by way of CPAN (www.cpan.org). Give this a try, as superuser: cpan install DBI Regards, Imran -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
