Tag Info

New answers tagged

0

From RDS Console > Parameter Groups > Edit Parameters, You can change the value of the max_connections parameter to a custom value.


0

afaik, this is not possible. However, as an workaround you can create function which makes count(*) under required schema and grant execute on this function to user. User will not be able to access table, but will get required count.


1

Also you can use fail2ban for this goal as too easily way . In this case you just install it on you linux OS, then enable the section for [mysqld-iptables] in the /etc/fail2ban/jail.local. [mysqld-iptables] enabled = true filter = mysqld-auth action = iptables[name=mysql, port=3306, protocol=tcp] sendmail-whois[name=MySQL, dest=root, ...


0

With regards to writes, you might want to change innodb_flush_log_at_trx_commit to either 0 or 2, see http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit There are other variables affecting performance, most notably innodb_buffer_pool_size that might be too low in your case thus IO reads might be more common ...


0

For addtion, when you delete some rows in table. Table will be fragmented. Remove the fragment will make your table is shinked. Optimize table like Ike Walker said will help. Optimization table makes downtime for mysqld.


0

Some people tell me about mydumper. I have not used it before but it is very hopeful. About mydumper: http://imagexmedia.com/blog/2014/11/speeding-your-mysql-dumprestores-mydumper http://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/


1

Based on your comment, the trigger has multiple lines. You need a BEGIN...END block as follows: USE mcet DELIMITER $$ DROP TRIGGER IF EXISTS tg_student_details_insert; CREATE TRIGGER tg_student_details_insert BEFORE INSERT ON student_details FOR EACH ROW BEGIN SET NEW.created_date = NOW(); SET NEW.dobw = date_to_words(NEW.dob); END; DELIMITER ; ...


0

You must do some work in PHP. You have a set of numbers: find the non empty ones, build a string, and put the string into the IN() clause. Sample code: $a = array(); foreach (array($mon, $tues, ...) as $val) { if (! empty($val)) $a[] = $val; // add the useful values to the array } $str = implode(',', $a); // turn the array into a comma-list ...


0

If it is a Stored Function, remove the keyword "CALL". If that does not suffice, please show us SHOW CREATE TRIGGER ... SHOW CREATE TABLE ...


0

Subqueries are not necessarily well optimized. This is a "correlated" subquery, which makes it difficult to reformulate. Please show us SHOW CREATE TABLE for each table -- there could be subtle issue, such as datatypes. Try this 'compound' index on results: INDEX(person_id, date) If that does not help, let's try to turn the query inside-out.


0

Any UPDATE to a row requires the following (sooner or later): Read the 16KB block containing the row (unless cached in the buffer pool) Write the block back to disk (or cache it a long time) The "doublebuffer" is written once per transaction Do one write to the InnoDB log for transactional integrity. (The details here depend on various settings and ...


1

I have some good news and some bad news. The good news is that this will work for MySQL. The bad news is that it won't work for 5.5. In any case, I would advise moving to 5.6 anyway, in no small part due to the vast improvements made to the performance schema (see below). I compiled and installed 5.6 (am currently running 5.7 - bit of a bleeding edge type ...


0

There are lots of tables : 30k tables That's really bad, even if you have a modern amount of RAM (instead of 0.5GB). The OS needs to space for them; mysql needs space; InnoDB also needs space. I can't prove it, but I think that is the main problem.


0

Store the "BO" and "1" as two columns. INDEX that pair of columns so that you can find the max Construct 'BO_002' when fetching the row by doing CONCAT(prefix, '_', seq). (And use ZEROFILL instead of LPAD, etc).


0

Okay, in case anyone stumbles upon a similar problem and Google directs them here: I don't exactly figured out why, but sort of figured out what causes it, phpMyAdmin was the culprit. Issuing the same commands directly in the mysql command line did not cause the temporary tables to be written to disk. Now, I also have phpMyAdmin on my development machine ...


2

I am glad you found your own answer. I am sure you found out you can add this to my.cnf [mysqld] expire_logs_days = 14 You do not have to restart mysql. You login as root@localhost and run this: mysql> SET GLOBAL expire_logs_days = 14; To trigger the auto rotate immediately, run mysql> FLUSH LOGS; If you want to zap binary logs from the mysql ...


1

The ODBC Connection needs to be a System DSN. The connection is made as the service account, not you, and so the service account doesn't know about your User DSN. From the article: Select the System DSN tab to configure a data source for the entire system.


0

OK here is your situation: your INNODB_FILE_PER_TABLE is OFF. The data and index pages for the InnoDB tables are all inside the system tablespace (ibdata1). For those tables, you only see the .frm files. Therefore, everything is OK. Here is what InnoDB looks like (image courtesy of Percona CTO Vadim Tkachenko) These days, many use the default ...



Top 50 recent answers are included