Re: i need advice on redundancy of mysql server.
Guys - thanks for the replys - do any of you guys are on odesk or elancer.com ??
thanks
On Wed, Jun 13, 2012 at 7:28 PM, Rick James wrote:
> I prefer:
> * Master-Master ("dual master") but write to only one of them. (Writing to
> both can lead to duplicate keys, etc., unless you are very careful in your
> code.)
> * Have the two Masters geographically separate. (Think tornados, floods,
> earthquakes, etc)
> * Have Slave(s) hanging of each master -- (1) for read scaling, and (2) to
> avoid a major outage when one Master goes down and you need to take the other
> one down to clone it.
>
> Another thing to consider:
> Backing up via a "LVM snapshot" requires only a minute or so of downtime,
> regardless of dataset size.
> Percona's XtraBackup is also very good.
>
> I also agree that MyISAM in not best. But, caution, InnoDB's disk footprint
> is 2x=3x bigger than MyISAM's.
>
> You can Load Balance reads (among slaves and, optionally, masters); you
> cannot do writes.
>
> Any number of Apache servers can talk to MySQL. But watch out -- MaxClients
> should not be so large that it swamps max_connections.
>
> Load balancing:
> DNS is the simple way to load balance Apache.
> There are low-impact software solutions.
> There are hardware solutions. (This is what I am used to at work; it is
> severe overkill for most users.)
>
> Bottom line: There is no "best" or "perfect" solution. First decide what
> 'keeps you up at night'.
>
>> -Original Message-
>> From: Joey L [mailto:mjh2...@gmail.com]
>> Sent: Monday, June 11, 2012 7:26 AM
>> To: mysql@lists.mysql.com
>> Subject: i need advice on redundancy of mysql server.
>>
>> I am running a site with about 50gig myisam databases which are the
>> backend to different websites.
>> I can not afford any downtime and the data is realtime.
>>
>> What is the best method for this setup? master-master or master-slave?
>>
>> What are the best utilities to create and maintain this setup? as far
>> as load balancing between the two physical servers that i am running.
>> I am currently working with percona utilities - is there something
>> better ?
>> what would you use to load balance mysql ? what would you use to load
>> balance apache.
>>
>>
>> thanks
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
It sounds like you are all consultants. On Mon, Jun 11, 2012 at 4:46 PM, Baron Schwartz wrote: > Yeah -- that was an unintentional omission. There are solo consultants > like Ronald Bradford too. > > On Mon, Jun 11, 2012 at 3:14 PM, Andrew Moore wrote: >> Not forgetting Pythian, Baron ;) >> >> On Mon, Jun 11, 2012 at 8:12 PM, Baron Schwartz wrote: >>> >>> Ultimately, if you intend to use MyISAM, you must keep in mind that it >>> eliminates some of your options. One problem is that MyISAM is very >>> slow to repair after a crash. Remember, if a crash can happen, it >>> eventually will, it's just a question of when. And MyISAM doesn't have >>> recovery -- it only has "repair," which will not necessarily recover >>> all of your data. >>> >>> If you are not aware of Percona XtraDB Cluster, it might be >>> interesting for you. (I work for Percona.) There is also Continuent >>> Tungsten to consider. >>> >>> Frankly, though, I'd step back a bit from such microscopic focus on >>> technologies. It looks like you need advice from someone who's done >>> this before, to get the high-level things right before you dive deeply >>> into details. If it's really this important, I personally wouldn't >>> trust it to a mailing list, I'd hire someone. It's well worth it. >>> There's Percona again, of course, but there's also MySQL, SkySQL, >>> PalominoDB, and lots more to choose from. >>> >>> Baron >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: http://lists.mysql.com/mysql >>> >> > > > > -- > Baron Schwartz > Author, High Performance MySQL > http://www.xaprb.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
Sorry new to this part - but I am looking for both. I have setup similar configuration using other technologies. I was asking the group for recommendations - concrete ones ? Can you offer up any ? On Mon, Jun 11, 2012 at 11:54 AM, Andrew Moore wrote: > That's not a description of 'load balancing'; it is a high availability > solution you're looking for. > > > On Mon, Jun 11, 2012 at 4:43 PM, Joey L wrote: >> >> I understand ..I am looking for load balancing - something that i do >> not have to worry about if one server goes down - the other server >> will be up and running by itself and i can bring back the other server >> later on when i have time. >> >> On Mon, Jun 11, 2012 at 10:36 AM, Ananda Kumar wrote: >> > when u say redudency. >> > Do u just want replication like master-slave, which will be >> > active-passive >> > or >> > Master-master which be active-active. >> > >> > master-slave, will work just a DR, when ur current master fails you can >> > failover the slave, with NO LOAD balancing. >> > >> > Master-master allows load balancing. >> > >> > On Mon, Jun 11, 2012 at 7:56 PM, Joey L wrote: >> >> >> >> I am running a site with about 50gig myisam databases which are the >> >> backend to different websites. >> >> I can not afford any downtime and the data is realtime. >> >> >> >> What is the best method for this setup? master-master or master-slave? >> >> >> >> What are the best utilities to create and maintain this setup? as far >> >> as load balancing between the two physical servers that i am running. >> >> I am currently working with percona utilities - is there something >> >> better >> >> ? >> >> what would you use to load balance mysql ? what would you use to load >> >> balance apache. >> >> >> >> >> >> thanks >> >> >> >> -- >> >> MySQL General Mailing List >> >> For list archives: http://lists.mysql.com/mysql >> >> To unsubscribe: http://lists.mysql.com/mysql >> >> >> > >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
You listed a lot of things - but no solution - i am looking for master - master configuration. Any tools you have used ? Anything concrete you can offer? thanks On Mon, Jun 11, 2012 at 11:39 AM, Shawn Green wrote: > On 6/11/2012 10:36 AM, Ananda Kumar wrote: >> >> ... >> Master-master allows load balancing. >> > > Why do people keep replication rings as if they are the best possible > configuration? A master-slave relationship also permits load balancing and > is easier to maintain and recover in the event of a node failure. > > Any MySQL replication topology cannot, in a generic sense, allow load > balancing. Careful precautions must be maintained in your load balancer and > in your application code to avoid data collisions (adding or modifying the > same PK on the same table on two or more nodes at the same time). > > For continuous uptime, you do need redundancy. For the 'limited downtime' > scenario that the customer proposed, this includes geographical redundancy > as well as physical duplication and should also include sharding his data so > that the loss of one sharded set (due to extreme disaster or comms failure) > does not knock all of his users offline at the same time. > > "realtime" data is also a variable definition. For hydrologic monitoring > data 'realtime' could mean within the last 15 minutes. For some > applications (such as telecommunication) 'realtime' is measured in > microseconds. It may be that the customer's requirements can tolerate a > normal variance in duplication time provided by MySQL's native replication. > If not, then rewriting (notice I did not say "porting") their application to > use MySQL Cluster may be the way to meet their "realtime" requirements. > > Yes, master-master replication can be useful (in an active-passive setup) > for rapid failover and recovery if you take the appropriate precautions. It > is not recommended for the newest administrators because recovery can become > complicated. One should really understand basic, top-down master-slave > replication before attempting to create a replication ring. > > Active-Active (dual master) configuration is even more complicated and is > suited only for specific application purposes. This is definitely an > advanced technique and requires careful planning and engineering to perform > properly. > > Regards, > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: i need advice on redundancy of mysql server.
I understand ..I am looking for load balancing - something that i do not have to worry about if one server goes down - the other server will be up and running by itself and i can bring back the other server later on when i have time. On Mon, Jun 11, 2012 at 10:36 AM, Ananda Kumar wrote: > when u say redudency. > Do u just want replication like master-slave, which will be active-passive > or > Master-master which be active-active. > > master-slave, will work just a DR, when ur current master fails you can > failover the slave, with NO LOAD balancing. > > Master-master allows load balancing. > > On Mon, Jun 11, 2012 at 7:56 PM, Joey L wrote: >> >> I am running a site with about 50gig myisam databases which are the >> backend to different websites. >> I can not afford any downtime and the data is realtime. >> >> What is the best method for this setup? master-master or master-slave? >> >> What are the best utilities to create and maintain this setup? as far >> as load balancing between the two physical servers that i am running. >> I am currently working with percona utilities - is there something better >> ? >> what would you use to load balance mysql ? what would you use to load >> balance apache. >> >> >> thanks >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
i need advice on redundancy of mysql server.
I am running a site with about 50gig myisam databases which are the backend to different websites. I can not afford any downtime and the data is realtime. What is the best method for this setup? master-master or master-slave? What are the best utilities to create and maintain this setup? as far as load balancing between the two physical servers that i am running. I am currently working with percona utilities - is there something better ? what would you use to load balance mysql ? what would you use to load balance apache. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: multi master auto syncing when servers back on line
Can you tell me what permissions are needed for the user to run these
utilities so i can sync the tables ?
I have only assigned them replication slave right.
thanks
On Wed, Jun 6, 2012 at 9:09 AM, Andrew Moore wrote:
> Joey, sounds like you've got a lot of testing to do before you can deploy
> and support this in production yourself with out fear of shooting yourself
> in the foot. Whilst you're trialling things, you could try 'XtraDB Cluster'
> from percona.
>
> A
>
>
> On Wed, Jun 6, 2012 at 5:09 AM, Joey L wrote:
>>
>> My current situation arises out of testing really - i restored a db
>> that is out of sync with the other server (having less records) and
>> then i tried to update the "master" server - and only seen the records
>> that I updated and not the records that were additional to the other
>> server when I first restored.
>>
>> I am just trying different scenerios before i put this in production.
>> I was looking to do load balancing and i am concerned about
>> high-availablitity.
>> Is there a product that is opensource out there that I can use to
>> maintian a master master setup with ease ?
>>
>>
>> thanks
>> mjh
>>
>> On Tue, Jun 5, 2012 at 6:18 PM, Andrew Moore wrote:
>> > Joey,
>> >
>> > The master master replication topology comes with it's own potential
>> > pitfalls and trials. Be sure your use case needs master master and that
>> > you're not implementing it because you think it's 'nice to have'.
>> >
>> > pt-table-sync should assist you getting your data in sync but how have
>> > you
>> > arrived at this out-of-sync predicament you find yourself in?
>> >
>> > A
>> >
>> >
>> >
>> > On Tue, Jun 5, 2012 at 11:03 PM, Joey L wrote:
>> >>
>> >> with all do respect - I am new to this - i did read the docs and
>> >> having a hard time.
>> >> I also was asking if you know something easier or does the trick as
>> >> this utility does.
>> >> thanks
>> >>
>> >> On Tue, Jun 5, 2012 at 5:06 PM, Claudio Nanni
>> >> wrote:
>> >> > Joey,
>> >> >
>> >> > from what I can see from your email you lack of a lot of basics and I
>> >> > suggest you to read some documentation before proceeding.
>> >> >
>> >> > Maatkit is now Percona Toolkit and contains some of the best tools
>> >> > for
>> >> > MySQL.
>> >> >
>> >> > Cheers
>> >> >
>> >> > Claudio
>> >> >
>> >> > 2012/6/5 Joey L
>> >> >>
>> >> >> Hi -
>> >> >> I have setup mysql mult master setup on debian squeeze.
>> >> >> I have realized that the databases have to be initially in sync
>> >> >> before
>> >> >> multi master can operate properly.
>> >> >>
>> >> >> This can require a lot of down time on the one functioning server.
>> >> >> Is there a way to do an automatic sync from the 1 server that is
>> >> >> still
>> >> >> running ??
>> >> >>
>> >> >> I have found a tool dpkg package called Maakit , but having trouble
>> >> >> running it - get this error on the master:
>> >> >>
>> >> >> mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
>> >> >> mailserver 192.168.1.11
>> >> >> DBI
>> >> >>
>> >> >> connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
>> >> >> failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
>> >> >> password: YES) at /usr/bin/mk-table-sync line 1284
>> >> >>
>> >> >> can anyone assist with the error ?
>> >> >> Or can someone tell me of a better opensource tool to use to sync
>> >> >> the
>> >> >> servers without a mysql dump ? my db is rather large.
>> >> >>
>> >> >> thanks
>> >> >>
>> >> >> --
>> >> >> MySQL General Mailing List
>> >> >> For list archives: http://lists.mysql.com/mysql
>> >> >> To unsubscribe: http://lists.mysql.com/mysql
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Claudio
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe: http://lists.mysql.com/mysql
>> >>
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: multi master auto syncing when servers back on line
But andrew do you have an answer for me why i am getting this error ?
thanks
On Wed, Jun 6, 2012 at 9:09 AM, Andrew Moore wrote:
> Joey, sounds like you've got a lot of testing to do before you can deploy
> and support this in production yourself with out fear of shooting yourself
> in the foot. Whilst you're trialling things, you could try 'XtraDB Cluster'
> from percona.
>
> A
>
>
> On Wed, Jun 6, 2012 at 5:09 AM, Joey L wrote:
>>
>> My current situation arises out of testing really - i restored a db
>> that is out of sync with the other server (having less records) and
>> then i tried to update the "master" server - and only seen the records
>> that I updated and not the records that were additional to the other
>> server when I first restored.
>>
>> I am just trying different scenerios before i put this in production.
>> I was looking to do load balancing and i am concerned about
>> high-availablitity.
>> Is there a product that is opensource out there that I can use to
>> maintian a master master setup with ease ?
>>
>>
>> thanks
>> mjh
>>
>> On Tue, Jun 5, 2012 at 6:18 PM, Andrew Moore wrote:
>> > Joey,
>> >
>> > The master master replication topology comes with it's own potential
>> > pitfalls and trials. Be sure your use case needs master master and that
>> > you're not implementing it because you think it's 'nice to have'.
>> >
>> > pt-table-sync should assist you getting your data in sync but how have
>> > you
>> > arrived at this out-of-sync predicament you find yourself in?
>> >
>> > A
>> >
>> >
>> >
>> > On Tue, Jun 5, 2012 at 11:03 PM, Joey L wrote:
>> >>
>> >> with all do respect - I am new to this - i did read the docs and
>> >> having a hard time.
>> >> I also was asking if you know something easier or does the trick as
>> >> this utility does.
>> >> thanks
>> >>
>> >> On Tue, Jun 5, 2012 at 5:06 PM, Claudio Nanni
>> >> wrote:
>> >> > Joey,
>> >> >
>> >> > from what I can see from your email you lack of a lot of basics and I
>> >> > suggest you to read some documentation before proceeding.
>> >> >
>> >> > Maatkit is now Percona Toolkit and contains some of the best tools
>> >> > for
>> >> > MySQL.
>> >> >
>> >> > Cheers
>> >> >
>> >> > Claudio
>> >> >
>> >> > 2012/6/5 Joey L
>> >> >>
>> >> >> Hi -
>> >> >> I have setup mysql mult master setup on debian squeeze.
>> >> >> I have realized that the databases have to be initially in sync
>> >> >> before
>> >> >> multi master can operate properly.
>> >> >>
>> >> >> This can require a lot of down time on the one functioning server.
>> >> >> Is there a way to do an automatic sync from the 1 server that is
>> >> >> still
>> >> >> running ??
>> >> >>
>> >> >> I have found a tool dpkg package called Maakit , but having trouble
>> >> >> running it - get this error on the master:
>> >> >>
>> >> >> mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
>> >> >> mailserver 192.168.1.11
>> >> >> DBI
>> >> >>
>> >> >> connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
>> >> >> failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
>> >> >> password: YES) at /usr/bin/mk-table-sync line 1284
>> >> >>
>> >> >> can anyone assist with the error ?
>> >> >> Or can someone tell me of a better opensource tool to use to sync
>> >> >> the
>> >> >> servers without a mysql dump ? my db is rather large.
>> >> >>
>> >> >> thanks
>> >> >>
>> >> >> --
>> >> >> MySQL General Mailing List
>> >> >> For list archives: http://lists.mysql.com/mysql
>> >> >> To unsubscribe: http://lists.mysql.com/mysql
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Claudio
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe: http://lists.mysql.com/mysql
>> >>
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: I am trying to setup a multi master mysql setup.
thanks - i had to reset the whole db and the error went away - do not know what i did wrong. thanks On Tue, Jun 5, 2012 at 3:35 PM, Andrew Moore wrote: > Joey, please consult your mysql error log. Something has gone wrong on your > server where innodb is not started. > > On Tue, Jun 5, 2012 at 3:53 PM, Joey L wrote: >> >> I am following or trying to follow this document: >> http://www.howtoforge.com/mysql5_master_master_replication_debian_etch >> >> I think it is running in a good state because when i query the show >> slave state, I get awaiting connection from host, like : >> >> on server1: >> >> mysql> SHOW MASTER STATUS; >> +--+--+--+--+ >> | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | >> +--+--+--+--+ >> | mysql-bin.35 | 106 | mailserver | | >> +--+--+--+--+ >> 1 row in set (0.00 sec) >> >> mysql> show slave status\G; >> *** 1. row *** >> Slave_IO_State: Waiting for master to send event >> Master_Host: 192.168.2.121 >> Master_User: slave1_user >> Master_Port: 3306 >> Connect_Retry: 60 >> Master_Log_File: mysql-bin.24 >> Read_Master_Log_Pos: 106 >> Relay_Log_File: relay-bin.25 >> Relay_Log_Pos: 251 >> Relay_Master_Log_File: mysql-bin.24 >> Slave_IO_Running: Yes >> Slave_SQL_Running: Yes >> Replicate_Do_DB: mailserver >> Replicate_Ignore_DB: >> Replicate_Do_Table: >> Replicate_Ignore_Table: >> Replicate_Wild_Do_Table: >> Replicate_Wild_Ignore_Table: >> Last_Errno: 0 >> Last_Error: >> Skip_Counter: 0 >> Exec_Master_Log_Pos: 106 >> Relay_Log_Space: 545 >> Until_Condition: None >> Until_Log_File: >> Until_Log_Pos: 0 >> Master_SSL_Allowed: No >> Master_SSL_CA_File: >> Master_SSL_CA_Path: >> Master_SSL_Cert: >> Master_SSL_Cipher: >> Master_SSL_Key: >> Seconds_Behind_Master: 0 >> Master_SSL_Verify_Server_Cert: No >> Last_IO_Errno: 0 >> Last_IO_Error: >> Last_SQL_Errno: 0 >> Last_SQL_Error: >> 1 row in set (0.00 sec) >> >> ERROR: >> No query specified >> >> >> On Server2: >> >> mysql> show master status ; >> +--+--+--+--+ >> | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | >> +--+--+--+--+ >> | mysql-bin.24 | 106 | mailserver | | >> +--+--+--+--+ >> 1 row in set (0.00 sec) >> >> mysql> show slave status\G; >> *** 1. row *** >> Slave_IO_State: >> Master_Host: 192.168.2.121 >> Master_User: slave2_user >> Master_Port: 3306 >> Connect_Retry: 60 >> Master_Log_File: >> Read_Master_Log_Pos: 4 >> Relay_Log_File: relay-bin.01 >> Relay_Log_Pos: 4 >> Relay_Master_Log_File: >> Slave_IO_Running: No >> Slave_SQL_Running: No >> Replicate_Do_DB: exampledb,mailserver >> Replicate_Ignore_DB: >> Replicate_Do_Table: >> Replicate_Ignore_Table: >> Replicate_Wild_Do_Table: >> Replicate_Wild_Ignore_Table: >> Last_Errno: 0 >> Last_Error: >> Skip_Counter: 0 >> Exec_Master_Log_Pos: 0 >> Relay_Log_Space: 125 >> Until_Condition: None >> Until_Log_File: >> Until_Log_Pos: 0 >> Master_SSL_Allowed: No >> Master_SSL_CA_File: >> Master_SSL_CA_Path: >> Master_SSL_Cert: >> Master_SSL_Cipher: >> Master_SSL_Key: >> Seconds_Behind_Master
Re: multi master auto syncing when servers back on line
Claudio thank you for your input. I can see that you have set this up before.
My issue is that my db is getting very big approaching 50G and
performance is being impacted.
I am also very concerned about recovery time - this is production - so
i can have very little recovery time - so what else is new :)
I will look at the site for active-active config - but have you had
any experience with active-active yourself?
what was your solution - i am on a seriously low budget - so any thing
free is great.
thanks
mjh
On Tue, Jun 5, 2012 at 6:42 PM, Claudio Nanni wrote:
> Joey,
>
> read this to setup the second instance with no o little service disruption.
>
> then be aware that you have basically two options for Multimaster
> replication with two nodes, Active-Passive and Active-Active
>
> Active-Passive can be used for:
>
> -Having a stand by Master using a failover loadbalancer (using only one
> active at a time)
>
> Avoid by all means Active-Active unless you have a great team of developers
>
> Claudio
>
> 2012/6/6 Andrew Moore
>>
>> Joey,
>>
>> The master master replication topology comes with it's own potential
>> pitfalls and trials. Be sure your use case needs master master and that
>> you're not implementing it because you think it's 'nice to have'.
>>
>> pt-table-sync should assist you getting your data in sync but how have you
>> arrived at this out-of-sync predicament you find yourself in?
>>
>> A
>>
>>
>>
>> On Tue, Jun 5, 2012 at 11:03 PM, Joey L wrote:
>>
>> > with all do respect - I am new to this - i did read the docs and
>> > having a hard time.
>> > I also was asking if you know something easier or does the trick as
>> > this utility does.
>> > thanks
>> >
>> > On Tue, Jun 5, 2012 at 5:06 PM, Claudio Nanni
>> > wrote:
>> > > Joey,
>> > >
>> > > from what I can see from your email you lack of a lot of basics and I
>> > > suggest you to read some documentation before proceeding.
>> > >
>> > > Maatkit is now Percona Toolkit and contains some of the best tools for
>> > > MySQL.
>> > >
>> > > Cheers
>> > >
>> > > Claudio
>> > >
>> > > 2012/6/5 Joey L
>> > >>
>> > >> Hi -
>> > >> I have setup mysql mult master setup on debian squeeze.
>> > >> I have realized that the databases have to be initially in sync
>> > >> before
>> > >> multi master can operate properly.
>> > >>
>> > >> This can require a lot of down time on the one functioning server.
>> > >> Is there a way to do an automatic sync from the 1 server that is
>> > >> still
>> > >> running ??
>> > >>
>> > >> I have found a tool dpkg package called Maakit , but having trouble
>> > >> running it - get this error on the master:
>> > >>
>> > >> mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
>> > >> mailserver 192.168.1.11
>> > >> DBI
>> > >>
>> > >> connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
>> > >> failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
>> > >> password: YES) at /usr/bin/mk-table-sync line 1284
>> > >>
>> > >> can anyone assist with the error ?
>> > >> Or can someone tell me of a better opensource tool to use to sync the
>> > >> servers without a mysql dump ? my db is rather large.
>> > >>
>> > >> thanks
>> > >>
>> > >> --
>> > >> MySQL General Mailing List
>> > >> For list archives: http://lists.mysql.com/mysql
>> > >> To unsubscribe: http://lists.mysql.com/mysql
>> > >>
>> > >
>> > >
>> > >
>> > > --
>> > > Claudio
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: http://lists.mysql.com/mysql
>> >
>> >
>
>
>
>
> --
> Claudio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: multi master auto syncing when servers back on line
My current situation arises out of testing really - i restored a db
that is out of sync with the other server (having less records) and
then i tried to update the "master" server - and only seen the records
that I updated and not the records that were additional to the other
server when I first restored.
I am just trying different scenerios before i put this in production.
I was looking to do load balancing and i am concerned about high-availablitity.
Is there a product that is opensource out there that I can use to
maintian a master master setup with ease ?
thanks
mjh
On Tue, Jun 5, 2012 at 6:18 PM, Andrew Moore wrote:
> Joey,
>
> The master master replication topology comes with it's own potential
> pitfalls and trials. Be sure your use case needs master master and that
> you're not implementing it because you think it's 'nice to have'.
>
> pt-table-sync should assist you getting your data in sync but how have you
> arrived at this out-of-sync predicament you find yourself in?
>
> A
>
>
>
> On Tue, Jun 5, 2012 at 11:03 PM, Joey L wrote:
>>
>> with all do respect - I am new to this - i did read the docs and
>> having a hard time.
>> I also was asking if you know something easier or does the trick as
>> this utility does.
>> thanks
>>
>> On Tue, Jun 5, 2012 at 5:06 PM, Claudio Nanni
>> wrote:
>> > Joey,
>> >
>> > from what I can see from your email you lack of a lot of basics and I
>> > suggest you to read some documentation before proceeding.
>> >
>> > Maatkit is now Percona Toolkit and contains some of the best tools for
>> > MySQL.
>> >
>> > Cheers
>> >
>> > Claudio
>> >
>> > 2012/6/5 Joey L
>> >>
>> >> Hi -
>> >> I have setup mysql mult master setup on debian squeeze.
>> >> I have realized that the databases have to be initially in sync before
>> >> multi master can operate properly.
>> >>
>> >> This can require a lot of down time on the one functioning server.
>> >> Is there a way to do an automatic sync from the 1 server that is still
>> >> running ??
>> >>
>> >> I have found a tool dpkg package called Maakit , but having trouble
>> >> running it - get this error on the master:
>> >>
>> >> mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
>> >> mailserver 192.168.1.11
>> >> DBI
>> >> connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
>> >> failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
>> >> password: YES) at /usr/bin/mk-table-sync line 1284
>> >>
>> >> can anyone assist with the error ?
>> >> Or can someone tell me of a better opensource tool to use to sync the
>> >> servers without a mysql dump ? my db is rather large.
>> >>
>> >> thanks
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe: http://lists.mysql.com/mysql
>> >>
>> >
>> >
>> >
>> > --
>> > Claudio
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: multi master auto syncing when servers back on line
with all do respect - I am new to this - i did read the docs and
having a hard time.
I also was asking if you know something easier or does the trick as
this utility does.
thanks
On Tue, Jun 5, 2012 at 5:06 PM, Claudio Nanni wrote:
> Joey,
>
> from what I can see from your email you lack of a lot of basics and I
> suggest you to read some documentation before proceeding.
>
> Maatkit is now Percona Toolkit and contains some of the best tools for
> MySQL.
>
> Cheers
>
> Claudio
>
> 2012/6/5 Joey L
>>
>> Hi -
>> I have setup mysql mult master setup on debian squeeze.
>> I have realized that the databases have to be initially in sync before
>> multi master can operate properly.
>>
>> This can require a lot of down time on the one functioning server.
>> Is there a way to do an automatic sync from the 1 server that is still
>> running ??
>>
>> I have found a tool dpkg package called Maakit , but having trouble
>> running it - get this error on the master:
>>
>> mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
>> mailserver 192.168.1.11
>> DBI
>> connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
>> failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
>> password: YES) at /usr/bin/mk-table-sync line 1284
>>
>> can anyone assist with the error ?
>> Or can someone tell me of a better opensource tool to use to sync the
>> servers without a mysql dump ? my db is rather large.
>>
>> thanks
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
>>
>
>
>
> --
> Claudio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
multi master auto syncing when servers back on line
Hi -
I have setup mysql mult master setup on debian squeeze.
I have realized that the databases have to be initially in sync before
multi master can operate properly.
This can require a lot of down time on the one functioning server.
Is there a way to do an automatic sync from the 1 server that is still
running ??
I have found a tool dpkg package called Maakit , but having trouble
running it - get this error on the master:
mk-table-sync -u sl2 -ppswd --print --sync-to-master --replicate
mailserver 192.168.1.11
DBI connect(';host=192.168.1.11;mysql_read_default_group=client','sl2',...)
failed: Access denied for user 'sl2'@'mybox.somedomain.com' (using
password: YES) at /usr/bin/mk-table-sync line 1284
can anyone assist with the error ?
Or can someone tell me of a better opensource tool to use to sync the
servers without a mysql dump ? my db is rather large.
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
I am trying to setup a multi master mysql setup.
I am following or trying to follow this document: http://www.howtoforge.com/mysql5_master_master_replication_debian_etch I think it is running in a good state because when i query the show slave state, I get awaiting connection from host, like : on server1: mysql> SHOW MASTER STATUS; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.35 | 106 | mailserver | | +--+--+--+--+ 1 row in set (0.00 sec) mysql> show slave status\G; *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.121 Master_User: slave1_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.24 Read_Master_Log_Pos: 106 Relay_Log_File: relay-bin.25 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.24 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: mailserver Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 106 Relay_Log_Space: 545 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) ERROR: No query specified On Server2: mysql> show master status ; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.24 | 106 | mailserver | | +--+--+--+--+ 1 row in set (0.00 sec) mysql> show slave status\G; *** 1. row *** Slave_IO_State: Master_Host: 192.168.2.121 Master_User: slave2_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-bin.01 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: exampledb,mailserver Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 125 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) ERROR: No query specified On server1 - I am able to do a select statement to the table and get back results. On server2 - i get the error regarding no innodb.below: ERROR 1286 (42000): Unknown table engine 'InnoDB' mysql> select * from virtual_users ; ERROR 1286 (42000): Unknown table engine 'InnoDB' can anyone assist ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table cache value error in my.cnf file
Can you explain this further ? Sorry a little slow ? > table count * expected connections > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
table cache value error in my.cnf file
i am getting this wierd error in the mysql log: 120604 8:31:32 [Warning] option 'table_cache': unsigned value 536870912 adjusted to 524288 I have 28G of ram in my server, can anyone tell me what this value should be set to ? what is the syntax - i have tried different syntax -- get the same error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
i am running mysqlrepair on 80G myisam table.
i am running mysqlrepair on 80G myisam table. It is taking forever to repair - i am getting a lot of "Waiting for table" messeges when i execute "show full processlist" 613 | p_040912 | localhost | p_040912| Query | 24 | Waiting for table | SELECT `oldurl`, `newurl Can anyone help for this ? is there anything i can tweak in the my.cnf file that avoids this issue - it is taking forever !!! thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
i am new to innobackupex and getting some errors..can anyone hellp?
I am running debian with mysql 5.1 I am running the backup command like this : innobackupex --user=root --password=pass --stream=tar .^C| bzip2 - > ./xtra/052912backup.tar.bz2 and doing the restore command like this in the /var/lib/mysql directory: tar -xvif ./xtra/052912backup.tar.bz2 and then i permission the files with mysql.mysql user --- can you tell me what i am doing wrong ? 120603 15:07:30 InnoDB: Error: page 50 log sequence number 0 144162 InnoDB: is in the future! Current system log sequence number 0 142388. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: for more information. 120603 15:07:49 [Note] /usr/sbin/mysqld: Normal shutdown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: large temp files created by mysql
>> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: General Statistics -- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.49-3-log [OK] Operating on 64-bit architecture Storage Engine Statistics --- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 9G (Tables: 2512) [--] Data in InnoDB tables: 1M (Tables: 40) [!!] Total fragmented tables: 58 Performance Metrics - [--] Up for: 10s (2K q [237.300 qps], 591 conn, TX: 947K, RX: 261K) [--] Reads / Writes: 97% / 3% [--] Total buffers: 2.3G global + 34.6M per thread (200 max threads) [OK] Maximum possible memory usage: 9.1G (41% of installed RAM) [OK] Slow queries: 0% (0/2K) [OK] Highest usage of available connections: 3% (6/200) [!!] Key buffer size / total MyISAM indexes: 2.0G/12.0G [!!] Key buffer hit rate: 89.9% (5K cached / 524 reads) [!!] Query cache efficiency: 17.9% (298 cached / 1K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 45 sorts) [!!] Joins performed without indexes: 25 [!!] Temporary tables created on disk: 29% (93 on disk / 316 total) [OK] Thread cache hit rate: 98% (6 created / 591 connections) [!!] Table cache hit rate: 3% (407 open / 13K opened) [OK] Open file limit used: 78% (808/1K) [OK] Table locks acquired immediately: 100% (997 immediate / 997 locks) [OK] InnoDB data size / buffer pool: 1.3M/8.0M Recommendations - General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: key_buffer_size (> 12.0G) query_cache_limit (> 2M, or use smaller result sets) join_buffer_size (> 32.0M, or always use indexes with joins) table_cache (> 407) I just did some adjustments and used mysqltuner.pl ---any advice ?? I have 22gigs of ram on the server - so if i need to allocate - i can. thanks mjh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: large temp files created by mysql
> > You should do EXPLAINs on your slow-running queries to find out which ones > are likely... If it is generating a 30gig file, I'd expect it must be a very > slow query. > I know why the files are being created - but is there anything i can do on the server to accomodate for large file operations ? in my.cnf ? possibly? I have 22gig of ram -- should i bump these up ??? anything else ? table_cache= 256M max_heap_table_size = 256M tmp_table_size = 256M thanks mjh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: large temp files created by mysql
On Mon, Oct 24, 2011 at 10:52 AM, Joey L wrote: > I have a very large table - approx 3 or 4 gig in size. > When i initiate a process on my webpage - mysql starts to create a > temporary table in /tmp directory. > Is there a way i can create this file/table ahead of time so mysql > does not have to create it ? > > thanks > mjh > Sorry - it is 30 gig file . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
large temp files created by mysql
I have a very large table - approx 3 or 4 gig in size. When i initiate a process on my webpage - mysql starts to create a temporary table in /tmp directory. Is there a way i can create this file/table ahead of time so mysql does not have to create it ? thanks mjh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
Guys - I wanted to thank you all very much for your help I found the offending code on the website ! thank you very very very much... what did it for me was a combination of show processlist and show full processlist. I saw the full queries and the main thing was that it was doing a query about 20 miles long. thanks again mjh On Thu, Oct 6, 2011 at 7:48 PM, Jan Steinman wrote: >> From: Joey L >> >> i did google search - myisam is faster...i am not really doing any >> transaction stuff. > > That's true for read-only. But if you have a mix of reads and writes, MYISAM > locks tables during writes, which could be blocking reads. > > > In a museum in Havana, there are two skulls of Christopher Columbus; one when > he was a boy and one when he was a man. -- Mark Twain > Jan Steinman, EcoReality Co-op > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mjh2...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
p;city=Milt | | 2608 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&airportid=5892&lan | | 2609 | p_092211 | localhost | p_092211 | Query |6 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Shar | | 2610 | p_092211 | localhost | p_092211 | Query |5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Fish | | 2611 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&airportid=7705&lan | | 2612 | p_092211 | localhost | p_092211 | Query |5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Fish | | 2613 | p_092211 | localhost | p_092211 | Query |6 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Milt | | 2614 | p_092211 | localhost | p_092211 | Query |6 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Armo | | 2615 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls WHERE `oldurl`='MLF/index.php/' ORDER | | 2616 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT * FROM w6h8a_sh404sef_urls WHERE `oldurl`='MLF/index.php' | | 2617 | p_092211 | localhost | p_092211 | Query |7 | Locked | INSERT INTO `w6h8a_sh404sef_urls` (`cpt`, `rank`, `oldurl`, `newurl`, `dateadd`) VALUES (1, 0, '2O1/ | +--+--+---+--+-+--+--+--+ 58 rows in set (0.00 sec) On Thu, Oct 6, 2011 at 1:17 PM, Andrew Moore wrote: > Precisely my point Singer. There's a workload here that isn't friendly with > table level locking and I would hazard a guess that there's some fights over > IO due to load vs resources. The count is going to be queued as you > describe. > > A > > On Thu, Oct 6, 2011 at 6:09 PM, Singer X.J. Wang > wrote: >> >> Okay, lets hold on for a minute here and go back. We're side tracking too >> much. >> >> Lets state the facts here: >> >> 1) MyISAM stores the row count internally, a 'select count(*) from table' >> DOES NOT DO A FULL TABLE SCAN >> 2) hell, a software RAID6 of 2 MFM drives could do a seek to the metadata >> faster then 4 minutes.. >> >> But lets remember that if another thread is writing or updating the MyISAM >> table, the count(*) must wait.. >> >> So I recommend this: >> >> run a select count(*) from the table that you see is long.. if it is >> taking a long time open another session, do a show processlist >> >> I bet you that you will see another process updating or deleting or >> inserting into the MyISAM table. >> >> >> On Thu, Oct 6, 2011 at 12:35, Joey L wrote: >>> >>> i did google search - myisam is faster...i am not really doing any >>> transaction stuff. >>> thanks >>> >>> On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore >>> wrote: >>> > Sorry, hit send by accident there! *face palm* >>> > Just had a quick scan of the report. You've got 2 1GB disks in software >>> > raid >>> > - RAID1 or RAID5? I can also see you're creating a lot of temporary >>> > files on >>> > disk. I think in your previous email that your biggest table's index(s) >>> > were >>> > larger then the keybuffer size. I would suspect that you're disk bound >>> > with >>> > limited IO performance through 2 disks and effectively 1 if in a >>> > mirrored >>> > configuration. The stats show that you're configured for MyISAM and >>> > that >>> > you're tables are taking reads and writes (read heavy though), MyISAM >>> > doesn't like high concurrency mixed workloads such as yours, it will >>> > cause >>> > locking and maybe thats why your count has such a delay. Such activity >>> > may >>> > be better suited to InnoDB engine (you must configure and tune for >>> > this, not >>> > JUST change the engine). >>> > HTH >>> > Andy >>> > >>> > >>> > On Thu, Oct 6, 2011 a
Re: 4 minute slow on select count(*) from table - myisam type
i did google search - myisam is faster...i am not really doing any transaction stuff. thanks On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore wrote: > Sorry, hit send by accident there! *face palm* > Just had a quick scan of the report. You've got 2 1GB disks in software raid > - RAID1 or RAID5? I can also see you're creating a lot of temporary files on > disk. I think in your previous email that your biggest table's index(s) were > larger then the keybuffer size. I would suspect that you're disk bound with > limited IO performance through 2 disks and effectively 1 if in a mirrored > configuration. The stats show that you're configured for MyISAM and that > you're tables are taking reads and writes (read heavy though), MyISAM > doesn't like high concurrency mixed workloads such as yours, it will cause > locking and maybe thats why your count has such a delay. Such activity may > be better suited to InnoDB engine (you must configure and tune for this, not > JUST change the engine). > HTH > Andy > > > On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore wrote: >> >> Joey, does your 'large' table get >> >> On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote: >>> >>> here is mysqlreport --- >>> >>> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb >>> Use of uninitialized value $is in multiplication (*) at ./mysqlreport >>> line 829. >>> Use of uninitialized value in formline at ./mysqlreport line 1227. >>> MySQL 5.1.49-3-log uptime 0 0:25:5 Thu Oct 6 10:20:49 2011 >>> >>> __ Key _ >>> Buffer used 727.43M of 2.00G %Used: 35.52 >>> Current 963.24M %Usage: 47.03 >>> Write hit 29.41% >>> Read hit 99.79% >>> >>> __ Questions ___ >>> Total 50.20k 33.4/s >>> QC Hits 32.56k 21.6/s %Total: 64.87 >>> DMS 12.28k 8.2/s 24.46 >>> Com_ 3.21k 2.1/s 6.39 >>> COM_QUIT 2.89k 1.9/s 5.76 >>> -Unknown 745 0.5/s 1.48 >>> Slow 10 s 68 0.0/s 0.14 %DMS: 0.55 Log: OFF >>> DMS 12.28k 8.2/s 24.46 >>> SELECT 11.09k 7.4/s 22.10 90.36 >>> UPDATE 539 0.4/s 1.07 4.39 >>> INSERT 384 0.3/s 0.77 3.13 >>> DELETE 260 0.2/s 0.52 2.12 >>> REPLACE 0 0/s 0.00 0.00 >>> Com_ 3.21k 2.1/s 6.39 >>> set_option 1.10k 0.7/s 2.20 >>> show_fields 1.03k 0.7/s 2.05 >>> admin_comma 707 0.5/s 1.41 >>> >>> __ SELECT and Sort _ >>> Scan 1.65k 1.1/s %SELECT: 14.87 >>> Range 493 0.3/s 4.44 >>> Full join 310 0.2/s 2.79 >>> Range check 339 0.2/s 3.06 >>> Full rng join 0 0/s 0.00 >>> Sort scan 887 0.6/s >>> Sort range 628 0.4/s >>> Sort mrg pass 0 0/s >>> >>> __ Query Cache _ >>> Memory usage 5.96M of 16.00M %Used: 37.25 >>> Block Fragmnt 5.17% >>> Hits 32.56k 21.6/s >>> Inserts 5.66k 3.8/s >>> Insrt:Prune 5.66k:1 3.8/s >>> Hit:Insert 5.76:1 >>> >>> __ Table Locks _ >>> Waited 513 0.3/s %Total: 3.62 >>> Immediate 13.65k 9.1/s >>> >>> __ Tables __ >>> Open 1024 of 1024 %Cache: 100.00 >>> Opened 14.96k 9.9/s >>> >>> __ Connections _ >>> Max used 70 of 100 %Max: 70.00 >>> Total 2.89k 1.9/s >>> >>> __ Created Temp >>> Disk table 1.34k 0.9/s >>> Table 2.35k 1.6/s Size: 32.0M >>> File 5
Re: 4 minute slow on select count(*) from table - myisam type
here is mysqlreport --- root@rider:~/tmp# ./mysqlreport --user root --password barakobomb Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829. Use of uninitialized value in formline at ./mysqlreport line 1227. MySQL 5.1.49-3-log uptime 0 0:25:5Thu Oct 6 10:20:49 2011 __ Key _ Buffer used 727.43M of 2.00G %Used: 35.52 Current 963.24M%Usage: 47.03 Write hit 29.41% Read hit 99.79% __ Questions ___ Total 50.20k33.4/s QC Hits 32.56k21.6/s %Total: 64.87 DMS 12.28k 8.2/s 24.46 Com_ 3.21k 2.1/s6.39 COM_QUIT 2.89k 1.9/s5.76 -Unknown745 0.5/s1.48 Slow 10 s 68 0.0/s0.14 %DMS: 0.55 Log: OFF DMS12.28k 8.2/s 24.46 SELECT 11.09k 7.4/s 22.10 90.36 UPDATE 539 0.4/s1.07 4.39 INSERT 384 0.3/s0.77 3.13 DELETE 260 0.2/s0.52 2.12 REPLACE 0 0/s0.00 0.00 Com_3.21k 2.1/s6.39 set_option1.10k 0.7/s2.20 show_fields 1.03k 0.7/s2.05 admin_comma 707 0.5/s1.41 __ SELECT and Sort _ Scan1.65k 1.1/s %SELECT: 14.87 Range 493 0.3/s4.44 Full join 310 0.2/s2.79 Range check 339 0.2/s3.06 Full rng join 0 0/s0.00 Sort scan 887 0.6/s Sort range628 0.4/s Sort mrg pass 0 0/s __ Query Cache _ Memory usage5.96M of 16.00M %Used: 37.25 Block Fragmnt 5.17% Hits 32.56k21.6/s Inserts 5.66k 3.8/s Insrt:Prune 5.66k:1 3.8/s Hit:Insert 5.76:1 __ Table Locks _ Waited513 0.3/s %Total: 3.62 Immediate 13.65k 9.1/s __ Tables __ Open 1024 of 1024%Cache: 100.00 Opened 14.96k 9.9/s __ Connections _ Max used 70 of 100 %Max: 70.00 Total 2.89k 1.9/s __ Created Temp Disk table 1.34k 0.9/s Table 2.35k 1.6/sSize: 32.0M File5 0.0/s __ Threads _ Running32 of 37 Cached 0 of8 %Hit: 93.26 Created 195 0.1/s Slow0 0/s __ Aborted _ Clients 0 0/s Connects2 0.0/s __ Bytes ___ Sent 100.33M 66.7k/s Received 12.48M8.3k/s __ InnoDB Buffer Pool __ Usage 1.67M of 8.00M %Used: 20.90 Read hit 99.70% Pages Free405%Total: 79.10 Data107 20.90 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 26.18k17.4/s From file78 0.1/s0.30 Ahead Rnd 2 0.0/s Ahead Sql 1 0.0/s Writes 3 0.0/s Flushes 3 0.0/s Wait Free 0 0/s __ InnoDB Lock _ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows Data Reads96 0.1/s Writes 12 0.0/s fsync11 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read107 0.1/s Written 3 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 20.98k13.9/s Updated 0 0/s root@rider:~/tmp# and the mysqltuner.pl report : - root@rider:~/tmp# perl mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter
Re: 4 minute slow on select count(*) from table - myisam type
thanks for the response - but do not believe queries are the issue because - Like I said - i have other websites doing the same exact queries as I am doing on the site with the 9gig table. -- my issue is optimizing mysql to handle lots of queries on a 9gig db. --- i think that is the focus. All other websites (10 websites) are being handled fine in terms of performance - with same queries -- just that table is about 100meg. I have run optimize on it and recover and prune,etcno luck. thanks mjh On Thu, Oct 6, 2011 at 9:15 AM, Johnny Withers wrote: > I think in order to solve your problem you will need to post the queries > running against this table along with the explain output of each problem > query. Optimizing server settings is a good start, however, individual query > performance sounds like your problem now. > > Sent from my iPad > > On Oct 6, 2011, at 6:47 AM, Joey L wrote: > >> Just as an fyi - I have other databases and their corresponding apache >> websites on the same server - performing okay. >> It seems that apache/mysql server is just having a hard time dealing >> with the access to those pages that deal with the 9gig table on that >> particular site. -- Most of the access is done by webcrawlers to the >> site - so there is a lot of activity occuring on the 9gig tables. >> >> thanks >> mjh >> >> On Thu, Oct 6, 2011 at 6:13 AM, Joey L wrote: >>> guys - i am having such a hard time with this..it is killing me!!! >>> Sorry - had to vent. >>> my machine is running an tyan S2912G2NR -- with 2 opterons and 12gig >>> of memory. I have 2 software raided drives 1gig each. >>> I run a couple of databases --- my largest table is about 9gig in >>> size. --it is being accessed a lot. >>> My my.cnf is as follows: >>> >>> # >>> # The MySQL database server configuration file. >>> # >>> # You can copy this to one of: >>> # - "/etc/mysql/my.cnf" to set global options, >>> # - "~/.my.cnf" to set user-specific options. >>> # >>> # One can use all long options that the program supports. >>> # Run program with --help to get a list of available options and with >>> # --print-defaults to see which it would actually understand and use. >>> # >>> # For explanations see >>> # http://dev.mysql.com/doc/mysql/en/server-system-variables.html >>> >>> # This will be passed to all mysql clients >>> # It has been reported that passwords should be enclosed with ticks/quotes >>> # escpecially if they contain "#" chars... >>> # Remember to edit /etc/mysql/debian.cnf when changing the socket location. >>> [client] >>> port = 3306 >>> socket = /var/run/mysqld/mysqld.sock >>> >>> # Here is entries for some specific programs >>> # The following values assume you have at least 32M ram >>> >>> # This was formally known as [safe_mysqld]. Both versions are currently >>> parsed. >>> [mysqld_safe] >>> socket = /var/run/mysqld/mysqld.sock >>> nice = 0 >>> >>> [mysqld] >>> # >>> # * Basic Settings >>> # >>> user = mysql >>> pid-file = /var/run/mysqld/mysqld.pid >>> socket = /var/run/mysqld/mysqld.sock >>> port = 3306 >>> basedir = /usr >>> datadir = /var/lib/mysql >>> tmpdir = /tmp >>> language = /usr/share/mysql/english >>> skip-external-locking >>> # >>> # Instead of skip-networking the default is now to listen only on >>> # localhost which is more compatible and is not less secure. >>> bind-address = 127.0.0.1 >>> # >>> # * Fine Tuning >>> # >>> key_buffer = 2G >>> key_buffer_size = 2G >>> max_allowed_packet = 16M >>> thread_stack = 192K >>> thread_cache_size = 8 >>> join_buffer_size = 128 >>> # This replaces the startup script and checks MyISAM tables if needed >>> # the first time they are touched >>> myisam-recover = BACKUP >>> max_connections = 100 >>> table_cache = 1024 >>> max_heap_table_size = 32M >>> tmp_table_size = 32M >>> thread_concurrency = 10 >>> # >>> # * Query Cache Configuration >>> # >>> query_cache_limit = 2M >>> query_cache_size = 16M >>> #
Re: 4 minute slow on select count(*) from table - myisam type
Just as an fyi - I have other databases and their corresponding apache websites on the same server - performing okay. It seems that apache/mysql server is just having a hard time dealing with the access to those pages that deal with the 9gig table on that particular site. -- Most of the access is done by webcrawlers to the site - so there is a lot of activity occuring on the 9gig tables. thanks mjh On Thu, Oct 6, 2011 at 6:13 AM, Joey L wrote: > guys - i am having such a hard time with this..it is killing me!!! > Sorry - had to vent. > my machine is running an tyan S2912G2NR -- with 2 opterons and 12gig > of memory. I have 2 software raided drives 1gig each. > I run a couple of databases --- my largest table is about 9gig in > size. --it is being accessed a lot. > My my.cnf is as follows: > > # > # The MySQL database server configuration file. > # > # You can copy this to one of: > # - "/etc/mysql/my.cnf" to set global options, > # - "~/.my.cnf" to set user-specific options. > # > # One can use all long options that the program supports. > # Run program with --help to get a list of available options and with > # --print-defaults to see which it would actually understand and use. > # > # For explanations see > # http://dev.mysql.com/doc/mysql/en/server-system-variables.html > > # This will be passed to all mysql clients > # It has been reported that passwords should be enclosed with ticks/quotes > # escpecially if they contain "#" chars... > # Remember to edit /etc/mysql/debian.cnf when changing the socket location. > [client] > port = 3306 > socket = /var/run/mysqld/mysqld.sock > > # Here is entries for some specific programs > # The following values assume you have at least 32M ram > > # This was formally known as [safe_mysqld]. Both versions are currently > parsed. > [mysqld_safe] > socket = /var/run/mysqld/mysqld.sock > nice = 0 > > [mysqld] > # > # * Basic Settings > # > user = mysql > pid-file = /var/run/mysqld/mysqld.pid > socket = /var/run/mysqld/mysqld.sock > port = 3306 > basedir = /usr > datadir = /var/lib/mysql > tmpdir = /tmp > language = /usr/share/mysql/english > skip-external-locking > # > # Instead of skip-networking the default is now to listen only on > # localhost which is more compatible and is not less secure. > bind-address = 127.0.0.1 > # > # * Fine Tuning > # > key_buffer = 2G > key_buffer_size = 2G > max_allowed_packet = 16M > thread_stack = 192K > thread_cache_size = 8 > join_buffer_size = 128 > # This replaces the startup script and checks MyISAM tables if needed > # the first time they are touched > myisam-recover = BACKUP > max_connections = 100 > table_cache = 1024 > max_heap_table_size = 32M > tmp_table_size = 32M > thread_concurrency = 10 > # > # * Query Cache Configuration > # > query_cache_limit = 2M > query_cache_size = 16M > # > # * Logging and Replication > # > # Both location gets rotated by the cronjob. > # Be aware that this log type is a performance killer. > # As of 5.1 you can enable the log at runtime! > general_log_file = /var/log/mysql/mysql.log > general_log = 2 > # > # Error logging goes to syslog due to > /etc/mysql/conf.d/mysqld_safe_syslog.cnf. > # > # Here you can see queries with especially long duration > #log_slow_queries = /var/log/mysql/mysql-slow.log > #long_query_time = 2 > #log-queries-not-using-indexes > # > # The following can be used as easy to replay backup logs or for replication. > # note: if you are setting up a replication slave, see README.Debian about > # other settings you may need to change. > #server-id = 1 > #log_bin = /var/log/mysql/mysql-bin.log > expire_logs_days = 10 > max_binlog_size = 100M > #binlog_do_db = include_database_name > #binlog_ignore_db = include_database_name > # > # * InnoDB > # > # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. > # Read the manual for more InnoDB related options. There are many! > # > # * Security Features > # > # Read the manual, too, if you want chroot! > # chroot = /var/lib/mysql/ > # > # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". > # > # ssl-ca=/etc/mysql/cacert.pem > # ssl-cert=/etc/mysql/server-cert.pem > # ssl-key=/etc/mysql/server-key.pem > > > > [mysqldump] > quick > quote-name
Re: 4 minute slow on select count(*) from table - myisam type
guys - i am having such a hard time with this..it is killing me!!! Sorry - had to vent. my machine is running an tyan S2912G2NR -- with 2 opterons and 12gig of memory. I have 2 software raided drives 1gig each. I run a couple of databases --- my largest table is about 9gig in size. --it is being accessed a lot. My my.cnf is as follows: # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port= 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] # # * Basic Settings # user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address= 127.0.0.1 # # * Fine Tuning # key_buffer = 2G key_buffer_size = 2G max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 8 join_buffer_size= 128 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections= 100 table_cache= 1024 max_heap_table_size = 32M tmp_table_size = 32M thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size= 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! general_log_file= /var/log/mysql/mysql.log general_log = 2 # # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin= /var/log/mysql/mysql-bin.log expire_logs_days= 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 64M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ any thoughts or help would be appricated. thanks On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen wrote: > Can you run show processlist in another connection while the select > count(*) query is running and say what the state column is? > > On Mon, Oct 3, 2011 at 7:00 AM, Joey L wrote: >> this is not a real query on the site - it is just a way i am measuring >> performance on mysql - I do not know if it is such a great way to test. >> Looking for a better way to get a performance read on my site...do you have >> any ?? besides just viewing pages on it. >> thanks >> mjh >> >> >> On Mon, Oct 3, 2011 at 9:58 AM, Andrés Tello wrote: >> >>&g
Re: 4 minute slow on select count(*) from table - myisam type
this is not a real query on the site - it is just a way i am measuring performance on mysql - I do not know if it is such a great way to test. Looking for a better way to get a performance read on my site...do you have any ?? besides just viewing pages on it. thanks mjh On Mon, Oct 3, 2011 at 9:58 AM, Andrés Tello wrote: > have you tried > > select count(yourindex) instead of select count(*) ? > > > On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote: > >> Thanks for the input - >> 1. I will wait 48 hours and see what happens. >> 2. can you tell me what are some performance tests I can do to help me >> better tune my server ? >> 3. I am concerned about this table : | w6h8a_sh404sef_urls >> | >> MyISAM | 10 | Dynamic| 8908402 |174 | 1551178184 | >> 281474976710655 | 2410850304 | 0 |8908777 | 2011-09-22 >> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general_ci | >> NULL || | >> what can I do to make it run faster - i did not write the code...but need >> to >> optimize server to handle this table when it gets larger. It is used for >> url re-writes - so it has a lot of urls. >> thanks >> mjh >> >> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell > >wrote: >> >> > >> > The meaning is: >> > >> > increase max_connections >> > reduce wait_timeout >> > -- 28800 is wait 8 hours before closing out dead connections >> > same for interactive_timeout >> > >> > >> > increase key_buffer_size (> 7.8G) increase join_buffer_size >> > -- This keeps mysql from having to run to disk constantly for keys >> > -- Key buffer size / total MyISAM indexes: 256.0M/7.8G >> > -- You have a key buffer of 256M and 7.8G of keys >> > >> > join_buffer_size (> 128.0K, or always use indexes with joins) >> > Joins performed without indexes: 23576 of 744k queries. >> > -- You probably want to look at the slow query log. Generalize the >> queries >> > and the do an explain on the query. I have seen instances where a query >> I >> > thought was using an index wasn't and I had to re-write... with help >> from >> > this list :-) Thanks gang! >> > >> > >> > increase tmp_table_size (> 16M) >> > increase max_heap_table_size (> 16M) >> > -- When making adjustments, make tmp_table_size/max_heap_table_size >> equal >> > >> > increase table_cache ( > 1k ) >> > -- Table cache hit rate: 7% (1K open / 14K opened) >> > -- Increase table_cache gradually to avoid file descriptor limits >> > >> > All of the aside, you need to let this run for at least 24 hours. I >> > prefer 48 hours. The first line says mysql has only been running 9 >> > hours. You can reset the timeouts interactivly by entering at the >> > mysql prompt: >> > >> > set global wait_timeout= >> > >> > You can do the same for the interactive_timeout. >> > >> > Setting these values too low will cause long running queries to abort >> > >> > >> > On 10/02/2011 07:02 PM, Joey L wrote: >> > > Variables to adjust: >> > > > max_connections (> 100) >> > > > wait_timeout (< 28800) >> > > > interactive_timeout (< 28800) >> > > > key_buffer_size (> 7.8G) >> > > > join_buffer_size (> 128.0K, or always use indexes with joins) >> > > > tmp_table_size (> 16M) >> > > > max_heap_table_size (> 16M) >> > > > table_cache (> 1024) >> > >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe:http://lists.mysql.com/mysql?unsub=mjh2...@gmail.com >> > >> > >> > >
Re: 4 minute slow on select count(*) from table - myisam type
Thanks for the input - 1. I will wait 48 hours and see what happens. 2. can you tell me what are some performance tests I can do to help me better tune my server ? 3. I am concerned about this table : | w6h8a_sh404sef_urls | MyISAM | 10 | Dynamic| 8908402 |174 | 1551178184 | 281474976710655 | 2410850304 | 0 |8908777 | 2011-09-22 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general_ci | NULL || | what can I do to make it run faster - i did not write the code...but need to optimize server to handle this table when it gets larger. It is used for url re-writes - so it has a lot of urls. thanks mjh On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell wrote: > > The meaning is: > > increase max_connections > reduce wait_timeout > -- 28800 is wait 8 hours before closing out dead connections > same for interactive_timeout > > > increase key_buffer_size (> 7.8G) increase join_buffer_size > -- This keeps mysql from having to run to disk constantly for keys > -- Key buffer size / total MyISAM indexes: 256.0M/7.8G > -- You have a key buffer of 256M and 7.8G of keys > > join_buffer_size (> 128.0K, or always use indexes with joins) > Joins performed without indexes: 23576 of 744k queries. > -- You probably want to look at the slow query log. Generalize the queries > and the do an explain on the query. I have seen instances where a query I > thought was using an index wasn't and I had to re-write... with help from > this list :-) Thanks gang! > > > increase tmp_table_size (> 16M) > increase max_heap_table_size (> 16M) > -- When making adjustments, make tmp_table_size/max_heap_table_size equal > > increase table_cache ( > 1k ) > -- Table cache hit rate: 7% (1K open / 14K opened) > -- Increase table_cache gradually to avoid file descriptor limits > > All of the aside, you need to let this run for at least 24 hours. I > prefer 48 hours. The first line says mysql has only been running 9 > hours. You can reset the timeouts interactivly by entering at the > mysql prompt: > > set global wait_timeout= > > You can do the same for the interactive_timeout. > > Setting these values too low will cause long running queries to abort > > > On 10/02/2011 07:02 PM, Joey L wrote: > > Variables to adjust: > > > max_connections (> 100) > > > wait_timeout (< 28800) > > > interactive_timeout (< 28800) > > > key_buffer_size (> 7.8G) > > > join_buffer_size (> 128.0K, or always use indexes with joins) > > > tmp_table_size (> 16M) > > > max_heap_table_size (> 16M) > > > table_cache (> 1024) > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=mjh2...@gmail.com > >
Re: 4 minute slow on select count(*) from table - myisam type
Also i did run mysqlcheck and i did optimize and repair the database/table.. thanks On Sun, Oct 2, 2011 at 9:19 PM, Joey L wrote: > The section called: Variables to adjust: --when it says ">" -- does this > mean I have to set it higher in my.cnf file ?? and if I have a "<" -- does > this mean I have to set it lower ?? > thanks...here is the info below you both asked for : > > > mysql> select count(*) from w6h8a_sh404sef_urls ; > > +--+ > | count(*) | > +--+ > | 8908193 | > +--+ > 1 row in set (2 min 5.53 sec) > > | w6h8a_session | MyISAM | 10 | Dynamic| > 171 | 1576 | 531176 | 281474976710655 |34816 | > 261548 | NULL | 2011-09-30 16:18:30 | 2011-10-02 21:17:19 | > 2011-10-02 08:52:33 | utf8_general_ci | NULL || > | > | w6h8a_sh404sef_aliases | MyISAM | 10 | Dynamic| > 0 | 0 | 0 | 281474976710655 | 4096 | > 0 | 1 | 2011-09-22 11:16:03 | 2011-09-22 11:16:03 | > 2011-09-23 00:00:58 | utf8_general_ci | NULL || > | > | w6h8a_sh404sef_metas| MyISAM | 10 | Dynamic| > 0 | 0 | 0 | 281474976710655 | 4096 | > 0 | 1 | 2011-09-22 11:16:03 | 2011-09-22 11:16:03 | > 2011-09-23 00:00:58 | utf8_general_ci | NULL || > | > | w6h8a_sh404sef_pageids | MyISAM | 10 | Dynamic| > 218 | 84 | 18484 | 281474976710655 |35840 | > 0 |219 | 2011-09-22 11:16:03 | 2011-10-02 13:29:12 | > 2011-10-02 08:52:33 | utf8_general_ci | NULL || > | > | w6h8a_sh404sef_urls | MyISAM | 10 | Dynamic| > 8908402 |174 | 1551178184 | 281474976710655 | 2410850304 | > 0 |8908777 | 2011-09-22 11:16:03 | 2011-10-02 21:17:20 | > 2011-10-02 10:12:04 | utf8_general_ci | NULL || > | > | w6h8a_states| MyISAM | 10 | Dynamic| > 51 | 22 |1132 | 281474976710655 | 2048 | > 0 | 57 | 2011-09-22 11:16:21 | 2011-09-22 11:16:21 | > 2011-09-23 00:39:36 | utf8_general_ci | NULL || > | > > > > General Statistics > -- > [--] Skipped version check for MySQLTuner script > [OK] Currently running supported MySQL version 5.1.49-3-log > [OK] Operating on 64-bit architecture > > Storage Engine Statistics > --- > [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster > > > [--] Data in MyISAM tables: 5G (Tables: 2233) > [--] Data in InnoDB tables: 1M (Tables: 36) > [!!] Total fragmented tables: 46 > > Security Recommendations > --- > [!!] User 'asterisk@%' has no password set. > > Performance Metrics > - > [--] Up for: 9h 57m 33s (744K q [20.762 qps], 13K conn, TX: 1B, RX: 200M) > [--] Reads / Writes: 87% / 13% > [--] Total buffers: 794.0M global + 2.7M per thread (100 max threads) > [OK] Maximum possible memory usage: 1.0G (26% of installed RAM) > [OK] Slow queries: 0% (956/744K) > [!!] Highest connection usage: 100% (101/100) > [!!] Key buffer size / total MyISAM indexes: 256.0M/7.8G > [!!] Key buffer hit rate: 92.4% (4B cached / 372M reads) > [OK] Query cache efficiency: 68.8% (450K cached / 655K selects) > [OK] Query cache prunes per day: 0 > [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 57K sorts) > [!!] Joins performed without indexes: 23576 > [!!] Temporary tables created on disk: 32% (27K on disk / 85K total) > [OK] Thread cache hit rate: 97% (329 created / 13K connections) > [!!] Table cache hit rate: 7% (1K open / 14K opened) > [OK] Open file limit used: 66% (1K/2K) > [OK] Table locks acquired immediately: 98% (358K immediate / 362K locks) > [!!] Connections aborted: 16% > [OK] InnoDB data size / buffer pool: 1.1M/8.0M > > Recommendations > - > General recommendations: > Run OPTIMIZE TABLE to defragment tables for better performance > MySQL started within last 24 hours - recommendations may be inaccurate > Enable the slow query log to troubleshoot bad queries > Reduce or eliminate persistent connections to reduce connection usa
Re: 4 minute slow on select count(*) from table - myisam type
(> 16M) table_cache (> 1024) On Sun, Oct 2, 2011 at 12:56 PM, Andrew Moore wrote: > Did you fix the issue? > > > On Sun, Oct 2, 2011 at 4:05 PM, Singer X.J. Wang wrote: > >> Are you sure? Do a show create table and send it to us please >> >> >> >> >> On Sun, Oct 2, 2011 at 10:02, Joey L wrote: >> >>> thanks for the quick reply! >>> My table is MyISAM >>> further top says this: >>> top - 10:01:29 up 8:25, 4 users, load average: 1.42, 1.85, 2.69 >>> Tasks: 338 total, 1 running, 337 sleeping, 0 stopped, 0 zombie >>> Cpu(s): 10.3%us, 0.9%sy, 0.0%ni, 56.6%id, 32.0%wa, 0.0%hi, 0.2%si, >>> 0.0%st >>> Mem: 8198044k total, 8158784k used,39260k free, 199852k buffers >>> Swap: 8210416k total,44748k used, 8165668k free, 5457920k cached >>> >>> PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND >>> 10682 mysql 20 0 958m 343m 6588 S 31 4.3 57:25.69 >>> /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql >>> --pid-file=/va >>> 14627 www-data 20 0 50088 14m 4744 S3 0.2 0:10.43 >>> /usr/sbin/apache2 -k start >>> 14637 www-data 20 0 50088 14m 4744 S3 0.2 0:07.66 >>> /usr/sbin/apache2 -k start >>> 14737 www-data 20 0 50092 14m 4744 S3 0.2 0:07.25 >>> /usr/sbin/apache2 -k start >>> 14758 www-data 20 0 50092 14m 4748 S3 0.2 0:07.36 >>> /usr/sbin/apache2 -k start >>> 15145 root 20 0 2596 1328 896 R1 0.0 0:00.55 top >>> 1895 bind 20 0 98452 24m 1980 S1 0.3 0:31.34 >>> /usr/sbin/named >>> -u bind >>> 401 root 20 0 000 D0 0.0 0:42.63 [md0_raid1] >>> 1398 root 20 0 000 S0 0.0 2:59.33 [flush-9:0] >>> 2428 asterisk -11 0 33500 15m 6660 S0 0.2 0:19.39 >>> /usr/sbin/asterisk -p -U asterisk >>>1 root 20 0 2032 604 568 S0 0.0 0:01.14 init [2] >>>2 root 20 0 000 S0 0.0 0:00.01 [kthreadd] >>>3 root RT 0 000 S0 0.0 0:00.04 [migration/0] >>>4 root 20 0 000 S0 0.0 0:00.16 [ksoftirqd/0] >>> >>> >>> On Sun, Oct 2, 2011 at 9:55 AM, Andrew Moore >>> wrote: >>> >>> > Is your table MyISAM or InnoDB? >>> > >>> > A >>> > >>> > >>> > On Sun, Oct 2, 2011 at 2:44 PM, Joey L wrote: >>> > >>> >> I have having issues with mysql db - I am doing a "select count(*) >>> from >>> >> table" -- and it take 3 to 4 min. >>> >> My table has about 9,000,000 records in it. >>> >> I have noticed issues on my web pages so that is why i did this test. >>> >> I have about 4 gig of memory on the server. >>> >> Is there anything I can do to fix the issue >>> >> My my.cnf looks like this : >>> >> # * Fine Tuning >>> >> # >>> >> key_buffer = 256M >>> >> max_allowed_packet = 16M >>> >> thread_stack= 192K >>> >> thread_cache_size = 32 >>> >> # This replaces the startup script and checks MyISAM tables if needed >>> >> # the first time they are touched >>> >> myisam-recover = BACKUP >>> >> max_connections= 100 >>> >> table_cache= 1024 >>> >> thread_concurrency = 20 >>> >> # >>> >> # * Query Cache Configuration >>> >> # >>> >> query_cache_limit = 1M >>> >> query_cache_size= 512M >>> >> # >>> >> # * Logging and Replication >>> >> # >>> >> # Both location gets rotated by the cronjob. >>> >> # Be aware that this log type is a performance killer. >>> >> # As of 5.1 you can enable the log at runtime! >>> >> general_log_file= /var/log/mysql/mysql.log >>> >> general_log = 1 >>> >> # >>> >> # Error logging goes to syslog due to >>> >> /etc/mysql/conf.d/mysqld_safe_syslog.cnf. >>> >> # >>> >> # Here you can see queries with especially long duration >>> >> #log_slow_queries = /var/log/mysql/mysql-slow.log >>> >> #long_query_time = 2 >>> >> #log
Re: 4 minute slow on select count(*) from table - myisam type
thanks for the quick reply! My table is MyISAM further top says this: top - 10:01:29 up 8:25, 4 users, load average: 1.42, 1.85, 2.69 Tasks: 338 total, 1 running, 337 sleeping, 0 stopped, 0 zombie Cpu(s): 10.3%us, 0.9%sy, 0.0%ni, 56.6%id, 32.0%wa, 0.0%hi, 0.2%si, 0.0%st Mem: 8198044k total, 8158784k used,39260k free, 199852k buffers Swap: 8210416k total,44748k used, 8165668k free, 5457920k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 10682 mysql 20 0 958m 343m 6588 S 31 4.3 57:25.69 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/va 14627 www-data 20 0 50088 14m 4744 S3 0.2 0:10.43 /usr/sbin/apache2 -k start 14637 www-data 20 0 50088 14m 4744 S3 0.2 0:07.66 /usr/sbin/apache2 -k start 14737 www-data 20 0 50092 14m 4744 S3 0.2 0:07.25 /usr/sbin/apache2 -k start 14758 www-data 20 0 50092 14m 4748 S3 0.2 0:07.36 /usr/sbin/apache2 -k start 15145 root 20 0 2596 1328 896 R1 0.0 0:00.55 top 1895 bind 20 0 98452 24m 1980 S1 0.3 0:31.34 /usr/sbin/named -u bind 401 root 20 0 000 D0 0.0 0:42.63 [md0_raid1] 1398 root 20 0 000 S0 0.0 2:59.33 [flush-9:0] 2428 asterisk -11 0 33500 15m 6660 S0 0.2 0:19.39 /usr/sbin/asterisk -p -U asterisk 1 root 20 0 2032 604 568 S0 0.0 0:01.14 init [2] 2 root 20 0 000 S0 0.0 0:00.01 [kthreadd] 3 root RT 0 000 S0 0.0 0:00.04 [migration/0] 4 root 20 0 000 S0 0.0 0:00.16 [ksoftirqd/0] On Sun, Oct 2, 2011 at 9:55 AM, Andrew Moore wrote: > Is your table MyISAM or InnoDB? > > A > > > On Sun, Oct 2, 2011 at 2:44 PM, Joey L wrote: > >> I have having issues with mysql db - I am doing a "select count(*) from >> table" -- and it take 3 to 4 min. >> My table has about 9,000,000 records in it. >> I have noticed issues on my web pages so that is why i did this test. >> I have about 4 gig of memory on the server. >> Is there anything I can do to fix the issue >> My my.cnf looks like this : >> # * Fine Tuning >> # >> key_buffer = 256M >> max_allowed_packet = 16M >> thread_stack= 192K >> thread_cache_size = 32 >> # This replaces the startup script and checks MyISAM tables if needed >> # the first time they are touched >> myisam-recover = BACKUP >> max_connections= 100 >> table_cache= 1024 >> thread_concurrency = 20 >> # >> # * Query Cache Configuration >> # >> query_cache_limit = 1M >> query_cache_size= 512M >> # >> # * Logging and Replication >> # >> # Both location gets rotated by the cronjob. >> # Be aware that this log type is a performance killer. >> # As of 5.1 you can enable the log at runtime! >> general_log_file= /var/log/mysql/mysql.log >> general_log = 1 >> # >> # Error logging goes to syslog due to >> /etc/mysql/conf.d/mysqld_safe_syslog.cnf. >> # >> # Here you can see queries with especially long duration >> #log_slow_queries = /var/log/mysql/mysql-slow.log >> #long_query_time = 2 >> #log-queries-not-using-indexes >> # >> # The following can be used as easy to replay backup logs or for >> replication. >> # note: if you are setting up a replication slave, see README.Debian about >> # other settings you may need to change. >> #server-id = 1 >> #log_bin= /var/log/mysql/mysql-bin.log >> expire_logs_days= 10 >> max_binlog_size = 100M >> #binlog_do_db = include_database_name >> #binlog_ignore_db = include_database_name >> # >> # * InnoDB >> # >> # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. >> # Read the manual for more InnoDB related options. There are many! >> # >> # * Security Features >> # >> # Read the manual, too, if you want chroot! >> # chroot = /var/lib/mysql/ >> # >> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". >> # >> # ssl-ca=/etc/mysql/cacert.pem >> # ssl-cert=/etc/mysql/server-cert.pem >> # ssl-key=/etc/mysql/server-key.pem >> > >
4 minute slow on select count(*) from table - myisam type
I have having issues with mysql db - I am doing a "select count(*) from table" -- and it take 3 to 4 min. My table has about 9,000,000 records in it. I have noticed issues on my web pages so that is why i did this test. I have about 4 gig of memory on the server. Is there anything I can do to fix the issue My my.cnf looks like this : # * Fine Tuning # key_buffer = 256M max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 32 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections= 100 table_cache= 1024 thread_concurrency = 20 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 512M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! general_log_file= /var/log/mysql/mysql.log general_log = 1 # # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin= /var/log/mysql/mysql-bin.log expire_logs_days= 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem
Help With a Week ( date ) query
Hi Guys, I'm kind of at a standstill in coming up with how to get a query I need to write. I'm trying to find a record which matches the week we are in. Example today is Thursday the 29th, it is within the week which has the 26th through the 1st, and if the day is within this week display the record that has the date 3/26/2007. Basically we are returning a special which is dated each Monday, any day within that week should show the Monday value. I appreciate your help! Joey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help understanding the whole password issue
I need some clarification on the old password issue as I am having problems logging in. Here is what I am running into now. Original Server config: my.cnf is NOT using the old_passwords=1 under MySQL-server-4.1.14-0 I am able to login via phpmyadmin without problem, remotely with mysqladmin and of course applications. perl-DBD-MySQL-2.9003-5 MySQL-devel-4.1.14-0 MySQL-server-4.1.14-0 MySQL-client-4.1.14-0 php-mysql-4.3.11-2.8.4.legacy mod_auth_mysql-20030510-5 MySQL-shared-compat-4.1.13-0 MySQL-embedded-4.1.14-0 MySQL-bench-4.1.14-0 New Server config: tried old_passwords=1 both on & off under mysql-4.1.20-1.RHEL4.1 libdbi-dbd-mysql-0.6.5-10.RHEL4.1 mysql-4.1.20-1.RHEL4.1 mod_auth_mysql-2.6.1-2.2 php-mysql-4.3.9-3.22 mysql-bench-4.1.20-1.RHEL4.1 mysql-server-4.1.20-1.RHEL4.1 mysqlclient10-3.23.58-4.RHEL4.1 perl-DBD-MySQL-2.9004-3.1 MySQL-python-1.0.0-1.RHEL4.1 mysql-devel-4.1.20-1.RHEL4.1 New server tried running with both the old_passwords=1 and not really getting consistant results. yes I restart mysqld after changes etc. Part of the problem may also be related to the host field in which some times I use the % so that I can connect via the gui tools. So are my questions: 1. if I want to use the older apps, and phpmyadmin etc do I need to run old_passwords=1? 2. If I start mysqld with the old_passwords=1 set, when I use phpmyadmin to edit someone's password do I need to use the password drop down or the old_password drop down? 3. For remote access if I setup % in the host as I have done in the past that should allow me to login both remotely & to localhost right? ( again this works perfectly on my older box) Hope this makes sense. Thanks, Joey
Problem logging into mysql
I wanted to confirm something as I am having a problem logging into mysql from the network. I have a server configured with a user showing host as % and the user with it's appropriate privalages. It allows me to login from phpmyadmin as well as via port 3306 to connect to the DB with MySQL gui tools. My new server has the same configuration for the user host showing % and all privalages, but it won't allow me to connect except for via phpmyadmin Any ideas? Thanks
MySQL 4 & Old Password
Is there a way to tell mysql 4 to defaul to the old style passwords instead
of having to run this every time we add a user?
SET PASSWORD FOR 'user'@'localhost' =
OLD_PASSWORD('passowordo');
Applications we develop in php, and phpMyadmin don't seem to work with the
new style password.
Thanks!
RE: Still having problems with MySQL 4.1 on Fedora Core 3
I am running MySQL-server-4.1.8-0 without problem on FC3. This could also be hardware related. Joey -Original Message- From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 1:24 PM To: Mysql List Subject: Still having problems with MySQL 4.1 on Fedora Core 3 Hello all, I now have disabled my SELinux and so it should work. However it still does not work. I have a bunch of errors now on my .err file that I am copying bellow. I've tried to create the tables again by issuing mysql_install_db and then tried to run the daemon. Is there any ideias on how to fix this? Thanks in advance: errors from .err file: 050414 11:19:19 mysqld started 050414 11:19:19 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050414 11:19:20 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050414 11:19:21 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050414 11:19:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050414 11:19:23 InnoDB: Started; log sequence number 0 0 050414 11:19:24 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050414 11:19:24 mysqld ended 050414 11:21:15 mysqld started 050414 11:21:15 [Warning] Asked for 196608 thread stack, but got 126976 050414 11:21:15 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050414 11:21:15 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 36808. InnoDB: Doing recovery: scanned up to log sequence number 0 43684 050414 11:21:15 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.01 050414 11:21:15 InnoDB: Flushing modified pages from the buffer pool... 050414 11:21:15 InnoDB: Started; log sequence number 0 43684 050414 11:21:15 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:21:15 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:21:15 mysqld ended 050414 11:23:49 mysqld started 050414 11:23:49 [Warning] Asked for 196608 thread stack, but got 126976 050414 11:23:49 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050414 11:23:49 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43684. InnoDB: Doing recovery: scanned up to log sequence number 0 43724 InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.04 050414 11:23:49 InnoDB: Flushing modified pages from the buffer pool... 050414 11:23:49 InnoDB: Started; log sequence number 0 43724 050414 11:23:49 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator not working correctly 2nd Request
INARY) AS
CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Alter_priv' as pn, cast(cast(Alter_priv AS BINARY) AS CHAR
CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY) AS
CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Create_tmp_table_priv' as pn,
cast(cast(Create_tmp_table_priv AS BINARY) AS CHAR CHARACTER SET utf8) as pv
from mysql.db WHERE cast(cast(User AS BINARY) AS CHAR CHARACTER SET
utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Lock_tables_priv' as pn, cast(cast(Lock_tables_priv AS
BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User
AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS
CHAR CHARACTER SET utf8)
25917 Query UPDATE mysql.user SET
Password=Password('testtest') WHERE [EMAIL PROTECTED]
Now the bad part is if this person logs in there are times that things work,
and there are times where that user can see ALL the DB's on the server.
Additionally when they go to create a table within their DB the default
collation is lanin1_swedish_ci which earlier mentioned that that may not be
good.
I am at a bad point here with the security being compromised and really need
some help.
Thanks,
Joey
--
MySQL GUI Tools Mailing List
For list archives: http://lists.mysql.com/gui-tools
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with MySQL 4.1.8 Not starting under Fedora Core3
OK I am running Fedora Core3, Kernel kernel-2.6.9-1.724_FC3 along with MySQL-server 4.1.8-0.i386. No matter what I do MySQL the process will not start. This happens to me on a couple of boxes. One of the boxes which was upgraded from Fedora Core2 to Core3 does work with the exact same versions of everything. Here are the versions I installed via RPM: -rw-r--r-- 1 root root 3.1M Dec 23 10:03 MySQL-bench-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 5.3M Dec 23 10:05 MySQL-client-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 2.7M Dec 16 03:43 MySQL-devel-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 2.7M Dec 16 03:43 MySQL-embedded-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 15M Dec 23 10:23 MySQL-server-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 1.1M Dec 16 03:43 MySQL-shared-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 1.3M Dec 16 14:41 MySQL-shared-compat-4.1.8-0.i386.rpm When I start mysql with /etc/rc.d/init.d/mysql start I don't get the typical OK, and the mysql log shows this: /usr/sbin/mysqld, Version: 4.1.8-standard-log. started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument Making it look like everything is OK. Nothing is in /var/log/messages. I have read several posts on the web and found similar issues with no resolve. I have seen people try to run mysqld but of course get a security issue because it's not supposed to run as root, and that seems to be a popular problem which is not really the heart of the issue. When I su mysql and run mysqld I get this: 050107 14:09:12 [Warning] Asked for 196608 thread stack, but got 126976 mysqld: Can't create/write to file '/tmp/ib5ArcLz' (Errcode: 13) 050107 14:09:12 InnoDB: Error: unable to create temporary file; errno: 13 050107 14:09:12 [ERROR] Can't init databases 050107 14:09:12 [ERROR] Aborting 050107 14:09:12 [Note] mysqld: Shutdown complete I hope this helps as I have already created a flat spot on my head from banging it against the wall, and look forward to a more relaxing weekend. Any help appreciated... Joey
UDF and embedded MySQL
I know the documentation says UDF and embedded MySQL server is not allowed, but then it says, some of these limitations can be changed by editing mysql_embed.h. I see in there that it has HAVE_DLOPEN undefined. I assume it is not as simple as uncommenting that line to whether or not UDF will work with MySQL embedded. Anyway, my question is 1) is it really that simple, 2) if not, what steps might I need to take to undo this limitation if it's even possible? For my application, I use two of my own functions which I currently compile as a shared library. I would love to embed MySQL so my end users do not have to install/admin a MySQL database. Thanks for any tips! Joey +--+ + + + Joey Mukherjee "The price of freedom is eternal + + [EMAIL PROTECTED] vigilance, or $12.50 as seen on + + E-Bay... + + + +--+ - 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: Chinese support at MySQL
Hi Egor Egorov, If I set to big5, any influence to English data? By the way, will MySQL support UTF-8? Thanks for your help. At 20:18 02-27-2002, "Egor Egorov" wrote the following: >You should set big5 character set, look at: > http://www.mysql.com/doc/C/h/Character_sets.html - 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
Chinese support at MySQL
Dear all, How to make MySQL with Chinese Big5 support? 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
Upgrade MySQL
Dear all, I am running MySQL 3.22.xx at Mandrake 8.1, how can I grade my MySQL?? 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
Re: beowulfen and mysql
Gary, Thanks for your response. Basically, what we are doing is buidling a huge database that will hold a lot of different info on each of our customers. I can't tell you what we do for a living, but basically we want to find out (for instance) how many of our customers used our product (the green one, not the blue or red one) last month, and travelled to Tennesee while using it. We might be able to sell this info to our vendors so they can make a better product next year. We have well over 100,000 customers. We will probably need to run other types of queries against the data next month, and I have 2 programmers to write the queries. What kind of algorithms my coders will construct is up to them (I'm just the admin). I do know that beowulfen are great number crunchers, and that a huge number of selects might not run faster on a cluster than one one huge machine because of the I/O bottlenecks between machines. We are planning on doing what we can to compensate for this. That said, what kid of experiences have you and others had with mysql and clusters? --Joey From: "Gary Huntress" <[EMAIL PROTECTED]> To: "Joey Kelly" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject:Re: beowulfen and mysql Date sent: Sun, 15 Jul 2001 21:19:02 -0400 > First, lets make sure we're all talking about the same thing. A general > definition of data mining would be "the automated extraction of predictive > information from large databases". Automated implies some sort of agent, > and by its very nature the predictions are statistical (hence the large data > sets). Most agents that I have seen could be classified as decision tree, > neural network, or genetic algorithm. > > Note that data mining is not considered to be data warehousing, ad hoc > querying, OLTP or visualization. > > Are you trying to build some sort of predictive model? Perhaps you can > describe it a bit further. In general, once you pick an algorithm or > approach you would have to build an application layer above your query > layer. I imagine you would do that in C, for speed. > > You probably want to read about MySQL replication here > http://www.mysql.com/documentation/mysql/bychapter/manual_Replication.html#R > eplication > > Now, regarding Beowulf clusters. They are defined as (by the people that > created it, Donald Becker and company) solely a computational cluster and > not something geared toward data mining. You certainly can learn some > great lessons from their architecture (channel bonded ethernet or myrinet) > but don't expect them to answer any questions in the database arena! > > Regards, > Gary "SuperID" Huntress > === > FreeSQL.org offering free database hosting to developers > Visit http://www.freesql.org > > > > > > - Original Message - > From: "Joey Kelly" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, July 15, 2001 8:26 PM > Subject: beowulfen and mysql > > > > Howdy. > > > > My company needs to implement a data mining setup. I am > > building a cluster using dual athlons and perhaps firewire instead of > > 100baseTX. > > > > I need to find out as much as I can from those who have done > > mysql on beowulfen. Please contact me at [EMAIL PROTECTED] > > > > Thanks :) > > > > +++ > > > > Joey Kelly > > /Minister of the Gospel | Computer Networking Consultant/ > > http://nolalinuxcoop.dhs.org/~jkelly/home/ > > > > "Experience hath shewn, that even under the best forms [of government] > those entrusted with power have, in time, and by slow op > > erations, perverted it into tyranny." - Thomas Jefferson > > > > > > - > > 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 > > > +++ Joey Kelly /Minister of the Gospel | Computer Networking Consultant/ http://nolalinuxcoop.dhs.org/~jkelly/home/ "Experience hath shewn, that even under the best forms [of government] those entrusted with power have, in time, and by slow op erations, perverted it into tyranny." - Thomas Jefferson - 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
beowulfen and mysql
Howdy. My company needs to implement a data mining setup. I am building a cluster using dual athlons and perhaps firewire instead of 100baseTX. I need to find out as much as I can from those who have done mysql on beowulfen. Please contact me at [EMAIL PROTECTED] Thanks :) +++ Joey Kelly /Minister of the Gospel | Computer Networking Consultant/ http://nolalinuxcoop.dhs.org/~jkelly/home/ "Experience hath shewn, that even under the best forms [of government] those entrusted with power have, in time, and by slow op erations, perverted it into tyranny." - Thomas Jefferson - 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
beowulfen and mysql
Howdy. My company needs to implement a data mining setup. I am building a cluster using dual athlons and perhaps firewire instead of 100baseTX. I need to find out as much as I can from those who have done mysql on beowulfen. Please contact me at [EMAIL PROTECTED] Thanks :) +++ Joey Kelly /Minister of the Gospel | Computer Networking Consultant/ http://nolalinuxcoop.dhs.org/~jkelly/home/ "Experience hath shewn, that even under the best forms [of government] those entrusted with power have, in time, and by slow op erations, perverted it into tyranny." - Thomas Jefferson - 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
strange behavior (to me, at least)
Howdy.
Some of the following is part rant. I know this email is too long, but
I want to be sure to give enough info so you folks have to ask me
for more info. All of it will probably be forwarded to the bugs list,
depending on what solutions I find.
I'm running SuSE 7.0 and I had mysql 3.22 installed from SuSE's
site via rpm, and everything worked just fine. One of our
programmers insisted that I upgrade to 3.23 to take advantage of
that version's new features, which I tried to do last weekend. Below
is a description of what happened.
I removed everything mysql-related from my system via YaST, then
attempted to download and rpm the newer version from mysql.com.
I decided to install mysql-max. From the page on max, I assumed
that simply installing max would give me a mysql server, with the
transactions capability. I was wrong. I had to also grab the regular
3.23 server. Apparently, max layers on top of a regular server
install. This was not clear in the documentation on max.
Ok, so I get everything installed (except for the benchmarks, which
kind of failed). Mysql seems to be running, and I was able to
access the program via the command line, but all I got was errors
when I tried to use php. Here is a snippet from irc:
ok, I did:
mysql_connect(":/var/lib/mysql/mysql.sock", $user,
$pass);
?>
and I got:
Warning: MySQL Connection Failed: Can't connect to
local MySQL server through socket '/tmp/mysql.sock' (111) in
/home/jkelly/public_html/socket.php on line 2
Someone told me to execute the following at my shell promt:
mysqladmin variables -u root -p | grep socket
and this came back:
| socket | /var/lib/mysql/mysql.sock
Ok, so we figured that a symlink would make things work, and it
did... until the daemon died:
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
Ok, so yesterday I have no mysql daemon running. I reboot, and it
won't come up. I tried to start it manually, and no go. As a non-root
user (the daemon told me to read the docs before trying to run it as
root), I get an error telling me that I already have an instance
running on /var/lib/mysql/mysql.sock --- the symlink, I figure. The
command "ps aux | grep sql" yields only postgresql, no mysql.
I haven't yet tried removing the symlink to see if it will start, but I'll
probably do that later tonight.
Ok, I'm sure I made a stupid blunder somewhere, but can anyone
tell me what to do?
Thanks :)
+++
Joey Kelly
/Minister of the Gospel | Computer Networking Consultant/
http://nolalinuxcoop.dhs.org/~jkelly/home/
"Experience hath shewn, that even under the best forms [of government] those entrusted
with power have, in time, and by slow op
erations, perverted it into tyranny." - Thomas Jefferson
-
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
