MySql responding slow



I have a difficult question now: in fact one that anybody who helps on a forum will hate because the information I can offer is minimal and not complete.

I have a LAMP setup (Fedora 12, Apache 2, MySql 5 and PHP 5) which is exhibiting a very strange behaviour: MySql response times are very slow. Let me explain this better. The website which is built on this server (and which uses a number of MySql queries at each page load) responds fine - or at least with no perceptible slow down. The repsonse time of MySql gets slow only when I run queries through PhpMyAdmin or through some small PHP scripts.

Here are some further notes:
1. By slow response time I mean that although the query is still run in 0.0002s (as reported by PMA) it takes at least 10 seconds before the query is executed.

2. I have tried emptying the DB and leaving only 10 records in one table, but the problem persists.

3. "SELECT * FROM" queries seem to behave normally.

4. Examples of queries that I know have a problem are:
SELECT * FROM table WHERE id = '1'
ALTER TABLE
INSERT

I am starting to think this might be a setup issue, but I have no clue where to look. I also understand this is very limited information, so I will appreciate anybody who will take the time to point me in the right direction.

I have tried activating the cache, turning on slow query loggin (no slow queries) and have tried "skip-name-resolve", but none of these things have worked.

Here below is my my.cnf file.
Code: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Recommendations from MySqlTuner max_connections = 150 max_user_connections = 150 key_buffer = 36M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M open_files_limit = 2048 table_cache = 1024 wait_timeout = 60 interactive_timeout = 30 query_cache_size = 512M query_cache_limit = 512M thread_cache_size = 4 table_cache = 256 log-slow-queries = /var/log/mysql-slow.log long_query_time = 1 max_allowed_packet = 1M max_connect_errors = 10 max_heap_table_size = 64M tmp_table_size = 64M # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [ndbd] # If you are running a MySQL Cluster storage daemon (ndbd) on this machine, # adjust its connection to the management daemon here. # Note: ndbd init script requires this to include nodeid! connect-string="nodeid=2;host=localhost:1186" [ndb_mgm] # connection string for MySQL Cluster management tool connect-string="host=localhost:1186"
Thank you very much for your help,
Adrien
Posted On: Thursday 25th of October 2012 10:15:24 PM Total Views:  153
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Problems logging into mysql via shell script

Greetings, I am trying to come up with an import script to automate nightly updates of a remote mysql database. I can't figure out how to make the login command to the password as an argument and not prompt me for it. It gives me a password error even though the password is correct (believe me, ive checked a hundred times). I just don't think im sending it correctly Here is the whole script: mysql -h database -u username --password=password use databasename truncate table tablename LOAD DATA LOCAL INFILE '/path/to/csv/file.csv' INTO TABLE `tablename` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; If I try to log in manually I have no problems mysql -h database -u username -p It then prompts me to enter my password, I enter it, and get in without a problem. If there is not a way to send the password in the script, is there a way to have it entered automatically in the prompt
VIEWS ON THIS POST

121

Posted on:

Wednesday 24th October 2012
View Replies!

mysql datetime columns vs integer columns using unixtimestamps

Up until now, I've been storing my times in an integer field as unix timestamps. I'm thinking about making the switch to mysql's date/time column types instead but I have a question. I do a lot of comparing with dates and was wondering if using mysql's date/time column types will slow down my queries since it has to convert the column to an "internal long integer" to do a comparison rather than not just comparing straight integers with a unix timestamp in an integer column. source: http://dev.mysql.com/doc/refman/5.0/en/using-date.html Quote: Originally Posted by mysql.com When you compare a DATE, TIME, DATETIME, or TIMESTAMP to a constant string with the , or BETWEEN operators, MySQL normally converts the string to an internal long integer for faster comparision (and also for a bit more relaxed string checking). I'm hoping that I'm just being paranoid and the actual performance difference is neglible. Please discuss.
VIEWS ON THIS POST

144

Posted on:

Wednesday 24th October 2012
View Replies!

Are too many mysql privileges/users a bad thing?

I was wondering what the suggested practice is when it comes to mysql privileges/users I have a bunch of individual apps that I currently have a dedicated username per app. I was reading that too many usernames/privileges may actually be a bad thing and was wondering what the best practice would be Having individual passwords I would think would be best for security as in case there is an issue with the code, peoeple could not exploit the other databases. Is it common practice to create 1 username/password for all your databases
VIEWS ON THIS POST

135

Posted on:

Wednesday 24th October 2012
View Replies!

exporting to mysql from access with primary keys intact?

Is there any way to export my tables from access and keep the primary keys intact At the moment I use the DSN driver to link the tables and feed them into mysql but I lose the primary keys.
VIEWS ON THIS POST

153

Posted on:

Wednesday 24th October 2012
View Replies!

mysql conferences (was "newly joined")

hello everybody, i have joined this group newlly. will there be any mysql conferences every year if anybody knows about them please inform me..
VIEWS ON THIS POST

139

Posted on:

Wednesday 24th October 2012
View Replies!

select next from in mysql

id date panaid 5 1332533806 14 543 1332533943 16 322 1332533825 29 anyone have simple idea to select by date next row if true then give next id
VIEWS ON THIS POST

160

Posted on:

Thursday 25th October 2012
View Replies!

insert 4 digit only in database mysql

hi .. i want to insert ID NUMBER in my table, ex. i want to insert "1" and in table stored "0001", if i insert "12", in table stored "0012". The point is i want to insert 4 digits in my table. Can my master help my problem
VIEWS ON THIS POST

146

Posted on:

Thursday 25th October 2012
View Replies!

3 tables delete query. mysql geek need :)

I have 3 tables! Need a MYSQL delete query to delete rows in 3 tables at same time... table1: log_visit 1) Need to DELETE from log_visit WHERE counter > 5 id | visit | visitor | counter 1 - 10 - qwerty | 15 2 - 11 - qwerty | 10 3 - 12 - azerty | 8 4 - 13 - azerty | 1 it will delete row 1 and 2 table2: log_link_visit_action 2) need to get visit and visitor rows deleted from table1 and delete in table2, in this case [row 1-table1] DELETE from log_link_visit_action WHERE visit = 10 AND visitor= querty [row 2-table1] DELETE from log_link_visit_action WHERE visit = 11 AND visitor=querty [row 3-table1] DELETE from log_link_visit_action WHERE visit = 12 AND visitor=azerty id | visit | visitor | url 15 - 8 - dodid - yahoo.com 16 - 10 - qwerty - google.com 17 - 11 - qwerty - abc.com 18 - 15 - kaz - zzz.com it will delete row 16 and 17 table3: log_action 3) Need to delete all url deleted in table2, in this case [row 17-table2] DELETE from log_action WHERE idaction = 'google.com' [row 18-table2] DELETE from log_action WHERE idaction = 'abc.com' id | idaction 1 - google.com 2 - yahoo.com 3 - abc.com it will delete row 1 and 3 Any possible help joining delete queries in one query would be extremely helpful Many
VIEWS ON THIS POST

147

Posted on:

Thursday 25th October 2012
View Replies!

Doing more in mysql over php - in general a bad idea or good one?

MySQL offers some ways to perform things that you might put in your application logic. For example, say you want to find the oldest people in a database with an age column. SELECT name, age FROM persons WHERE age = (SELECT MAX(age) FROM persons) This would select only the rows that have the highest integer in the age column (so it would select one or more people). But you could also just do: "SELECT name, age FROM persons" and then use the results of this query to do some php to find the oldest people. In general, which is better for speed and performance I generally try to minimize using my database usage for doing logic, but I don't know if this is a good plan or where to draw the line. It would be helpful to have a good rule of thumb to judge whether I should use the database to do a calculation or organization over php and vice versa.
VIEWS ON THIS POST

130

Posted on:

Thursday 25th October 2012
View Replies!

Problem connecting to mysql through terminal and through mamp

Can anyone help I don't have much experience of solving this kind of issue. I can no longer connect to mysql through mamp - the light remains red (the apache light is green). In the past typing 'killall -9 mysqld' has done the trick and I have been able to connect to mysql through mamp afterwards. In addition, I am now also unable to connect to mysql through the terminal window. I get the error message ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/Applications/MAMP/tmp/mysql/mysql.sock' (2) The only changes I've made have been to the file config.inc.php in the folder phpMyAdmin, as instructed on page 315 of 'Build your own database driven web site'. (I reverted my changes back but I still get this error message.) Any ideas how I can connect to the database again and get mamp working
VIEWS ON THIS POST

213

Posted on:

Thursday 25th October 2012
View Replies!

Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2)

I know I have the correct information to login to the MySQL database on my site. I am getting an error that states: Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2) The login information is all in the same config file that is included in every page that needs to access the database. I am ending on the last line below, with this error. PHP Code: \t\t\t\t\t\t\t\t\t\techo"{$_SESSION['first_name']}-you'reloggedin.";if(isset($_POST['submitted'])){/*Handletheform*/\t\t\t\t$query="SELECTemail,user_idFROMusers_tblWHEREfirst_name='$_SESSION[first_name]'";\t\t\t\techo$query;\t\t\t\t$result=mysql_query($query);\t\t\t\tif(!$result){\t\tdie('Invalidquery:'.mysql_error());} When I login, I create the session and when get to this page, it displays "Bill you're logged in." showing that I have the correct first_name. What should I do to determine the cause of this error
VIEWS ON THIS POST

217

Posted on:

Thursday 25th October 2012
View Replies!

how to write the sql command in mysql?

for example: the table named products there is a product name field in the table. i want to show some related items which matched by the knowed products's product name.($product_name) how to write the sql query command eg: if the knowed products's product name is True Blood Season 1 DVD i want to get all the product name which begins as True Blood Season.. if the knowed products's product name is 24 Hours Season 7 DVD i want to get all the product name which begins as 24 Hours Season..
VIEWS ON THIS POST

207

Posted on:

Thursday 25th October 2012
View Replies!

how to use SAAS(plans based tables) in mysql

all I am just implementing a SASS based database for a sample application. I have 3 plans and all three are different basic medium Advanced I need to develop a db tables in mysql for these 3 memberships I have different options in each plan How can I design the basic db and how can I know who is belongs to which plan using mysql Can anyone help me
VIEWS ON THIS POST

180

Posted on:

Thursday 25th October 2012
View Replies!

mysql how to create a dynamic table

. i have a problem that I am unable to resolve myself. I am building a jobs board for members to be able to communicate with each other. I anticipate that members will send out and receive a lot of correspondence between each other via the site. So i obviously need to have a database to store their communications with each other. My questions are What is the best way to approach this Do I dynamically create individuals table for each members ( i suspect this is the right approach). Or simple table for all the members ( i suspect that such a table will be too cumbersome and difficult to control in the long run. It would end up with millions of emails) . I tried to dynamically create a table for each member. The title for the dynamic table is member ( plus a concatenating of the $user_Id ( thus creating a unique table for each indivudial ). However when i tried to run it to the database, the mysql database did not create the tables. i have no idea where i have gone wrong with the SQL syntax. could someone please look at my SQL query and tell me if its correct. Thank you in advance for your kind help. PHP Code: require_once('databaseb_functions.php');//Connecttothedatabase. \t\t\t\t\t\t\t\t\t\t \t\t\t\t \t\t\t\t$sqlCommand\t="USEjobs; \t\t\t\t\t\t\t\tCREATETABLE\tmembers.'$id(\t\t \t\t\t\t\t\t\t\tuser_idint(11)NOTNULL, \t\t\t\t\t\t\t\tforeign_id\tint(11)NOTNULL, \t\t\t\t\t\t\t\tmessages_sentvarchar(255)NOTNULL, \t\t\t\t\t\t\t\tdate_sentvarchar(255)NOTNULL, \t\t\t\t\t\t\t\t\t\tmessage_received\tDATETIMENOTNULL, \t\t\t\t\t\t\t\tdate_received\tDATETIMENOTNULL,\t\t \t\t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\t)"; \t\t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\t\t\t \t\t$SqlCommand\t=mysqli_query($dbc,\t\t$sqlCommand);\t\t\t\t//Runthequery.
VIEWS ON THIS POST

146

Posted on:

Thursday 25th October 2012
View Replies!

I don't know query for this custom fetch from mysql

I don't know query for this custom selection from mysql. My question is similar to the thing as in PHP, we have a function isset($variable) and !empty($variable), is there anything similar in mysql because I want to fetch a set of records, where book_id is set or not left empty.
VIEWS ON THIS POST

111

Posted on:

Thursday 25th October 2012
View Replies!

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

last week I installed and successfully set up mysql on mac.. (10.6.6) was able to connect fine from unix shell and from PHP and JSP.. today, just a week later, I can't connect with either PHP or JSP, and when try to connect from unix shell I get this error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) what is this please... would appreciate some help...
VIEWS ON THIS POST

234

Posted on:

Thursday 25th October 2012
View Replies!

Import .xls file into a mysql database

Import .xls file into a mysql database I have a .xls file that i would like to import into a mysql database, I am ok creating the mysql table _temp in my webspace and try this code in mysql: Code: LOAD DATA INFILE 'D:/11_02_11.xls' INTO TABLE _temp FIELDS TERMINATED BY ',' IGNORE 1 LINES; But error: [Err] 1261 - Row 1 doesn't contain data for all columns Any ideas most welcome.
VIEWS ON THIS POST

159

Posted on:

Thursday 25th October 2012
View Replies!

mysqladmin -u root status; command not found

Im just starting with Kevin Yanks book. Im on OSX 10.4 so i installed MAMP. when i type in "mysqladmin -u root status" terminal responds: command not found.
VIEWS ON THIS POST

218

Posted on:

Thursday 25th October 2012
View Replies!

which mysql server to download??

hey friends...i request you all to pls help me out...im damn confused... im planning to make a website by March,2011 in which i will be using HTML/JSP and Mysql as d/b. however, i have a few doubts abt. Mysql.... 1) on the mysql site, there are many packages available to download. which one should i d/l. im using windows 7 and netbeans IDE 2) im confused whether i should use only the essentials package or the complete package wats d difference between both as far as query processing time is considered. my web application needs lowest query response time. 3) any tutorial site covering all the queries of Mysql....\t 4) is there any other d/b that i should use instead of Mysql im not willing to invest any money since im a student and i have previusly worked on oracle and sql. thanx a lot in advance.
VIEWS ON THIS POST

180

Posted on:

Thursday 25th October 2012
View Replies!

mysql privilege: does "select" allow me to join

After I changed my privileges to just select, this query ceased to function: Code SQL: SELECT base_SalesTable.* ,buckets.width ,buckets.teeth FROM base_SalesTable LEFT OUTER JOIN buckets ON base_SalesTable.name = buckets.name WHERE base_SalesTable.category = :category AND base_SalesTable.mincap = :mincap ORDER BY weight Happy Holidays
VIEWS ON THIS POST

136

Posted on:

Thursday 25th October 2012
View Replies!