Re: Replaying the mysqld.log file from production onto QA???
So.. I am trying to mimic replaying production like queries so joins, temp tables etc... are stuff I am trying to test as well. Just doing a dump and import is no more than export and importing, I also want to test selects, updates :-) Thanks for replying :-) Nunzio From: andrew.2.mo...@nokia.com andrew.2.mo...@nokia.com To: nunziodav...@yahoo.com; mysql@lists.mysql.com Sent: Wed, September 8, 2010 4:19:19 PM Subject: Re: Replaying the mysqld.log file from production onto QA??? Try using a mysqldump. - Reply message - From: ext Nunzio Daveri nunziodav...@yahoo.com Date: Wed, Sep 8, 2010 19:25 Subject: Replaying the mysqld.log file from production onto QA??? To: mysql@lists.mysql.com mysql@lists.mysql.com Hello all, I am trying to REPLAY the mysqld.log file from a production 5.1.4 server onto a QA server with 5.5 running and was wondering if there is an easy way of replaying the mysqld.log file? Just want to make sure all of the kinds of inserts, updates, selects and deletes work just as well on the 5.5 box, esp since we are turning on replication and copying tables from another server onto this server. Any ideas please? TIA... Nunzio
Replaying the mysqld.log file from production onto QA???
Hello all, I am trying to REPLAY the mysqld.log file from a production 5.1.4 server onto a QA server with 5.5 running and was wondering if there is an easy way of replaying the mysqld.log file? Just want to make sure all of the kinds of inserts, updates, selects and deletes work just as well on the 5.5 box, esp since we are turning on replication and copying tables from another server onto this server. Any ideas please? TIA... Nunzio
How To Duplicate Number of Hits from Prod Sever to NEW QA server?
Hello, I have been asked to replay the traffic load we have on one of our 5.1.4X servers against a new 5.5 test server we are getting ready to put into production as a stand alone. My question is that I have 6GB of mysqld.log BUT how to I figure out how MUCH to play back at a given time and how fast / number of clients etc...? The log only tells me this is all the data that has come to the production server but it doesn't tell me how fast, how many connections, how many users at a time, how many inserts, updates or delets per second etc... How do I replay the log against the 5.5 server so as to duplicate real time traffic and not just replay the logs? Is there a tool or a shell script? I know there are built in benchmarking tools but I am trying to tell mgmt that 5.1.4x was lets say 60% percentage busy (cpu/mem/io) with traffic hitting it on Monday, the SAME amount of traffic on 5.5 is only 48% busy. Any help or advise is greatly appreciated please. Thanks in advance. Nunzio
Can't install perl-DBD-MySQL
Hello gurus, I am trying to install the perl-DBD-MySQL and each time I get this error message. Any ideas? I have tried it on a Sun X4150 (Dual Quad Cored Intel) and a Sun X4200 (Dual Dual Core Opteron) both on CentOS 4.8. I have tried to install and build with MySQL 4.x on the box and then removed it and installed 5.x but both times I get the same error message :-( Anyone have any ideas why it constantly dies and how to fix it please? I can't even locate what is is complaining about, I think it's v14 of libmysqlclient.so??? Thanks in advance :-) Nunzio [root]# rpm -i ./perl-DBD-MySQL-2.9004-3.1.centos4.i386.rpm error: Failed dependencies: libmysqlclient.so.14 is needed by perl-DBD-MySQL-2.9004-3.1.centos4.i386 Suggested resolutions: /home/buildcentos/CENTOS/en/4.0/i386/CentOS/RPMS/mysql-4.1.22-2.el4.i386.rpm [root]# updatedb [root]# locate libmysqlclient.so.14 [root]# locate libmysqlclient.so /usr/src/redhat/BUILD/mysql-5.1.44/mysql-debug-5.1.44/libmysql/.libs/libmysqlclient.so.16.0.0 /usr/src/redhat/BUILD/mysql-5.1.44/mysql-debug-5.1.44/libmysql/.libs/libmysqlclient.so /usr/src/redhat/BUILD/mysql-5.1.44/mysql-debug-5.1.44/libmysql/.libs/libmysqlclient.so.16 /usr/lib/mysql/libmysqlclient.so.10.0.0 /usr/lib/mysql/libmysqlclient.so.10 /home/nunzio/mysql-5.1.44-linux-i686-glibc23/lib/libmysqlclient.so /home/nunzio/mysql-5.1.44-linux-i686-glibc23/lib/libmysqlclient.so.16.0.0 /home/nunzio/mysql-5.1.44-linux-i686-glibc23/lib/libmysqlclient.so.16 /mysql_5.1/lib/libmysqlclient.so /mysql_5.1/lib/libmysqlclient.so.16.0.0 /mysql_5.1/lib/libmysqlclient.so.16
Creating a dedicated reporting server for management?
Hello Gurus, I have a customer who wants to create a reporting server for his management team. He wants to take server 1,2,3 and move the 3 databases from all 3 servers to one server server 4 and then have the management team run all the reports from server 4 since there are tons and tons of joins. How can I accomplish this? I can't do replication since server 4 is going to be a slave AND a slave can only have one master so I am sitting here trying to figure out how to get this done? This is an ongoing transfer of data as the reports have to be in sync with the 3 master servers so replication would be perfect. Any help, docs and directions is most appreciated. Thanks In Advance :-) Nunzio
Is this the right export / import command for all databases and users?
Hi all, I have upgraded a few test boxes and everything seems to work fine BUT I wanted to verify with the gurus if my syntax is correct so as to avoid any future problems ;-) The purpose is to dump all databases and users / user privileges from our 4.1.20 server and import it into our 5.1.49 server. On the 4.x server I ran: # mysqldump -A -uroot -p | gzip -4 ./4.x_mysqldump.sql.gz Once done and I have MySQL 5.x running on the same box, I type: # gzip -dc 4.x_mysqldump.sql.gz | mysql -uroot -p Question: So with these 2 commands I export all the databases and user's and user's privileges and then import the databases and user's and user's privileges into 5.x? Question: Will the above export and import change the 5.x mysql root password which is different than the 4.x root password? Question: Will the above export the mysql database (inside mysql 4.x) from 4.x and re-import it into 5.X (the database that controls users and access and ...)? If so is this okay to do since it's a version upgrade? Thanks in advance... Nunzio
Is upgrading from 4.X to 5.X really that easy?
Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio
Re: Is upgrading from 4.X to 5.X really that easy?
Thanks William and Keith. So how to have min down time since this is a stand alone mysql 4.1.22 box? Are you saying install 5.X on the same box (port 3307), then replicate the data as it comes into 4.x to 5.x and when it is all sync'd up then turn 4.x off, remove it and have 5.x responding on port 3306? Mysqldump takes over an hour and then prob more to reimport? I only have a 30 - 45 min window. If I do a straight dump from 4.1.22 with all options why stop at 5.0 and not just go straight to 5.1.48? This is a single box, no replication or clustering going on ;-) Also all the data is in MyISAM, zero InnoDB :-) Thanks again for the advice :-) Nunzio From: Keith Murphy bmur...@paragon-cs.com To: Nunzio Daveri nunziodav...@yahoo.com Sent: Mon, August 16, 2010 9:42:07 AM Subject: Re: Is upgrading from 4.X to 5.X really that easy? No, that would be a huge mistake. There are subtle differences between the two versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous new reserved words. You need to think about this carefully before you do it. I know there is binary incompatability between Innodb tables (vers 4.X - 5.X). I will take you word that what you are saying would actually work, but I still wouldn't recommend it. Just my 2 cents... keith On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri nunziodav...@yahoo.com wrote: Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: Is upgrading from 4.X to 5.X really that easy?
Thanks Keith :-) Last question, do you think it's ok for me to do a sqldump on 4.1.22 at say 3 am on sun, then import to 5.1.48 at 4 am and then just edit etc/hosts and have the web servers now point to 5.1? This should work without any problems right? Plus I have the original 4.x in case I break something during the dump and can revert within mins back to the 4.x version? Still don't know why I should do a mysql dump from 4.1.X to 5.0.x and then upgrade 5.0.x to 5.1.48 esp. if I am doing nothing more than a mysql dump and not upgrading in place ;-) TIA... Nunzio From: Keith Murphy bmur...@paragon-cs.com To: Nunzio Daveri nunziodav...@yahoo.com Cc: mysql@lists.mysql.com Sent: Mon, August 16, 2010 11:47:39 AM Subject: Re: Is upgrading from 4.X to 5.X really that easy? I would really recommend that you have a second server set up running MySQL 5.0. Otherwise the complexity is going to drive you crazy. Either way you are going to have to get a backup of the master (4.1) server somehow. I am curious. If you can't take an hour or so take a mysqldump of the server how are you running backups now? And if you aren't runninng backups you need to run to your boss and say It's REALLY REALLY REALLY critical that we start making backups. And do it beginning tonight at the latest. Otherwise something is going to happen, the data is going to be lost and you are best case going to look REALLY REALLY bad. I wouldn't recommend going straight to 5.1. The upgrade from 5.0 to 5.1 is fairly trivial and doesn't require a dump/reload but I would still take the time to stop at 5.0 and make sure everything is working before moving on to 5.1. keith On Mon, Aug 16, 2010 at 12:42 PM, Nunzio Daveri nunziodav...@yahoo.com wrote: Thanks William and Keith. So how to have min down time since this is a stand alone mysql 4.1.22 box? Are you saying install 5.X on the same box (port 3307), then replicate the data as it comes into 4.x to 5.x and when it is all sync'd up then turn 4.x off, remove it and have 5.x responding on port 3306? Mysqldump takes over an hour and then prob more to reimport? I only have a 30 - 45 min window. If I do a straight dump from 4.1.22 with all options why stop at 5.0 and not just go straight to 5.1.48? This is a single box, no replication or clustering going on ;-) Also all the data is in MyISAM, zero InnoDB :-) Thanks again for the advice :-) Nunzio From: Keith Murphy bmur...@paragon-cs.com To: Nunzio Daveri nunziodav...@yahoo.com Sent: Mon, August 16, 2010 9:42:07 AM Subject: Re: Is upgrading from 4.X to 5.X really that easy? No, that would be a huge mistake. There are subtle differences between the two versions. For example, check up on DECIMAL. Also, 5.0 and 5.1 have numerous new reserved words. You need to think about this carefully before you do it. I know there is binary incompatability between Innodb tables (vers 4.X - 5.X). I will take you word that what you are saying would actually work, but I still wouldn't recommend it. Just my 2 cents... keith On Mon, Aug 16, 2010 at 10:26 AM, Nunzio Daveri nunziodav...@yahoo.com wrote: Hi all, I was reading a few of the notes on this forum from a few months back and it seems that ONE WAY of upgrading from 4.x to 5.X with MyISAM only databases is to copy the actual data folder from the 4.X version to a temp place, then remove 4.x from the OS, install 5.X and then just put the 4.X data folder into the 5.X folder??? Is it really that simple? Has anyone done this and can verify this please? I am thinking I am missing a few commands you have to run at least??? My database is pretty small is an only 1.8GB so I am thinking this is a walk in the park :-) Please advise... And as always... TIA... Nunzio -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- Chief Training Officer Paragon Consulting Services 850-637-3877
RHEL Auto Start / stop mysql???
Hi all, I just installed the no-install version of mysql 5.1.44 on our RHEL 5 box AFTER I yum removed the older 4.X version. The question is how do I tell RHEL to auto start and stop mysql when I have to reboot the server? I know that if you use an rpm or a yum it kinda / sorta created a mysqld startup script in /etc/init.d but what all do I have to have and how do I set it up please since all I did was untar the file and then type in mysql_5/bin/mysqld_safe ? I tried using the service command, but no luck there either. Any help is much appreciated... Nunzio
Re: RHEL Auto Start / stop mysql???
Hi Guifre, thanks for answering. I already have mysql installed and works just fine, but I did untar and then go to folder and run. I used what is called mysql no-install so no yum, rpm etc.. No files in /etc/init.d and no startup or services script since this is using the no-install version. Thanks... Nunzio From: Guifre Bosch Fabregas guifre.bo...@gmail.com To: Nunzio Daveri nunziodav...@yahoo.com Cc: mysql@lists.mysql.com Sent: Thu, August 12, 2010 3:40:32 PM Subject: Re: RHEL Auto Start / stop mysql??? hi Nunzio! Sorry but my english is very poor... (reading and writing! Thanks http://translate.google.com/ ! ) At first can you install yet mysql? If you have installet yet mysql you can go to /etc/init.d and look if the startup script is in this directory. I understand your question right? 2010/8/12 Nunzio Daveri nunziodav...@yahoo.com Hi all, I just installed the no-install version of mysql 5.1.44 on our RHEL 5 box AFTER I yum removed the older 4.X version. The question is how do I tell RHEL to auto start and stop mysql when I have to reboot the server? I know that if you use an rpm or a yum it kinda / sorta created a mysqld startup script in /etc/init.d but what all do I have to have and how do I set it up please since all I did was untar the file and then type in mysql_5/bin/mysqld_safe ? I tried using the service command, but no luck there either. Any help is much appreciated... Nunzio -- Guifre Bosch Fabregas Tlf.: 687911075
Re: Dropping ALL indexes from a database / not just a table?
How does one do that? How do you drop auto-increment attribute then drop the
index then restart the auto increment value where it was before you dropped
it?? I did not know you could do that.
The reason I ask is because the dbf_UID is a unique id tag the coders use to
identify a product by manufacturer kinda like a upc code for their internal db.
Can't have dups and don't want to have non-used id's in the db.
Any help, direction is much appreciated.
TIA...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 5:03:44 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
auto_increment is only allowed on primary-keyed columns. I expect it
is not allowing you to drop the primary key because that column has
the auto_increment attribute. Drop that manually, and the primary key
should be able to let go.
- md
On Tue, Aug 10, 2010 at 5:58 PM, Nunzio Daveri nunziodav...@yahoo.com wrote:
Hi Micheal and all, ok so I did some digging around and I still can't find
why I cant drop the last few indexes.
mysql SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema
= 'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY';
+--+
| COUNT(1) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql DESCRIBE dbt_Fruit;
+--+--+--+-+-++
| Field| Type | Null | Key | Default |
Extra |
+--+--+--+-+-++
| dbf_UID | int(10) unsigned | NO | PRI | NULL|
auto_increment |
| dbf_Vendor | varchar(30) | NO | |
||
| dbf_Code | varchar(30) | NO | |
||
| dbf_Notes| text | YES | | NULL
||
+--+--+--+-+-++
mysql ALTER TABLE dbt_Fruit DROP FOREIGN KEY dbf_UID;
Query OK, 2947 rows affected (0.05 sec)
Records: 2947 Duplicates: 0 Warnings: 0
mysql ALTER TABLE dbt_Fruit DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto
column and it must be defined as a key
mysql ALTER TABLE dbt_Fruit DROP PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '' at line 1
Any ideas??? I am wondering if it has something to do with the fact that
dbf_UID is a primary AND auto_increment?
TIA...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 4:10:37 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
It's not a completely solution and will need some tweaking.. You
might have to run the PRIMARY KEYS distinctly from the rest.
- michael dykman
On Tue, Aug 10, 2010 at 4:43 PM, Nunzio Daveri nunziodav...@yahoo.com
wrote:
Hello Michael, thanks for the one liner. I ran it BUT I started to get
errors after I ran it the first time, this is what I got the 2nd time I
ran
it (first time I ran it I had 63 rows in the query, the 2nd time I have
9).
I ran it twice to make sure it got rid of the indexed. I verified the
index
size dropped from 850 mb to 65 mb.
+-+
| concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';')
|
+-+
| ALTER TABLE dbt_Fruits DROP INDEX PRIMARY; |
| ALTER TABLE dbt_Veggies DROP INDEX PRIMARY;
|
.
.
.
| ALTER TABLE dbt_Logs DROP INDEX
PRIMARY; |
+-+
9 rows in set (0.01 sec)
mysql ALTER TABLE dbt_Fruits DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'PRIMARY' at line 1
mysql ALTER TABLE dbt_Logs DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'PRIMARY' at line 1
mysql
Thanks again...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 3:17:48 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
This should give
Re: Dropping ALL indexes from a database / not just a table?
Thanks for the feedback. What I am trying to do is two things: 1. Remove all indexes and make the database smaller to copy and move to another prod box. Currently my indexes are in the double digit GB! Yikes ;-) 2. Remove all indexes so I can find out which ones are needed then tell mysql to recreate them and apparently it lessen data fragmentation if it starts from scratch vs. turning on and off. I was hoping to just remove all and then start from scratch so I know the data is not fragmented on the drives. Thanks again... Nunzio From: Anirudh Sundar sundar.anir...@gmail.com To: Nunzio Daveri nunziodav...@yahoo.com Cc: mysql@lists.mysql.com Sent: Tue, August 10, 2010 1:06:41 AM Subject: Re: Dropping ALL indexes from a database / not just a table? Hello Nunzio, Instead of Dropping a index, you can disable the indexes and get the work done and re-enable them. If you are ok with this then run the below as a shell script :- MUSER=username MPASS=password DATABASE=dbname for db in $DATABASE do echo starting disabling indexes for database -- $db echo -- TABLES=`mysql -u $MUSER -p$MPASS $db -e show tables` for table in $TABLES do mysql -u $MUSER -p$MPASS $db -e Alter table $table disable keys done echo completed disabling indexes for database -- $db done Cheers, Anirudh Sundar On Tue, Aug 10, 2010 at 1:33 AM, Nunzio Daveri nunziodav...@yahoo.com wrote: Hello Gurus, is there a way / script that will let me DROP ALL the indexes in a single database? for example, lets say my database is call db_Animals, and inside db_Animals there are 97 tables, is there a SINGLE command or a perl script of some kind that can read all the MYI files, remove the .MYI from the file name then proceed to deleting whatever indexes it finds? I am doing this to debug a server that seems to be slow and sluggish. After I am done deleting I will review the slow query logs and then re-index to get the best performance? TIA... Nunzio
Re: Dropping ALL indexes from a database / not just a table?
Hello Michael, thanks for the one liner. I ran it BUT I started to get errors
after I ran it the first time, this is what I got the 2nd time I ran it (first
time I ran it I had 63 rows in the query, the 2nd time I have 9). I ran it
twice to make sure it got rid of the indexed. I verified the index size
dropped
from 850 mb to 65 mb.
+-+
| concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';') |
+-+
| ALTER TABLE dbt_Fruits DROP INDEX PRIMARY; |
| ALTER TABLE dbt_Veggies DROP INDEX PRIMARY; |
.
.
.
| ALTER TABLE dbt_Logs DROP INDEX PRIMARY; |
+-+
9 rows in set (0.01 sec)
mysql ALTER TABLE dbt_Fruits DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'PRIMARY' at line 1
mysql ALTER TABLE dbt_Logs DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'PRIMARY' at line 1
mysql
Thanks again...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 3:17:48 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
This should give you a good starting point (not tested):
select distinct concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ',
CONSTRAINT_NAME,';')
from information_schema.key_column_usage where
TABLE_SCHEMA='mydatabase';
- md
On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri nunziodav...@yahoo.com wrote:
Thanks for the feedback. What I am trying to do is two things:
1. Remove all indexes and make the database smaller to copy and move to
another
prod box. Currently my indexes are in the double digit GB! Yikes ;-)
2. Remove all indexes so I can find out which ones are needed then tell mysql
to
recreate them and apparently it lessen data fragmentation if it starts from
scratch vs. turning on and off.
I was hoping to just remove all and then start from scratch so I know the data
is not fragmented on the drives.
Thanks again...
Nunzio
From: Anirudh Sundar sundar.anir...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Tue, August 10, 2010 1:06:41 AM
Subject: Re: Dropping ALL indexes from a database / not just a table?
Hello Nunzio,
Instead of Dropping a index, you can disable the indexes and get the work done
and re-enable them.
If you are ok with this then run the below as a shell script :-
MUSER=username
MPASS=password
DATABASE=dbname
for db in $DATABASE
do
echo starting disabling indexes for database -- $db
echo --
TABLES=`mysql -u $MUSER -p$MPASS $db -e show tables`
for table in $TABLES
do
mysql -u $MUSER -p$MPASS $db -e Alter table $table disable keys
done
echo completed disabling indexes for database -- $db
done
Cheers,
Anirudh Sundar
On Tue, Aug 10, 2010 at 1:33 AM, Nunzio Daveri nunziodav...@yahoo.com wrote:
Hello Gurus, is there a way / script that will let me DROP ALL the indexes in
a
single database? for example, lets say my database is call db_Animals, and
inside db_Animals there are 97 tables, is there a SINGLE command or a perl
script of some kind that can read all the MYI files, remove the .MYI from the
file name then proceed to deleting whatever indexes it finds? I am doing this
to debug a server that seems to be slow and sluggish. After I am done
deleting
I will review the slow query logs and then re-index to get the best
performance?
TIA...
Nunzio
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
Re: Dropping ALL indexes from a database / not just a table?
Hi Micheal and all, ok so I did some digging around and I still can't find why
I
cant drop the last few indexes.
mysql SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema =
'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY';
+--+
| COUNT(1) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql DESCRIBE dbt_Fruit;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| dbf_UID | int(10) unsigned | NO | PRI | NULL| auto_increment |
| dbf_Vendor | varchar(30) | NO | | ||
| dbf_Code | varchar(30) | NO | | ||
| dbf_Notes| text | YES | | NULL||
+--+--+--+-+-++
mysql ALTER TABLE dbt_Fruit DROP FOREIGN KEY dbf_UID;
Query OK, 2947 rows affected (0.05 sec)
Records: 2947 Duplicates: 0 Warnings: 0
mysql ALTER TABLE dbt_Fruit DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto
column and it must be defined as a key
mysql ALTER TABLE dbt_Fruit DROP PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
Any ideas??? I am wondering if it has something to do with the fact that
dbf_UID is a primary AND auto_increment?
TIA...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 4:10:37 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
It's not a completely solution and will need some tweaking.. You
might have to run the PRIMARY KEYS distinctly from the rest.
- michael dykman
On Tue, Aug 10, 2010 at 4:43 PM, Nunzio Daveri nunziodav...@yahoo.com wrote:
Hello Michael, thanks for the one liner. I ran it BUT I started to get
errors after I ran it the first time, this is what I got the 2nd time I ran
it (first time I ran it I had 63 rows in the query, the 2nd time I have 9).
I ran it twice to make sure it got rid of the indexed. I verified the index
size dropped from 850 mb to 65 mb.
+-+
| concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';') |
+-+
| ALTER TABLE dbt_Fruits DROP INDEX PRIMARY; |
| ALTER TABLE dbt_Veggies DROP INDEX PRIMARY; |
.
.
.
| ALTER TABLE dbt_Logs DROP INDEX
PRIMARY; |
+-+
9 rows in set (0.01 sec)
mysql ALTER TABLE dbt_Fruits DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'PRIMARY' at line 1
mysql ALTER TABLE dbt_Logs DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'PRIMARY' at line 1
mysql
Thanks again...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 3:17:48 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
This should give you a good starting point (not tested):
select distinct concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ',
CONSTRAINT_NAME,';')
from information_schema.key_column_usage where
TABLE_SCHEMA='mydatabase';
- md
On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri nunziodav...@yahoo.com
wrote:
Thanks for the feedback. What I am trying to do is two things:
1. Remove all indexes and make the database smaller to copy and move to
another
prod box. Currently my indexes are in the double digit GB! Yikes ;-)
2. Remove all indexes so I can find out which ones are needed then tell
mysql to
recreate them and apparently it lessen data fragmentation if it starts
from
scratch vs. turning on and off.
I was hoping to just remove all and then start from scratch so I know the
data
is not fragmented on the drives.
Thanks again...
Nunzio
From: Anirudh Sundar sundar.anir...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Tue, August 10, 2010 1:06:41 AM
Subject: Re: Dropping ALL indexes from a database
Dropping ALL indexes from a database / not just a table?
Hello Gurus, is there a way / script that will let me DROP ALL the indexes in a single database? for example, lets say my database is call db_Animals, and inside db_Animals there are 97 tables, is there a SINGLE command or a perl script of some kind that can read all the MYI files, remove the .MYI from the file name then proceed to deleting whatever indexes it finds? I am doing this to debug a server that seems to be slow and sluggish. After I am done deleting I will review the slow query logs and then re-index to get the best performance? TIA... Nunzio
Re: Moving from one MySQL server to three MySQL servers?
Hi all, thanks for the feedback. Good information for me to work with :-) The server in this case is a stand alone with nothing more then CentOS and MySQL 5.1.44 on it. The drives are sas 10K rpm drives. The problem I see is that when you stress test the server (typically by running loads of reports - selects, joins) the machine hits 98% cpu and leaves only 800mb of free RAM out of the 16 GB of which I told it to allocate 12GB for Innodb in my.cnf. Once the server sucks up all the memory when we are stress testing it, it holds the 12 gb as hostage and refuses to release it back into the pool, regardless of weather there is load or not and on box. So when I do top-c before I run the reports, it says mysql is using 2GB, then I run the stress test (several reports) and it hits 12GB then I stop the stress and even 30 mins later the server says there is only 800mb of ram free??? If I start to stress it again then it starts to go into swap. Really weird, thus wanting to split the load onto 3 machines. Also it doesn't help when your innodb index is larger than physical memory ;-) The server only reports 50 to 100 slow queries per day out of the hundreds and thousands of queries it is running. But after all the chatter, I think I will use one of our test/dev servers, install fresh OS, install 5.1.49 then import the db without indexing, run a good 100mb of sql statements against it from our prod servers logs, then look for what fields need to be indexed under slow query logs and then go from there. Is this a good idea vs. going straight to splitting the load into 3 servers? I KNOW the tables and format and the way they have setup the database including Indexing is bad, but mgmt says throw hardware as it's cheaper then re-writting code and re-architecting the db ;-) Again... Thanks for all of your feedback Gurus :-) Nunzio From: Steven Staples sstap...@mnsi.net To: Nunzio Daveri nunziodav...@yahoo.com; mysql@lists.mysql.com Sent: Thu, August 5, 2010 7:23:19 AM Subject: RE: Moving from one MySQL server to three MySQL servers? Have you double checked the hardware? Are you using 5400rpm drives, or 15k rpm drives? I/O bottlenecks are common, if you can't read the data fast enough, then it will definitely be slower, and appear to have more issues that it really does. If the client can't/won't change/alter the code, then maybe looking at changing the hardware would be better. Having a smaller drive size raid array with faster harddrives may solve the I/O bottleneck if that is the case. And maybe it is just poorly written queries with crappy indexing? Maybe look at the slow query log, and ensure that the RIGHT indexes are there (140gb/21gb index doesn't mean that the indexes are the correct ones) Going to a replication setup may not be the solution to your problems, and could just be a bandaid (and prolly cause you many sleepless nights maintaining data integrity). Find out the cause of the problem, before adding to it. Steven Staples -Original Message- From: Nunzio Daveri [mailto:nunziodav...@yahoo.com] Sent: August 4, 2010 2:40 PM To: mysql@lists.mysql.com Subject: Moving from one MySQL server to three MySQL servers? Hello Gurus :-) I was running a simple load generator against our 16GB Dual Quad core server and it pretty much came down to it's knees within two hours of running tests. The customer DOES NOT WANT to change any code, they just want to throw hardware at it since it took them a year to create all of the code. It is a 140GB database with 21GB of indexs all using InnoDB - currently doing 70% reads and 30% writes. My question is what is the best way of distributing the load without changing any of the php / perl code that their web server uses? This is what I am thinking but need someone to tell me it is a good idea or bad please? 1. Setup a single master and 2 slaves. The question is how to tell the web servers to get all the read data from the slaves and to only write to the master? 2. Install a MySQL proxy box and let mysql proxy handle the load, problem is now it is the SPOF! 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master for writes and ONLY use one of the 2 slaves for reads? Any links, ideas or suggestions is most appreciated. TIA... Nunzio No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/04/10 00:45:00
Moving from one MySQL server to three MySQL servers?
Hello Gurus :-) I was running a simple load generator against our 16GB Dual Quad core server and it pretty much came down to it's knees within two hours of running tests. The customer DOES NOT WANT to change any code, they just want to throw hardware at it since it took them a year to create all of the code. It is a 140GB database with 21GB of indexs all using InnoDB - currently doing 70% reads and 30% writes. My question is what is the best way of distributing the load without changing any of the php / perl code that their web server uses? This is what I am thinking but need someone to tell me it is a good idea or bad please? 1. Setup a single master and 2 slaves. The question is how to tell the web servers to get all the read data from the slaves and to only write to the master? 2. Install a MySQL proxy box and let mysql proxy handle the load, problem is now it is the SPOF! 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master for writes and ONLY use one of the 2 slaves for reads? Any links, ideas or suggestions is most appreciated. TIA... Nunzio
Re: Importing User credentials from mysql.sql file???
Thanks Paul, I guess then all I need to do is remove the root entries at the begining of the insert statement and then just insert the rest into 5.X since the column names are there it should just import with no problems right? Thanks again :-) Nunzio From: Paul DuBois paul.dub...@oracle.com To: Nunzio Daveri nunziodav...@yahoo.com Cc: mysql@lists.mysql.com Sent: Tue, August 3, 2010 12:09:05 AM Subject: Re: Importing User credentials from mysql.sql file??? On Aug 2, 2010, at 3:57 PM, Nunzio Daveri wrote: Hello Gurus, I just upgraded several MySQL 4.1 to 5.1 versions and also wanted to know how to extract the user name, password and credentials from the mysql.sql file (around 22 of them per server - have 8 servers total)? The contract admin emailed me a sql file which is a dump of the default mysql database from the 4.1 version and I am trying to see if I can just grep out of the mysql.sql file the INSERT INTO... from the .sql file and import that into the 5.1. When I tried it on our test box it keeps on saying: ERROR 1136 - Column count doesn't match value count at row 2? Can someone please tell me how I can extract the data out of the 4.1 mysql.sql file and inject it into the 5.1 version please? Ask the admin to re-dump the data, this time using mysqldump --complete-insert so that the INSERT statements include the column names. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com
Importing User credentials from mysql.sql file???
Hello Gurus, I just upgraded several MySQL 4.1 to 5.1 versions and also wanted to know how to extract the user name, password and credentials from the mysql.sql file (around 22 of them per server - have 8 servers total)? The contract admin emailed me a sql file which is a dump of the default mysql database from the 4.1 version and I am trying to see if I can just grep out of the mysql.sql file the INSERT INTO... from the .sql file and import that into the 5.1. When I tried it on our test box it keeps on saying: ERROR 1136 - Column count doesn't match value count at row 2? Can someone please tell me how I can extract the data out of the 4.1 mysql.sql file and inject it into the 5.1 version please? Thanks in advance... Nunzio
Re: CentOS 4.8 no-install of MySQL 5.1.4X???
Thanks Claudio but I do that all the time. The question is where is the file? You said to go to the download area, there are hundreads of files and not one tar file says rhel or cent os. What is the URL / file name to download and untar please? Thanks for responding :-) Nunzio
Re: CentOS 4.8 no-install of MySQL 5.1.4X???
Awesome, thanks for helping that's just what I needed :-) Nunzio From: Joerg Bruehe joerg.bru...@oracle.com To: Nunzio Daveri nunziodav...@yahoo.com Cc: MySQL General List mysql@lists.mysql.com Sent: Fri, July 30, 2010 10:49:14 AM Subject: Re: CentOS 4.8 no-install of MySQL 5.1.4X??? Hi Nunzio, all (cc' to the list)! Nunzio Daveri schrieb: Hello Joerg, thanks for replying. I am at: http://mysql.llarian.net/Downloads/MySQL-5.1/ I cannot comment on that server, do neither know it nor the origin of the packages they provide (own builds or mirrored ones from the MySQL Build Team). I am looking for what you described but cannot find a 5.1.44 for CentOS 4.8 on Intel Quad Core CPU??? Any help is most appreciated. I have no problems with Solaris but need it for CentOS 4.8 specificially on intel dual cpu quad core x5000 seriec chipset running 64 bit with 16GB Ram. That's ok, the packages built by MySQL (- Sun - Oracle) do not differ between single- and multi-CPU machines. Based on what you have said, I should be looking for a: mysql-5.1.44-linux-x86_64.tar.gz Right. but all I find is a: mysql-5.1.44.tar.gz That's the plain sources. What am I doing wrong please? You might try the MySQL download site I mentioned: http://dev.mysql.com/downloads/mysql/5.1.html If you are looking for 5.1.44, you will not find it directly on that page, because the current version is 5.1.49. But at the very bottom of the page, in the second column, there is a link Archives that will get you to http://downloads.mysql.com/archives.php Click MySQL Database Server 5.1 (4th entry from the top), on the next page select 5.1.44, on the next page the first five entries are generic Linux tar.gz packages. HTH, Jörg From: Joerg Bruehe joerg.bru...@oracle.com To: Nunzio Daveri nunziodav...@yahoo.com Cc: mysql@lists.mysql.com Sent: Fri, July 30, 2010 9:19:29 AM Subject: Re: CentOS 4.8 no-install of MySQL 5.1.4X??? Nunzio, all, Nunzio Daveri schrieb: [[...]] What is the URL / file name to download and untar please? It depends on your CPU: Go to http://dev.mysql.com/downloads/mysql/5.1.html;, [[...]] -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Thanks again :-) Nunzio From: Joerg Bruehe joerg.bru...@oracle.com To: Nunzio Daveri nunziodav...@yahoo.com; mysQL General List mysql@lists.mysql.com Sent: Fri, July 30, 2010 1:31:54 PM Subject: Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?) Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603
Can I have 30GB of Innodb Index on a 16GB 2CPU quad core server?
Hi All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Any suggestions on what I should do? I am thinking of doing one of these: 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. Thanks... Nunzio
CentOS 4.8 no-install of MySQL 5.1.4X???
Hello Gurus, I am trying to see if there is a no install version of MySQL 5.1.4X for Cent OS 4.8? We got a copy for Solaris x86 and it works AWESOME, I cant seem to find one for Cent OS? We wanted to install several flavors and test a 200 MB query script file against it to see how the performance changes between the iterations of the software. Any help / advice is much appreciated. P.S. I know there are RPM's but the last time I tried using RPM's it started to rip other components out of of prod boxes and I was in backup mode restoring from the AM snapshot. Besides RPM's dont like to go up a version then down a version like in a sandbox ;-) Thanks... Nunzio
MySQL 5.0.44 with Innodb Max memory problem :-(
Hello Gurus, I just inhereted a Sun 2 U Server with 2 Intel Quad Core CPU's and 16 GB of ram. Here is the problem. The machine is constantly at 99% Memory utilization and we get random row locking, we are only using InnoDB. The database is around 150GB with over 5,000 tables. To make things worse, if I shutdown MySQL, top-c still says all the memory is still used? Is this a bug, why would it say all the memory is used when I turn off MySQL. The weird thing is that when I reboot it is fine and purring. When I start to create reports and run a few SUPER THICK queries, is starts to eat up memory and CPU then once it hits it's 12GB limit that I have setup in the my.cnf file it just stays there. The coders are using indexing for several hundread tables so I am wondering if it just loaded all of the index into memory based on my query and then it just leaves it there, BUT if that is the case why is the OS still max'd out of memory when I shutdown MySQL??? :-( Any help, guidance or advise is much appreciated. Nunzio
