# Please Help what is wrong with the setting
**URL:** https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196
**Category:** Other MySQL® Questions
**Created:** [June 24, 2009, 3:21am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196 "2009-06-24T03:21:14Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 24, 2009, 3:21am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/1 "2009-06-24T03:21:14Z")
I have 10 different web sites access to sql database and the hosting company is saying that there is a problem with the indexes these are the configuration below.
Will you please tell me how to fix it I have 15 days either i fix the problem or i will be thrown from the hosting company.
thank you
Luca
Server variables and settings
Variable Session value / Global value
back log 50
basedir /
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8\_unicode\_ci
(Global value) latin1\_swedish\_ci
collation database latin1\_swedish\_ci
collation server latin1\_swedish\_ci
concurrent insert ON
connect timeout 5
datadir /var/lib/mysql/
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
expire logs days 0
flush OFF
flush time 0
ft boolean syntax + -\>\<()~\*:“”&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1,024
have archive NO
have bdb NO
have blackhole engine NO
have compress YES
have crypt YES
have csv NO
have example engine NO
have geometry YES
have innodb YES
have isam NO
have merge engine YES
have ndbcluster NO
have openssl NO
have query cache YES
have raid NO
have rtree keys YES
have symlink YES
init connect
init file
init slave
innodb additional mem pool size 1,048,576
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 209,715,200
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb fast shutdown ON
innodb file io threads 4
innodb file per table OFF
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log arch dir
innodb log archive OFF
innodb log buffer size 1,048,576
innodb log file size 5,242,880
innodb log files in group 2
innodb log group home dir ./
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb table locks ON
innodb thread concurrency 8
interactive timeout 28,800
join buffer size 3,141,632
key buffer size 629,145,600
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language /usr/share/mysql/english/
large files support ON
lc time names en\_US
license GPL
local infile ON
locked in memory OFF
log OFF
log bin OFF
log error
log slave updates OFF
log slow queries OFF
log update OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 1,048,576
max binlog cache size 4,294,967,295
max binlog size 1,073,741,824
max connect errors 10
max connections 45
max delayed threads 20
max error count 64
max heap table size 16,777,216
max insert delayed threads 20
max join size 4,294,967,295
max length for sort data 1,024
max prepared stmt count 16,382
max relay log size 0
max seeks for key 4,294,967,295
max sort length 1,024
max tmp tables 32
max user connections 15
max write lock count 4,294,967,295
myisam data pointer size 4
myisam max extra sort file size 2,147,483,648
myisam max sort file size 2,147,483,647
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 8,388,608
myisam stats method nulls\_unequal
net buffer length 16,384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old passwords OFF
open files limit 4,096
pid file /var/lib/mysql/alexis.internetwebserver.net.pid
port 3,306
preload buffer size 32,768
prepared stmt count 0
protocol version 10
query alloc block size 8,192
query cache limit 20,971,520
query cache min res unit 4,096
query cache size 209,715,200
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 2,048
read buffer size 5,238,784
read only OFF
read rnd buffer size 262,144
relay log purge ON
relay log space limit 0
rpl recovery rank 0
secure auth OFF
server id 0
skip external locking ON
skip networking OFF
skip show database OFF
slave net timeout 3,600
slave transaction retries 0
slow launch time 2
socket /var/lib/mysql/mysql.sock
sort buffer size 5,242,872
sql mode
sql notes ON
sql warnings ON
storage engine MyISAM
sync binlog 0
sync frm ON
sync replication 0
sync replication slave id 0
sync replication timeout 0
system time zone EDT
table cache 512
table type MyISAM
thread cache size 50
thread stack 196,608
time format %H:%i:%s
time zone SYSTEM
tmp table size 33,554,432
tmpdir
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
version 4.1.22-standard
version comment MySQL Community Edition - Standard (GPL)
version compile machine i686
version compile os pc-linux-gnu
wait timeout 28,800
Variable Value
Flush\_commands 37
Slow\_queries 2,873
Begin Handler
Variable Value
Handler\_commit 561
Handler\_delete 646 k
Handler\_discover 0
Handler\_read\_first 3,513 k
Handler\_read\_key 777 M
Handler\_read\_next 387 M
Handler\_read\_prev 9,935 k
Handler\_read\_rnd 54 M
Handler\_read\_rnd\_next 1,331 M
Handler\_rollback 6,583
Handler\_update 9,574 k
Handler\_write 107 M
Begin Query cache
Variable Value
Flush query cache
Qcache\_free\_blocks 4,553
Qcache\_free\_memory 23 M
Qcache\_hits 3,704.61 M
Qcache\_inserts 121 M
Qcache\_lowmem\_prunes 2,604 k
Qcache\_not\_cached 4,470 k
Qcache\_queries\_in\_cache 78 k
Qcache\_total\_blocks 164 k
Begin Threads
Variable Value
Show processes
Slow\_launch\_threads 0
Threads\_cached 37
Threads\_connected 10
Threads\_created 46
Threads\_running 5
Threads\_cache\_hitrate\_% 100.00%
Begin Binary log
Variable Value
Binlog\_cache\_disk\_use 0
Binlog\_cache\_use 0
Begin Temporary data
Variable Value
Created\_tmp\_disk\_tables 246 k
Created\_tmp\_files 8,352
Created\_tmp\_tables 781 k
Begin Delayed inserts
Variable Value
Delayed\_errors 0
Delayed\_insert\_threads 1
Delayed\_writes 66 k
Not\_flushed\_delayed\_rows 0
Begin Key cache
Variable Value
Key\_blocks\_not\_flushed 0
Key\_blocks\_unused 539 k
Key\_blocks\_used 19 k
Key\_read\_requests 2,115 M
Key\_reads 3,888 k
Key\_write\_requests 6,565 k
Key\_writes 3,366 k
Key\_buffer\_fraction\_% 12.21%
Key\_write\_ratio\_% 51.27%
Key\_read\_ratio\_% 0.18%
Begin Joins
Variable Value
Select\_full\_join 246 k
Select\_full\_range\_join 719
Select\_range 318 k
Select\_range\_check 6,395
Select\_scan 4,591 k
Begin Replication
Variable Value
Show slave hosts Show slave status
Rpl\_status NULL
Slave\_open\_temp\_tables 0
Slave\_retried\_transactions 0
Slave\_running OFF
Begin Sorting
Variable Value
Sort\_merge\_passes 4,174
Sort\_range 250 k
Sort\_rows 60 M
Sort\_scan 1,162 k
Begin Tables
Variable Value
Flush (close) all tables Show open tables
Open\_tables 512
Opened\_tables 1,102 k
Table\_locks\_immediate 247 M
Table\_locks\_waited 100 k
Begin
Variable Value
Open\_files 981
Open\_streams 0
---
### Author:  [@januzi](https://forums.percona.com/u/januzi)
#### Post date: [June 24, 2009, 5:58am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/2 "2009-06-24T05:58:45Z")
You should fix Your tables and queries.
Slow\_queries 2,873
Almost 3k of the slow queries (at least 10 seconds of execution)
Handler\_read\_rnd 54 M
The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use keys properly.
Handler\_read\_rnd\_next 1,331 M
The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 24, 2009, 8:50pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/3 "2009-06-24T20:50:32Z")
Yes sir thank you for your help but one problem I dont know how to fix them can you please help me little more.
regards
Lucas
I got the cache size larger and play some I dont event know what I did but i got this
Select\_full\_join 7,745
Select\_full\_range\_join 25
Select\_range 16 k T
Select\_range\_check 80
Handler\_read\_rnd 2,000 k
---
### Author:  [@sterin71](https://forums.percona.com/u/sterin71)
#### Post date: [June 25, 2009, 2:48am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/4 "2009-06-25T02:48:36Z")
You need to create indexes on your tables.
Especially for your JOIN queries.
Do you know about indexes?
Otherwise read here to get some more information: [http://20bits.com/articles/interview-questions-database-inde](http://20bits.com/articles/interview-questions-database-inde) xes/.
So what you need to do is:
1. Find out which queries that are a problem.
If you have a small site then you probably don’t have that many queries and then you can just search for them in the source code.
If you have a large site then I suggest the MySQL slow-query-log[/url] if you can use it for your webhosting company.
And start by looking at which tables that contain a lot of records. Very often on a site you only have a couple of tables that really contains a lot of records, the rest is usually quite small, so focus you energy with indexes for these large tables where they will be put to most use.
-
Check the design of your database.
Either SHOW CREATE TABLE yourTable; or SHOW INDEXES yourTable or using some GUI.
1.
Start with adding indexes for the columns that are part of the JOIN condition.
This is most important because it is causing a lot of load on your database as it looks like.
So if you have a query that look like:
SELECT …FROM yourTableAINNER JOIN yourTableB ON yourTableA.id = yourTableB.parent\_id
Then you should index _both_ yourTable.id and yourTableB.parent\_id.
Example:
ALTER TABLE yourTableA ADD INDEX yourTableA\_ix\_id(id);ALTER TABLE yourTableB ADD INDEX yourTableB\_ix\_parentid(parent\_id);
If you start with this you can at least get rid of the table scans with the joins which very fast puts a huge load on your server.
Then you can start looking at the rest of your queries that are just like “… WHERE x = 3;” and verify that the x column is indexed so that the query does not have to scan the entire table to find the matching rows.
When you have done this you will gain much more speed in your case than any server variables will ever give you.
Good luck!
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 3:02am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/5 "2009-06-25T03:02:31Z")
can i give you login name and the password )
I got lost sorry but I will try i dont want to mess things up
I greatly appreciated for your help.
Luca…
---
### Author:  [@januzi](https://forums.percona.com/u/januzi)
#### Post date: [June 25, 2009, 6:08am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/6 "2009-06-25T06:08:32Z")
Are You aware that this will take time and it may be connected with serious application rebuild ?
Is there some sort of query log or debug ? It would be easier to work on ready queries.
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 6:09am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/7 "2009-06-25T06:09:19Z")
No I am not
---
### Author:  [@januzi](https://forums.percona.com/u/januzi)
#### Post date: [June 25, 2009, 7:59am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/8 "2009-06-25T07:59:00Z")
So, is there a query list or there is php mess like:
$query = "select columns from ".$prefix.“table “.$where.” “.$orderby.” limit “.$start.”,”.$stop ;
$result = mysql\_query( $query ) ;
?
And what type of webpages are there ? Handmade or made by other people (like phpbb, phpnuke, etc) ?
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 7:59am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/9 "2009-06-25T07:59:53Z")
> **[欧美成人在线视频 - 欧美福利 - 欧美福利视频 - 欧美啪啪视频](http://www.mupefun.com)**
>
> 欧美成人在线视频,欧美福利,欧美福利视频,欧美啪啪视频是一款人性化在线直播观看APP。提供ios苹果下载/安卓下载。片源丰富，高潮迭起！
thank you for all your help
lucas
---
### Author:  [@sterin71](https://forums.percona.com/u/sterin71)
#### Post date: [June 25, 2009, 10:40am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/10 "2009-06-25T10:40:00Z")
And by looking at your site I guess that you are running osCommerce, correct?
When I look at the sql code in the install directory I can say that indexes looks to be very scarce in the default database for osCommerce. Basically they only seem to have a primary key and some tables have one more index but that’s all.
And looking at the PHP code then yes Januzi it looks like it’s a php mess. 😉
My suggestion is that you either figure out how to use the slow query log and turn it on to find which queries that are worst or hire someone to optimize it for you.
I don’t think that you at this stage need to rewrite any php code, but I do think that you will have to create quite a few indexes in your database to solve your problem.
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 10:41am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/11 "2009-06-25T10:41:26Z")
so how am i gonna do that )
sorry
Lucas
---
### Author:  [@sterin71](https://forums.percona.com/u/sterin71)
#### Post date: [June 25, 2009, 10:54am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/12 "2009-06-25T10:54:47Z")
What about:
[http://www.google.se/search?q=mysql+optimization+consultant&](http://www.google.se/search?q=mysql+optimization+consultant&) amp;ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US :official&client=firefox-a 😉
I googled a bit more for you, read this article:
[URL=“http://[Speed / Performance Optimizations - General Discussions - osCommerce Community Forum](http://forums.oscommerce.com/index.php?showtopic=144736)”][Forums - osCommerce Community Forum](http://forums.oscommerce.com/index.php?showtopic=144736%5B/URL%5D)
Then you can apparently install this plugin if you don’t want to use the mysql slow query log:
[URL=“http://[www.oscommerce.com/community/contributions,2575](http://www.oscommerce.com/community/contributions,2575)”][http://www.oscommerce.com/community/contributions,2575[/URL]](http://www.oscommerce.com/community/contributions,2575%5B/URL%5D)
I would have been glad to help you but I’m going on vacation in less than 36 hours and I can’t really squeeze in the 4-8 hours I think will take to get it somewhat under control.
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 10:59am UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/13 "2009-06-25T10:59:28Z")
Thank you I greatly appreciated
Lucas
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 12:39pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/14 "2009-06-25T12:39:37Z")
Okay I did the easiest way and I install the contribution will you please check the botton.
thank you
I don’t know what to do with that I am sorry.
I attached a file if you can please take a look
Regards
Luca
---
### Author:  [@januzi](https://forums.percona.com/u/januzi)
#### Post date: [June 25, 2009, 1:39pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/15 "2009-06-25T13:39:46Z")
Those are queries only from one display of the front page ?
Disable products counter. Oscommerce is running query for every category, the more categories and subcategories the worst situation. (There was other solution at the oscommerce forums, maybe You’ll find it)
As for tax rate, there is only one tax rate ? I changed friends shop so the query “select sum(tax\_rate) as tax\_rate from tax\_rates …” is running only once at the begin of the script. 100 products = 1 tax query
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 1:53pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/16 "2009-06-25T13:53:46Z")
where else the queries is gonna be ?
Thank you
Lucas
how can i disable the products counter?
---
### Author:  [@januzi](https://forums.percona.com/u/januzi)
#### Post date: [June 25, 2009, 2:16pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/17 "2009-06-25T14:16:32Z")
Configuration (Your shop), 4th option from the bottom (“show categories amount” or something like that)
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 2:28pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/18 "2009-06-25T14:28:16Z")
okay the number that shows sorry
i got that and I deleted all the taxes I dont do taxes
thank you
---
### Author:  [@januzi](https://forums.percona.com/u/januzi)
#### Post date: [June 25, 2009, 2:31pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/19 "2009-06-25T14:31:02Z")
Create second query log and post it.
---
### Author:  [@murtim](https://forums.percona.com/u/murtim)
#### Post date: [June 25, 2009, 2:47pm UTC](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196/20 "2009-06-25T14:47:00Z")
so what is the different now ? )
now only 136 query
[Next page](https://forums.percona.com/t/please-help-what-is-wrong-with-the-setting/1196.md?page=2)