Re: update a row only if any column has changed, in a very large table

[Andrés Tello]

Take a look here.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

timestamp field can be autoupdated and autoinitilizated

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the
column has the current timestamp for its default value and is automatically
updated to the current timestamp.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


And after that, just export data >  a defined timestamp.

I think this is the easiest way and more straight forward...


On Sat, Apr 6, 2013 at 9:49 PM, Rajeev Prasad  wrote:

> thx all, the source data is in text file.
>
>
>
>
> - Original Message -
> From: "h...@tbbs.net" 
> To: mysql list 
> Cc:
> Sent: Saturday, April 6, 2013 8:02 PM
> Subject: Re: update a row only if any column has changed, in a very  large
> table
>
>  2013/04/06 13:56 -0700, Rajeev Prasad 
> I have a table with around 2,000,000 records (15 columns). I have to sync
> this from an outside source once every day. not all records are
> changed/removed /new-added everyday. so what is the best way to update only
> those which have changed/added/or deleted?
>
> i can use update_or_create but that will update (re-write the row) even if
> nothing has changed in the row/record. wont that be an overhead? how can i
> escape that? what would be the fastest and least resources consuming way to
> do this table update?
>
> I also have another table with 500,000 rows and i wish to implement the
> same solution to that too.
>
> I earlier posted this on DBIx list, as i thought i could use DBIx tools to
> manage this. but based on response, it seems that MySQL tools would be more
> helpful in doing it in most efficent way. Plz. advice how can i address
> this.
>
> I also considered to delete and simply recreate the table each day.
> but changes/add and delete are not too many (may be a few hundreds.. max)
> 
> Sounds like a case for replication (look it up:
> http://dev.mysql.com/doc/refman/5.5/en/replication.html
> http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There
> is statement-replication, and row-replication. The former replicates all
> operations on the database, in the form wherin they were made. False
> changes (changing a field to its former value), too, are recorded. The
> latter records only those changes to a table that are real changes. (In
> MySQL statement-replication is of earlier implementation.)
>
> After changes are recorded, they are passed from the master --the
> wellspring of the changes-- to the slave --the taker of them.
>
> These are not tools, as such: replication is something implemented in the
> database-management system. If both your databases are in MySQL you can get
> help here.
>
>
> --
> 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: Update and lock question.

[Andrés Tello]

Thanks Urvashi.

Based on your answer, instead of the data I looked into the index, and it
appears that it was an index issue...

I think I have nailed the wait lock contdition due a updating indexes
unnecesarely...


On Thu, Apr 4, 2013 at 10:52 AM, Urvashi Pathak  wrote:

>
>
> Hi Andrés,
>
> Select for update makes sure that no other process can change the data
> between you selected it for update and then actually changed it and commit
> it.
> If you do not use "select for update"  then it is possible that some other
> process can change the data  in the mean time between you selected and
>  actually changes it. In this case you not see the result you actually
> intend to have.
>
> Innodb will only lock whole table only if there is no where clause in the
> update statement, which I sure you do not have. Innodb follows row level
> locking.
>
>
> -Urvi
>
> -Original Message-
> From: Andrés Tello [mailto:mr.crip...@gmail.com]
> Sent: Thursday, April 04, 2013 9:08 AM
> To: mysql
> Subject: Update and lock question.
>
> I'm doing some tests, but have a questions about locking.
>
> In a innodb table, if you issue an select for update lock for a row,
> supposedly, it only locks that row, but if you don't issue a select for
> update, and trow the update... does it locks the hole table?
>
> The update goes over an indexed field, or the effect of locking the hole
> table is due I'm updating an indexed field?
>
> This is because I'm running into dead locks, but I know there is no select
> for update to the row being updated.
>
>
> Thanks.
>

Update and lock question.

[Andrés Tello]

I'm doing some tests, but have a questions about locking.

In a innodb table, if you issue an select for update lock for a row,
supposedly, it only locks that row, but if you don't issue a select for
update, and trow the update... does it locks the hole table?

The update goes over an indexed field, or the effect of locking the hole
table is due I'm updating an indexed field?

This is because I'm running into dead locks, but I know there is no select
for update to the row being updated.


Thanks.

Re: MySQL dying?

[Andrés Tello]

Are u kidding?

Mysql is dead easy and damn good... obviously it has its perks, but any
database engine has them... (I'm looking at you DB2)...

There has been a lot of improvements lately, I "feel" that mysql is moving
much more faster under oracle umbrella than when it was alone...

Replication... omg, replicacion is DEAD easy! so easy, that usually you
doublecheck things just because you are unsure that is SO easy...

Partitioning, views, storeprocedures, explain and analise are good
enough... it simply work... I never, ever had a database corruption (I'm
looking at to you Mssql!) ...

I have tables with more the 5millon rows, noproblem, I have a table with
more than 40 millon rows... and of course I have troubles, but always,
always been resolved...

Mysql is... wonderful, it just works... You want it to "work as a storage
with out integrity?", done.. with integrity? done, replicaion, encryption,
secure connections, partition... name your feature!









On Tue, Dec 4, 2012 at 12:50 PM, Singer Wang  wrote:

> Lol! Good point Karen!
>
>
> On Tue, Dec 4, 2012 at 1:02 PM, Karen Abgarian  wrote:
>
> > A touch of realism: we are all dying.   For some, it may take a while,
> > hopefully.
> >
> > On 04.12.2012, at 9:53, Tim Pownall wrote:
> >
> > > Mysql is used by just about every web host and is one of the most
> common
> > > database servers around the world. I do not have any intent to stop
> using
> > > mysql unless they start charging for it which I do not think will
> happen.
> > >
> > > Thanks,
> > >
> > > Tim Pownall
> > > Sr. Linux Systems Monitoring
> > > Hostgator.com LLC
> > >
> > > On Tue, Dec 4, 2012 at 11:45 AM, Anthony Pace <
> anthony.p...@utoronto.ca
> > >wrote:
> > >
> > >> I have heard that due to Oracle taking over, the OS community is
> > shifting
> > >> to other type of DB's .
> > >>
> > >> Any thoughts?
> > >>
> > >> --
> > >> MySQL General Mailing List
> > >> For list archives: http://lists.mysql.com/mysql
> > >> To unsubscribe:http://lists.mysql.com/mysql
> > >>
> > >>
> > >
> > >
> > > --
> > >
> > > Thanks,
> > >
> > > Tim Pownall
> > > GNU/Linux Systems Monitoring
> > > 610-621-9712
> > > pownall...@gmail.com
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
>

Still struggling witn like 'CTV%' over varchar.... I simple cannot understand..

[Andrés Tello]

mysql> explain select * from cuenta where rutaCuenta like 'CTV%';
++-++---+---++-+--++-+
| id | select_type | table  | type  | possible_keys | key| key_len
| ref  | rows   | Extra   |
++-++---+---++-+--++-+
|  1 | SIMPLE  | cuenta | range | rutaCuenta| rutaCuenta | 258
| NULL | 876824 | Using where |
++-++---+---++-+--++-+
1 row in set (0.00 sec)

mysql> explain select * from cuenta where rutaCuenta like 'CTV%';
++-++--+---+--+-+--+-+-+
| id | select_type | table  | type | possible_keys | key  | key_len | ref
| rows| Extra   |
++-++--+---+--+-+--+-+-+
|  1 | SIMPLE  | cuenta | ALL  | rutaCuenta| NULL | NULL| NULL
| 5274306 | Using where |
++-++--+---+--+-+--+-+-+
1 row in set (0.00 sec)

Any ideas? I'm creating running a process which populates the rutaCuenta
field with some codification to retrieve some hierarchical  based in that
field... I did the explain meanwhile doing the populating process, and it
showed the usage of the index, then, some time later, it show, for the same
query, the usage of no index...

Why?

I really appreciate some guidance... I find no logic at all...

Re: Error message I am getting today. All help appreciated.

[Andrés Tello]

humm... I bet he have just run of space...

Some thing like tht...



On Tue, Oct 16, 2012 at 10:43 PM, Dehua Yang  wrote:

> Hi
> There many reasons to lose connection to MySQL server.
> And what's more , the important thing is that you should provide the error
> code to us.
>
> If you got the error code , you can check it by perror  X
>
> Anymore detail information would be appreciate !
>
> On Wed, Oct 17, 2012 at 10:32 AM, ad...@canadianinvestors.com <
> ad...@canadianinvestors.com> wrote:
>
> > Hi,
> >
> > I run www.canadianinvestors.com and am getting this error message.
> >
> > Warning: mysql_connect() [function.mysql-connect]: Lost connection to
> MySQL
> > server during query in
> > /var/www/domains/
> > x.canadianinvestors.com/docs/common/library/db_connect.inc
> > on line 15
> > Lost connection to MySQL server during query
> >
> > Able to log in to the phpmyadmin database account and the script that the
> > query is pointing to has the right password and appears to the untrained
> > eye to be working.
> >
> > I host with myhosting.com and the site has been down for about 8 hours.
> >
> > Any and all help is appreciated.
> >
> > Yours Sincerely,
> >
> > Adrian Burridge
> > CanadianInvestors.com Inc.
> >
> > 
> > mail2web - Check your email from the web at
> > http://link.mail2web.com/mail2web
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
>
>
> --
> B.rgds / Whitepoplar (杨德华）
>

Re: innodb_lock_wait_timeout

[Andrés Tello]

Are you managing transactions with mysql + innodb?

I had a similar issue, and I need to rework the application.

innodb does a row level transaction lock. Read locks aren't exclusive,
update locks are exclusive.

What I was doing was something like this:

Thread P1
begin;
innodb: update field set field + K where filter="A";
(other transactions)

in a parallel thread P2 the same excecution,
begin;
innodb: update field set field + K where filter="A";
(other transactions)


P2-> commit;
P1-> commit;
SInce I was trying to update the same field, with a self reference, within
2 separete threads, I  had a lock issue, because, at the end, it was a non
deterministic situation. What was the field value at the end I wanted to
update? what version?

The command
show engine innodb status;

will provide information about the last wait lock / dead lock, and that
will give you an Idea of what sql is making the fuss...


My current lock wait ratio after code rework:

Current Lock Wait ratio = 1 : 1110458921

(using tuning-primer.sh)

Why is better to retry, because since "things happens almost at random"
maybe your next retry will not encouter the issue.

But neither retrying or making bigger the wait time for the lock will solve
the issue, and the more load you have, the more this trouble will arise...






On Thu, Oct 11, 2012 at 7:43 AM, Johan De Meersman wrote:

>
> - Original Message -
> > From: "Markus Falb" 
> >
> > But why is retrying better than raising the value?
> >
> > So what is better, adjusting the timeout or retrying application side
> > and why?
>
> Well, raising the timeout would probably help, but may cause more
> concurrent connections to stay open until they block the server.
>
> The idea of retrying is that everything you've done before that particular
> transaction has already been committed, so you (probably) can't easily undo
> it. A retry gives you a chance to still finish what you were doing with no
> other loss than some time waiting. Regardless of how many retries fail, you
> will still be in the same position as you were when the first attempt
> failed.
>
>
>
> --
> Linux Bier Wanderung 2012, now also available in Belgium!
> August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Slow queries / inserts InnoDB

[Andrés Tello]

You are forcing mysql to do full table scans with the substr...

Use explain to see that you aren't using any index.

Avoid the use of substr in the where clause, by splitting your data, index
that field and do you query over that field.



That is why your query is so slow.

the slow insert, is due you S.O...


On Mon, Oct 8, 2012 at 2:59 AM, Adrián Espinosa Moreno <
aespinosamor...@gmail.com> wrote:

> Hi.
>
>
>
> I have developed my first application with VB.NET at work (junior
> developer) and basically it reads several thousands of lines from X number
> of files, and inserts them into a mysql database.
>
>
>
> The application consists of several steps:
>
> 1)  Dump all lines with all fields into a temp table.
>
> a.   This works fine. Optimal speed for the hardware we have.
>
> 2)  Query temp table to obtain certain. I query by a unique ID. I
> insert all unique ID (isn field). If the ID matches my interests, I insert
> it into an ArrayList.
>
> a.   Query: SELECT COUNT(SUBSTR(ISN,2)), SUBSTR(isn, 2) FROM
> SMS.TEMPDATADUMP WHERE error = 0 GROUP BY SUBSTR(ISN,2) HAVING
> COUNT(SUBSTR(ISN,2)) = 4
>
> b.  The isn is not unique per line, but per data (sms)
>
> c.   Once I have all isn on an arraylist, I do the following query:
>
>i.
>  SELECT
>  select, my, fields,of,interest  FROM SMS.TEMPDATADUMP WHERE SUBSTR(ISN, 2)
> = '" & isn & "' ORDER BY type LIMIT 1
>
> d.  To obtain some data. During the process I query around 10 times
> other table per ISN.
>
> e.  Here is the problem. If I have a few files to process (around
> 3000-4000 lines in total, small array) this steps work fine, good speed.
> But If I have big files or a lot of files (more than 1 lines in total,
> big array), this steps are incredibly slow. Queries and inserts are too
> slow. Meaning, one-two inserts per second, while the other case inserts are
> around 800 per second.
>
>
>
> Our hardware is not optimized for database server, but I don’t have other
> choice. It is mostly a desktop computer
>
> Intel core i5, windows 32 bits, 3GB RAM, one disk 500GB 7200rpm.
>
>
>
> I have tried some optimizations commented in mysqlperformance blog without
> success.
>
> Any way to optimize this?
>
>
>
> Thank you very much in advance.
>
>
>
>
>
> Adrián Espinosa Moreno.
>

InnoDB: The InnoDB memory heap is disabled

[Andrés Tello]

Hello everyone.

Today I restarted a server and got this at the log file:
InnoDB: The InnoDB memory heap is disabled


I read a little (maybe to little) about and it says something about the
native use of malloc.

The system is a linux
Linux  2.6.34.7-0.7 #1 SMP PREEMPT 2010-12-13 11:13:53 +0100 x86_64 x86_64
x86_64 GNU/Linux

Mysql is:
Server version: 5.5.22 Source distribution

But for what I read, I didn't confirm if it was to use it 0 or 1  at the
my.cnf file..

Any practical advice?

Re: Mysql starts to die at 27 SQL processes

[Andrés Tello]

Don't you have any message? For the number of max connections, I suppose
you are hitting the limit of file opened, there are no messages at error
log?

Check this:
http://www.geeksww.com/tutorials/database_management_systems/mysql/configuration/mysql_open_files_limit_openfileslimit_vs_openfileslimit_on_linux.php

and maybe you need to set files to ulimit...

 Which distribution and kernel would be usable...
uname -a
to get kernel information


On Thu, May 31, 2012 at 11:26 AM, J M  wrote:

> hi all,
>
> i have an issue with our db which hangs and the only way to
> recover from it is to restart mysql if you had console access or
> restart the box itself ( only if i can't wait )
>
> show processlist result can be found here:
> http://pastebin.com/BYvZ5ZFR
>
> listed below are info about the setup:
>
> ==
>  * Master-slave config...
> ==
>  * config..
>
> [client]
> port= 3306
> socket  = /var/lib/mysql/mysql.sock
>
>
> [mysqld]
> port= 3306
> socket  = /var/lib/mysql/mysql.sock
> skip-locking
> key_buffer_size = 256M
> max_allowed_packet = 1M
> sort_buffer_size = 2M
>
> read_buffer_size = 256K
> read_rnd_buffer_size = 256K
>
> # Testing Config
> #log = /tmp/sql.log
> # Testing Config End
>
> myisam_max_sort_file_size = 2G
> myisam_sort_buffer_size = 64M
> thread_cache_size = 8
>
> # Change 20110825 10:30AM
> query_cache_size  = 256M
>
> query_cache_limit = 1M
> thread_concurrency = 8
> max_connections = 600
> wait_timeout = 10
> interactive_timeout = 300
>
> table_open_cache = 750
> table_definition_cache=750
>
> # Change 20110825 10:30AM
> #max_heap_table_size = 64M
> #tmp_table_size = 64M
> max_heap_table_size = 256M
> tmp_table_size = 256M
>
>
> #max_write_lock_count = 1
> #low_priority_updates = 1
>
> #skip-networking
>
> log-bin=mysql-bin
> server-id   = 1
> binlog-ignore-db = mysql
> expire_logs_days = 7
>
> skip-innodb
>
> [mysqldump]
> quick
> max_allowed_packet = 16M
>
> [mysql]
> no-auto-rehash
>
> [myisamchk]
> key_buffer_size = 256M
> sort_buffer_size = 256M
> read_buffer = 2M
> write_buffer = 2M
>
> [mysqlhotcopy]
> interactive-timeout
>
> ==
>
>  use: website
>
> ==
>
>  I have a decent amount of resources:
>
> total   used   free sharedbuffers cached
> Mem:  1642587668835649542312  033784682384308
> -/+ buffers/cache:1120788   15305088
> Swap:  61448522086144644
>
>
>  16 core server ( DB is dedicated )
>
> ==
>
>  DB: v5.1.x
>
>
> ==
>
>  Storage: local disk @ Raid 10
>
> Notes:
>
>  assuming that application alteration is limited.. so i want to push
> the DB as far as it can go..
>
>  since i have 16G approximately @ current config i take 3G @600
> though the highest connection i get is 230 connection.
>
> tia,
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

[Andrés Tello]

Claudio, would you please extend the example to the use of in?


On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni wrote:

> In my experience if you have a poor designed code that run the same query
> for hundreds or thousands of times in a very short timespan (like some
> programmers do in for-loop instead of using a IN for example) you can put
> mysql on its knees, in some cases it may be the practical implementation of
> some lock mechanisms are particularly challenged by this  ultra high data
> 'locality' which bring to very high contention on a few hotspots at
> different levels (mutexes, indexes, pages).
>
>
> Just reflections :)
>
> Claudio
>
> 2012/5/14 Baron Schwartz 
>
> > Argh. I meant to send this to the list but it doesn't have the
> > reply-to set as I expect... 
> >
> > On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz 
> wrote:
> > > Johan,
> > >
> > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman  >
> > wrote:
> > >> What I fail to understand, Baron, is how there can be a deadlock here
> -
> > both transactions seem to be hanging on a single-table, single-row update
> > statement. Shouldn't the oldest transaction already have acquired the
> lock
> > by the time the youngest came around; and shouldn't the youngest simply
> > wait until the eldest finished it's update?
> > >
> > > Take a look at the output again:
> > >
> > >  8< ===
> > >
> > > *** (1) TRANSACTION:
> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> > > mysql tables in use 1, locked 1
> > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> > > update `account` set `balance`= 0.00 + '-6.07' where
> accountid='3235296'
> > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but
> not
> > > gap waiting
> > >
> > > *** (2) TRANSACTION:
> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
> declared
> > > inside InnoDB 500
> > > mysql tables in use 1, locked 1
> > > 14 lock struct(s), heap size 3112, 27 row lock(s)
> > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
> 32378480
> > > 90.0.0.51 mario Updating
> > > update `account` set `balance`= 0.00 + '-1.37' where
> accountid='3235296'
> > >
> > > *** (2) HOLDS THE LOCK(S):
> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but
> not
> > > gap
> > >
> > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but
> not
> > > gap waiting
> > >
> > > *** WE ROLL BACK TRANSACTION (1)
> > >
> > >
> > >  8< ===
> > >
> > > Here is how to interpret that: Transaction 1 has locked 27 rows (not
> > > just a single row!) and is waiting for an exclusive lock on some row.
> > > Transaction 2 holds a shared lock on that same row and is trying to
> > > upgraded its shared lock to an exclusive lock.
> > >
> > > Both transactions have locked 27 rows, so this is not a single-row,
> > > single-table problem. It may be the case that it is a single-statement
> > > problem, but in that case the statement needs to be optimized somehow
> > > so that it does not access too many rows.  But there is not enough
> > > information to really diagnose what is going on.
> >
> >
> >
> > --
> > 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
> >
> >
>
>
> --
> Claudio
>

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Andrés Tello]

Yes, I'm using indexes, accountid is the primary key, and is numeric and
autoincrement.  The process doing the deadlock is no longer done...

The structure of the inserted database has changed.
Originaly it was a single table with 219millions rows, now I partitioned
the hable in... 60 tables, 1 for each month for 5 years..

I gain a lot of speed with the partitioning and sql adjustments, but I
think I lost a lot of speed at the insert...

The database stats are like 95% reading 5% writting...but that 5% is mostly
done in a batch process who happends to be done inside a time window...

I know I need to do more profiling, but... at least for now dev team is
updating the batch process from long secuencial process with huge slow
inserts, to small parallel task with burst of inserts...




On Mon, May 14, 2012 at 8:18 AM, Ananda Kumar  wrote:

> is accountid a number or varchar column
>
>
> On Sat, May 12, 2012 at 7:38 PM, Andrés Tello wrote:
>
>> While doning a batch process...
>>
>> show full processlist show:
>>
>> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
>> end  | update `account` set `balance`= 0.00 +
>> '-4000' where accountid='2583092'
>>
>> No other process, lo locking no nothing...
>>
>> so you take this same query... run it isolated, and the mufu... is
>> just...f fast!
>>
>>
>> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
>> Query OK, 0 rows affected (0.00 sec)
>> Rows matched: 1  Changed: 0  Warnings: 0
>>
>>
>> ARRRG!  I have seen this type of query take as long as 100+ seconds.. and
>> I
>> don't have a F*** clue...
>>
>
>

Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Andrés Tello]

While doning a batch process...

show full processlist show:

| 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
end  | update `account` set `balance`= 0.00 +
'-4000' where accountid='2583092'

No other process, lo locking no nothing...

so you take this same query... run it isolated, and the mufu... is
just...f fast!


update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0


ARRRG!  I have seen this type of query take as long as 100+ seconds.. and I
don't have a F*** clue...

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

[Andrés Tello]

humm, I see.. and if is encapusulated with it's own begin-commit inside a
bigger transacion, only that small part get rolled back...

If I get this straigth...


On Fri, May 11, 2012 at 2:32 PM, Baron Schwartz  wrote:

> Andres,
>
> On Fri, May 11, 2012 at 1:48 PM, Andrés Tello 
> wrote:
> > Ok, so I had a deadlock...
> >
> > But then, why a deadlock doesn't rollback all the transaccion?
>
> Because it can be resolved by rolling back just one of them. Why
> destroy ALL the work people are trying to accomplish, if you could
> just throw away some of it?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

[Andrés Tello]

The genral log is the log that "logs" everything?

humm... dunno if I can.. as always... stuuupid production server with no
testing instance available...

And it happens very seldom, but force us to do a
select (sum) from the movements table instead just a select balance from
account...






On Fri, May 11, 2012 at 1:53 PM, Claudio Nanni wrote:

> Andrés,
>
> may be you can enable the general log, recreate the deadlock,
> and attach the general log?
>
> If I had to reason as InnoDB, what I see is two updates statements that
> arrive and want to update the same record,
> I would be confused exactly as InnoDB is because I would not know which
> update is the 'good' one,
> I'd close my eyes and kill one.
> This is a deadlock.
>
> Claudio
>
> 2012/5/11 Andrés Tello 
>
>> Yup, but a far I understand...
>> I made a
>>
>> select balance for update where accountid=3235296  lock in shared mode;
>>
>>  over the same accountid , so the second transacion just would need to
>> wait to the first transaccion to finish...
>>
>> That is why I'm confuse if I have a Deadlock o a wait lock...
>>
>> That is why I'm
>>
>> On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni 
>> wrote:
>>
>>> Hello Andrés
>>>
>>> did you notice that both transactions are trying to update records with
>>> same *accountid='3235296' *
>>> and that they lock the same index page? *space id 5806 page no 69100 n
>>> bits 176 index*
>>>
>>> Cheers
>>>
>>> Claudio
>>>
>>> 2012/5/11 Andrés Tello 
>>>
>>>> Ok, so I had a deadlock...
>>>>
>>>> But then, why a deadlock doesn't rollback all the transaccion?
>>>>
>>>>
>>>> On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz 
>>>> wrote:
>>>>
>>>> > Deadlocks and lock wait timeouts are independent of one another. A
>>>> > deadlock happens when there is a cycle in the waits-for graph. Your
>>>> > transactions are *active* for 132 and 33 seconds, but the deadlock
>>>> > happens at the instant the conflict is detected, not after waiting. A
>>>> > deadlock cannot be resolved by waiting, by definition. Hence the name,
>>>> > deadlock. The only way to resolve it is to choose a victim.
>>>> >
>>>> > On Fri, May 11, 2012 at 3:06 AM, Andrés Tello 
>>>> > wrote:
>>>> > > Ok... I have one of those pesky error, in an application not
>>>> handling
>>>> > > deadlocks or lockwaits.
>>>> > >
>>>> > > The database object can't be modified to support
>>>> deadlock/lockwatis...
>>>> > > I can only change database parameteres
>>>> > >
>>>> > > Database info: Server version: 5.5.22-log Source distribution
>>>> > >
>>>> > >
>>>> > > from show engine innodb status;
>>>> > > {abstract}
>>>> > > *** (1) TRANSACTION:
>>>> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
>>>> > > mysql tables in use 1, locked 1
>>>> > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
>>>> > > update `account` set `balance`= 0.00 + '-6.07' where
>>>> accountid='3235296'
>>>> > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
>>>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
>>>> of
>>>> > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec
>>>> but not
>>>> > > gap waiting
>>>> > >
>>>> > > *** (2) TRANSACTION:
>>>> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
>>>> declared
>>>> > > inside InnoDB 500
>>>> > > mysql tables in use 1, locked 1
>>>> > > 14 lock struct(s), heap size 3112, 27 row lock(s)
>>>> > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
>>>> 32378480
>>>> > > 90.0.0.51 mario Updating
>>>> > > update `account` set `balance`= 0.00 + '-1.37' where
>>>> accountid='3235296'
>>>> > >
>>>> > > *** (2) HOLDS THE LOCK(S):
>>>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY`
>>>> 

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

[Andrés Tello]

Yup, but a far I understand...
I made a

select balance for update where accountid=3235296  lock in shared mode;

 over the same accountid , so the second transacion just would need to wait
to the first transaccion to finish...

That is why I'm confuse if I have a Deadlock o a wait lock...

That is why I'm

On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni wrote:

> Hello Andrés
>
> did you notice that both transactions are trying to update records with
> same *accountid='3235296' *
> and that they lock the same index page? *space id 5806 page no 69100 n
> bits 176 index*
>
> Cheers
>
> Claudio
>
> 2012/5/11 Andrés Tello 
>
>> Ok, so I had a deadlock...
>>
>> But then, why a deadlock doesn't rollback all the transaccion?
>>
>>
>> On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz  wrote:
>>
>> > Deadlocks and lock wait timeouts are independent of one another. A
>> > deadlock happens when there is a cycle in the waits-for graph. Your
>> > transactions are *active* for 132 and 33 seconds, but the deadlock
>> > happens at the instant the conflict is detected, not after waiting. A
>> > deadlock cannot be resolved by waiting, by definition. Hence the name,
>> > deadlock. The only way to resolve it is to choose a victim.
>> >
>> > On Fri, May 11, 2012 at 3:06 AM, Andrés Tello 
>> > wrote:
>> > > Ok... I have one of those pesky error, in an application not handling
>> > > deadlocks or lockwaits.
>> > >
>> > > The database object can't be modified to support deadlock/lockwatis...
>> > > I can only change database parameteres
>> > >
>> > > Database info: Server version: 5.5.22-log Source distribution
>> > >
>> > >
>> > > from show engine innodb status;
>> > > {abstract}
>> > > *** (1) TRANSACTION:
>> > > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
>> > > mysql tables in use 1, locked 1
>> > > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
>> > > update `account` set `balance`= 0.00 + '-6.07' where
>> accountid='3235296'
>> > > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
>> > > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but
>> not
>> > > gap waiting
>> > >
>> > > *** (2) TRANSACTION:
>> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
>> declared
>> > > inside InnoDB 500
>> > > mysql tables in use 1, locked 1
>> > > 14 lock struct(s), heap size 3112, 27 row lock(s)
>> > > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id
>> 32378480
>> > > 90.0.0.51 mario Updating
>> > > update `account` set `balance`= 0.00 + '-1.37' where
>> accountid='3235296'
>> > >
>> > > *** (2) HOLDS THE LOCK(S):
>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
>> > > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but
>> not
>> > > gap
>> > >
>> > > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
>> > > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
>> > > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but
>> not
>> > > gap waiting
>> > >
>> > > *** WE ROLL BACK TRANSACTION (1)
>> > >
>> > > The issue is that I had a lock for over 132 seconds and the other was
>> > > waiting for 33 seconds, so I get a lockwait.
>> > >
>> > > accountid is locked by a
>> > > select balance from account where accountid='3235296' lock in shared
>> mode
>> > > How can I tell mysql to wait longer? I know the process which is doing
>> > the
>> > > deadlock, is a long balance process... I know that it takes time,
>> > sometives
>> > > over 15 minutes, but they always resolve...
>> > >
>> > > How Can I tell mysql to wait for the lock as needed? like for over 12
>> > > minutes?
>> > >
>> > > TIA
>> >
>> >
>> >
>> > --
>> > 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
>> >
>> >
>>
>
>
>
> --
> Claudio
>

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

[Andrés Tello]

Ok, so I had a deadlock...

But then, why a deadlock doesn't rollback all the transaccion?


On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz  wrote:

> Deadlocks and lock wait timeouts are independent of one another. A
> deadlock happens when there is a cycle in the waits-for graph. Your
> transactions are *active* for 132 and 33 seconds, but the deadlock
> happens at the instant the conflict is detected, not after waiting. A
> deadlock cannot be resolved by waiting, by definition. Hence the name,
> deadlock. The only way to resolve it is to choose a victim.
>
> On Fri, May 11, 2012 at 3:06 AM, Andrés Tello 
> wrote:
> > Ok... I have one of those pesky error, in an application not handling
> > deadlocks or lockwaits.
> >
> > The database object can't be modified to support deadlock/lockwatis...
> > I can only change database parameteres
> >
> > Database info: Server version: 5.5.22-log Source distribution
> >
> >
> > from show engine innodb status;
> > {abstract}
> > *** (1) TRANSACTION:
> > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> > mysql tables in use 1, locked 1
> > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> > update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
> > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
> > gap waiting
> >
> > *** (2) TRANSACTION:
> > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
> > inside InnoDB 500
> > mysql tables in use 1, locked 1
> > 14 lock struct(s), heap size 3112, 27 row lock(s)
> > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480
> > 90.0.0.51 mario Updating
> > update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296'
> >
> > *** (2) HOLDS THE LOCK(S):
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not
> > gap
> >
> > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not
> > gap waiting
> >
> > *** WE ROLL BACK TRANSACTION (1)
> >
> > The issue is that I had a lock for over 132 seconds and the other was
> > waiting for 33 seconds, so I get a lockwait.
> >
> > accountid is locked by a
> > select balance from account where accountid='3235296' lock in shared mode
> > How can I tell mysql to wait longer? I know the process which is doing
> the
> > deadlock, is a long balance process... I know that it takes time,
> sometives
> > over 15 minutes, but they always resolve...
> >
> > How Can I tell mysql to wait for the lock as needed? like for over 12
> > minutes?
> >
> > TIA
>
>
>
> --
> 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
>
>

Deadlock due lockwait. How can I tell mysql to wait longer?

[Andrés Tello]

Ok... I have one of those pesky error, in an application not handling
deadlocks or lockwaits.

The database object can't be modified to support deadlock/lockwatis...
I can only change database parameteres

Database info: Server version: 5.5.22-log Source distribution


from show engine innodb status;
{abstract}
*** (1) TRANSACTION:
TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
gap waiting

*** (2) TRANSACTION:
TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
inside InnoDB 500
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 27 row lock(s)
MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480
90.0.0.51 mario Updating
update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not
gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not
gap waiting

*** WE ROLL BACK TRANSACTION (1)

The issue is that I had a lock for over 132 seconds and the other was
waiting for 33 seconds, so I get a lockwait.

accountid is locked by a
select balance from account where accountid='3235296' lock in shared mode
How can I tell mysql to wait longer? I know the process which is doing the
deadlock, is a long balance process... I know that it takes time, sometives
over 15 minutes, but they always resolve...

How Can I tell mysql to wait for the lock as needed? like for over 12
minutes?

TIA

Re: RFE: Allow to use version-specific my.cnf files

[Andrés Tello]

Reads interesting, but...

Why would you need that?

I mean... If I run several databases in the same hardware, I use completely
diferent paths for evertying, so I can have atomic, clean  and specific
files for each instance/version of the database

I think is much more easy to migrato to another hardware that way, just
copy the instance and you are set...





On Wed, Apr 25, 2012 at 9:23 AM, Honza Horak  wrote:

> Hi,
>
> PostgreSQL allows to use version-specific configuration files, which
> allows to change some settings only for particular version of DB.
>
> I think a similar enhancement would be nice and usable for administrators
> of MySQL as well.
>
> Please, consider the attached patch as a simple proposal. Any comments are
> welcome.
>
> Cheers,
>
> Honza
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>

Re: problems with INNODB tables

[Andrés Tello]

switch to innodb...

and use one_file_per_table

I use both, but I try to use myisam for cataloges.

Innodb and myisam are truly different engines, they do things completely
different, for example, with myisam you have parameters to configure the
size of the memory for the indexes, and several others, meanwhile most of
innodb performase is bound to innodb buffer pools, and with the newerst
mysql version, yo
u can have several innodb buffer pools lowering your mutex wait a lot...

Also you can switch from myisam to innodb quickly, for that type of tasks,
I do a mysqldump with tab formatted texts because it gives 2 files per
table, 1 file with the sql query to create de database and other, tab
delimited file with all the data of that table, to be used with mysqlimport

For a properly recommendation, we would need to know much more about the
system using the database, some statistics...

What is the database used for?




On Wed, Apr 25, 2012 at 5:14 AM, Malka Cymbalista <
malki.cymbali...@weizmann.ac.il> wrote:

> Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memorywhere it 
> tells you to do one thing if using MYIASM tables and another if
> using INNODB tables.  We are using both. Any suggestions?
> Thanks for any help.
>
> Malki Cymbalista
> Webmaster, Weizmann Institute of Science
> malki.cymbali...@weizmann.ac.il
> 08-9343036
>
>
> -Original Message-
> From: Rick James [mailto:rja...@yahoo-inc.com]
> Sent: Monday, April 23, 2012 9:42 PM
> To: Andrés Tello; Malka Cymbalista
> Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
> Subject: RE: problems with INNODB tables
>
> Check your memory usage according to
> http://mysql.rjweb.org/doc.php/memory
>
> > -Original Message-
> > From: Andrés Tello [mailto:mr.crip...@gmail.com]
> > Sent: Monday, April 23, 2012 9:00 AM
> > To: Malka Cymbalista
> > Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
> > Subject: Re: problems with INNODB tables
> >
> > Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql
> > process with pkill -9 -f mysql
> >
> > Y suppose the way drupal is being programed.
> > PHP open and closes database connections each time a webpage with db
> > access is issued.
> > When a php exceution ends and the apache webserver have fullfilled the
> > http request, again, php memory is freed and connections closed...
> > UNLESS:.. you are using a mem cached db connection, wich I doubt it
> > since drupal doens't requiere one, or using persistent connections,
> > again, I doubt it, because persistante database connections aren't
> > recommended to innodb tables...
> >
> > Mysql server by default can handles 100 conections, if you get to
> > thata limit you need to fine tune the number of connections allowed.
> >
> > show full processlist can give you a better idea of what is going on,
> > connections with the sleep status, are open connections with no
> > currently no transacctions...
> >
> > I never use script based stop, I always use mysqladmin -u root -p -h
> > localhost shutdown which properly tells mysql to flush tables and
> > terminate.
> >
> > I can almost bet that you are using Ubuntu... ubuntu had given me
> > sometimes very hard times because of the "edgy " code they use to use,
> > ext4 last version, and so on... what can you tell us about that?
> >
> > How much amount of memory you have?
> > How much concurrent apache/php users you have?
> > Can you provide more cuantitive data please? Hardware, php version,
> > distro, kernel...
> >
> > Cheers...
> >
> >
> >
> > To start, 100 process is quite a lot, something isn't fine. Each time
> >
> > On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista <
> > malki.cymbali...@weizmann.ac.il> wrote:
> >
> > > We are running MySQL version 5.0.45 on a Linux machine. Over the
> > > past few months we have been having several problems:
> > >
> > > 1.   Our mysql processes have increased the memory used from
> > about .3%
> > > per process to 8% per process
> > >
> > > 2.   We sometimes can have over 100 processes running which
> > brings the
> > > machine to its knees and we have to stop and start MySQL in order to
> > > kill all the processes. We think that maybe the processes are not
> > > finishing normally and are just hanging around.
> > >
> > > 3.   The machine is a web server and in the last few months we
> > are
> > > moving over to drupal 7 to build our sites and Drupal 7 requires
> > INNODB
> > 

Re: problems with INNODB tables

[Andrés Tello]

Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql
process with pkill -9 -f mysql

Y suppose the way drupal is being programed.
PHP open and closes database connections each time a webpage with db access
is issued.
When a php exceution ends and the apache webserver have fullfilled the http
request, again, php memory is freed and connections closed... UNLESS:.. you
are using a mem cached db connection, wich I doubt it since drupal doens't
requiere one, or using persistent connections, again, I doubt it, because
persistante database connections aren't recommended to innodb tables...

Mysql server by default can handles 100 conections, if you get to thata
limit you need to fine tune the number of connections allowed.

show full processlist can give you a better idea of what is going on,
connections with the sleep status, are open connections with no currently
no transacctions...

I never use script based stop, I always use
mysqladmin -u root -p -h localhost shutdown
which properly tells mysql to flush tables and terminate.

I can almost bet that you are using Ubuntu... ubuntu had given me sometimes
very hard times because of the "edgy " code they use to use, ext4 last
version, and so on... what can you tell us about that?

How much amount of memory you have?
How much concurrent apache/php users you have?
Can you provide more cuantitive data please? Hardware, php version, distro,
kernel...

Cheers...



To start, 100 process is quite a lot, something isn't fine. Each time

On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista <
malki.cymbali...@weizmann.ac.il> wrote:

> We are running MySQL version 5.0.45 on a Linux machine. Over the past few
> months we have been having several problems:
>
> 1.   Our mysql processes have increased the memory used from about .3%
> per process to 8% per process
>
> 2.   We sometimes can have over 100 processes running which brings the
> machine to its knees and we have to stop and start MySQL in order to kill
> all the processes. We think that maybe the processes are not finishing
> normally and are just hanging around.
>
> 3.   The machine is a web server and in the last few months we are
> moving over to drupal 7 to build our sites and Drupal 7 requires INNODB
> tables.   Sometimes, when we restart MySQL using the commands
> /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were
> built in drupal 7 do not come up.  In order for the INNODB tables to work,
> we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and
> then restart mysql. Otherwise the INNODB tables are not accessable.
>
>
>
> In the past all our tables were MYIASM.  Our problems started as we
> started using more and more INNODB tables. Is there anything special that
> has to be done to configure MySQL when using INNODB tables?
> We clearly have a problem  but we have no idea where to start looking. Our
> error logs don't show anything.   If anyone has any suggestions, we will be
> happy to hear them.
> We are considering hiring a consultant who is an expert in MySQL. We are
> in Israel and we are open to suggestions.
>
> Thanks for any help.
>
> Malki Cymbalista
> Webmaster, Weizmann Institute of Science
> malki.cymbali...@weizmann.ac.il
> 08-9343036
>
>

Re: Design advice for hotel availability program

[Andrés Tello]

Verify the regulation that a Hotel should submit, read the part of the
occupation reports, information gathering, ask management for their
currents report...

 Remember the data output should came from the data stored...

On Mon, Feb 20, 2012 at 4:42 PM, Chaim Rieger  wrote:

> On 2/17/2012 4:01 AM, Tompkins Neil wrote:
>
>> Hi,
>>
>> I'm in the process of starting to design a hotel booking system that will
>> eventually consist of over 10,000 hotels all with different room types,
>> rates and availability for different dates.  My question is does anyone
>> have any experience with regards the best way to store the daily rates.
>>  Am
>>
>> I best using the following pattern
>>
>> (1) Default rates/rooms stored in a generic table
>> (2) Any exceptions/changes/closed days to the daily rates are store in
>> a separate table.
>> (3) Any special offer exceptions are stored as a rule
>>
>> All, should I consider that for any hotel, for any room, for any day I
>> have
>> a record in a huge single table ???
>>
>> Thanks for any help and input.
>>
>>  There is already more than one package out there that does what you are
> trying to do, so why not take a look at how they do the db stuff.
>
> also keep in mind that you are working two reservations at once for each
> entry
>
> a guest requests an asett (the room)
> a guest then promises to pay a certain amount for an asset
> an asset can be tied to  a variety or prices, but only one at a time,
> unless certain conditions are met.
> so no its not easy to do, but it has been done.
> http://drupal.org/project/uc_**hotel 
>
> (ps. no i do not do drupal)
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

How to split a mysqldump file of multiple databases to singlefile databases... SOLVED with script

[Andrés Tello]

Today I needed to split a mysqldump -A into it several databases.
I didn't have access to the original source, so I only had the texr file to
work.
It was a webhosting server dump, so there was a LOT of databases...

I split the file with this little script I made:

file=
nextTable=""
nextStart=0
nextEnd=0
lastTable=""
lastStart=0

for i in `grep -n "^CREATE DATABASE" $file | awk '{print $1":"$7}' | sed
"s/CREATE://g"`
do
i=`echo $i | sed "s/\\\`//g"`
nextTable=`echo $i | cut -d : -f 2`
nextStart=`echo $i | cut -d : -f 1`

nextEnd=$(( $nextStart -1 ))


if [ "$lastTable" != "" ]
then
  echo "Tabla: $lastTable from: $lastStart to $nextEnd"
  sed -n ${lastStart},${nextEnd}p  $file > new/$lastTable.portabla.sql
fi

lastTable=$nextTable
lastStart=$(( $nextStart ))

done

Re: 'myisam_use_mmap' unstable like hell

[Andrés Tello]

When I had memory issues, with something relatively stable, mostly is due
faulty ram...

Can you use or less ram or change fisically the ram?



On Thu, Dec 15, 2011 at 2:23 AM, Reindl Harald wrote:

>
>
> Am 15.12.2011 08:47, schrieb Rob Wultsch:
> > To be brutally honest, if you want stability you should
> > not be using MyISAM
>
> this is bullshit
>
> without 'myisam_use_mmap' i never saw mysqld crashing
> in the past 10 years, independent of the storage engine
>
> > much less a not particularly commonly used feature.
>
> mmap is not rocket science, so i do not understnd why this
> is not properly debugged and EFAULT on
>
>
>
>

Re: 4 minute slow on select count(*) from table - myisam type

[Andrés Tello]

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: problem

[Andrés Tello]

Seems more a java issue than a mysql issue... check for your parameters for
any keepalive or persostent option...

I think you wouldn't need to recompile just re-run or at leat, reboot the
server..



On Mon, May 2, 2011 at 2:34 AM, swaroop jois  wrote:

> Hello friends, I have  MySQL server version  " 5.0.51a-Ubuntu"
>  installed on Ubuntu 8.04 machine . I would describe briefly what we are
> doing .
> Basically we have built a server that listen to Gprs connection from client
>  and accepts data in form packets and inserts that data
> into MySQL database.I run three commands .1.listening to Gprs connection
> and displaying all the received packets on the terminal.2.Number of packets
> read will showed in Java serial forwarder (Tinyos for reference )which
> listens on another port 3.command that invokes Java files  for inserting
> data into database table .
> Initially when i run the command  everything works fine and when he
>  receive packets he is  able to insert data into table in MySQL database .
>
>  He will still be listening on the port  (i.e he is running 24*7)Assume i
> receive data after 12 hrs .i am experiencing the problem of .It may not
> necessarily be 12 hrs .
>  If i have to insert data again i have to  recompile the code again and run
> all the commands .
> The error that is troubling is Result for query failed.  SQLState = 08003i
> googled the error and found that this Sqlstate indicates connection does not
> exist .
> I dont have any clues.Can any one help me please ?
> Regards,Swaroop

Memory Usage.

[Andrés Tello]

How can I know how memory is being used by Mysql?

I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and even
that I have tables over 40GB...

Thanks! xD

How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...

[Andrés Tello]

I'm running into some deadlocks issues.

I have this structure
accounting
  |---movements

To know the balance of the account, I usualy do a sum(movements.amount)
where accounting.id=someid

The issue is that the sum is starting to run very slow due hardware
constraints, and I can't trow more hardware :(, so I need to find a software
solution

My approach was to create a balance field inside accounting, but I'm running
into deadlocks, because accounting has a tree structure based upon
accounting.id and accounting.parentid.

Btw, I can't use triggers or store procedure due . Out of discussion.

So, to have all the accounts in balance, I do a drill down to get all
paretns, grandparent from an account...
So I think, I have race conditions and multiversioning issues.
I read about innodb locking and decided to use lock in share mode...
But I'm runing with some deadlocks:

TRANSACTION 0 264994, ACTIVE 1 sec, process no 5031, OS thread id
140061201196816 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 151 lock struct(s), heap size 30704, 36974 row lock(s)
MySQL thread id 62743, query id 35566790 localhost 127.0.0.1 vortex Updating
UPDATE `account` SET `balance` = '-3961.30' WHERE `accountid` ='408' LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table
`management`.`account` trx id 0 264994 lock_mode X locks rec but not gap
waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format; info
bits 0
 0: len 4; hex 0198; asc ;; 1: len 6; hex 0001b314; asc   ;;
2: len 7; hex 3b0b18; asc ;  ;; 3: len 4; hex 0194; asc
;; 4: len 1; hex 02; asc  ;; 5: len 20; hex
414e4149535f5a41495a41525f414c434152415a; asc LOANS;; 6: SQL NULL; 7: SQL
NULL; 8: len 1; hex 00; asc  ;; 9: len 1; hex 01; asc  ;; 10: len 3; hex
8fb56d; asc   m;; 11: len 1; hex 00; asc  ;; 12: len 1; hex 80; asc  ;; 13:
len 9; hex 7086e1; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 0 264995, ACTIVE 1 sec, process no 5031, OS thread id
140061201999632 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4360 lock struct(s), heap size 456688, 579126 row lock(s)
MySQL thread id 60636, query id 35566800 localhost 127.0.0.1 vortex Updating
UPDATE `account` SET `balance` = '1.31' WHERE `accountid` ='11009' LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table
`management`.`account` trx id 0 264995 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info
bits 0
 0: len 4; hex 0001; asc ;; 1: len 6; hex 00040aba; asc   ;;
2: len 7; hex 3c1b0a; asc <  ;; 3: SQL NULL; 4: len 1; hex 01;
asc  ;; 5: len 6; hex 41435449564f; asc ACTIVO;; 6: len 1; hex 31; asc 1;;
7: SQL NULL; 8: len 1; hex 00; asc  ;; 9: len 1; hex 01; asc  ;; 10: len 3;
hex 8faa7d; asc   };; 11: len 1; hex 01; asc  ;; 12: len 1; hex 80; asc  ;;
13: len 9; hex 78f72f3efa; asc   /> ;;


Any clues about how to avoid this?

Re: Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!

[Andrés Tello]

Yup, I'm doing clean tests,lshutdown, and reload mysql each test.
The raid setup is similar, Faster is raid1 with 10k harddisk, slower is raid
10 with 15k.

Metrics show
Old raid
Secuecial writting 1G: 533 mb/s  (using dd if=/dev/zero of=1G bs=1024
count=102400)
Secuencial reading 1G: 500 mb/s

New raid
Writting: 500 mb/s
Reading 800 mb/s

Average...


On Wed, Feb 16, 2011 at 4:33 PM, Singer X.J. Wang wrote:

> Dumb questions
>
> 1) Are you doing clean tests? Shutdown and restart MySQL after each test?
> 2) Is the RAID setup similar on the servers? Are you doing something like
> RAID10 on the old and RAID6 on the new?
>
> Singer
>
>
>
> On Sun, Feb 13, 2011 at 16:40, Andrés Tello  wrote:
>
>> I have a test process, which runs in the "old server" in 35 seconds, the
>> new
>> server runs the same process in 110.
>>
>> There is a change of version from mysql 4.1.22 to  5.1.22.
>> We were stuck at 5.1.22 because higher version give us another issules
>> like
>> encoding, case sensitivity...
>>
>> I really belive that the issue is regarding the mysql server
>> there is extensive information about my setup...
>>
>> I have more processors, more memory, more disk speed, but lower results...
>> T_T, because the wtf is long forgotten.
>>
>>
>>
>> hdparm  -tT /dev/sda
>> /dev/sda:
>>  Timing cached reads:   13392 MB in  2.00 seconds = 6699.90 MB/sec
>>  Timing buffered disk reads:  174 MB in  3.02 seconds =  57.64 MB/sec
>>
>>  free
>> total   used   free sharedbuffers cached
>> Mem:  16631296   16065356 565940  0  83148   13415520
>> -/+ buffers/cache:2566688   14064608
>> Swap: 16779852128   16779724
>>
>> uname -r
>> 2.6.16.21-0.8-bigsmp
>>
>>
>> Your MySQL connection id is 21 to server version: 4.1.22-standard-log
>>
>> more /proc/cpuinfo  | grep -e "processor\|name"
>> processor   : 0
>> model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
>> processor   : 1
>> model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
>> processor   : 2
>> model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
>> processor   : 3
>> model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
>>
>> grep -v ^# /etc/my.cnf | sed '/^$/d'
>> [client]
>> port= 3306
>> socket  = /tmp/mysql.sock
>> [mysqld]
>> innodb_file_per_table
>> port= 3306
>> socket  = /tmp/mysql.sock
>> back_log = 50
>> max_connections = 100
>> max_connect_errors = 10
>> table_cache = 2048
>> max_allowed_packet = 256M
>> binlog_cache_size = 16M
>> max_heap_table_size = 64M
>> sort_buffer_size = 16M
>> join_buffer_size = 32M
>> thread_cache = 8
>> thread_concurrency = 8
>> query_cache_size = 256M
>> query_cache_limit = 32M
>> ft_min_word_len = 4
>> memlock
>> default_table_type = INNODB
>> thread_stack = 192K
>> transaction_isolation = REPEATABLE-READ
>> tmp_table_size = 256M
>> log_slow_queries
>> long_query_time = 2
>> log_long_format
>> tmpdir = /tmp
>> key_buffer_size = 128M
>> read_buffer_size = 64M
>> read_rnd_buffer_size = 128M
>> bulk_insert_buffer_size = 64M
>> myisam_sort_buffer_size = 128M
>> myisam_max_sort_file_size = 10G
>> myisam_max_extra_sort_file_size = 10G
>> myisam_repair_threads = 1
>> myisam_recover
>> skip-bdb
>> innodb_additional_mem_pool_size = 16M
>> innodb_buffer_pool_size = 2G
>> innodb_data_file_path = ibdata1:10M:autoextend
>> innodb_file_io_threads = 4
>> innodb_thread_concurrency = 32
>> innodb_flush_log_at_trx_commit = 1
>> innodb_log_buffer_size = 8M
>> innodb_log_file_size = 256M
>> innodb_log_files_in_group = 3
>> innodb_max_dirty_pages_pct = 90
>> innodb_lock_wait_timeout = 120
>> [mysqldump]
>> quick
>> max_allowed_packet = 16M
>> [mysql]
>> no-auto-rehash
>> [isamchk]
>> key_buffer = 512M
>> sort_buffer_size = 512M
>> read_buffer = 8M
>> write_buffer = 8M
>> [myisamchk]
>> key_buffer = 512M
>> sort_buffer_size = 512M
>> read_buffer = 8M
>> write_buffer = 8M
>> [mysqlhotcopy]
>> interactive-timeout
>> [mysqld_safe]
>> open-files-limit = 10240
>>
>> New Server, which happnes to be 2x SLOWER!
>>
>> hdparm -tT /dev/sda ; free ; uname -r ; cat /proc/cpuinfo | grep -e
>> "proces

Re: Messed up mysql-server, cant reinstall?!

[Andrés Tello]

didn't u forgot  to run mysql_intsall_db?


On Fri, Feb 11, 2011 at 6:53 AM, Adam smalin  wrote:

> I accidentally drop the database mysql. I now cannot restore tables to the
> database or do much of anything some details can be found here
>
> http://serverfault.com/questions/234321/i-accidently-dropped-the-mysql-db/234340#234340
>
> after a few unsuccessful uninstalled/reinstalls i wrote whereis mysql and
> rm
> -rf every directory in that list. I ran `dpkg -P mysql-server`. `apt-get
> install mysql-server` saw "Stopping MySQL database server: mysqld.", ran
> `/etc/init.d/mysql start` and got the error "Starting MySQL database
> server:
> mysqld . . . . . . . . . . . . . . failed!".
>
> I wrote whereis mysql again and got NO DIRECTORIES.
>
> How do i properly reinstall this?
> I cant even restore my backups because of this.
>
> Yes i did all of this as root
>

Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!

[Andrés Tello]

I have a test process, which runs in the "old server" in 35 seconds, the new
server runs the same process in 110.

There is a change of version from mysql 4.1.22 to  5.1.22.
We were stuck at 5.1.22 because higher version give us another issules like
encoding, case sensitivity...

I really belive that the issue is regarding the mysql server
there is extensive information about my setup...

I have more processors, more memory, more disk speed, but lower results...
T_T, because the wtf is long forgotten.



hdparm  -tT /dev/sda
/dev/sda:
 Timing cached reads:   13392 MB in  2.00 seconds = 6699.90 MB/sec
 Timing buffered disk reads:  174 MB in  3.02 seconds =  57.64 MB/sec

 free
 total   used   free sharedbuffers cached
Mem:  16631296   16065356 565940  0  83148   13415520
-/+ buffers/cache:2566688   14064608
Swap: 16779852128   16779724

uname -r
2.6.16.21-0.8-bigsmp


Your MySQL connection id is 21 to server version: 4.1.22-standard-log

more /proc/cpuinfo  | grep -e "processor\|name"
processor   : 0
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
processor   : 1
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
processor   : 2
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
processor   : 3
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz

grep -v ^# /etc/my.cnf | sed '/^$/d'
[client]
port= 3306
socket  = /tmp/mysql.sock
[mysqld]
innodb_file_per_table
port= 3306
socket  = /tmp/mysql.sock
back_log = 50
max_connections = 100
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 256M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 16M
join_buffer_size = 32M
thread_cache = 8
thread_concurrency = 8
query_cache_size = 256M
query_cache_limit = 32M
ft_min_word_len = 4
memlock
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /tmp
key_buffer_size = 128M
read_buffer_size = 64M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 10240

New Server, which happnes to be 2x SLOWER!

hdparm -tT /dev/sda ; free ; uname -r ; cat /proc/cpuinfo | grep -e
"processor\|name"

/dev/sda:
 Timing cached reads:   5858 MB in  2.00 seconds = 2932.17 MB/sec
 Timing buffered disk reads:  1304 MB in  3.00 seconds = 434.06 MB/sec

 total   used   free sharedbuffers cached
Mem:  330086242097924   30910700  0  21308  76024
-/+ buffers/cache:2000592   31008032
Swap:  8388604  08388604

uname -r
2.6.34.7-0.7-desktop

processor   : 0
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 1
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 2
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 3
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 4
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 5
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 6
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 7
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 8
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 9
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 10
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 11
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz

Server version: 5.1.52-log Source distribution



grep -v ^# /etc/my.cnf | sed '/^$/d'
[client]
port  = 3306
socket= /tmp/mysqld.sock
[safe_mysqld]
err_log   = /mysql/logs/mysql.err

[mysqld]
skip-external-locking
server_id = 9000
user  = mysql
port 

Re: Moving from one MySQL server to three MySQL servers?

[Andrés Tello]

Any kind of optimization you need works.
I would use a explain sql statements istead to import without indexes, that
will shed more light...

Even if u optimize the report, if you  have concurrent access demanding a
bunch load of data you will other operations get stuck... for that is a good
reason to have a master/write server and slave/read servers... if you make a
row level block or table level block at a slave/read server, master/write
still can work.

And mysql is kinda greedy with the ram once it has it. Tries to keep index
information, select cache, etc, as long as possible, which is a good
thing...

and if the client is willing to trow more hardware to the issue, trow more
RAM to those servers...

Re: Moving from one MySQL server to three MySQL servers?

[Andrés Tello]

My experience with replication:

Most of the times, is good enough, fast enough... I have just reworked some
part of an application to split the reporting module from all other modules.


We are still using php 4.3 with pear::db module (what? legacy software is
hard to kill! we are trying!, Honest!)..

So, we need to pull 2 tricks, first of it was, that we setup replicatio to
two slaves... one for reporting, one for selects and had our master
instance.

we had an autoinclude file, which invoqued the db connection. The first
trick we pulled of was to detect if we where accesing the reports, by path
name using the a regexp over $_SERVER['SCRIPT_FILENAME'], if the filename
matched the modules regexp we instanciated the db connection to one of
slaves, the reports slave.

The other trick we made, was to modifie pear::db so we matched, again each
->query or ->simple_query against a regeexp containing the ^select pattern
and used the conection to the select database

this allowed us to manage 3 databases, 1 for writing, 1 for selects and 1
for reporting with almos 0 changes to the actual code.

Hope this ideas work for you.

Maybe you aren't use php, but I bet you are using some kind of data base
abstraction layer...

Re: which version is better for production servers?

[Andrés Tello]

I have just upgraded from mysql4.1 to mysql 5.5.3.. I will upgrade to 5.5.4.


5.5. has a lot of speed improvements...

and OF course you should NOT use mysql developtment release for production
server!

On Mon, Jul 19, 2010 at 8:05 AM, Nilnandan Joshi
wrote:

> Hi all,
>
> I just wanna make new MySQL server for OLTP kind of environment. I want to
> use InnoDB storage engine.
> Which version is better for this kind of environment and which will give
> the great performance?
> MySQL 5.1.47/48 or MySQL 5.5?
>
> Should we use mysql development release for production server?
>
> regards,
> Nilnandan
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
>
>

First impression of mysql 5.5.3 vs mysql 4.1.22

[Andrés Tello]

WW...

While uploading the database to a clean mysql, mysql 4.1.22 didn't even get
over 26 mb/s of writting speed, but I'm monitoring the speed mysql 5.5.3 is
reaching and can squeeze 100mb/s, averga I'm seeing like 35mb/s

22GB at mysql 4.1 lasted like 3 hours to fully load, this one I belive less
than an hour...

I like the performance I'm seeing...

Going to sleep XD can't wait to test it! but have to sleep... battery mode
and I'm watching the special about LOTR from History Channel...

*Three hardware family  for the sysadmin under the sky,
Seven version for the bazar  in their halls of code,
Nine engines for DBAs doomed to die,
One Enterprise for the Dark Lord on his dark throne
In the Land of Redwood where the Shadows lie.
One Enterprise to rule them all, One License to find them,
One Contract to bring them all and in the darkness bind them*

Re: mysql 5.5.3 and innodb from source. (SOLVED)

[Andrés Tello]

Thanks to everybody, and to Rob Wultsch, his link helped me to understand
what I was doing...

./configure select an automake configure or a perl one...

I ended building mysql with innodb,heap,myisam and partitions, statically
built with:

./configure --with-plugins=heap,partition,innobase,myisam
--prefix=/usr/local/mysql5/ --with-plugin-heap --with-plugin-partition
--with-plugin-innobase --with-plugin-myisam

The first line says that the flags are recognzed:
configure.pl : calling cmake /usr/src/mysql-5.5.4-m3  -DWITH_HEAP=1
-DWITH_PARTITION=1 -DWITH_INNOBASE=1 -DWITH_MYISAM=1
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql5/ -DWITH_PLUGIN_HEAP=1
-DWITH_PLUGIN_PARTITION=1 -DWITH_PLUGIN_INNOBASE=1 -DWITH_PLUGIN_MYISAM=1

and after install:
show engines;
mysql> show engines;
++-++--+--++
| Engine | Support |
Comment| Transactions |
XA   | Savepoints |
++-++--+--++
| MRG_MYISAM | YES | Collection of identical MyISAM
tables  | NO   | NO   | NO |
| PERFORMANCE_SCHEMA | YES | Performance
Schema | NO   | NO   |
NO |
| CSV| YES | CSV storage
engine | NO   | NO   |
NO |
| InnoDB | YES | Supports transactions, row-level locking,
and foreign keys | YES  | YES  | YES|
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great
performance | NO   | NO   | NO |
| MEMORY | YES | Hash based, stored in memory, useful for
temporary tables  | NO   | NO   | NO |
++-++--+--++

If I'm unable to get a report from the application, I will test partitions
tomorrow, well, in a while xD

Thanks everybody...

mysql 5.5.3 and innodb from source.

[Andrés Tello]

Who you build mysql 5.5.3 with innodb suport?

I made
sh configure.am --with-plugins=all
I see the makefile at innodb subdir being created.
I build the system correctly but when I log in to the mysql 5 instance and
do a

show engines;


I only have this:

++-+---+--+--++
| Engine | Support |
Comment   | Transactions |
XA   | Savepoints |
++-+---+--+--++
| MRG_MYISAM | YES | Collection of identical MyISAM
tables | NO   | NO   | NO |
| PERFORMANCE_SCHEMA | YES | Performance
Schema| NO   | NO   |
NO |
| CSV| YES | CSV storage
engine| NO   | NO   |
NO |
| MEMORY | YES | Hash based, stored in memory, useful for
temporary tables | NO   | NO   | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great
performance| NO   | NO   | NO |
++-+---+--+--++


my my.cnf...

skip-name-resolve
log_error=/mysql5/mysql5.err
socket = /mysql5/mysql.sock
port   = 3308
pid-file   = /mysql5/mysql5.pid
datadir= /mysql5/data

tmpdir=/tmpfs

binlog_cache_size=64M #tamaño de la transaccion a cachear
bulk_insert_buffer_size=256M #cache de insert por thread
delay_key_write=OFF #detiene la creacion de llaves? No, no nos conviene por
integridad.

max_allowed_packet=256M
table_cache=4096
join_buffer_size=256M
tmp_table_size=1024M
sort_buffer_size=1024M
thread_cache_size=64
#default_storage_engine=INNODB
query_cache_size=1024M
query_cache_limit=256M
innodb_file_per_table


innodb_data_home_dir = /mysql5/innodb
innodb_data_file_path = ibdata/innodb:2000M:autoextend
innodb_buffer_pool_size=512M
innodb_flush_method=fdatasync #ls opciones son fdatasync (default), O_DSYNC,
(lento?) O_DIRECT
innodb_locks_unsafe_for_binlog=1 #solo usa indices
innodb_additional_mem_pool_size=512M
innodb_log_file_size=2000M
innodb_log_buffer_size=32M
innodb_max_dirty_pages_pct=95
innodb_max_purge_lag=0
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
innodb_thread_concurrency=200

max_heap_table_size=4G


what I'm doing wrong? Please advice.
Thanks.

Mysql 4 and or Partitions.

[Andrés Tello]

Ok... I solved my mistery of the slow 22G table rebuild the kernel to
support memory and now things are working.

But still quite slow.

I do sum() operations over the 22G table. I'm using the latest version of
mysql 4... (I WILL migrate soon, I promise), one thing I have notice, is
that operations over that 22G table, seasuring the I/O with iostat never
surpass the 23mb/s for reading... even if I test the IO of the array, it can
easily give me 300mb/s for reads... and like 150 for random reads...

first of all, is there any way to squeeze more speed out from mysql 4?
No... I imagined that...

So, my next step is migate to a newer version of mysql ( YEY ^_^  )... I
have read a few about partitions.

The specific query I'm making is a query which do a sum filtered over a date
field.

What would be the best approach to partition this table?

Can I mix innodb with partioned tables and still have acid compliance?

The split in several partitions creates more lecture threads  or how
parelelization over partitions works?

And last, does mysql 4 support partitions? XD





tia.

Re: Why is MySQL always linked to Php? The history channel version...

[Andrés Tello]

HIstorical convinience:

A certification program for oracle or db2 requires a lot of money and time.
The facilities oracle and db2 can offer to the dba includes things that the
S.O should to. Exaple: raw spaces and table spaces, hot copy, replication,
etc...

Many of this "facilities" used to be provided by the S.O with tools like
lvm, drdb, heartbeat and so on in Linux.

But that was  years ago. Back then, mysql didn't eve had locking, less
replication. I remember when Mysql added replication, long before postgress
sql, that allowed "us", mysql people to kick ass postgress people, because
while postgress was fully ACID and mysql wasn't, we had... REPLICATION...
(insert toy story *wow* here please)...

So, back in those years, when web was just as a emergin technology, a fast,
relaiable, easy to use and install database was Mysql. If I don't remeber
bad, even php support for mysql was included earlier than postgress... and
then the concept of LAMP started to gestate, as Linux Apache PHP & Mysql...

Meanwhile a LAMP people was working with small systems for the web, as a new
trend, the "olds" dba were working with store procedures, oracle rac,
informix being bougth by IBM2 those dbas used to admin big, big
projects, cus smaller installations where using... Acess *shudder*

So, 2 branches of DBA arised, one brach, the corporate DBA, which should
know how to configure and administer everything from a big, big, and
difficult package, and the "script kiddiedbas", which didn't care about
acid, used the S.O to make table able to grow with LVM, and developed code
with php... Mysql was simple...

And the leyend asired... WEB 2.0... and people started to alinate that lamp
included Perl, not only php... those pesky monks, quite a bit fanatics...

Mysql evolved into an enterprise, and a business model arised, and
certification for mysql were created by a central organization, the mysql
people it self. And THEN, we started to have a path to get "Authorized"
trainning... and mysql capabilites started to match with those big database,
acid, store procedure, georeferencing, etc, etc, etc...

So, the reason why mysql is bound to php is historical and because LAMP was
very cheap to implement against DB2, Informix and Sybase. (I will not
mention oracle, cus... well, you know this "new situation with oracle")

In my particular point of view, I don't like too certified people because
the tend to lack the hability to work aroud issues if their certified
knowledge over the certifed tool doesn't had a certified way of doing the
certified thing...

And in the end, a multi skilled resource is much better than a single
skilled resource...

Hope this answer your question.


On Thu, Jul 15, 2010 at 9:00 AM, alba.albetti wrote:

> Browsing the Web I've seen that usually companies look for developers
> working on MySQL and Php. Why are the two things linked? I mean I've not
> found any requests for just a MySQL developer or DBA (as for example it
> happens for Oracle), but it's always requested a MySQL/Php expert.
> I ask for it 'cause I've always been a developer/DBA on RDBMS (Oracle and
> DB2) and as I've been learning MySQL for few weeks, I'd like to know whether
> and why it's so important to learn Php as well. It would be so difficult to
> find a job as MySQL developer/DBA without knowing Php as well.
> Thanks!
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
>
>

Re: Slow disk access: 1.4m to do a select count over 23GB table.

[Andrés Tello]

As usual, after you send a mail, you check for other things

>From the 32GB of ram, I only was allocating 2G, not by the process, but by
all the S.O...
free -G reported barely 1 gig of ram...

XD -> happy face that I have lots of ram...

now my face is like: ¬¬ -> why I didn't verfy the amount of ram earlier?

I'll verify this ram issue, cus if this is the issue then 1.4M isn't a bad
time...

TIA

unallocated innodb buffer pool. This is nothing more than a guess
>
> What is ram XD?
> --
> Rob Wultsch
> wult...@gmail.com
>
My  guess is that you have a innodb table and that you have

Slow disk access: 1.4m to do a select count over 23GB table.

[Andrés Tello]

Hi, I'm using mysql 4.1.21, a legacy system. Next step to migrate it, but
for now, I need the community help.

I have a 23GB table, if I do a select count(over_an_index) from table it
uses 1.4minutes to read. The main issue is that this table is the main table
of a system and each query is taking too long to complete.

How can I optimize the access of the database?

any mysql variable I could use?
or how can I load the index into memory? I have 32GB of ram XD plenty to
useit into something more useful


Thanks.

Re: How to slim MySQL?

[Andrés Tello]

The more information, the easiest to pinpoint solutions.

delete all client and administrativ tools (mysql, mysqladmin, etc from the
"server", since you aren't doing any transactions, I think you can ditch all
engines excepto the isam/myisam, the basic one, also you migth want to leave
memory engine...

If I understand your project, you migth copy the database to the ram into a
tmpfs and if any change is needed, you update it, maybe to another engine??

What would I do. Build  mysql in some machine, only copy mysqld and it's
libraries, start it and trace it with strace -e open to see what files does
it needs at startup. Supply them.

Add the database, run the aplication.

If not working, repeat... until it works...

that would be my approach, I think that would take like... 2 days at most.
Don't think about leaning down the mysql, better thik about providing what
it barely needs to run properly.

Good luck.





On Tue, Jul 6, 2010 at 10:59 AM, Nima Mohammadi  wrote:

> On Tue, Jul 6, 2010 at 7:10 PM, mos  wrote:
> Hope the scenario is clear now!
> Did you really need this explanation to answer my question?
>
> -- Nima Mohammadi
>

Re: How to corrupt a database please???

[Andrés Tello]

What if the DBA ask for the backup?

And those recommendations can be "fixed" or they have a very high chance of
making recovery impossible?





On Sun, Apr 18, 2010 at 1:09 PM, Rob Wultsch  wrote:

> On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna 
> wrote:
> > open the file and remove some data and close it for both data file and
> index
> > files, So the tables will be corrupted when access.
> >
> >
> > On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri 
> wrote:
> >
> >> Hi all, I am hiring a few new junior DBA's and I want to put them thru a
> >> simple db repair training.  Does anyone know how I can deliberately
> corrupt
> >> a MyISAM and InnoDB database in different ways please?  So what I want
> to do
> >> is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH
> >> DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour
> period
> >> :-)  I have fixed oodles of db's but NEVER thought I would see the say
> where
> >> I would WANT to corrupt a db on purpose, but that day is here and am
> looking
> >> for advise please.
> >>
> >> Thanks...
> >>
> >> Nunu
> >>
>
> Umm, shouldn't you train your Junion DBA to:
> 1. fail off of the corrupted servers.
> 2. restore from backup.
> 3. or at least get a non-junior dba and then have them shadow?
>
> I have a problem with the idea of "repairing" Innodb. Depending on
> where the corruption (checksum mismatch) has occurred it can be very
> difficult to get all the original data out. Don't get me wrong, there
> are way to do it, but it is a nasty endeavor.
> For that mater I don't trust "repairing" MyISAM all that much either.
> I try my very best to keep MyISAM out of production.  In my opinion
> MyISAM should be treated as something one step higher than the
> blackhole engine. Put data in and you might be able to pull it out
> later. (don't get me wrong, packed myisam has its place...)
>
> --
> Rob Wultsch
> wult...@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
>
>

Windows installer source code...

[Andrés Tello]

I followed the instructions from Mysql site to donwload the code using
bazaar...

I need to make a modification to the installer, but I wasn't able to find
the source code where the is the reference to a screen at the wizard...  so
I wonder, does the windows installer code is distributed with the mysql
code? If not where can I find it, if yes, where is it in the source tree?


I'm more a linux dev, more than windows, I have no idea how a msi source
file should look... :/ but you know, management requirements...


Please Advice...